Исчерпание номеров транзакций в PostgreSQL

Содержание

Введение

При исчерпании свободных идентификаторов транзакций в журнале PostgreSQL, например в /var/log/postgresql/postgresql-10-main.log, начнут появляться предупреждающие сообщения следующего вида:

2023-08-27 23:41:20.242 +05 [1496] redqueen@redqueen WARNING:  database "redqueen" must be vacuumed within 11000000 transactions
2023-08-27 23:41:20.242 +05 [1496] redqueen@redqueen HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions.

Если не обращать внимание на эти предупреждения и не предпринять своевременных мер по устранению проблемы, то проблемная база данных перейдёт в режим "только чтение":

2023-08-28 03:33:50.341 +05 [19781] redqueen@redqueen WARNING:  database "redqueen" must be vacuumed within 1000001 transactions
2023-08-28 03:33:50.341 +05 [19781] redqueen@redqueen HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions.
2023-08-28 03:33:50.341 +05 [6557] redqueen@redqueen ERROR:  database is not accepting commands to avoid wraparound data loss in database "redqueen"
2023-08-28 03:33:50.341 +05 [6557] redqueen@redqueen HINT:  Stop the postmaster and vacuum that database in single-user mode.
        You might also need to commit or roll back old prepared transactions.

Своевременное обнаружение проблемы

Для своевременного обнаружения проблемы на уровне СУБД можно поставить на контроль в систему мониторинга значение, возвращаемое следующим запросом:

SELECT MIN(power(2, 31) - age(datfrozenxid)) AS remaining
FROM pg_database;

Если это значение опускается ниже порогового значения 11000000, при котором в журнале PostgreSQL начинают появляться предупреждения, значит пора локализовать и устранить проблему.

Поиск источника проблемы

Первым делом найдём проблемную базу данных. Следующий запрос может помочь обнаружить базы данных, автоматическая очистка в которых не срабатывает должным образом:

SELECT datname,
       age(datfrozenxid),
       current_setting('autovacuum_freeze_max_age')
FROM pg_database
ORDER BY 2 DESC;

Если значение во второй превышает значение из третьей колонки, то имеются проблемы с автоматическим очисткой в этой базе данных.

Или можно сразу посмотреть список проблемных баз данных:

SELECT datname,
       age(datfrozenxid),
       current_setting('autovacuum_freeze_max_age')
FROM pg_database
WHERE age(datfrozenxid)::bigint > current_setting('autovacuum_freeze_max_age')::bigint
ORDER BY 2 DESC;

Теперь, когда известны проблемные базы данных, можно найти в них проблемные таблицы:

SELECT c.relnamespace::regnamespace AS schema_name,
       c.relname as table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age,
       2^31 - 1000000 - greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS remaining
FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 4;

Большие значеия age указывают на то, что автоматическая очистка по каким-то причинам не срабатывает. С помощью следующего запроса можно найти осиротевшие и просроченные подготовленные транзакции:

SELECT age(transaction), * FROM pg_prepared_xacts;

Устранение проблемы

Если обнаружены осиротевшие или просроченные подготовленные транзакции, их можно заверршить с помощью следующего запроса, подставив на место <gid> идентификатор из результатов, выведенных предыдущим запросом:

ROLLBACK PREPARED <gid>;

Для того, чтобы запустить полную очистку таблицы, можно воспользоваться следующим запросом, подставив вместо <table> имя таблицы:

VACUUM FULL FREEZE ANALYSE <table>;

Учтите, что во время выполнения этого запроса таблица будет недоступна для изменений.

Устранение аварии

Наконец, если проблема не была устранена заранее и база данных всё-таки перешла в режим "только чтение", можно попробовать описанные выше операции. Более кардинальное решение заключается в выполнении очистки баз данных целиком, в однопользовательском режиме. Для этого нужно сначала остановить СУБД:

# systemctl stop postgresql

А затем запустить однопользовательский сервер, указав в конце команды имя базы данных, которую собираемся сжимать:

# /usr/lib/postgresql/10/bin/postgres --single -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf redqueen

В запустившейся диалоговой среде введём команду очистки базы данных:

VACUUM FULL VERBOSE;

В зависимости от производительности сервера, его загруженности, настроек СУБД, объёма и структуры базы данных, очистка может занять продолжительное время, в течение которого ни одна из баз данных не будет доступна.

Настройка автоматической очистки

При настройке автоматической очистки рекомендуется обратить внимание на следующие настройки:

autovacuum_freeze_max_age = 500000000 # по умолчанию 200000000
autovacuum_max_workers = 6            # по умолчанию 3
autovacuum_naptime = '15s'            # по умолчанию 1 минута
autovacuum_vacuum_cost_delay = 0      # по умолчанию 2 миллисекунды
maintenance_work_mem = '10GB'         # по умолчанию 64 мегабайта
vacuum_freeze_min_age = 10000000      # по умолчанию 50000000

Для подбора подходящих значений можно воспользоваться утилитой timescaledb-tune из пакета timescaledb-tools. Ниже приведено объяснение каждого из значений из документации.

autovacuum_freeze_max_age (integer)

Задаёт максимальный возраст (в транзакциях) для поля pg_class.relfrozenxid некоторой таблицы, при достижении которого будет запущена операция VACUUM для предотвращения зацикливания идентификаторов транзакций в этой таблице. Заметьте, что система запустит процессы автоочистки для предотвращения зацикливания, даже если для всех других целей автоочистка отключена.

При очистке могут также удаляться старые файлы из подкаталога pg_xact, поэтому значение по умолчанию сравнительно мало - 200 миллионов транзакций. Задать этот параметр можно только при запуске сервера, но для отдельных таблиц его можно определить по-другому, изменив их параметры хранения.

autovacuum_max_workers (integer)

Задаёт максимальное число процессов автоочистки (не считая процесс, запускающий автоочистку), которые могут выполняться одновременно. По умолчанию это число равно трём. Задать этот параметр можно только при запуске сервера.

autovacuum_naptime (integer)

Задаёт минимальную задержку между двумя запусками автоочистки для отдельной базы данных. Демон автоочистки проверяет базу данных через заданный интервал времени и выдаёт команды VACUUM и ANALYZE, когда это требуется для таблиц этой базы. Если это значение задаётся без единиц измерения, оно считается заданным в секундах. По умолчанию задержка равна одной минуте (1min). Этот параметр можно задать только в postgresql.conf или в командной строке при запуске сервера.

autovacuum_vacuum_cost_delay (floating point)

Задаёт задержку при превышении предела стоимости, которая будет применяться при автоматических операциях VACUUM. Если это значение задаётся без единиц измерения, оно считается заданным в миллисекундах. При значении -1 применяется обычная задержка vacuum_cost_delay. Значение по умолчанию - 2 миллисекунды. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Однако его можно переопределить для отдельных таблиц, изменив их параметры хранения.

maintenance_work_mem (integer)

Задаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. Если это значение задаётся без единиц измерения, оно считается заданным в килобайтах. Значение по умолчанию - 64 мегабайта (64MB). Так как в один момент времени в сеансе может выполняться только одна такая операция и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше work_mem. Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии.

Учтите, что когда выполняется автоочистка, этот объём может быть выделен autovacuum_max_workers раз, поэтому не стоит устанавливать значение по умолчанию слишком большим. Возможно, будет лучше управлять объёмом памяти для автоочистки отдельно, изменяя autovacuum_work_mem.

Заметьте, что для сбора идентификаторов мёртвых кортежей VACUUM может использовать не более 1GB памяти.

vacuum_freeze_min_age (integer)

Задаёт возраст для отсечки (в транзакциях), при достижении которого команда VACUUM должна замораживать версии строк при сканировании таблицы. Значение по умолчанию - 50 миллионов транзакций. Хотя пользователи могут задать любое значение от нуля до одного миллиарда, в VACUUM введён внутренний предел для действующего значения, равный половине autovacuum_freeze_max_age, чтобы принудительная автоочистка выполнялась не слишком часто.

Настройка очистки для твердотельных дисков

В статье Алексея Лесовского Давайте отключим vacuum?! можно найти следующие рекомендации по настройке очистки для твердотельных дисков:

vacuum_cost_delay = 0      # значение по умолчанию
vacuum_cost_page_hit = 0   # по умолчанию 1
vacuum_cost_page_miss = 5  # по умолчанию 10
vacuum_cost_page_dirty = 5 # по умолчанию 20
vacuum_cost_limit = 200    # значение по умолчанию

autovacuum_max_workers = 10            # по умолчанию 3
autovacuum_naptime = 1s                # по умолчанию 1 минута
autovacuum_vacuum_threshold = 50       # значение по умолчанию
autovacuum_analyze_threshold = 50      # значение по умолчанию
autovacuum_vacuum_scale_factor = 0.05  # по умолчанию 0.2
autovacuum_analyze_scale_factor = 0.05 # по умолчанию 0.2
autovacuum_vacuum_cost_delay = 5ms     # по умолчанию 2 миллисекунды, даже агрессивнее, а ещё более агрессивные настройки можно найти выше - 0
autovacuum_vacuum_cost_limit = -1      # значение по умолчанию

Ниже приведены объяснения настроек, ещё не описанных в предыдущем разделе, из документации:

vacuum_cost_delay (floating point)

Продолжительность времени, в течение которого будет простаивать процесс, превысивший предел стоимости. Если это значение задаётся без единиц измерения, оно считается заданным в миллисекундах. Значение по умолчанию равно нулю, то есть задержка очистки отсутствует. При положительных значениях интенсивность очистки будет зависеть от стоимости.

При настройке интенсивности очистки для vacuum_cost_delay обычно выбираются довольно небольшие значения, вплоть до 1 миллисекунды и меньше. Хотя в vacuum_cost_delay можно задавать дробные значения в миллисекундах, такие задержки могут быть неточными на старых платформах. На таких платформах для увеличения интенсивности VACUUM по сравнению с уровнем, обеспечиваемым при задержке 1 мс, потребуется настраивать другие параметры стоимости очистки. Тем не менее имеет смысл выбирать настолько малую задержку vacuum_cost_delay, насколько может обеспечить платформа; большие задержки не будут полезны.

vacuum_cost_page_hit (integer)

Примерная стоимость очистки буфера, оказавшегося в общем кеше. Это подразумевает блокировку пула буферов, поиск в хеш-таблице и сканирование содержимого страницы. По умолчанию этот параметр равен одному.

vacuum_cost_page_miss (integer)

Примерная стоимость очистки буфера, который нужно прочитать с диска. Это подразумевает блокировку пула буферов, поиск в хеш-таблице, чтение требуемого блока с диска и сканирование его содержимого. По умолчанию этот параметр равен 2.

vacuum_cost_page_dirty (integer)

Примерная стоимость очистки, при которой изменяется блок, не модифицированный ранее. В неё включается дополнительная стоимость ввода/вывода, связанная с записью изменённого блока на диск. По умолчанию этот параметр равен 20.

vacuum_cost_limit (integer)

Общая стоимость, при накоплении которой процесс очистки будет засыпать. По умолчанию этот параметр равен 200.

autovacuum_vacuum_threshold (integer)

Задаёт минимальное число изменённых или удалённых кортежей, при котором будет выполняться VACUUM для отдельно взятой таблицы. Значение по умолчанию — 50 кортежей. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Однако данное значение можно переопределить для избранных таблиц, изменив их параметры хранения.

autovacuum_analyze_threshold (integer)

Задаёт минимальное число добавленных, изменённых или удалённых кортежей, при котором будет выполняться ANALYZE для отдельно взятой таблицы. Значение по умолчанию — 50 кортежей. Этот параметр можно задать только в postgresql.conf или в командной строке при запуске сервера. Однако данное значение можно переопределить для избранных таблиц, изменив их параметры хранения.

autovacuum_vacuum_scale_factor (floating point)

Задаёт процент от размера таблицы, который будет добавляться к autovacuum_vacuum_threshold при выборе порога срабатывания команды VACUUM. Значение по умолчанию — 0.2 (20% от размера таблицы). Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Однако данное значение можно переопределить для избранных таблиц, изменив их параметры хранения.

autovacuum_analyze_scale_factor (floating point)

Задаёт процент от размера таблицы, который будет добавляться к autovacuum_analyze_threshold при выборе порога срабатывания команды ANALYZE. Значение по умолчанию — 0.1 (10% от размера таблицы). Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Однако данное значение можно переопределить для избранных таблиц, изменив их параметры хранения.

autovacuum_vacuum_cost_delay (floating point)

Задаёт задержку при превышении предела стоимости, которая будет применяться при автоматических операциях VACUUM. Если это значение задаётся без единиц измерения, оно считается заданным в миллисекундах. При значении -1 применяется обычная задержка vacuum_cost_delay. Значение по умолчанию — 2 миллисекунды. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Однако его можно переопределить для отдельных таблиц, изменив их параметры хранения.

autovacuum_vacuum_cost_limit (integer)

Задаёт предел стоимости, который будет учитываться при автоматических операциях VACUUM. При значении -1 (по умолчанию) применяется обычное значение vacuum_cost_limit. Заметьте, что это значение распределяется пропорционально среди всех работающих процессов автоочистки, если их больше одного, так что сумма ограничений всех процессов никогда не превосходит данный предел. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Однако его можно переопределить для отдельных таблиц, изменив их параметры хранения.

Просмотр состояния процессов очистки

Посмотреть текущее состояние выполняемых процессов очистки можно с помощью следующего запроса:

SELECT p.pid,
       now() - a.xact_start AS duration,
       coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
       CASE 
           WHEN a.query ~ '^autovacuum.*to prevent wraparound' THEN 'wraparound' 
           WHEN a.query ~ '^vacuum' THEN 'user'
           ELSE 'regular'
       END AS mode,
       p.datname AS database,
       p.relid::regclass AS table,
       p.phase,
       pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
       pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
       round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
       round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
       p.index_vacuum_count,
       round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
RIGHT JOIN pg_stat_activity a ON a.pid = p.pid
WHERE (a.query ~* '^autovacuum:' OR a.query ~* '^vacuum') AND a.pid <> pg_backend_pid()
ORDER BY now() - a.xact_start DESC;

Источники

• Nishchay Kothari. How to fix transaction wraparound in PostgreSQL?
• Keith Fiske. Managing Transaction ID Exhaustion (Wraparound) in PostgreSQL
• David Cramer. Transaction ID Wraparound in Postgres
• Joe Wilm. Challenges and Solutions When Scaling PostgreSQL
• Документация PostgresPro Standard 15 / 18.4.1. Память
• Документация PostgresPro Standard 15 / 18.4.4. Задержка очистки по стоимости
• Документация PostgresPro Standard 15 / 18.10. Автоматическая очистка
• Документация PostgresPro Standard 15 / 18.11.1. Поведение команд
• Алексей Лесовский. Давайте отключим vacuum?!