Top developer's mistakes when working with PostgreSQL

Slide 1

Slide 1 text

Топ ошибок со стороны разработки при работе с PostgreSQL Алексей Лесовский

Slide 2

Slide 2 text

2 Кто говорит Системный администратор PostgreSQL DBA в DataEgret

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Строка 1 Строка 2 Строка 3 Строка 4 0 2 4 6 8 10 12 Столбец 1 Столбец 2 Столбец 3 5 Проблемы есть всегда

Slide 6

Slide 6 text

6 Откуда берутся проблемы. История 1. Фичи

Slide 7

Slide 7 text

7 postgresql

Slide 8

Slide 8 text

8 plpgsql postgresql CTE LEFT JOIN RIGHT JOIN aggregate functions subqueries arrays

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Slide 14

Slide 14 text

14 Откуда берутся проблемы. История 2. Хранение данных

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

18 Откуда берутся проблемы. История 3. Жизненный цикл

Slide 19

Slide 19 text

19 Разработка Приложение Новые фичи Девелоперы

Slide 20

Slide 20 text

20 Инстансы Деплой Конфиги Админы

Slide 21

Slide 21 text

21 Как итог Дефолтные конфиги Нет времени на тюнинг Работает? Не трожь!

Slide 22

Slide 22 text

22 Давайте наводить порядок.

Slide 23

Slide 23 text

23 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация

Slide 24

Slide 24 text

24 История 4. Новый проект

Slide 25

Slide 25 text

Новый проект 25 Активная разработка Мало трафика Небольшие объемы данных Простые запросы

Slide 26

Slide 26 text

Все работает! 26

Slide 27

Slide 27 text

Но... 27 Приходит трафик База растет Проблемы производительности Ошибки в 4 утра

Slide 28

Slide 28 text

Что не так? 28

Slide 29

Slide 29 text

Чаще всего не хватает 29 Дисков

Slide 30

Slide 30 text

30 Пример 1.

Slide 31

Slide 31 text

31 $ du -csh -t 100M /pgdb/9.6/main/* 15G /pgdb/9.6/main/base 58G /pgdb/9.6/main/pg_xlog 72G итого

Slide 32

Slide 32 text

«Давайте удалим pg_xlog!»» 32

Slide 33

Slide 33 text

Slide 34

Slide 34 text

34 Пример 2.

Slide 35

Slide 35 text

35 $ du -csh -t 100M /pgdb/9.6/main/* 70G /pgdb/9.6/main/base 2G /pgdb/9.6/main/pg_xlog 72G итого

Slide 36

Slide 36 text

Slide 37

Slide 37 text

«Давайте, зачистим всё, что ммм… старше октября!»» 37

Slide 38

Slide 38 text

=# DELETE FROM history_log -# WHERE created_at < "2018-10-01"; DELETE 165517399 Time: 585478.451 ms 38

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Чаще всего не хватает 40 Дискового пространства

Slide 41

Slide 41 text

Чаще всего не хватает 41 Дискового пространства Дисков

Slide 42

Slide 42 text

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.

Slide 43

Slide 43 text

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 тысяч раз в секунду ОРМ Разработчик составил запрос

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Можно вспомнить про 45 Тормоза фоновых процессов СУБД Накладные расходы от виртуализации Хранилище от китайского noname Дефолтные конфигурации ...

Slide 46

Slide 46 text

Чаще всего не хватает 46 Дискового пространства Дисковой производительности

Slide 47

Slide 47 text

Чаще всего не хватает 47 Дискового пространства Дисковой производительности CPU, RAM, Network -- OK

Slide 48

Slide 48 text

Как быть? 48

Slide 49

Slide 49 text

49 Мониторинг & Планирование

Slide 50

Slide 50 text

Slide 51

Slide 51 text

51 Планирование

Slide 52

Slide 52 text

52 Планирование SSD не раздумывая

Slide 53

Slide 53 text

53 Планирование SSD не раздумывая Схема данных

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

55 Планирование SSD не раздумывая Схема данных Партиционирование

Slide 56

Slide 56 text

56 =# DELETE FROM history_log -# WHERE created_at < '2018-10-01'; DELETE 165517399 Time: 585478.451 ms

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

58 Мониторинг

Slide 59

Slide 59 text

59 Мониторинг Storage -- Latency, Utilization PostgreSQL Highload++ Siberia: PostgreSQL Monitoring https://youtu.be/Hbi2AFhd4nY PostgreSQL Monitoring Wiki https://wiki.postgresql.org/wiki/Monitoring Подключенные клиенты Ошибки Запросы (statemestatements)

Slide 60

Slide 60 text

60 &

Slide 61

Slide 61 text

61 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация

Slide 62

Slide 62 text

– Дайте мне адрес базы, дальше я сам. (С) Anonymous developer 62

Slide 63

Slide 63 text

– Дайте мне адрес базы, дальше я сам. (С) Anonymous developer 63 Неинтересно как работает база База в виде строки в конфиге Единственная точка входа в базу Фантазии при написании запросов, дают ошеломляющие эффекты

Slide 64

Slide 64 text

64 История 5. Немного теории

Slide 65

Slide 65 text

65 OLTP – транзакции ● Быстрые ● Короткие ● Легкие OLAP – аналитика ● Медленные ● Долгие ● Тяжелые

Slide 66

Slide 66 text

66 OLTP – транзакции ● Быстрые ● Короткие ● Легкие OLAP – аналитика ● Медленные ● Долгие ● Тяжелые

Slide 67

Slide 67 text

67 У нас HTAP.

Slide 68

Slide 68 text

68 Масштабирование Как обстоят дела в PostgreSQL ?

Slide 69

Slide 69 text

Streaming replication 69 Масштабирование

Slide 70

Slide 70 text

Streaming replication 70 Масштабирование

Slide 71

Slide 71 text

Streaming replication 71 Масштабирование

Slide 72

Slide 72 text

Streaming replication 72 Масштабирование

Slide 73

Slide 73 text

Logical publications, subscriptions 73 Масштабирование

Slide 74

Slide 74 text

Logical publications, subscriptions 74 Масштабирование

Slide 75

Slide 75 text

Foreign tables, Declarative Partitioning 75 Масштабирование

Slide 76

Slide 76 text

Foreign tables, Declarative Partitioning 76 Масштабирование

Slide 77

Slide 77 text

Foreign tables, Declarative Partitioning 77 Масштабирование

Slide 78

Slide 78 text

78 Масштабирование

Slide 79

Slide 79 text

79 С чего начать?

Slide 80

Slide 80 text

80 Репликация Чтение с реплик Запись в мастер Не забывайте про аналитиков

Slide 81

Slide 81 text

81 Репликация Балансировка На стороне приложения DNS Round Robin Keepalived, Haproxy Patroni, DCS

Slide 82

Slide 82 text

И с репликацией бывают нюансы... 82

Slide 83

Slide 83 text

Большой лаг репликации? 83

Slide 84

Slide 84 text

84 KEEP CALM AND DROP DATABASE

Slide 85

Slide 85 text

Slide 86

Slide 86 text

86 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация

Slide 87

Slide 87 text

Idle transactions 87 Снижение производительности Блокировки и дедлоки Источник HTTP 50* Все становится интереснее, когда появляются очереди

Slide 88

Slide 88 text

88 История 6. Снова теория

Slide 89

Slide 89 text

89 MVCC – движок СУБД

Slide 90

Slide 90 text

90 MVCC – движок СУБД Необходим сборщик мусора

Slide 91

Slide 91 text

91 MVCC – движок СУБД Необходим сборщик мусора Долгие транзакции – источник мусора

Slide 92

Slide 92 text

92 MVCC – движок СУБД Необходим сборщик мусора Долгие транзакции – источник мусора Дольше транзакции – больше мусора, ниже производительность

Slide 93

Slide 93 text

93 Давайте сходим во внешний источник Нет обработки ошибок «Человеческий фактор» Откуда они берутся?

Slide 94

Slide 94 text

94 Давайте сходим во внешний источник Нет обработки ошибок «Человеческий фактор» Что делать? Откуда они берутся? Алерты в мониторинге pg_terminate_backend(pid) Рефакторинг приложения

Slide 95

Slide 95 text

95 Избегайте долгих транзакций

Slide 96

Slide 96 text

«Надо аккуратно посчитать агрегаты» 96

Slide 97

Slide 97 text

97 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация

Slide 98

Slide 98 text

98 Пред-агрегация статистики Рассылка уведомлений Настройка кабинета пользователя … Фоновая обработка событий

Slide 99

Slide 99 text

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) |

Slide 100

Slide 100 text

100

Slide 101

Slide 101 text

101 Таблицы распухают Растет время обработки Очередь не работает

Slide 102

Slide 102 text

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'

Slide 103

Slide 103 text

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'

Slide 104

Slide 104 text

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'

Slide 105

Slide 105 text

105 Skytools PgQ

Slide 106

Slide 106 text

106 Skytools PgQ Мало документации Джедайские техники Mailing-lists Настроил и забыл Дешевле отдельных брокеров

Slide 107

Slide 107 text

107 Какой вывод?

Slide 108

Slide 108 text

108 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация

Slide 109

Slide 109 text

109 Инстансы Деплой Конфиги Админы

Slide 110

Slide 110 text

110 Деплой Катить миграции Девелоперы

Slide 111

Slide 111 text

111

Slide 112

Slide 112 text

Split-brain 112 Auto-failover

Slide 113

Slide 113 text

Split-brain 113 Auto-failover

Slide 114

Slide 114 text

Cascade failover 114 Auto-failover

Slide 115

Slide 115 text

Cascade failover 115 Auto-failover

Slide 116

Slide 116 text

Cascade failover 116 Auto-failover

Slide 117

Slide 117 text

Cascade failover 117 Auto-failover

Slide 118

Slide 118 text

118 Bash скрипты Ansible Stolon https://github.com/sorintlab/stolon PAF https://github.com/clusterlabs/PAF Patroni https://github.com/zalando/patroni #!/bin/bash

Slide 119

Slide 119 text

119 Планирование и мониторинг Масштабирование Приложения и СУБД-транзакции Велосипедостроение Автоматизация Контейнеры и Оркестрация

Slide 120

Slide 120 text

120 Kubernetes Docker

Slide 121

Slide 121 text

121 А что если развернуть базу в Kubernetes...

Slide 122

Slide 122 text

122 Stateful Нужно где-то хранить Open Source: CEPH, GlusterFS, DRBD, ... Оно не будет быстро Дополнительный саппорт кластерной FS

Slide 123

Slide 123 text

123 Работает, пока... Размер базы небольшой Нет требований к производительности Не страшно потерять данные

Slide 124

Slide 124 text

124 Если сильно хочется Local volumes Streaming replication PostgreSQL операторы Zalando https://github.com/zalando-incubator/postgres-operator Crunchy https://github.com/CrunchyData/postgres-operator

Slide 125

Slide 125 text

125

Slide 126

Slide 126 text

126 Итого

Slide 127

Slide 127 text

127 Планирование и мониторинг ● Не жмитесь на SSD ● Не пишите в базу всё подряд ● Мониторинг нужен даже консалтерам ● https://youtu.be/Hbi2AFhd4nY

Slide 128

Slide 128 text

128 Масштабирование ● Разносите нагрузку – Postgres is ready ● Streaming replication ● Publications, subscriptions ● Foreign Tables, Declarative partitioning

Slide 129

Slide 129 text

129 Приложения и СУБД-транзакции

Slide 130

Slide 130 text

130 Велосипедостроение ● Очереди? Skytools PgQ! ● Всё, почти, уже давно придумано

Slide 131

Slide 131 text

131 Автоматизация и Контейнеризация ● Локальные volumes ● Потоковая репликация ● PostgreSQL операторы ● Все очень быстро меняется

Slide 132

Slide 132 text

132 Спасибо за внимание! Алексей Лесовский [email protected]