Top developer's mistakes when working with PostgreSQL

Slides from my talk about most frequent mistakes that developers do when working with PostgreSQL. Highload++ 2018 Moscow, Russia

1. Топ ошибок со стороны разработки при работе с PostgreSQL Алексей Лесовский
2. 2 Кто говорит Системный администратор PostgreSQL DBA в DataEgret
5. Строка 1 Строка 2 Строка 3 Строка 4 0 2 4 6 8 10 12 Столбец 1 Столбец 2 Столбец 3 5 Проблемы есть всегда
6. 6 Откуда берутся проблемы. История 1. Фичи
7. 7 postgresql
8. 8 plpgsql postgresql CTE LEFT JOIN RIGHT JOIN aggregate functions subqueries arrays
9. 9 plproxy pgq jsonb foreign tables procedural languages extensions hstore plpgsql parallel queries postgresql CTE LEFT JOIN RIGHT JOIN LATERAL window functions aggregate functions subqueries arrays enums intarray
10. 10 full text search plproxy pgq postgis jsonb listen/notify partitioning inheritance streaming replication logical replication foreign tables data wrappers procedural languages extensions WAL archiving hstore xml2 plpgsql parallel queries postgresql CTE LEFT JOIN RIGHT JOIN LATERAL window functions aggregate functions subqueries arrays custom types enums intarray
11. 11 full text search plproxy pgq londiste postgis jsonb listen/notify partitioning inheritance streaming replication logical replication foreign tables data wrappers procedural languages extensions pgbouncer pgpool-II patroni stolon repmgr pgbarman pg_basebackup WAL archiving hstore xml2 plpgsql parallel queries postgresql pg_prewarm CTE LEFT JOIN RIGHT JOIN LATERAL window functions aggregate functions subqueries arrays custom types enums intarray
12. 12 full text search plproxy pgq londiste postgis jsonb listen/notify partitioning inheritance streaming replication logical replication foreign tables data wrappers procedural languages extensions pgbouncer pgpool-II patroni stolon repmgr pgbarman pg_basebackup WAL archiving hstore xml2 plpgsql parallel queries postgresql pg_prewarm CTE LEFT JOIN RIGHT JOIN LATERAL window functions aggregate functions subqueries arrays custom types enums intarray tablespaces pgadmin pgBackRest row level security SSL temporary tables serializable savepoints prepared transactions publications subscriptions OLAP analytics audit logging OLTP ltree bloom gist gin dblink uuid cube pgcrypto isn tcn tablefunc citext pg_repack triggers pg_upgrade slony BDR bucardo
14. 14 Откуда берутся проблемы. История 2. Хранение данных
18. 18 Откуда берутся проблемы. История 3. Жизненный цикл
19. 19 Разработка Приложение Новые фичи Девелоперы
20. 20 Инстансы Деплой Конфиги Админы
21. 21 Как итог Дефолтные конфиги Нет времени на тюнинг Работает? Не трожь!
22. 22 Давайте наводить порядок.
23. 23 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация
24. 24 История 4. Новый проект
25. Новый проект 25 Активная разработка Мало трафика Небольшие объемы данных Простые запросы
26. Все работает! 26
27. Но... 27 Приходит трафик База растет Проблемы производительности Ошибки в 4 утра
28. Что не так? 28
29. Чаще всего не хватает 29 Дисков
30. 30 Пример 1.
31. 31 $ du -csh -t 100M /pgdb/9.6/main/* 15G /pgdb/9.6/main/base 58G /pgdb/9.6/main/pg_xlog 72G итого
32. «Давайте удалим pg_xlog!»» 32
34. 34 Пример 2.
35. 35 $ du -csh -t 100M /pgdb/9.6/main/* 70G /pgdb/9.6/main/base 2G /pgdb/9.6/main/pg_xlog 72G итого
36. List of relations Schema | Name | Type | Owner | Size | Description --------+------------------+-------+----------+---------+------------- public | history_log | table | postgres | 61 GB | public | profiling | table | postgres | 785 MB | public | symbols | table | postgres | 580 MB | public | module_deps | table | postgres | 505 MB | 36
37. «Давайте, зачистим всё, что ммм… старше октября!»» 37
38. =# DELETE FROM history_log -# WHERE created_at < "2018-10-01"; DELETE 165517399 Time: 585478.451 ms 38
39. List of relations Schema | Name | Type | Owner | Size | Description --------+------------------+-------+----------+---------+------------- public | history_log | table | postgres | 61 GB | public | profiling | table | postgres | 785 MB | public | symbols | table | postgres | 580 MB | public | module_deps | table | postgres | 505 MB | 39
40. Чаще всего не хватает 40 Дискового пространства
41. Чаще всего не хватает 41 Дискового пространства Дисков
42. SELECT MAX(loc_pp.PriceStd) FROM C_BPartner_Location bpl, M_PriceList_Vers WHERE bpl.C_BPartner_Location_ID = p_bp_location_id AND loc_plv.M_PriceList_ID = bpl.M_PriceList_ID AND loc_plv.ValidFrom = (SELECT MAX(plv2.ValidFrom) FROM M_PriceList_Version plv2, M_ProductPrice loc_pp2 WHERE plv2.M_PriceList_ID = bpl.M_PriceList_ID AND plv2.IsActive=? AND plv2.ValidFrom <= NOW() AND loc_pp2.M_Product_ID = loc_pp.M_Product_ID AND loc_pp2.M_PriceList_Version_ID = plv2.M_PriceL AND loc_pp2.IsActive=?) AND loc_pp.M_PriceList_Version_ID = loc_plv.M_PriceList_ AND loc_pp.M_Product_ID = p_product_id 42 Разработчик составил запрос Пример 3.
43. SELECT MAX(loc_pp.PriceStd) FROM C_BPartner_Location bpl, M_PriceList_Vers WHERE bpl.C_BPartner_Location_ID = p_bp_location_id AND loc_plv.M_PriceList_ID = bpl.M_PriceList_ID AND loc_plv.ValidFrom = (SELECT MAX(plv2.ValidFrom) FROM M_PriceList_Version plv2, M_ProductPrice loc_pp2 WHERE plv2.M_PriceList_ID = bpl.M_PriceList_ID AND plv2.IsActive=? AND plv2.ValidFrom <= NOW() AND loc_pp2.M_Product_ID = loc_pp.M_Product_ID AND loc_pp2.M_PriceList_Version_ID = plv2.M_PriceL AND loc_pp2.IsActive=?) AND loc_pp.M_PriceList_Version_ID = loc_plv.M_PriceList_ AND loc_pp.M_Product_ID = p_product_id 43 Вычитывает M таблиц Параллельно в N потоков X тысяч раз в секунду ОРМ Разработчик составил запрос
45. Можно вспомнить про 45 Тормоза фоновых процессов СУБД Накладные расходы от виртуализации Хранилище от китайского noname Дефолтные конфигурации ...
46. Чаще всего не хватает 46 Дискового пространства Дисковой производительности
47. Чаще всего не хватает 47 Дискового пространства Дисковой производительности CPU, RAM, Network -- OK
48. Как быть? 48
49. 49 Мониторинг & Планирование
51. 51 Планирование
52. 52 Планирование SSD не раздумывая
53. 53 Планирование SSD не раздумывая Схема данных
54. 54 =# \d+ beautiful_logs Table "public.beautiful_logs" Column | Type | ------------+-----------------------------+ id | integer | md5 | character varying(255) | data | json | created_at | timestamp without time zone | lessons | integer[] | =# SELECT length(data::text) -# FROM beautiful_logs -# ORDER BY beautiful_logs.id -# DESC LIMIT 1; length --------- 8818942
55. 55 Планирование SSD не раздумывая Схема данных Партиционирование
56. 56 =# DELETE FROM history_log -# WHERE created_at < '2018-10-01'; DELETE 165517399 Time: 585478.451 ms
57. 57 =# DELETE FROM history_log -# WHERE created_at < '2018-10-01'; DELETE 165517399 Time: 585478.451 ms =# DROP TABLE history_log_2018_10; DROP TABLE Time: 4.225 ms
58. 58 Мониторинг
59. 59 Мониторинг Storage -- Latency, Utilization PostgreSQL Highload++ Siberia: PostgreSQL Monitoring https://youtu.be/Hbi2AFhd4nY PostgreSQL Monitoring Wiki https://wiki.postgresql.org/wiki/Monitoring Подключенные клиенты Ошибки Запросы (statemestatements)
60. 60 &
61. 61 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация
62. – Дайте мне адрес базы, дальше я сам. (С) Anonymous developer 62
63. – Дайте мне адрес базы, дальше я сам. (С) Anonymous developer 63 Неинтересно как работает база База в виде строки в конфиге Единственная точка входа в базу Фантазии при написании запросов, дают ошеломляющие эффекты
64. 64 История 5. Немного теории
65. 65 OLTP – транзакции • Быстрые • Короткие • Легкие OLAP – аналитика • Медленные • Долгие • Тяжелые
66. 66 OLTP – транзакции • Быстрые • Короткие • Легкие OLAP – аналитика • Медленные • Долгие • Тяжелые
67. 67 У нас HTAP.
68. 68 Масштабирование Как обстоят дела в PostgreSQL ?
69. Streaming replication 69 Масштабирование
70. Streaming replication 70 Масштабирование
71. Streaming replication 71 Масштабирование
72. Streaming replication 72 Масштабирование
73. Logical publications, subscriptions 73 Масштабирование
74. Logical publications, subscriptions 74 Масштабирование
75. Foreign tables, Declarative Partitioning 75 Масштабирование
76. Foreign tables, Declarative Partitioning 76 Масштабирование
77. Foreign tables, Declarative Partitioning 77 Масштабирование
78. 78 Масштабирование
79. 79 С чего начать?
80. 80 Репликация Чтение с реплик Запись в мастер Не забывайте про аналитиков
81. 81 Репликация Балансировка На стороне приложения DNS Round Robin Keepalived, Haproxy Patroni, DCS
82. И с репликацией бывают нюансы... 82
83. Большой лаг репликации? 83
84. 84 KEEP CALM AND DROP DATABASE
86. 86 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация
87. Idle transactions 87 Снижение производительности Блокировки и дедлоки Источник HTTP 50* Все становится интереснее, когда появляются очереди
88. 88 История 6. Снова теория
89. 89 MVCC – движок СУБД
90. 90 MVCC – движок СУБД Необходим сборщик мусора
91. 91 MVCC – движок СУБД Необходим сборщик мусора Долгие транзакции – источник мусора
92. 92 MVCC – движок СУБД Необходим сборщик мусора Долгие транзакции – источник мусора Дольше транзакции – больше мусора, ниже производительность
93. 93 Давайте сходим во внешний источник Нет обработки ошибок «Человеческий фактор» Откуда они берутся?
94. 94 Давайте сходим во внешний источник Нет обработки ошибок «Человеческий фактор» Что делать? Откуда они берутся? Алерты в мониторинге pg_terminate_backend(pid) Рефакторинг приложения
95. 95 Избегайте долгих транзакций
96. «Надо аккуратно посчитать агрегаты» 96
97. 97 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация
98. 98 Пред-агрегация статистики Рассылка уведомлений Настройка кабинета пользователя … Фоновая обработка событий
99. 99 Самописные очереди Table "queues.background_jobs" Column | Type | -----------------------------+-----------------------------+- id | bigint | created_at | timestamp without time zone | updated_at | timestamp without time zone | run_at | timestamp without time zone | priority | integer | queue | character varying(256) | manager | character varying(256) | n_attempts | integer | last_error | character varying(1024) | error_at | timestamp without time zone | locked_at | timestamp without time zone | locked_by | character varying(256) |
101. 101 Таблицы распухают Растет время обработки Очередь не работает
102. 102 pos:1 avg_time: 24656.41ms query: SELECT COUNT(*) AS count_all, priority AS priority FROM background_jobs WHERE (run_at <= $1 and error_at is NULL) GROUP BY priority pos:2 avg_time: 21241.07ms query: SELECT COUNT(*) FROM background_jobs WHERE (error_at is not NULL) pos:3 avg_time: 19573.19ms query: SELECT COUNT(*) FROM background_jobs WHERE (locked_by is not NULL) pos:4 avg_time: 17396.60ms query: SELECT COUNT(*) AS count_all, queue AS queue FROM background_jobs WHERE (run_at <= '2018-10-20 21:00:00.597019' and error_at is NULL) GROUP BY queue pos:5 avg_time: 46.81ms query: UPDATE background_jobs SET locked_at = $1, locked_by = $2 WHERE id IN (SELECT background_jobs.id FROM background_jobs WHERE ... pos:6 avg_time: 21931.48ms query: UPDATE background_jobs SET locked_by = NULL, locked_at = NULL WHERE background_jobs.locked_by = 'host:6f342155 pid:1'
103. 103 pos:1 avg_time: 24656.41ms query: SELECT COUNT(*) AS count_all, priority AS priority FROM background_jobs WHERE (run_at <= $1 and error_at is NULL) GROUP BY priority pos:2 avg_time: 21241.07ms query: SELECT COUNT(*) FROM background_jobs WHERE (error_at is not NULL) pos:3 avg_time: 19573.19ms query: SELECT COUNT(*) FROM background_jobs WHERE (locked_by is not NULL) pos:4 avg_time: 17396.60ms query: SELECT COUNT(*) AS count_all, queue AS queue FROM background_jobs WHERE (run_at <= '2018-10-20 21:00:00.597019' and error_at is NULL) GROUP BY queue pos:5 avg_time: 46.81ms query: UPDATE background_jobs SET locked_at = $1, locked_by = $2 WHERE id IN (SELECT background_jobs.id FROM background_jobs WHERE ... pos:6 avg_time: 21931.48ms query: UPDATE background_jobs SET locked_by = NULL, locked_at = NULL WHERE background_jobs.locked_by = 'host:6f342155 pid:1'
104. 104 pos:1 avg_time: 24656.41ms query: SELECT COUNT(*) AS count_all, priority AS priority FROM background_jobs WHERE (run_at <= $1 and error_at is NULL) GROUP BY priority pos:2 avg_time: 21241.07ms query: SELECT COUNT(*) FROM background_jobs WHERE (error_at is not NULL) pos:3 avg_time: 19573.19ms query: SELECT COUNT(*) FROM background_jobs WHERE (locked_by is not NULL) pos:4 avg_time: 17396.60ms query: SELECT COUNT(*) AS count_all, queue AS queue FROM background_jobs WHERE (run_at <= '2018-10-20 21:00:00.597019' and error_at is NULL) GROUP BY queue pos:5 avg_time: 46.81ms query: UPDATE background_jobs SET locked_at = $1, locked_by = $2 WHERE id IN (SELECT background_jobs.id FROM background_jobs WHERE ... pos:6 avg_time: 21931.48ms query: UPDATE background_jobs SET locked_by = NULL, locked_at = NULL WHERE background_jobs.locked_by = 'host:6f342155 pid:1'
105. 105 Skytools PgQ
106. 106 Skytools PgQ Мало документации Джедайские техники Mailing-lists Настроил и забыл Дешевле отдельных брокеров
107. 107 Какой вывод?
108. 108 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация
109. 109 Инстансы Деплой Конфиги Админы
110. 110 Деплой Катить миграции Девелоперы
112. Split-brain 112 Auto-failover
113. Split-brain 113 Auto-failover
114. Cascade failover 114 Auto-failover
115. Cascade failover 115 Auto-failover
116. Cascade failover 116 Auto-failover
117. Cascade failover 117 Auto-failover
118. 118 Bash скрипты Ansible Stolon https://github.com/sorintlab/stolon PAF https://github.com/clusterlabs/PAF Patroni https://github.com/zalando/patroni #!/bin/bash
119. 119 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация
120. 120 Kubernetes Docker
121. 121 А что если развернуть базу в Kubernetes...
122. 122 Stateful Нужно где-то хранить Open Source: CEPH, GlusterFS, DRBD, ... Оно не будет быстро Дополнительный саппорт кластерной FS
123. 123 Работает, пока... Размер базы небольшой Нет требований к производительности Не страшно потерять данные
124. 124 Если сильно хочется Local volumes Streaming replication PostgreSQL операторы Zalando https://github.com/zalando-incubator/postgres-operator Crunchy https://github.com/CrunchyData/postgres-operator
126. 126 Итого
127. 127 Планирование и мониторинг • Не жмитесь на SSD • Не пишите в базу всё подряд • Мониторинг нужен даже консалтерам • https://youtu.be/Hbi2AFhd4nY
128. 128 Масштабирование • Разносите нагрузку – Postgres is ready • Streaming replication • Publications, subscriptions • Foreign Tables, Declarative partitioning
129. 129 Приложения и СУБД-транзакции
130. 130 Велосипедостроение • Очереди? Skytools PgQ! • Всё, почти, уже давно придумано
131. 131 Автоматизация и Контейнеризация • Локальные volumes • Потоковая репликация • PostgreSQL операторы • Все очень быстро меняется
132. 132 Спасибо за внимание! Алексей Лесовский alexey.lesovsky@dataegret.com