В Postgresql вы можете настроить всё!
Часть 1: VACUUM и AUTOVACUUM
Вступление
База растёт, но данных не прибавляется? – Это PostgreSQL! Учим бороться с разрастанием таблиц (bloat) и разбираемся с настройками очисти.
Как известно, механизм многоверсионности (MVCC) в РСУБД служит для сокращения числа блокировок на уровне строк и позволяет «распараллелить» пишущие и читающие транзакции. Т.е. транзакция, выполняющая UPDATE, не блокирует строки, а создаёт новую версию данных и взаимодействует с этой версией, позволяя другим транзакциям выполнять SELECT и видеть «старую» версию, пока не будет получено подтверждение о фиксации или откате транзакции с UPDATE-ом.
Архитектура PostgreSQL и её производных предполагает реализацию данного механизма следующим образом: информация об изменяемых данных в строке хранится не в отдельной структуре в памяти, а заносится в сами дата-файлы, т.е. в самой таблице создаётся копия строки с изменёнными значениями полей (даже если менялось одно из полей). По результату оператора UPDATE в таблице остаётся минимум одна неактуальная версия (если апдейтилась одна строка), если транзакция откатилась, то неактуальной \мёртвой строкой становится добавленная «новая» версия, а если транзакция закоммитилась – неактуальной становится её «старая» версия. Понятие многоверсионность в PG понимается буквально – одновременное нахождение нескольких версий одной и той же строки в таблице. Версии строк различаются временем действия, а информация об их периоде «жизни» и текущем статусе находится в заголовках самих версий строк. Как долго мёртвые строки будут находиться в таблице, определяется видимостью этих неактуальных версий в снимках(snapshots) других транзакций и настройками автоочистки. Здесь мы не будем рассматривать плюсы и минусы такого подхода, об этом уже написано много статей, но освежить эту информацию важно для дальнейшего понимания. Здесь мы разберём, как быть с побочными эффектами такой архитектуры.
Новые версии строк заносятся непосредственно в саму таблицу, добавляя к экстенту(сегменту) таблицы новые блоки по 8Кб, если в существующих не нашлось свободного места. Есть оптимизация free space map – это отдельный файл, создаётся для каждого сегмента таблицы, в котором отмечается сколько свободного пространства в каждом блоке сегмента (по умолчанию размер сегмента 1Гб). Данная карта используется для вставки новых кортежей (строк), чтобы не выполнять просмотр всех блоков, в поисках свободного пространства внутри них. Обновляется карта после каждой очистки таблицы.
Важно напомнить, что в PG индекс по таблице – это самостоятельный relation, со своими файлами-сегментами. В табличном экстенте ничего кроме блоков с табличными кортежами не хранится.
Часть 1. VACUUM и AUTOVACUUM
Негативное влияние от разрастания файлов таблиц очевидно:
· замедление последовательного просмотра таблиц (узел seq scan в планах)
· замедление индексного доступа, т.к. в индекс нужно добавлять записи с указателями на новые версии табличных строк, отчего растут уже сами индексы
Возникает необходимость в СВОЕВРЕМЕННОЙ очистке старых версий строк из файлов с данными. Может запускаться как командой администратора VACUUM <tablename>, так и автоматически - процессом autovacuum launcher. Очистка в PG не уменьшает размеры таблиц! Процедура очистки заключается в удалении старых версий строк из табличных страниц, при условии, что нет старых, всё ещё активных транзакций, в которых эти строки могут быть видны, даже если не запрашивались. (т.е. такие транзакции, на момент старта которых, данные версии строк были актуальными, и в теории, должны быть видны в этих транзакциях, если к ним будет обращение). После очистки «пустоты» используются для вставки новых версий строк, таблица перестаёт расти на какое-то время, и так до следующей очистки.
Рассмотрим настройки автоочистки:
Необходимые для работы автоочистки:
|
autovacuum = on |
запускает процесс autovacuum launcher |
|
track_counts = on |
включает сбор статистик о количестве затронутых строк при выполнении запросов. Данную статистику можно видеть в представлении pg_stat_all_tables, на неё полагается autovacuum |
Частота запуска:
|
autovacuum_naptime = 1min |
как часто проверяются статистки и запускаются дополнительные воркеры |
|
autovacuum_vacuum_scale_factor = 0.2 |
доля мёртвых строк (по умолчанию 20%) |
|
autovacuum_vacuum_threshold = 50 |
базовый порог очистки таблицы (50 строк), чтобы очистка не запускалась каждый раз для маленьких таблиц |
Параметры по умолчанию предполагают, что таблица будет очищена, когда кол-во мёртвых строк дорастёт до значения 20% от общего числа строк в таблице + базовый порог очистки. Для больших таблиц имеет смысл выставлять autovacuum_vacuum_scale_factor в меньшее значение, сделать это можно при помощи табличных опций (параметров хранения на уровне таблицы):
· alter table T set (autovacuum_vacuum_scale_factor = 0.05);
Значение, переопределённое на уровне отдельного объекта БД, будет иметь преимущество над общим серверным значением этого параметра.
Важно! Статистики о количестве мёртвых строк в таблицах (pg_stat_all_tables.n_dead_tup) сохраняются только при остановке сервера в штатном режиме. При некорректном выключении счётчики статистик сбрасываются и 20% начнут копиться с нуля. Т.е. реально очистка произойдёт на 20% + D%, где D% - процент мёртвых строк в таблице до сбоя работы сервера.
Важно! Не стоит полагать, что чрезмерно частая очистка будет вытеснять буферный кеш «одноразовыми» данными или просматривать таблицу каждый раз целиком.
Во-первых, при очистке выделяется буферное кольцо, размером 32 буфера, нужные очистке страницы читаются из общего кеша, если они уже туда загружены, остальные страницы очищаемой таблицы читаются в буферное кольцо и не выходят за его пределы в общий кеш.
Во-вторых, очистка читает с диска только страницы, отмеченные в карте видимости как грязные, т.е. где есть неактуальные версии. Накладные расходы могут быть в том случае, если очистить строку не получилось, если она видна в активной транзакции или удалена младшей транзакцией, чем активная (см. Глава 3). Тогда действительно блок останется в карте видимости с пометкой «грязный», ведь фактически очистки этой строки не произошло, но ведь могут быть очищены другие строки из этого блока. При постоянном числе активных транзакций в системе, вероятность при очистке наткнуться на ситуацию, когда в блоке есть неактуальные строки, попадающие в снимки активных транзакций, остаётся постоянной. Другими словами, если нет долгих транзакций в базе, то и откладывать очистку нет смысла, если есть запас по В/В. Убедиться в том, что очистка не просматривает всю таблицу, можно запустив vacuum по таблице руками, а после его завершения запустить повторно и посмотреть, как долго он будет выполняться второй раз (почти мгновенно).
Выигрыш от более редких очисток заключается только в том, чтобы за операцию очистки одной табличной страницы удалить из неё стразу несколько неактуальных версий. Прикинем, сколько именно таких строк окажется в блоке.
PostgreSQL : Документация: 16: 73.6. Компоновка страницы базы данных
Возьмём среднюю длину строки 90 байт + 27 байт на заголовок каждой строки (вернее 23 байта на заголовок строки + 4 байта на указатель при заголовке блока). В таком случае, на одном табличном блоке (8Кб) помещается 71 строка, => при стандартном значении vacuum_scale_factor = 0.2, на момент срабатывания очистки, в каждом блоке таблицы будет очищено ~14 неактуальных версий строк.
Если вы наблюдаете, что количество таблиц в очереди на очистку растёт, необходимо повышать интенсивность работы процессов очистки. Посмотреть очередь таблиц, ожидающих очистки, можно с помощью такого запроса: (запрос учитывает возможные параметры хранения таблиц)
WITH table_vacuum_params AS (
SELECT
relname AS table_name,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows,
(SELECT get_setting(relname, 'autovacuum_vacuum_scale_factor')) AS scale_factor,
(SELECT get_setting(relname, 'autovacuum_vacuum_threshold')) AS threshold,
(SELECT reltuples FROM pg_class WHERE pg_class.relname = pg_stat_all_tables.relname) AS total_rows
pg_stat_all_tables)
SELECT
table_name,
live_rows,
dead_rows,
scale_factor,
threshold
FROM
table_vacuum_params
WHERE
dead_rows > (threshold::int + (scale_factor::real * live_rows))
ORDER BY
dead_rows DESC;
--------------------------------------------------------------создание функции-------------------------------------------------------------------------------------
create or replace function get_setting(in tab_name text , in sett_name text , out sett_value text ) language plpgsql as $$
DECLARE
set_mass text[];
_set_attr text;
BEGIN
sett_value := current_setting(sett_name);
select reloptions from pg_class where relname = tab_name into set_mass ;
if set_mass is null then
return ;
end if ;
select conf::text from (select unnest(set_mass)) conf where conf::text ~ sett_name into _set_attr ;
if FOUND then
sett_value := (regexp_match( _set_attr , '^(.*?)\=(.*?)[\)"]' ))[2] ;
end if;
END;
$$;
Данную метрику можно собирать с определённой частотой, например при помощи Zabbix, для того чтобы определить момент времени, когда отставание процесса очистки было максимальным, это может дать понимание того, на сколько увеличивать значения параметров интенсивности работы autovacuum. У УКЦ ФОРС есть подробный курс о настройке сбора произвольных метрик из базы PostgreSQL и их визуализации.
Интенсивность работы:
|
autovacuum_max_workers = 3 |
максимальное число одновременно работающих воркеров. Очистка одной таблицы, как и раньше, не распараллеливается, как и очистка одного отдельного индекса (1 relation – 1 процесс). |
|
autovacuum_vacuum_cost_delay = 2ms |
определяет задержки между периодами работы во время очистки таблиц |
|
autovacuum_vacuum_cost_limit = -1 |
определяет общий объем работы за одну итерацию, который складывается из базовых стоимостей отдельных операций (vacuum_cost_page_hit, vacuum_cost_page_miss, vacuum_cost_page_dirty) Значение параметра -1 означает, что используется значение параметра vacuum_cost_limit =200. |
Важно! Параметры cost_delay и cost_limit определяют общий пул выполняемой работы за итерацию и разделяются всеми воркерами, а не перемножаются на количество процессов autovacuum worker, что упрощает настройку.
Начиная с версии PG16 (commit: 7d71d3dd) изменение параметров интенсивности работы применяется «на лету» для уже запущенных рабочих процессов очистки.
