Мониторинг и разрешение известных проблем
Упреждающий мониторинг
В промышленной среде рекомендуется отслеживать:
- количество активных процессов автоочистки, по операциям принудительной автоочистки - отдельно;
- пороги заморозки баз в
pg_database:datfrozenxid,datminmxid; - количество транзакций до начала принудительной автоочистки;
- количество транзакций до переполнения счетчиков и остановки СУБД;
- количество операций
VACUUMи автоочистки, выполненных в системе; - длительные и бездействующие транзакции.
Детальную картину по операциям, которые выполняются прямо сейчас, показывают:
- по операциям VACUUM/автоочистки – п редставление pg_stat_progress_vacuum;
- по
VACUUM FULL– pg_stat_progress_cluster.
Эти представления не накапливают статистику по завершенным операциям.
В представлениях pg_stat_user_tables, pg_stat_all_tables, pg_stat_sys_tables можно просмотреть по каждой из таблиц:
- количество операций очистки и сбора статистики;
- время последних операций.
Информация сохраняется отдельно для ручных команд (VACUUM, ANALYZE) и отдельно – для автоочистки.
Статистика операций очистки:
First_db=# SELECT relname, last_autovacuum, autovacuum_count, last_vacuum, vacuum_count FROM pg_stat_user_tables order by greatest(last_autovacuum, last_vacuum);
relname | last_autovacuum | autovacuum_count | last_vacuum | vacuum_count
-----------------------------+-------------------------------+------------------+-------------------------------+--------------
pgbench_accounts | | 0 | 2022-09-21 16:15:30.630133+03 | 1
pgbench_branches | 2022-09-21 16:23:06.369022+03 | 5 | 2022-09-21 16:20:34.860153+03 | 3
pgbench_tellers | 2022-09-21 16:23:06.384742+03 | 5 | 2022-09-21 16:20:34.865595+03 | 3
pgbench_history | 2022-09-21 16:23:06.408257+03 | 5 | 2022-09-21 16:15:30.806273+03 | 1
data | 2022-09-22 15:25:35.500587+03 | 1 | | 0
tables_list | 2022-09-30 09:30:17.040575+03 | 1 | | 0
test_table | 2022-10-11 11:58:15.93199+03 | 1 | | 0
indexes_list | 2022-10-20 01:00:36.367268+03 | 6 | | 0
stmt_list | 2022-10-25 12:00:43.107278+03 | 5 | | 0
sample_stat_database | 2022-11-17 10:00:01.317671+03 | 58 | | 0
sample_stat_indexes | 2022-11-17 15:00:37.467067+03 | 71 | | 0
sample_statements_total | 2022-11-17 16:00:38.699547+03 | 53 | | 0
sample_stat_tablespaces | 2022-11-17 18:00:40.89414+03 | 56 | | 0
sample_stat_indexes_total | 2022-11-17 19:30:42.7233+03 | 58 | | 0
sample_stat_tables | 2022-11-17 22:00:46.134932+03 | 74 | | 0
sample_stat_tables_total | 2022-11-18 01:30:50.171187+03 | 62 | | 0
samples | 2022-11-18 12:00:03.287923+03 | 45 | | 0
sample_stat_cluster | 2022-11-18 12:30:33.792144+03 | 45 | | 0
sample_stat_archiver | 2022-11-18 12:30:33.805231+03 | 45 | | 0
sample_statements | 2022-11-18 16:00:07.761702+03 | 67 | | 0
last_stat_cluster | 2022-11-18 16:30:08.338455+03 | 174 | | 0
last_stat_archiver | 2022-11-18 16:30:08.348909+03 | 174 | | 0
last_stat_tablespaces | 2022-11-18 17:00:08.840485+03 | 561 | | 0
last_stat_tables | 2022-11-18 18:00:10.188018+03 | 4555 | | 0
last_stat_indexes | 2022-11-18 18:00:10.395835+03 | 4549 | | 0
last_stat_database | 2022-11-18 18:00:10.423185+03 | 646 | | 0
sample_stat_user_functions | | 0 | | 0
sample_kcache | | 0 | | 0
sample_stat_indexes_failures | | 0 | | 0
last_stat_user_functions | | 0 | | 0
servers | | 0 | | 0
outlines | | 0 | | 0
<..>
Операции сбора статистики:
First_db=# SELECT relname, last_autoanalyze, autoanalyze_count, last_analyze, analyze_count FROM pg_stat_user_tables order by greatest(last_autoanalyze, last_analyze);
relname | last_autoanalyze | autoanalyze_count | last_analyze | analyze_count
-----------------------------+-------------------------------+-------------------+-------------------------------+---------------
pgbench_accounts | 2022-09-21 16:22:06.727426+03 | 2 | 2022-09-21 16:15:30.801807+03 | 1
pgbench_branches | 2022-09-21 16:23:06.372145+03 | 5 | 2022-09-21 16:15:30.393236+03 | 1
pgbench_tellers | 2022-09-21 16:23:06.387216+03 | 5 | 2022-09-21 16:15:30.398529+03 | 1
pgbench_history | 2022-09-21 16:23:06.477852+03 | 5 | 2022-09-21 16:15:30.806426+03 | 1
data | 2022-09-22 15:25:35.525662+03 | 1 | | 0
table1 | 2022-10-03 16:40:26.699413+03 | 1 | | 0
table2 | 2022-10-03 16:40:26.711228+03 | 1 | | 0
test_table | 2022-10-11 11:58:16.029999+03 | 1 | | 0
tables_list | 2022-10-11 13:00:46.437222+03 | 5 | | 0
indexes_list | 2022-11-09 01:00:23.202822+03 | 14 | | 0
stmt_list | 2022-11-16 17:30:40.502317+03 | 11 | | 0
samples | 2022-11-18 05:30:55.323771+03 | 148 | | 0
sample_stat_cluster | 2022-11-18 05:30:55.34042+03 | 148 | | 0
sample_stat_archiver | 2022-11-18 05:30:55.356682+03 | 148 | | 0
sample_stat_database | 2022-11-18 09:01:00.031095+03 | 228 | | 0
sample_stat_tablespaces | 2022-11-18 10:01:00.994229+03 | 219 | | 0
sample_stat_indexes_total | 2022-11-18 10:01:01.258031+03 | 227 | | 0
sample_stat_tables_total | 2022-11-18 12:00:03.151718+03 | 245 | | 0
sample_statements_total | 2022-11-18 14:30:36.143307+03 | 188 | | 0
sample_stat_tables | 2022-11-18 15:00:06.685281+03 | 285 | | 0
last_stat_tablespaces | 2022-11-18 17:00:08.846138+03 | 995 | | 0
servers | 2022-11-18 17:00:09.054239+03 | 174 | | 0
last_stat_database | 2022-11-18 17:00:09.066835+03 | 1129 | | 0
sample_statements | 2022-11-18 17:30:09.816417+03 | 269 | | 0
last_stat_cluster | 2022-11-18 17:30:09.8297+03 | 348 | | 0
last_stat_archiver | 2022-11-18 17:30:09.842489+03 | 348 | | 0
last_stat_tables | 2022-11-18 18:00:10.199774+03 | 4517 | | 0
sample_stat_indexes | 2022-11-18 18:00:10.285382+03 | 275 | | 0
last_stat_indexes | 2022-11-18 18:00:10.40086+03 | 4517 | | 0
last_stat_user_functions | | 0 | | 0
sample_stat_user_functions | | 0 | | 0
sample_kcache | | 0 | | 0
outlines | | 0 | | 0
import_queries | | 0 | | 0
funcs_list | | 0 | | 0
<..>
Подробнее описано в документации PostgreSQL: https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW.
По умолчанию при инсталляции Pangolin в промышленной среде включается мониторинг по списку, а на тестовых средах можно добавить мониторинг по запросу.
Интересны:
- все метрики раздела Vacuum;
- Transactions - current max (active, idle, prepared, waiting) transaction time;
- Streaming replication - Get physical replication stats for {#SLOTNAME};
- Connections - number of idle in transaction, prepared, waiting connections.
Обычные проблемы и их устранение
Автоочистка не запускается периодически по большой таблице
Проблема: таблица содержит очень много записей (сотни миллионов). Изменяется постоянно, но небольшими порциями. В выборке видно, что последняя очистка выполнялась очень давно, с тех пор прошло много дней:
SELECT relname, last_autovacuum, autovacuum_count, last_vacuum, vacuum_count FROM pg_stat_user_tables where relname = 'table';
Причина: для этой таблицы слишком высоки относительные пороги autovacuum_vacuum_scale_factor, autovacuum_vacuum_insert_scale_factor, которые по умолчанию настроены на 20% от количества записей в таблице. В пересчете они составляют десятки миллионов записей.
Решение: настроить разумные пороги на уровне таблицы. Для очень больших таблиц имеет смысл установить относительные пороги в 0.1 .. 5% (0.001 .. 0.05 в параметрах) или установить на таблицах достаточно высокие абсолютные пороги, а относительные не использовать вовсе (0).
Автоочистка (или VACUUM) работает слишком медленно
Проблема: операция автоочистки или VACUUM по одной и той же таблице продолжается десятки минут, иногда – часы. В pg_stat_activity видны запущенные и постоянно активные процессы автоочистки, в pg_stat_progress_vacuum видна длительная обработка одной таблицы с нарастанием счетчиков.
Возможные причины:
- Автоочистка слишком «вежлива», не использует ресурсы в полной мере.
- Слишком мало процессов-обработчиков автоочистки.
- Не хватает кеша разделяемых буферов.
- Мало памяти
maintenance_work_mem(илиautovacuum_work_mem), поэтому алгоритм вынужден повторять в цикле проходы по индексам небольшими порциями, вpg_stat_progress_vacuumвидно нарастание счетчикаindex_vacuum_count. - Только для ручного параллельного
VACUUM: не хватает системных фоновых процессов (background workers).
Решения:
- Увеличиьте размер порции
vacuum_cost_limit(autovacuum_vacuum_cost_limit, если используется), сократите паузыvacuum_cost_delay(autovacuum_vacuum_cost_delay, если используется). - Добавьте
autovacuum_max_workers, если есть ресурсы для дополнительных процессов. - Расширьте
shared_buffers, если есть свободная память. maintenance_work_mem/autovacuum_work_memувеличьте до максимального приемлемого по ресурсам размера.- Добавьте
max_parallel_maintenance_workers(имеет смысл сразу пересмотретьmax_worker_processesиmax_parallel_workers).
Ни автоочистка, ни VACUUM не очищают устаревшие записи
Проблема: таблица раздувается, несмотря на постоянно активную автоочистку.
Возможные причины:
- Очистка подчиняется правилам MVCC и сохраняет нужные для других транзакций/сессий/других объектов версии записей.
- Процесс автоочистки постоянно прерывается, отменяется и перезапускается заново.
Решения:
-
Найдите и удалите те проблемные объекты MVCC, которые требуют длительной изоляции.
Можно определит ь проблемный объект по возрасту старейшей из удерживаемых транзакций одним запросом:
SELECT
(SELECT max(age(backend_xmin)) FROM pg_stat_activity) as oldest_running_xact,
(SELECT max(age(transaction)) FROM pg_prepared_xacts) as oldest_prepared_xact,
(SELECT max(age(xmin)) FROM pg_replication_slots) as oldest_replication_slot,
(SELECT max(age(backend_xmin)) FROM pg_stat_replication) as oldest_replica_xact
;Далее действуйте сле дующим образом:
oldest_running_xact– длительные и бездействующие транзакции:
SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;Удалите процессы или отмените операции, используя
pg_terminate_backend(),pg_cancel_backend().Постоянное решение — попросите команду разработки приложений исключить код, который оставляет на стороне СУБД длительные/бездействующие операции.
Если результат не достигнут на этом направлении, то со стороны СУБД примените параметры statement_timeout, idle_in_transaction_session_timeout, lock_timeout, old_snapshot_threshold.
oldest_replica_xact– длительные запросы на реплике с установленным параметромhot_standby_feedback = on:
SELECT pid, client_hostname, state, backend_xmin
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;На затронутых репликах удалите длительные или бездействующие запросы, как описано выше. Если проблема проявляется повторно, то установите на реплике параметр
hot_standby_feedback = offи выберите фиксированный порог возраста удерживаемых для реплики транзакций параметромvacuum_defer_cleanup_age– устанавливается на лидере и измеряется в единицах счетчика транзакций лидера.oldest_replication_slot– неиспользуемые слоты репликации:
SELECT slot_name, slot_type, database, xmin, catalog_xmin
FROM pg_replication_slots
ORDER BY age(xmin),
age(catalog_xmin) DESC;Если Pangolin Manager остался в Maintenance mode, то переведите кластер в обычный режим (patronictl resume). Если слоты не относятся к Pangolin Manager, то используйте
pg_drop_replication_slot()для удаления неиспользуемых слотов.-
oldest_prepared_xact– незафиксированные подготовленные транзакции:SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
Отмените ненужные подготовленные транзакции – используйте команду
ROLLBACK PREPARED. -
Выполните вручную
VACUUM VERBOSEпо таблице, отслеживая возникающие блокировки. Постоянное решение — исправьте логику приложений, исключив конкурирующий с транзакционной нагрузкой DDL.
После устранения причин проблем с очисткой лучше выполнить VACUUM (а не VACUUM FULL) по затронутым таблицам.
Если были затронуты системные каталоги и/или пороги заморозки relfrozenxid, relminmxid (datfrozenxid, datminmxid), то VACUUM (не VACUUM FULL) стоит выполнить с правами суперпользователя, лучше по всей базе.
Если необходимо освободить затраченное во время инцидента дисковое пространство — стоит запланировать VACUUM FULL по затронутым таблицам или использовать утилит у и расширение pg_repack/расширение pg_squeeze.
Предупреждение повторных инцидентов
Исключите длительные и бездействующие транзакции на стороне приложений.
Настройте скорость автоочистки на 50 .. 70% от максимальной скорости. Максимальную скорость можно определить, выполняя VACUUM (DISABLE_PAGE_SKIPPING, VERBOSE) по специально созданной большой таблице.
На стороне приложений пересмотрите код трансформаций объемных массивов данных так, чтобы трансформация завершалась выделенной командой VACUUM, а лучше – VACUUM ANALYZE.