Call of Postgres: Advanced Operations. Part III.

Slide 1

Slide 1 text

Call of Postgres: Advanced Operations Alexey Lesovsky [email protected]

Slide 2

Slide 2 text

PostgreSQL: теория 03

Slide 3

Slide 3 text

Устройство PostgreSQL 03 dataegret.com

Slide 4

Slide 4 text

План 03 dataegret.com Postmaster и клиенты. Postgres memory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.

Slide 5

Slide 5 text

Postmaster 03 dataegret.com * postmaster.c * This program 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.

Slide 6

Slide 6 text

Postmaster 03 dataegret.com Инициализирует shared buffers. Работает в бесконечном цикле, где: ● Принимает подключения, запускает клиентские процессы. ● Отслеживает работу фоновых процессов. ● Завершает всех при выключении.

Slide 7

Slide 7 text

Postmaster 03 dataegret.com Потенциальные проблемы: ● Шквал клиентов.

Slide 8

Slide 8 text

Postmaster 03 dataegret.com Каждый клиент = отдельный процесс. Много клиентов (n_clients > n_core * 10) – плохо. Много активных клиентов (n_clients > n_core) – плохо. Почему? ● (относительно) Долгая инициализация бэкенда. ● Нагрузка на планировщик ОС. ● Конкуренция за (аппаратные) ресурсы.

Slide 9

Slide 9 text

Процессная модель 03 dataegret.com Postgres opens dedicated 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

Slide 10

Slide 10 text

Postmaster 03 dataegret.com Общая рекомендация – использовать pgbouncer. Предпочтительный режим – transaction. Ограничения: ● Prepared statements. ● SET/RESET. ● https://wiki.postgresql.org/wiki/PgBouncer#Feature_matrix_for_pooling_modes Настройки планировщика ОС.

Slide 11

Slide 11 text

Pgbouncer 03 dataegret.com Pgbouncer keeps only active 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

Slide 12

Slide 12 text

Postgres Memory и ввод-вывод 03 dataegret.com Преимущества: ● Не тратится время на инициализацию бэкенда. ● Рестарт PG без отключения клиентов. ● Подмена PG без отключения клиентов.

Slide 13

Slide 13 text

Postgres Memory и ввод-вывод 03 dataegret.com Postmaster и клиенты. Postgres memory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.

Slide 14

Slide 14 text

Postgres Memory и ввод-вывод 03 dataegret.com Локальная (local) память. Общая (shared) память.

Slide 15

Slide 15 text

Postgres Memory и ввод-вывод 03 dataegret.com Локальная (local) память: ● Temp buffers – буфер для временные таблицы. ● Work mem – ORDER BY, DISTINCT, hash/merge JOIN. ● Maintenance/Autovacuum work mem – VACUUM, REINDEX. Выделяется при необходимости, не затрагивая shared память.

Slide 16

Slide 16 text

Postgres Memory и ввод-вывод 03 dataegret.com Общая (shared) память: ● Buffer pool – обработка данных. ● WAL buffer – буфер журналов транзакций. ● Commit Log – статусы транзакций. ● Прочие структуры – фоновые процессы, локи и пр. Инициализируется при старте сервера.

Slide 17

Slide 17 text

Buffer Manager 03 dataegret.com Backends access data through the buffer manager. Backend 2 Backend 1 Buffer Table Buffer Manager Buffer Pool Storage 8K page

Slide 18

Slide 18 text

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.

Slide 19

Slide 19 text

Write-Ahead Log 03 dataegret.com Postmaster и клиенты. Postgres memory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.

Slide 20

Slide 20 text

Write-Ahead Log 03 dataegret.com Write-Ahead Log: ● Страховка от потери данных в случае аварий. ● Аварии связанные с hardware, OS, PostgreSQL. ● Все изменения (почти) фиксируются в WAL.

Slide 21

Slide 21 text

Write-Ahead Log 03 dataegret.com Чуть-чуть очевидных вещей: ● WAL пишется бэкендами, либо асинхронно WAL Writer'ом. ● WAL в хранится в файлах-сегментах по 16MB каждый. ● WAL хранится в каталоге pg_xlog/ или pg_wal/. ● Рекомендуется держать на отдельном диске (например с ОС).

Slide 22

Slide 22 text

Write-Ahead Log 03 dataegret.com Потенциальные проблемы: ● Расход места на диске. ● Производительность на запись – долгие COMMIT'ы. ● Потеря COMMIT'ов в случае аварии при асинхронной записи WAL.

Slide 23

Slide 23 text

Write-Ahead Log 03 dataegret.com Что есть покрутить? ● wal_level – всегда или replica или logical. ● synchronous_commit = ? ● wal_writer_delay – окно потери транзакций. ● wal_writer_flush_after.

Slide 24

Slide 24 text

Write-Ahead Log 03 dataegret.com Что есть покрутить? – продолжение. ● full_page_writes – снижает риск повреждения данных. ● wal_compression – снижает объем записи WAL ценой CPU usage. ● wal_log_hints – требуется для pg_rewind.

Slide 25

Slide 25 text

Write-Ahead Log 03 dataegret.com test* table/index size, 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;

Slide 26

Slide 26 text

MVCC и Autovacuum 03 dataegret.com Postmaster и клиенты. Postgres memory и ввод-вывод. Write-Ahead Log и WAL writer. MVCC и Autovacuum. Checkpoint'ы и Bgwriter. Потоковая репликация. Stats collector и Logger.

Slide 27

Slide 27 text

MVCC и Autovacuum 03 dataegret.com MVCC (Multi-Version Concurrency Control): ● Хорошая производительность при конкурентном доступе. ● При высокой активности на чтение/запись. ● Читатели не блокируют читателей, Писатели не блокируют писателей. ● Почти...

Slide 28

Slide 28 text

MVCC и Autovacuum 03 dataegret.com transaction id timeline xid 1000 snapshot ? unfinished transaction finished transaction ? ? ?

Slide 29

Slide 29 text

MVCC и Autovacuum 03 dataegret.com Created: 123 Deleted: Created: 123 Deleted: 456 Created: 456 Deleted: Created: 456 Deleted: 789 Вставка (INSERT) строки транзакцией №123 Обновление (UPDATE) строки транзакцией №456 Удаление (DELETE) строки транзакцией №789

Slide 30

Slide 30 text

MVCC и Autovacuum 03 dataegret.com Уровни изоляции транзакций. Правила проверки видимости работают со служебными полями. Побочный эффект от MVCC – наличие устаревших версий строк. Задача вакуума – убирать устаревшие версии строк.

Slide 31

Slide 31 text

Autovacuum 03 dataegret.com AV Launcher AV Worker 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 FSM Update 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

Slide 32

Slide 32 text

Autovacuum 03 dataegret.com Общий взгляд на autovacuum и ключевые моменты работы. Подходы в настройке autovacuum. Потенциальные проблемы.

Slide 33

Slide 33 text

Autovacuum – общий взгляд 03 dataegret.com Postmaster запускает и AV Launcher, и воркеров. AV Launcher решает какую базу вакуумить. Воркер запускается против базы и строит список таблиц для вакуума.

Slide 34

Slide 34 text

Autovacuum – ключевые моменты 03 dataegret.com Как выбирается таблица? ● На основе reltuples, n_dead_tuples, changes_since_analyze. ● thresh = base_thresh + scale_factor * reltuples

Slide 35

Slide 35 text

Autovacuum – ключевые моменты 03 dataegret.com Как регулируется нагрузка создаваемая вакуумом: ● За обработку страницы начисляются очки. ● При достижении лимита очков, вакуум ставится на паузу. ● После паузы вакуум продолжается, набранные очки сбрасываются. ● Лимит очков делится поровну на всех активных воркеров.

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Autovacuum – ключевые моменты 03 dataegret.com Antiwraparound vacuum: ● Предотвращает потерю данных. ● Довольно тяжелый до версии 9.6.

Slide 38

Slide 38 text

Anti-wraparound vacuum 03 dataegret.com

Slide 39

Slide 39 text

Anti-wraparound vacuum 03 dataegret.com recentXid – текущая транзакция. vacuum_freeze_min_age – строки с возрастом старше должны быть заморожены. vacuum_freeze_table_age – полное сканирование, если достигнут возраст. autovacuum_freeze_max_age – возраст принудительного запуска wraparound- вакуума.

Slide 40

Slide 40 text

Autovacuum 03 dataegret.com Принципы настройки: ● Дефолтные настройки. ● Агрессивный/ленивый вакуум. ● Индивидуальные настройки таблиц.

Slide 41

Slide 41 text

Autovacuum 03 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 запись.

Slide 42

Slide 42 text

Autovacuum 03 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 Вакуумить чаще и меньшими порциями.

Slide 43

Slide 43 text

Autovacuum 03 dataegret.com Потенциальные проблемы: ● Нагрузка на storage – пересмотреть cost_delay, cost_limit. ● Большой объем генерируемого WAL (+лаг репликации). ● Затяжной вакуум (особенно при дефолтах) + bloat таблиц. ● Риск wraparound, аварийное выключение.

Slide 44

Slide 44 text

Autovacuum 03 dataegret.com Мониторинг: ● pg_stat_progress_vacuum (c 9.6). ● pg_stat_activity. ● Логи (log_autovacuum_min_duration). ● Iotop, pidstat.

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Checkpoint'ы и Bgwriter 03 dataegret.com Dirty pages Shared buffers Write-out Permanent storage Backend 1 Backend 2

Slide 47

Slide 47 text

Checkpoint'ы и Bgwriter 03 dataegret.com Checkpointer process – cоздает контрольные точки: ● Сбрасывает «грязные» страницы в момент создания КТ. ● Обновляет pg_control после КТ. Background Writer – сбрасывает «грязные» страницы постоянно.

Slide 48

Slide 48 text

Checkpoint'ы и Bgwriter 03 dataegret.com Особенности чекпоинтов: ● Выполняются с интервалом. ● Выполняются при выключении постгреса – важно при рестарте. ● Риск нагрузки на storage.

Slide 49

Slide 49 text

Checkpoint'ы и Bgwriter 03 dataegret.com Конфигурирование чекпоинтов: ● Что легче – записать 64GB в 5 минут или 60 минут? ● Главная мысль – разумно растянуть чекпоинт по времени: ● max_wal_size = ⅒ от размера инстанса – по-умолчанию 1GB ● checkpoint_timeout = 1h – по-умолчанию 5min ● checkpoint_completion_target = 0.9 – по-умолчанию 0.5 ● full_page_writes – быть или не быть? – по-умолчанию «on»

Slide 50

Slide 50 text

Checkpoint'ы и Bgwriter 03 dataegret.com Конфигурирование Bgwriter: ● Главная мысль – максимум агрессивности: ● bgwriter_delay = 10ms – по-умолчанию 200ms ● bgwriter_lru_maxpages = 1000 – по-умолчанию 100 ● bgwriter_lru_multiplier = 10.0 – по-умолчанию 2.0

Slide 51

Slide 51 text

Checkpoint'ы и Bgwriter 03 dataegret.com Особенности эксплуатации: ● Достаточно настроить один раз. ● Мониторинг – pg_stat_bgwriter, постгресовый лог.

Slide 52

Slide 52 text

Slide 53

Slide 53 text

Потоковая репликация 03 dataegret.com Потоковая репликация: ● Работает за счет передачи и воспроизведения WAL. ● Есть и физическая (с 9.0) и логическая (с 10.0). ● Легко настраивается, простая и надежная. Ограничения и недостатки: ● Работает в 1 поток.

Slide 54

Slide 54 text

Потоковая репликация 03 dataegret.com Мастер Реплика 5. Получение стартовой позиции 6. Проверка наличия журнала 7. Запуск WAL sender, отправка журнала 11. Обновление статистики 1. Проверка источника XLOG 2. Запуск WAL receiver 3. Вычисление стартовой позиции 4. Подключение к мастеру, отправка позиции 8. Запись журнала на диск 9. Обновление «отметки» 10. Отправка статистики 11. Воспроизведение журнала Начальная фаза Цикл репликации

Slide 55

Slide 55 text

Потоковая репликация 03 dataegret.com Какие бывают проблемы: ● Лаг репликации: ● Network/Storage/CPU/Stuck consumer. ● Конфликты репликации. ● Bloat таблиц/индексов.

Slide 56

Slide 56 text

Потоковая репликация 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

Slide 57

Slide 57 text

Потоковая репликация 03 dataegret.com Особенности эксплуатации: ● Мониторинг: pg_stat_replication, pg_replication_slots, функции(). ● pg_xlogdump – анализ WAL журнала. ● Top, Iostat, Nicstat.

Slide 58

Slide 58 text

Slide 59

Slide 59 text

Stats collector и Logging 03 dataegret.com Трекинг событий: ● DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, PANIC. Мониторинг активности: ● Системные функции и представления. ● Extension'ы.

Slide 60

Slide 60 text

Stats collector и Logging 03 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

Slide 61

Slide 61 text

Stats collector и Logging 03 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

Slide 62

Slide 62 text

Вопросы? dataegret.com [email protected]