Downloaded 16 times
![Stats collector и Logging03 dataegret.com Статистика и логирование: ● track_commit_timestamp = on ● log_checkpoints = on ● log_line_prefix = '%m %p %u@%d from %h [vxid:%v txid:%x] [%i] ' ● log_lock_waits = on ● log_temp_files = 0 ● log_min_duration_statement = 1000](https://image.slidesharecdn.com/copao-p3-43-170706184901/75/Call-of-Postgres-Advanced-Operations-part-3-60-2048.jpg)
Uploaded byAlexey Lesovsky
Call of Postgres: Advanced Operations (part 3)
Документ посвящен углубленному анализу работы PostgreSQL, включая аспекты использования памяти, логирования и репликации. Рассматриваются важные компоненты, такие как postmaster, уровень MVCC, механизмы autovacuum, а также настройки консистентности и производительности. Документ также содержит рекомендации по оптимизации работы с соединениями и управлению базами данных, основанные на различных сценариях использования.
Call of Postgres: Advanced Operations (part 3)
- 1. Call of Postgres: AdvancedOperations Alexey Lesovsky lesovsky@gmail.com
- 2.
- 3.
- 4. План03 dataegret.com Postmaster и клиенты. Postgresmemory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.
- 5. Postmaster03 dataegret.com * postmaster.c * Thisprogram acts as a clearing house for requests to the * POSTGRES system. Frontend programs send a startup message * to the Postmaster and the postmaster uses the info in the * message to setup a backend process.
- 6. Postmaster03 dataegret.com Инициализирует shared buffers. Работаетв бесконечном цикле, где: ● Принимает подключения, запускает клиентские процессы. ● Отслеживает работу фоновых процессов. ● Завершает всех при выключении.
- 7.
- 8. Postmaster03 dataegret.com Каждый клиент =отдельный процесс. Много клиентов (n_clients > n_core * 10) – плохо. Много активных клиентов (n_clients > n_core) – плохо. Почему? ● (относительно) Долгая инициализация бэкенда. ● Нагрузка на планировщик ОС. ● Конкуренция за (аппаратные) ресурсы.
- 9. Процессная модель03 dataegret.com Postgres opensdedicated backend for each client’s connection. Process 2 Process 1 . Process 3 . . Port 5432 Process 4 . . . . . . . . . Clients .. . . . . . . . . . . Backend 4 Backend 3 Backend 2 Backend 1 Client connections Postgres Backends
- 10. Postmaster03 dataegret.com Общая рекомендация –использовать pgbouncer. Предпочтительный режим – transaction. Ограничения: ● Prepared statements. ● SET/RESET. ● https://wiki.postgresql.org/wiki/PgBouncer#Feature_matrix_for_pooling_modes Настройки планировщика ОС.
- 11. Pgbouncer03 dataegret.com Pgbouncer keeps onlyactive connections and reuses idle ones. Process 2 Process 1 . Process 3 . . Port 5432 Process 4 . . . . . . . . . Clients . . . . Backend 2 Backend 1 Idle Postgres Backends Port 6432 Free Slot Active Pgbouncer Client connections Server connections
- 12. Postgres Memory иввод-вывод03 dataegret.com Преимущества: ● Не тратится время на инициализацию бэкенда. ● Рестарт PG без отключения клиентов. ● Подмена PG без отключения клиентов.
- 13. Postgres Memory иввод-вывод03 dataegret.com Postmaster и клиенты. Postgres memory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.
- 14. Postgres Memory иввод-вывод03 dataegret.com Локальная (local) память. Общая (shared) память.
- 15. Postgres Memory иввод-вывод03 dataegret.com Локальная (local) память: ● Temp buffers – буфер для временные таблицы. ● Work mem – ORDER BY, DISTINCT, hash/merge JOIN. ● Maintenance/Autovacuum work mem – VACUUM, REINDEX. Выделяется при необходимости, не затрагивая shared память.
- 16. Postgres Memory иввод-вывод03 dataegret.com Общая (shared) память: ● Buffer pool – обработка данных. ● WAL buffer – буфер журналов транзакций. ● Commit Log – статусы транзакций. ● Прочие структуры – фоновые процессы, локи и пр. Инициализируется при старте сервера.
- 17. Buffer Manager03 dataegret.com Backends accessdata through the buffer manager. Backend 2 Backend 1 Buffer Table Buffer Manager Buffer Pool Storage 8K page
- 18. Postgres Memory иввод-вывод03 dataegret.com Рекомендации: ● shared_buffers – 25% или 75% от объема RAM. ● Использовать HugePages при shared_buffers > 32GB ● До 9.2 – libhugetlbfs. ● 9.3 – исключение. ● С 9.4 – встроенная поддержка (huge_pages). ● Не стесняться поднимать maintenance/autovacuum_work_mem.
- 19. Write-Ahead Log03 dataegret.com Postmaster иклиенты. Postgres memory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.
- 20. Write-Ahead Log03 dataegret.com Write-Ahead Log: ● Страховкаот потери данных в случае аварий. ● Аварии связанные с hardware, OS, PostgreSQL. ● Все изменения (почти) фиксируются в WAL.
- 21. Write-Ahead Log03 dataegret.com Чуть-чуть очевидныхвещей: ● WAL пишется бэкендами, либо асинхронно WAL Writer'ом. ● WAL в хранится в файлах-сегментах по 16MB каждый. ● WAL хранится в каталоге pg_xlog/ или pg_wal/. ● Рекомендуется держать на отдельном диске (например с ОС).
- 22. Write-Ahead Log03 dataegret.com Потенциальные проблемы: ● Расходместа на диске. ● Производительность на запись – долгие COMMIT'ы. ● Потеря COMMIT'ов в случае аварии при асинхронной записи WAL.
- 23. Write-Ahead Log03 dataegret.com Что естьпокрутить? ● wal_level – всегда или replica или logical. ● synchronous_commit = ? ● wal_writer_delay – окно потери транзакций. ● wal_writer_flush_after.
- 24. Write-Ahead Log03 dataegret.com Что естьпокрутить? – продолжение. ● full_page_writes – снижает риск повреждения данных. ● wal_compression – снижает объем записи WAL ценой CPU usage. ● wal_log_hints – требуется для pg_rewind.
- 25. Write-Ahead Log03 dataegret.com test* table/indexsize, MB WAL size, MB WAL size, % minimal 256/43 922 0 replica 256/43 922 0.01 logical 256/43 924 0.2 logical (compressed) 256/43 672 -27 * pgbench -i -s 20; update pgbench_accounts set abalance = 1000;
- 26. MVCC и Autovacuum03 dataegret.com Postmasterи клиенты. Postgres memory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.
- 27. MVCC и Autovacuum03 dataegret.com MVCC(Multi-Version Concurrency Control): ● Хорошая производительность при конкурентном доступе. ● При высокой активности на чтение/запись. ● Читатели не блокируют читателей, Писатели не блокируют писателей. ● Почти...
- 28. MVCC и Autovacuum03 dataegret.com transactionid timeline xid 1000 snapshot ? unfinished transaction finished transaction ? ? ?
- 29. MVCC и Autovacuum03 dataegret.com Created:123 Deleted: Created: 123 Deleted: 456 Created: 456 Deleted: Created: 456 Deleted: 789 Вставка (INSERT) строки транзакцией №123 Обновление (UPDATE) строки транзакцией №456 Удаление (DELETE) строки транзакцией №789
- 30. MVCC и Autovacuum03 dataegret.com Уровниизоляции транзакций. Правила проверки видимости работают со служебными полями. Побочный эффект от MVCC – наличие устаревших версий строк. Задача вакуума – убирать устаревшие версии строк.
- 31. Autovacuum03 dataegret.com AV Launcher AVWorker Postmaster Initialization Shared memory Initialization Scan pg_class Check relations Do vacuum Recheck relations Process relations Vacuum/Analyze/Wrap ? Balance costs Process 1 relation Open relation Perform checks Set Xid limits Open indexes Prune HOT chains Freeze tuples Remove idx entries Vacuum heap Vacuum pages Update FSM, VM Close indexes Truncate relation Update FSMUpdate pg_class Close relation Vacuum TOAST ? Postgres Backend FREEZE ? 1 2 1 2 1 1 1 2 yes FULL ? CLUSTER yes no https://www.slideshare.net/alexeylesovsky/nine-circles-of-inferno-or-explaining-the-postgresql-vacuum
- 32. Autovacuum03 dataegret.com Общий взгляд наautovacuum и ключевые моменты работы. Подходы в настройке autovacuum. Потенциальные проблемы.
- 33. Autovacuum – общийвзгляд03 dataegret.com Postmaster запускает и AV Launcher, и воркеров. AV Launcher решает какую базу вакуумить. Воркер запускается против базы и строит список таблиц для вакуума.
- 34. Autovacuum – ключевыемоменты03 dataegret.com Как выбирается таблица? ● На основе reltuples, n_dead_tuples, changes_since_analyze. ● thresh = base_thresh + scale_factor * reltuples
- 35. Autovacuum – ключевыемоменты03 dataegret.com Как регулируется нагрузка создаваемая вакуумом: ● За обработку страницы начисляются очки. ● При достижении лимита очков, вакуум ставится на паузу. ● После паузы вакуум продолжается, набранные очки сбрасываются. ● Лимит очков делится поровну на всех активных воркеров.
- 36. Autovacuum – ключевыемоменты03 dataegret.com Как регулируется нагрузка создаваемая вакуумом: ● За обработку страницы начисляются очки. ● При достижении лимита очков, вакуум ставится на паузу. ● После паузы вакуум продолжается, набранные очки сбрасываются. ● Лимит очков делится поровну на всех активных воркеров. А что если покрутить величину паузы и размер лимита?
- 37. Autovacuum – ключевыемоменты03 dataegret.com Antiwraparound vacuum: ● Предотвращает потерю данных. ● Довольно тяжелый до версии 9.6.
- 38.
- 39. Anti-wraparound vacuum03 dataegret.com recentXid –текущая транзакция. vacuum_freeze_min_age – строки с возрастом старше должны быть заморожены. vacuum_freeze_table_age – полное сканирование, если достигнут возраст. autovacuum_freeze_max_age – возраст принудительного запуска wraparound- вакуума.
- 40. Autovacuum03 dataegret.com Принципы настройки: ● Дефолтные настройки. ● Агрессивный/ленивыйвакуум. ● Индивидуальные настройки таблиц.
- 41. Autovacuum03 dataegret.com Агрессивный вакуум, подходчерез cost-параметры: autovacuum_vacuum_cost_delay = 10ms – по-умолчанию 20ms vacuum_cost_page_hit = 1 – по-умолчанию 1 vacuum_cost_page_miss = 2 – по-умолчанию 10 vacuum_cost_page_dirty = 5 – по-умолчанию 20 autovacuum_vacuum_cost_limit = 1000 – по-умолчанию -1 Предполагаемая нагрузка: 80MB/s чтение, 16MB/s запись.
- 42. Autovacuum03 dataegret.com Агрессивный вакуум, подходчерез threshold: autovacuum_max_workers = 10 – по-умолчанию 3 autovacuum_naptime = 1s – по-умолчанию 1min autovacuum_vacuum_threshold = 50 – по-умолчанию 50 autovacuum_analyze_threshold = 50 – по-умолчанию 50 autovacuum_vacuum_scale_factor = 0.05 – по-умолчанию 0.2 autovacuum_analyze_scale_factor = 0.05 – по-умолчанию 0.1 Вакуумить чаще и меньшими порциями.
- 43. Autovacuum03 dataegret.com Потенциальные проблемы: ● Нагрузка наstorage – пересмотреть cost_delay, cost_limit. ● Большой объем генерируемого WAL (+лаг репликации). ● Затяжной вакуум (особенно при дефолтах) + bloat таблиц. ● Риск wraparound, аварийное выключение.
- 44. Autovacuum03 dataegret.com Мониторинг: ● pg_stat_progress_vacuum (c 9.6). ● pg_stat_activity. ● Логи(log_autovacuum_min_duration). ● Iotop, pidstat.
- 45. План03 dataegret.com Postmaster и клиенты. Postgresmemory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.
- 46. Checkpoint'ы и Bgwriter03 dataegret.com Dirtypages Shared buffers Write-out Permanent storage Backend 1 Backend 2
- 47. Checkpoint'ы и Bgwriter03 dataegret.com Checkpointerprocess – cоздает контрольные точки: ● Сбрасывает «грязные» страницы в момент создания КТ. ● Обновляет pg_control после КТ. Background Writer – сбрасывает «грязные» страницы постоянно.
- 48. Checkpoint'ы и Bgwriter03 dataegret.com Особенностичекпоинтов: ● Выполняются с интервалом. ● Выполняются при выключении постгреса – важно при рестарте. ● Риск нагрузки на storage.
- 49. Checkpoint'ы и Bgwriter03 dataegret.com Конфигурированиечекпоинтов: ● Что легче – записать 64GB в 5 минут или 60 минут? ● Главная мысль – разумно растянуть чекпоинт по времени: ● max_wal_size = ⅒ от размера инстанса – по-умолчанию 1GB ● checkpoint_timeout = 1h – по-умолчанию 5min ● checkpoint_completion_target = 0.9 – по-умолчанию 0.5 ● full_page_writes – быть или не быть? – по-умолчанию «on»
- 50. Checkpoint'ы и Bgwriter03 dataegret.com КонфигурированиеBgwriter: ● Главная мысль – максимум агрессивности: ● bgwriter_delay = 10ms – по-умолчанию 200ms ● bgwriter_lru_maxpages = 1000 – по-умолчанию 100 ● bgwriter_lru_multiplier = 10.0 – по-умолчанию 2.0
- 51. Checkpoint'ы и Bgwriter03 dataegret.com Особенностиэксплуатации: ● Достаточно настроить один раз. ● Мониторинг – pg_stat_bgwriter, постгресовый лог.
- 52. План03 dataegret.com Postmaster и клиенты. Postgresmemory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.
- 53. Потоковая репликация03 dataegret.com Потоковая репликация: ● Работаетза счет передачи и воспроизведения WAL. ● Есть и физическая (с 9.0) и логическая (с 10.0). ● Легко настраивается, простая и надежная. Ограничения и недостатки: ● Работает в 1 поток.
- 54. Потоковая репликация03 dataegret.com Мастер Реплика 5.Получение стартовой позиции 6. Проверка наличия журнала 7. Запуск WAL sender, отправка журнала 11. Обновление статистики 1. Проверка источника XLOG 2. Запуск WAL receiver 3. Вычисление стартовой позиции 4. Подключение к мастеру, отправка позиции 8. Запись журнала на диск 9. Обновление «отметки» 10. Отправка статистики 11. Воспроизведение журнала Начальная фаза Цикл репликации
- 55. Потоковая репликация03 dataegret.com Какие бываютпроблемы: ● Лаг репликации: ● Network/Storage/CPU/Stuck consumer. ● Конфликты репликации. ● Bloat таблиц/индексов.
- 56. Потоковая репликация03 dataegret.com Особенности настройки: ● max_wal_senders– по-умолчанию 0 ● wal_keep_segments – по-умолчанию 0 ● wal_sender_timeout – по-умолчанию 60s ● wal_receiver_timeout – по-умолчанию 60s ● track_commit_timestamp = on – по-умолчанию «off» ● hot_standby = on – по-умолчанию «off» ● hot_standby_feedback – по-умолчанию «off» ● max_standby_streaming_delay – по-умолчанию 30s
- 57. Потоковая репликация03 dataegret.com Особенности эксплуатации: ● Мониторинг:pg_stat_replication, pg_replication_slots, функции(). ● pg_xlogdump – анализ WAL журнала. ● Top, Iostat, Nicstat.
- 58. План03 dataegret.com Postmaster и клиенты. Postgresmemory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.
- 59. Stats collector иLogging03 dataegret.com Трекинг событий: ● DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, PANIC. Мониторинг активности: ● Системные функции и представления. ● Extension'ы.
- 60. Stats collector иLogging03 dataegret.com Статистика и логирование: ● track_commit_timestamp = on ● log_checkpoints = on ● log_line_prefix = '%m %p %u@%d from %h [vxid:%v txid:%x] [%i] ' ● log_lock_waits = on ● log_temp_files = 0 ● log_min_duration_statement = 1000
- 61. Stats collector иLogging03 dataegret.com Статистика и логирование – продолжение: ● track_counts = on ● track_activities = on ● track_io_timing = on ● track_functions = pl ● track_activity_query_size = 8192 ● log_autovacuum_min_duration = 10000
- 62.