Troubleshooting PostgreSQL Streaming Replication

Slides from my talk at Highload++ 2017 Moscow, Russia.
http://www.highload.ru/2017/abstracts/2937

1. Отладка и устранение проблем в PostgreSQL Streaming Replication Алексей Лесовский
2. План Немного теории или как работает постгресовая репликация. Troubleshooting tools или что есть у PostgreSQL и сообщества. Troubleshooting cases — симптомы, проблемы, диагностика, решения. Итоги, вопросы — ответы.
3. Зачем всё это? Для лучшего понимания потоковой репликации. Научиться быстро находить и устранять проблемы. https://www.slideshare.net/alexeylesovsky/presentations
4. План Немного теории или как работает постгресовая репликация. Troubleshooting tools или что есть у PostgreSQL и сообщества. Troubleshooting cases — симптомы, проблемы, диагностика, решения. Итоги, вопросы — ответы.
5. Немного теории Write-Ahead Log (XLOG) — история всех изменений в БД. • Бэкенды синхронно пишут все изменения в XLOG; • Либо это делает WAL writer асинхронно. Каталог pg_xlog/ (pg_wal/) в $DATADIR. Потоковая репликация основана на XLOG.
6. Немного теории Write-Ahead Log (XLOG) — история всех изменений в БД. почти;) • Бэкенды синхронно пишут все изменения в XLOG; • Либо это делает WAL writer асинхронно. Каталог pg_xlog/ (pg_wal/) в $DATADIR. Потоковая репликация основана на XLOG.
7. Немного теории WAL Sender process (мастер). WAL Receiver process (реплика). Startup process (реплика).
8. Немного теории WAL Buffers Storage WAL Sender Network WAL Receiver Storage Startup Process
9. План Немного теории или как работает постгресовая репликация. Troubleshooting tools или что есть у PostgreSQL и сообщества. Troubleshooting cases — проблемы, симптомы и диагностика. Итоги, вопросы — ответы.
10. Сторонние инструменты Top (procps). Iostat (sysstat), iotop. Nicstat. pgCenter. Perf.
11. Сторонние инструменты Top (procps) — утилизация CPU , load average, использование mem/swap. Iostat (sysstat), iotop — утилизация хранилища, per-process ввод/вывод. Nicstat — утилизация интерфейсов. pgCenter — статистика по репликации. Perf — подземные стуки.
12. Встроенные средства Системные представления (views). Вспомогательные функции. Утилита pg_waldump (pg_xlogdump).
13. Системные представления • pg_stat_replication, pg_stat_wal_receiver; • pg_stat_databases, pg_stat_databases_conflicts; • pg_stat_activity; • pg_stat_archiver.
14. Вспомогательные функции • pg_current_wal_lsn(), pg_current_xlog_location(); • pg_last_wal_receive_lsn(), pg_last_xlog_receive_location(); • pg_wal_lsn_diff(), pg_xlog_location_diff(); • \df *(wal|xlog|lsn|location)* — psql мета-команда
15. pg_waldump pg_waldump: • Декодирует XLOG в человеко-понятный формат; • Может врать при запущенном постгресе. • pg_waldump -f -p /wal_10 \ $(psql -qAtX -c "select pg_walfile_name(pg_current_wal_lsn())")
16. План Немного теории или как работает постгресовая репликация. Troubleshooting tools или что есть у PostgreSQL и сообщества. Troubleshooting cases — проблемы, симптомы и диагностика. Итоги, вопросы — ответы.
17. Проблемы репликации Лаги репликации. Распухание pg_wal/. Долгие запросы и конфликты при восстановлении. Recovery process: 100% CPU usage.
18. Проблемы репликации Лаги репликации. Распухание pg_wal/. Долгие запросы и конфликты при восстановлении. Recovery process: 100% CPU usage.
19. Лаги репликации Данные между мастером и репликами отличаются.
20. Лаги репликации Как искать? • pg_stat_replication, pg_wal_lsn_dif(); • pg_last_xact_replay_timestamp().
21. pg_stat_replication Column | Type | ------------------+--------------------------+ pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start | timestamp with time zone | backend_xmin | xid | state | text | sent_lsn | pg_lsn | <-- Log Sequence Number — позиция внутри XLOG write_lsn | pg_lsn | flush_lsn | pg_lsn | replay_lsn | pg_lsn | write_lag | interval | <-- Отставание выраженное во времени flush_lag | interval | replay_lag | interval | sync_priority | integer | sync_state | text |
22. Лаги репликации # SELECT client_addr AS client, usename AS user, application_name AS name, state, sync_state AS mode, (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending, (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write, (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay, (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::int / 1024 as total_lag FROM pg_stat_replication; client | user | name | state | mode | pending | write | flush | replay | total_lag ---------+--------+-------------+-----------+-------+---------+-------+-------+--------+----------- 10.6.6.9 | repmgr | walreceiver | streaming | async | 0 | 0 | 0 | 410480 | 410480 10.6.6.7 | repmgr | walreceiver | streaming | async | 0 | 2845 | 95628 | 112552 | 211025 10.6.6.6 | repmgr | walreceiver | streaming | async | 0 | 0 | 3056 | 9496 | 12552 10.6.6.8 | repmgr | walreceiver | streaming | async | 847582 | 0 | 0 | 3056 | 850638
23. Лаги репликации # SELECT client_addr AS client, usename AS user, application_name AS name, state, sync_state AS mode, (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending, (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write, (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay, (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::int / 1024 as total_lag FROM pg_stat_replication; client | user | name | state | mode | pending | write | flush | replay | total_lag ---------+--------+-------------+-----------+-------+---------+-------+-------+--------+----------- 10.6.6.9 | repmgr | walreceiver | streaming | async | 0 | 0 | 0 | 410480 | 410480 10.6.6.7 | repmgr | walreceiver | streaming | async | 0 | 2845 | 95628 | 112552 | 211025 10.6.6.6 | repmgr | walreceiver | streaming | async | 0 | 0 | 3056 | 9496 | 12552 10.6.6.8 | repmgr | walreceiver | streaming | async | 847582 | 0 | 0 | 3056 | 850638
24. Лаги репликации # SELECT client_addr AS client, usename AS user, application_name AS name, state, sync_state AS mode, (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending, <-- сеть? (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write, (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay, (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::int / 1024 as total_lag FROM pg_stat_replication; client | user | name | state | mode | pending | write | flush | replay | total_lag ---------+--------+-------------+-----------+-------+---------+-------+-------+--------+----------- 10.6.6.9 | repmgr | walreceiver | streaming | async | 0 | 0 | 0 | 410480 | 410480 10.6.6.7 | repmgr | walreceiver | streaming | async | 0 | 2845 | 95628 | 112552 | 211025 10.6.6.6 | repmgr | walreceiver | streaming | async | 0 | 0 | 3056 | 9496 | 12552 10.6.6.8 | repmgr | walreceiver | streaming | async | 847582 | 0 | 0 | 3056 | 850638
25. Лаги репликации # SELECT client_addr AS client, usename AS user, application_name AS name, state, sync_state AS mode, (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending, (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write, <-- диски? (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay, (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::int / 1024 as total_lag FROM pg_stat_replication; client | user | name | state | mode | pending | write | flush | replay | total_lag ---------+--------+-------------+-----------+-------+---------+-------+-------+--------+----------- 10.6.6.9 | repmgr | walreceiver | streaming | async | 0 | 0 | 0 | 410480 | 410480 10.6.6.7 | repmgr | walreceiver | streaming | async | 0 | 2845 | 95628 | 112552 | 211025 10.6.6.6 | repmgr | walreceiver | streaming | async | 0 | 0 | 3056 | 9496 | 12552 10.6.6.8 | repmgr | walreceiver | streaming | async | 847582 | 0 | 0 | 3056 | 850638
26. Лаги репликации # SELECT client_addr AS client, usename AS user, application_name AS name, state, sync_state AS mode, (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending, (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write, (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush, <-- диски? (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay, (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::int / 1024 as total_lag FROM pg_stat_replication; client | user | name | state | mode | pending | write | flush | replay | total_lag ---------+--------+-------------+-----------+-------+---------+-------+-------+--------+----------- 10.6.6.9 | repmgr | walreceiver | streaming | async | 0 | 0 | 0 | 410480 | 410480 10.6.6.7 | repmgr | walreceiver | streaming | async | 0 | 2845 | 95628 | 112552 | 211025 10.6.6.6 | repmgr | walreceiver | streaming | async | 0 | 0 | 3056 | 9496 | 12552 10.6.6.8 | repmgr | walreceiver | streaming | async | 847582 | 0 | 0 | 3056 | 850638
27. Лаги репликации # SELECT client_addr AS client, usename AS user, application_name AS name, state, sync_state AS mode, (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending, (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write, (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay, <-- диски/CPU? (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::int / 1024 as total_lag FROM pg_stat_replication; client | user | name | state | mode | pending | write | flush | replay | total_lag ---------+--------+-------------+-----------+-------+---------+-------+-------+--------+----------- 10.6.6.9 | repmgr | walreceiver | streaming | async | 0 | 0 | 0 | 410480 | 410480 10.6.6.7 | repmgr | walreceiver | streaming | async | 0 | 2845 | 95628 | 112552 | 211025 10.6.6.6 | repmgr | walreceiver | streaming | async | 0 | 0 | 3056 | 9496 | 12552 10.6.6.8 | repmgr | walreceiver | streaming | async | 847582 | 0 | 0 | 3056 | 850638
28. Лаги репликации # SELECT client_addr AS client, usename AS user, application_name AS name, state, sync_state AS mode, (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending, (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write, (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay, (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::int / 1024 as total_lag FROM pg_stat_replication; client | user | name | state | mode | pending | write | flush | replay | total_lag ---------+--------+-------------+-----------+-------+---------+-------+-------+--------+----------- 10.6.6.9 | repmgr | walreceiver | streaming | async | 0 | 0 | 0 | 410480 | 410480 10.6.6.7 | repmgr | walreceiver | streaming | async | 0 | 2845 | 95628 | 112552 | 211025 10.6.6.6 | repmgr | walreceiver | streaming | async | 0 | 0 | 3056 | 9496 | 12552 10.6.6.8 | repmgr | walreceiver | streaming | async | 847582 | 0 | 0 | 3056 | 850638
29. Проверка гипотезы Сетевой лаг — nicstat. Проблемы в хранилище — iostat, iotop. Задержки восстановления — top, pg_stat_activity. Большой объем WAL: • pg_stat_activity, pg_stat_progress_vacuum; • pg_wal_lsn_diff().
30. Варианты решения Проблемы на уровне сети/хранения: • Проверить workload — запросы, миграции, CRUD. • upgrade hardware?
31. Варианты решения Задержки восстановления: • Стрелять долгие запросы на реплике; • Либо просто ждать.
32. Варианты решения Большой объем WAL: • Уменьшить объем «изменений» в БД в единицу времени; • Уменьшить объем записи в WAL в целом: • full_page_writes = of; • Увеличить интервал между чекпоинтами.
33. Проблемы репликации Лаги репликации. Распухание pg_wal/. Долгие запросы и конфликты при восстановлении. Recovery process: 100% CPU usage.
34. Распухание pg_wal/ Основные симптомы: • Непредсказуемый рост использования дискового пространства; • Ненормальный размер pg_wal/ каталога.
35. Распухание pg_wal/ Как обнаружить? • du -csh; • pg_replication_slots, pg_stat_archiver; • Ошибки в postgres'овых логах.
36. Распухание pg_wal/ Варианты проблем: • Тяжелый CRUD. • Забытый или неиспользуемый слот репликации. • Сломанная archive_command.
37. Распухание pg_wal/ Экстренные меры (100% used space) • Отстрелить долгие CRUD запросы — pg_terminate_backend(); • Уменьшить reserved space ratio (ext filesystems); • Добавить еще места (LVM, ZFS, etc);
38. Распухание pg_wal/ Экстренные меры (100% used space) • Отстрелить долгие CRUD запросы — pg_terminate_backend(); • Уменьшить reserved space ratio (ext filesystems); • Добавить еще места (LVM, ZFS, etc); • НИКОГДА НИЧЕГО НЕ УДАЛЯТЬ РУКАМИ ИЗ pg_xlog/, pg_wal/
39. Распухание pg_wal/ Что делать дальше: • Снова проверить workload — CRUD. • Состояние репликации. • Уменьшить checkpoints_segments/max_wal_size, wal_keep_segments; • Удалить слот репликации или починить подписчика; • Починить WAL archiving; checkpoint, checkpoint, cheсkpoint...
40. Проблемы репликации Лаги репликации. Распухание pg_wal/. Долгие запросы и конфликты при восстановлении. Recovery process: 100% CPU usage.
41. Конфликты восстановления Основные симптомы — ошибки в логах постгреса или приложения. • User was holding shared bufer pin for too long. • User query might have needed to see row versions that must be removed. • User was holding a relation lock for too long. • User was or might have been using tablespace that must be dropped. • User transaction caused bufer deadlock with recovery. • User was connected to a database that must be dropped.
42. Проблемы репликации Как обнаружить: • pg_stat_databases, pg_stat_databases_conflicts; • postgresql logs.
43. Проблемы репликации Когда это действительно становится проблемой: • Отмена запросов происходит слишком часто; • Большой лаг репликации.
44. Проблемы репликации Решения: • Увеличить max_standby_streaming_delay (риск лага репликации); • Включить hot_standby_feedback (риск распухания таблиц/индексов); • Переписать долгие запросы; • Настроить выделенную реплику для долгих запросов.
45. Проблемы репликации Лаги репликации. Распухание pg_wal/. Долгие запросы и конфликты при восстановлении. Recovery process: 100% CPU usage.
46. Задержка восстановления Основные симптомы: • Значительный «replay» лаг; • 100% утилизация CPU процессом recovery.
47. Задержка восстановления Как обнаружить? • top — CPU usage; • pg_stat_replication — replay лаг.
48. Задержка восстановления Что и как искать: • perf top/record/report (требуются debug–пакеты); • GDB; • pg_waldump.
49. Задержка восстановления Решения: • Зависят от результатов расследования; • Устранение проблемного workload (как правило).
50. План Немного теории или как работает постгресовая репликация. Troubleshooting tools или что есть у PostgreSQL и сообщества. Troubleshooting cases — проблемы, симптомы и диагностика. Итоги, вопросы — ответы.
51. Итоги Проблемы потоковой репликации всегда распределены между хостами Источниками проблем выступают: • Недостаток ресурсов, запросы, workload. Без мониторинга никак. Встроенные средства нужно знать и уметь.
52. Links PostgreSQL official documentation – The Statistics Collector https://www.postgresql.org/docs/current/static/monitoring-stats.html PostgreSQL Mailing Lists (general, performance, hackers) https://www.postgresql.org/list/ PostgreSQL-Consulting company blog http://blog.postgresql-consulting.com Эти слайды: https://www.slideshare.net/alexeylesovsky/presentations
53. Спасибо за внимание! dataegret.com alexey.lesovsky@dataegret.com