Обслуживание MVCC: VACUUM и autovacuum

Введение

В СУБД Pangolin для изоляции параллельных транзакций выбрана модель PostgreSQL MVCC (Multi Version Concurrency Control, управление параллельным доступом посредством многоверсионности). Реализация PostgreSQL предполагает хранение непосредственно в таблицах и индексах СУБД множества копий каждой модифицируемой записи, по одной на каждую из модификаций.

Постепенное накопление полностью устаревших версий записей (dead tuples), хранимых вместе с активными данными изменяемых объектов - известный недостаток реализации, который называется «раздуванием» или bloating. При раздувании таблиц и индексов расходуется дисковое пространство, заполняется память, падает производительность сервера. Требуется все больше и больше процессорных операций для выделения хаотично разбросанных действительных записей.

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

соседние транзакции и их снимки операций (запросов);
мультитранзакции;
подготовленные транзакции;
физические реплики в меру возможного отставания от лидера.

Правила видимости данных в каждой из транзакций MVCC Pangolin основаны на сравнении 32-битного идентификатора транзакции с идентификаторами соседних транзакций и сроками действия версионированной истории каждой из записей. Пространство идентификаторов транзакций строго ограничено, поэтому по мере поступления новых транзакций требуется постоянное высвобождение номеров транзакций «древней истори» СУБД.

Рассмотрим упомянутые объекты внимательнее и изучим настройки стандартных средств для их обслуживания.

Изоляция транзакций и история записей в MVCC Pangolin: подробности

Атомарная единица, по которой учитываются модификации (версии одной записи) в MVCC - одна транзакция.

Чтобы организовать очередь согласованных транзакций, сервер имеет внутренний счетчик транзакций - общий для экземпляра (кластера баз) системный объект, который работает как последовательность. Счетчик с момента инициализации кластера баз вначале монотонно возрастает, а затем допускает повторное использование значений. Почти как последовательность с атрибутом CYCLE, но есть особенности, которые описаны ниже.

Каждая транзакция на старте выбирает значение этого счетчика как следующий идентификатор из последовательности. Его и называют идентификатором транзакции или XID. Оно сохраняется за транзакцией и всеми данными, которые она изменила, от старта до «заморозки» - переноса транзакции в исторический архив (подробнее заморозка описана ниже в отдельном разделе).

Когда транзакция выполняется над записью DML, она завершает историю старой версии и, возможно, добавляет обновленную копию в виде новой версии записи. Каждая из версий автоматически помечается «сроком действия»: от XID транзакции, которая добавила версию (xmin) и до XID транзакции, которая ее удалила (xmax). Если версия активна (действительна, не удалена), то во втором поле xmax у нее может быть 0 или идентификатор такой удаляющей транзакции, которая уже отменена или еще активна, не зафиксирована.

Сроки действия разных версий одной записи не пересекаются никогда.

Номера транзакций невозможно напрямую сравнить между собой. Например, транзакция № 100 моложе транзакции с номером 3 миллиарда (почему - см. ниже). Поэтому для хранения номеров транзакций определен отдельный служебный тип данных: xid. Значения этого типа нельзя напрямую сравнивать, складывать или вычитать. Стандартная функция age(<идентификатор: xid>) возвращает «возраст» транзакции в единицах счетчика - количество номеров, выданных от ее старта до последнего значения счетчика транзакций на текущий момент. Возраст транзакций имеет тип integer, эти значения можно сравнивать между собой, складывать и вычитать.

Горизонты событий и снимки

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

Дополнительно строится свой согласованный образ базы на каждый из запросов (при уровне изоляции READ COMMITTED) или на первый запрос в транзакции (уровень REPEATABLE READ и выше). Для этого сессия на старте запроса/транзакции запоминает:

xid старшей из транзакций, активных прямо сейчас - xmin (можно понимать как локальный «горизонт» этой операции);
следующий, еще никому не выделенный сейчас xid - xmax (этот xid когда-то будет присвоен следующей транзакции в очереди);
список xid всех активных сейчас транзакций - xip_list.

В продолжение всей операции (запроса в REPEATABLE READ или транзакции в READ COMMITTED) правила видимости результатов между транзакциями таковы:

результаты транзакций до xmin - принять;
активности транзакций от xmin до xmax - просматривая xip_list, принять активности зафиксированных транзакций и отбросить активности транзакций, которые не завершены или отменены;
активности самой операции - принять;
активности более молодых транзакций (xid >= xmax) - отбрасывать, чтобы не разрушить представление о данных, которое было согласовано на момент старта операции.

Для того чтобы отбрасывать эффекты соседних транзакций, не требуется, как в Oracle, по каждому блоку данных читать векторы отмены (undo records) и формировать в памяти исторический образ блока. Поскольку здесь исторические версии записей хранятся вместе с текущими, достаточно из версий записи выбрать ту, которая действовала на момент старта операции.

Для операции (запроса/транзакции) совокупность (xmin, xmax, xip_list) называется «снимком» (snapshot). Каждый снимок исчерпывающе определяет согласованное состояние базы, видимое в заданной операции.

Пример просмотра горизонта событий транзакции:

First_db=# BEGIN;
BEGIN
First_db=*#  SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();
backend_xmin
--------------
        19838
(1 row)

Пример просмотра снимков операций:

$ pgbench -c 10 -j 5 -T 120 First_db &
<..>
$ psql -d First_db
<..>
First_db=# BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
First_db=*#  SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();
backend_xmin
--------------
       194230
(1 row)
First_db=*# SELECT txid_current_snapshot();
txid_current_snapshot
--------------------------------------------------
200128:200174:200128,200161,200168,200170,200172
(1 row)
First_db=*# SELECT txid_current_snapshot();
txid_current_snapshot
-----------------------------
204776:204782:204776,204780
(1 row)
First_db=*#  SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();
backend_xmin
--------------
       194230
(1 row)
First_db=*# SELECT txid_current_snapshot();
txid_current_snapshot
-------------------------------------------
247416:247432:247416,247428,247429,247430
(1 row)
First_db=*#  SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();
backend_xmin
--------------
       194230
(1 row)
First_db=*# COMMIT;
COMMIT
First_db=#  SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();
backend_xmin
--------------
       260196
(1 row)

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

мешает очистке исторических данных, заставляет удерживать значительное количество устаревших версий записей в таблицах;
вносит накладные расходы при разрастании массива статусов транзакций и сложности снимков операций, усложняет правила видимости версий записей в операциях (подробности – ниже).

Сервер по умолчанию автоматически поддерживает такое состояние по каждой из таблиц, в котором сохранены все версии строк, потенциально интересные любой из сессий, имеющих возможность увидеть таблицу. Изменить это поведение можно при помощи параметра old_snapshot_threshold (ниже).

Отметку, которая объединяет горизонты событий всех сессий базы, называют «горизонтом базы». Это старейшее значение по всем горизонтам сессий базы, исключая сессии, которые выполняют VACUUM и декодирование потока логической репликации.

Ниже приведен пример просмотра горизонтов баз, функция age(xid) возвращает «возраст» транзакции в единицах внутреннего счетчика – количество транзакций от заданного XID до текущего:

First_db=# SELECT pid, datname, usename, state, backend_xmin FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY datname, age(backend_xmin) DESC;
   pid | datname  | usename  | state  | backend_xmin
-------+----------+----------+--------+--------------
 13283 | First_db | postgres | active | 290747
(1 row)

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

Правила сохранения устаревших версий записей различаются для очистки разделяемых таблиц (таких таблиц системного каталога, как pg_database) и всех остальных (неразделяемых) таблиц. Именно:

если pg_class.relisshared = true (pg_database и некоторые другие объекты каталога в табличном пространстве pg_global) - определяется общий горизонт событий сервера, старейший из горизонтов баз;
иначе (все остальные таблицы) - актуален горизонт событий по той базе, которая содержит таблицу, поскольку сессии, подключенные к другим базам, эту таблицу не увидят. Если же открытых транзакций нет - определяется последняя завершенная транзакция.

За горизонтом сервера/базы действительные данные одинаково видны в каждой из активных транзакций, а недействительные - не могут быть видны ни одной из активных транзакций.

Недействительные версии, окончание срока действия которых находится за горизонтом событий сервера/базы, дальше будем называть «полностью устаревшими». По коду сервера они называются DEAD, в отличие от еще удерживаемых RECENTLY_DEAD.

Только полностью устаревшие записи могут быть удалены без потери данных.

Мультитранзакции

В архитектуре Pangolin не предусмотрено хранение в блоке данных списка нескольких транзакций, которые обрабатывают блок или запись. Нет раздела блока, похожего на Interested Transaction List – ITL в Oracle. Всего два поля по 32 бита отведены под идентификаторы транзакций срока действия в заголовке версии. Поэтому в случае, когда строка интересна двум и более транзакциям одновременно, Pangolin помещает список этих транзакций в новый объект - мультитранзакцию. Полученному списку присваивается новый идентификатор из отдельного системного счетчика, который устроен похожим на основной счетчик транзакций образом. Именно этот идентификатор мультитранзакции далее используется в поле срока действия xmax. Массив статусов мультитранзакций и списки объединенных в них транзакций хранятся в подкаталоге pg_multixact каталога данных (PGDATA).

Мультитранзакции также требуют хранения видимой им истории версий записей. Идентификаторы устаревших мультитранзакций должны периодически освобождаться (замораживаться) для предотвращения оборота их счетчика.

Подготовленные транзакции

Pangolin поддерживает двухфазную фиксацию транзакций – 2 Phase Commit, 2PC. Эта операция делится на два раздельных шага:

Выполнение PREPARE TRANSACTION и сохранение в БД «заготовки» транзакции.
Выполнение COMMIT PREPARED в тот момент, когда бизнес-логика приложений принимает окончательное решение о готовности транзакции.

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

Удалить ненужные подготовленные транзакции вручную можно командой ROLLBACK PREPARED.

Реплики и слоты репликации

Реплики в кластере Pangolin активны и могут выполнять запросы. Если удалить на лидере такие устаревшие версии данных, которые уже используются или могут быть использованы в запросах - возникнет конфликт репликации.

Параметр hot_standby_feedback = on предотвращает подобные ситуации. Он обеспечивает хранение лидером истории версий по потребностям реплики. При включенном параметре каждая из реплик по мере продвижения по потоку WAL оповещает лидера об изменениях ее общего «горизонта», а лидер удерживает старые версии записей до старейшего из горизонтов известных реплик. Известные отметки можно просмотреть на лидере в представлении pg_replication_slots.

Если обновление отметок прекратилось, то ненужные регистрации реплик придется убрать при помощи pg_drop_replication_slot(), иначе лидер продолжит ошибочно удерживать историю версий.

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

Особенности Pangolin

В физической репликации кластера высокой доступности Pangolin по умолчанию параметр hot_standby_feedback включен, при этом слотами репликации автоматически управляет кластер Patroni.

Отдельного внимания могут потребовать пользовательские конфигурации логической и физической репликации, которые не относятся к кластеру высокой доступности. И кластеры, где Patroni забыли в отключенном состоянии (Maintenance mode).

Механизмы очистки объектов

Предлагается три варианта очистки объектов:

Команда SQL VACUUM: перебор всех версий хранимых в объекте записей, вычисление срока действия каждой из версий, разметка карты свободного пространства объекта. Не требует исключительной блокировки, не освобождает физическое дисковое пространство и блоки разделяемой памяти, не затрагивает структуру объектов. Пространство, помеченное как свободное во время VACUUM, может быть повторно использовано только под новые версии записей в том же объекте.
Демон autovacuum: фоновые процессы, которые постепенно, небольшими порциями, параллельно с основной нагрузкой выполняют логику команды VACUUM.
Команда SQL VACUUM FULL или CLUSTER: физический перенос всех действующих записей из существующего объекта в новую копию, переключение между копиями, удаление старой копии объекта. Требует исключительной блокировки на все время операции.

Консольная утилита vacuumdb - оболочка для команд VACUUM, она облегчает запуск VACUUM из операционной системы.

Недостаток VACUUM FULL – полная блокировка обрабатываемой таблицы на все время обработки. Эту проблему в Pangolin решают утилита с расширением pg_repack и расширение pg_squeeze.

Результат их действий соответствует VACUUM FULL, но исключительная блокировка не требуется на время перемещения данных – только на самый краткий момент смены идентификаторов дисковых файлов таблиц/индексов. Они значительно облегчают полное пересоздание объектов.

Общие объекты и параметры для всех видов очистки

Массив статусов транзакций clog или xact

Как отмечалось ранее, снимки операций включают списки транзакций по состоянию на момент старта операции. Для оперативного составления списка и вычисления правил видимости версий записей используется отдельный массив статусов транзакций.

В разделяемой памяти сервера организована структура, называемая Commit Log или clog. Это массив, индексом которого служит номер транзакции, а элементы хранят один из четырех статусов: в процессе, зафиксирована, отменена или «порция зафиксирована» (только для подтранзакций между точками сохранения основной транзакции).

Массив фиксируется на диске во время контрольных точек и штатного закрытия сервера в поддиректории кластера баз pg_xact.

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

Зафиксированные на диске данные массива защищены журналом WAL и сохраняются в резервных копиях. При физическом восстановлении сервера clog должен быть полностью восстановлен из копии и обновлен по журналам транзакций (обычно это происходит автоматически).

На диске массив статусов хранится в виде нескольких файлов фиксированного размера. Файлы автоматически добавляются при нарастании списка активных транзакций в памяти. Обслуживание заключается в периодической очистке устаревшей части информации в памяти и удалении соответствующих неактуальных файлов с диска.

Карта видимости таблицы

Постоянное полное сканирование таблиц и индексов при периодической очистке устаревших версий и при каждой «заморозке» (переносе неактивных транзакций в архив) – не лучшее применение ресурсов сервера.

Чтобы во время очистки не повторять уже проделанные операции, процессы очистки запоминают состояние полностью обработанных блоков таблицы. Для этого служит хранимый на диске при файлах таблицы «слой» (отдельный набор файлов) – карта видимости (visibility map). В этих файлах на каждый 8КБ блок таблицы хранится два бита:

первый при очистке блока выставляется в 1, если все версии строк в блоке - действующие (видимые), или в 0, если в блоке есть хотя бы одна версия, срок действия которой завершен (эту версию сразу удалять нельзя – выше описано, почему);
второй при «заморозке» выставляется в 1, если все версии строк в блоке «заморожены» (открывшие их транзакции ушли в историю), иначе - в 0.

Во время очистки нет смысла просматривать блоки таблицы, в которых все версии записей действительны, поэтому очистка может пропустить блоки, для которых бит видимости выставлен в 1.

Точно так же блоки, в которых все записи уже «заморожены», можно пропускать при «заморозке».

Таблица изначально создается без карты видимости. Создают карту процессы очистки и они же выставляют в 1 биты статуса при очистке/заморозке блоков. А вот сбросить биты может любая из транзакций, иначе очистка пропускала бы недавно измененные блоки.

У индексов нет карты видимости, потому что в них видимость версий записей не отмечается.

Карта свободного пространства

В карте свободного пространства записано, сколько свободного места имеется в каждом из блоков таблицы или индекса для изменения и добавления записей. Она напоминает по структуре индекс (двоичное дерево), в котором каждому из 8КБ блоков объекта выделен 1 байт. В байте записана доля свободного места в блоке в баллах от 0 до 255. Двоичное дерево, похожее на индекс, построено на этом значении как на ключе и позволяет сессиям быстро находить блоки с заказанным минимальным количеством свободного места либо убеждаться, что таких блоков не осталось – придется расширять объект.

Физически карта хранится как отдельный файл (слой) при наборе файлов таблицы/индекса.

Картой пользуется каждая из сессий во время добавления и изменения записей: ищет свободный блок, обновляет карту после внесения изменений.

Карта активно пополняется при очистке объекта в процессе высвобождения пространства в блоках.

Фактор заполнения

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

Параметр fillfactor в описании таблицы и индекса задает фактор заполнения в процентах, от 10 до 100. Значение по умолчанию — 100 (плотное заполнение) для таблицы и 90 (почти полное заполнение) - для индекса.

При меньшем факторе заполнения операции INSERT упаковывают данные в страницы только до заданного процента, а оставшееся место резервируется для изменения строк на этой же странице. В результате UPDATE получает шанс поместить измененную копию строки в ту же страницу, откуда извлечена исходная копия. Это гораздо эффективнее, чем размещать копию на другой странице. Также, свободное пространство в блоке может использоваться для внутристраничных обновлений полей таблицы, не входящих в индексы (так называемых HOT update).

Для активно изменяемых таблиц и индексов стоит выбрать меньший фактор заполнения, чтобы зарезервировать больше пространства для изменений. Можно начать с 85..90%. При меньших значениях (больше свободного места), скорее всего, дополнительное свободное место не добавит производительности.

Нет глобального параметра fillfactor на уровне сервера/базы/схемы. Можно изменять только настройки объектов по одному. Изменения не применяются к существующим блокам. Чтобы переупаковать данные, потребуется выполнить VACUUM FULL или CLUSTER. Либо воспользоваться pg_repack/pg_squeeze.

Особенности Pangolin: дисциплина организации доступных блоков

В Pangolin, как и в PostgreSQL, нет списка свободных блоков объекта. Т.е. не поддерживается знакомая по Oracle до 10g дисциплина списка свободных блоков Freelists. Соответственно и аналога параметра PCTUSED тоже нет. Единственная доступная дисциплина - карта доступных блоков, аналог битовой карты из новой для Oracle дисциплины ASSM.

Параметр fillfactor примерно соответствует (PCTFREE - 100%) из Oracle.

Списка интересующихся транзакций (ITL) по каждому блоку в Pangolin тоже нет, поэтому нет и параметров объекта, которые напоминали бы INITRANS и MAXTRANS. Поддержка цепочек транзакций, конкурирующих за одну и ту же запись, возложена на общий для кластера системный объект - список мультитранзакций, который также требует периодического обслуживания.

Команда VACUUM и утилита vacuumdb

Команда SQL VACUUM – процесс обслуживания СУБД, который решает две основные задачи:

Удаление полностью устаревших версий записей (dead tuples).
«Заморозка» (перенос в архив и высвобождение) идентификаторов транзакций.

На эту же команду возложены и связанные вспомогательные задачи по обслуживанию системных объектов.

Алгоритм в общем виде:

В цикле по каждой из таблиц:
1. Получить на таблицу блокировку ShareUpdateExclusiveLock:
  1. Первый блок:
    1. Просмотрите все страницы, не отмеченные в карте видимости как «полностью видимые».
    2. Получите список устаревших версий и одновременно заморозьте версии, относящиеся к «древней истории» транзакций.
    3. Удалите из индексов ссылки, соответствующие устаревшим версиям записей.
  2. Второй блок:
    1. В цикле по страницам таблицы удалите устаревшие версии и плотно перераспределите оставшиеся действительные записи.
    2. Обновите карту свободного пространства и карту видимости.
    3. Завершите цикл по страницам.
  3. Третий блок:
    1. Очистите индексы.
    2. Уменьшите «хвост» таблицы на последнюю занятую страницу, если это возможно.
    3. Обновите статистику и описания системного каталога по таблице.
2. Освободите блокировку ShareUpdateExclusiveLock.
Завершите цикл по таблицам.
Постобработка. Обновите статистику и описания системного каталога - pg_stat_all_tables.n_live_tup, n_dead_tup, last_vacuum, vacuum_count и т.д.
Если возможно, то удалите старые файлы и страницы clog.

Подробнее по блокам:

Выполнение «заморозки» и удаление ссылок на устаревшие записи из индексов. Сессия для начала отводит буфер размером maintenance_work_mem в локальной памяти процесса и просматривает записи. Если записи подходят для "заморозки" (подробнее - ниже), то они замораживаются. В буфер постепенно набирается список полностью устаревших записей. После набора полного списка устаревших записей (или заполнения maintenance_work_mem, что раньше) попавшие в список записи удаляются из индексов (точнее, из индексов удаляются ссылки на устаревшие версии записей). Если в первом проходе maintenance_work_mem была переполнена, то будет набрана следующая порция списка, те же шаги повторятся для нее.
Удаление самих устаревших записей и обновление карт свободного пространства/видимости по каждой из страниц. Здесь алгоритм изменяет блок данных, плотно упорядочивая действительные записи и пропуская устаревшие. Порядок таблицы указателей на записи в заголовке блока не изменяется, чтобы не аннулировать ссылки на эти указатели из индексов (ctid). Изменения отмечаются в картах свободного пространства и видимости отдельно по каждому из блоков.
Очистка индексов после массового удаления ссылок. Здесь выполняется обновление статистики и других описаний объектов в системных каталогах по результатам обработки. Если «хвостовая часть» таблицы более не содержит данных, здесь же файл таблицы будет усечен, а дисковое пространство - возвращено операционной системе.
Постобработка.

Когда завершен процесс по всем таблицам, сервер обновляет некоторое количество статистики и системных каталогов по результатам операции.

Здесь же отсекаются устаревшие части массива статусов транзакций clog (часть буферов в памяти и старые файлы).

VACUUM не использует транзакции, но сам не может быть выполнен в рамках транзакции.

С версии 5.1.0 Pangolin (ядро PostgreSQL 13) VACUUM может параллельно выполнять операции над несколькими индексами (ключ PARALLEL). В варианте autovacuum эта возможность еще недоступна.

Эффективные права VACUUM на все время обработки таблицы совпадают с правами владельца таблицы.

«Настоящий» VACUUM по всей базе (включающий очистку таблиц системного каталога) – может быть выполнен только суперпользователем. Пользовательский VACUUM таблицы каталога не обрабатывает, а пропускает без ошибок и предупреждений.

Дополнительно, VACUUM не обрабатывает следующие таблицы:

временные таблицы других сессий, поскольку нет смысла искать их содержимое на диске;
секционированные таблицы, поскольку соответствующие действия выполняются на секциях поодиночке.

Обработка пропускается без предупреждений и ошибок, чтобы не осложнять администраторам полную обработку базы.

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

Параметр maintenance_work_mem крайне важен для VACUUM, потому что от объема буфера прямо зависит количество повторений цикла очистки при объемном списке устаревших версий. При инсталляции Pangolin параметр будет установлен в 1/24 от объема физической памяти сервера.

Утилита vacuumdb - оболочка команды VACUUM для внешних скриптов ОС.

Подходит для:

очистки и обновления статистики сразу после масштабной трансформации данных - лучше выполнять сразу с ANALYZE;
предотвращения оборота счетчика транзакций (wraparound); когда найдены проблемные таблицы по relfrozenxid – рекомендуется сразу же выполнить по ним VACUUM, можно с агрессивной заморозкой (FREEZE).

Не подходит для:

очистки таблицы после постоянного изменения ее назначения в архитектуре приложения - не освободит дисковое пространство и не уплотнит данные по блокам,
очистки таблицы, раздутой в результате пика аномальной нагрузки или отставания autovacuum - по той же причине;
полной дефрагментации таблицы;
применения новых параметров хранения таблицы;
постоянной фоновой очистки, периодического обслуживания.

Заморозка (перенос в историю) транзакций

Выше упоминалось, что внутренний счетчик идентификаторов транзакций сервера, XID, напоминает последовательность с атрибутом CYCLE - допускает повторное использование значений. Размерность значений этого счетчика - всего 32 бита. Для транзакций доступно 232 - 3 идентификатора (xid 0, 1 и 2 зарезервированы). Каждая из транзакций со своей точки зрения отличает XID прошлых транзакций от будущих. К примеру, транзакция номер 100, учитывая разрядность счетчика, видит примерно:

2^31 транзакций как прошлые (большинство из них видимы): (2^31 + 101, 2^31 + 102, 2^31 + (2^31 - 1), 3, 4, 5, .., 99);
2^31 транзакций как будущие (невидимы): (101, 102, .., 2^31 + 100).

Здесь удобнее представлять пространство xid не в виде линейки, а в виде кольца, похожего на циферблат часов:

Если часовая стрелка указывает на известную транзакцию, то по часовой стрелке расположатся будущие транзакции, а против - прошлые.

Правило видимости ограничивает количество одновременно отслеживаемых транзакций сервера (по всем базам) числом не 2^32, а 2^31, порядка 2.1 млрд. Система со средней скоростью 10 000 TPS израсходует это количество XID за двое с половиной суток. На первый взгляд, при исчерпании пространства номеров можно просто проверить, что активных транзакций старше (например) суток больше нет, освободить более не активные идентификаторы, передать их новым транзакциям. Однако, при более внимательном взгляде видна следующая проблема.

Дело в том, что в MVCC Pangolin номера транзакций сохранены среди пользовательских данных вместе с версиями записей, именно они указывают срок действия каждой версии.

Вспомним о транзакции номер 100 и перенесемся от ее старта в будущее на двое с половиной суток (для 10К TPS):

Пусть транзакция № 100 примерно 2.5 суток назад добавила запись. Срок действия этой записи (ее первой версии) стартует от xmin = 100. За 2.5 суток запись никто не менял (первую версию не закрывал, новые не добавлял). Сейчас активна транзакция № 2^31+100. Она видит запись, потому что транзакция 100 для нее - в прошлом. Но, если открыть следующую транзакцию № 2^31+101, то она не увидит эту запись, поскольку идентификатор 100 для нее соответствует далекому будущему!

Ситуация на иллюстрации называется transaction wraparound. По-русски – оборот или зацикливание счетчика транзакций.

Проблему же называют transaction wraparound problem.

Сервер при приближении transaction wraparound:

за 10 миллионов транзакций до 2^31 выдает в протоколе множество предупреждений вида:
```
WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".
```
за 1 миллион транзакций до 2^31 переходит в режим «только для чтения» по всем обслуживаемым базам (последний миллион транзакций резервируется для операций по выводу СУБД в обычное состояние).

Чтобы выйти из этого состояния, придется не только подтвердить, что транзакция 100 более не активна. Необходимо отказаться от использования идентификатора 100 в действительных версиях записей всех таблиц. В том числе, пометить упомянутую запись с xmin = 100 как «архивную» или «историческую» – одинаково видимую всем активным сейчас транзакциям. Вместе со всеми записями, которые не менялись с тех пор, как транзакция 100 добавила или обновила их. По всем базам и таблицам.

В остальных транзакциях при просмотре версии по одному признаку «архивная» ясно, что запись относится к «древней истории» базы. Признака достаточно, чтобы сразу же считать запись видимой. Поэтому фактическое начало «срока годности» более не важно, проверять xmin нет смысла.

Если пометить все действительные записи с xmin = 100 как «архивные», то можно

добавить транзакцию 2^31+101 без нарушения правил видимости – теперь она увидит неизменные записи транзакции № 100 по одному признаку «архивная» и
считать номер 100 свободным для следующих транзакций.

Признаком архивной версии в Pangolin служат одновременно установленные биты committed и aborted в маске битовых флагов записи t_infomask.

Процесс «переноса в исторический архив» - выставления архивных признаков версий записей – называется заморозкой кортежей (tuple freezing).

Поскольку отдельного процесса обслуживания структур данных (подобия Oracle SMON) нет в Pangolin, обязанность выполнять заморозку возложена на средства очистки - VACUUM и процессы autovacuum. К примеру, заморозка входит в первый из блоков алгоритма VACUUM.

Важно:

замораживаемые версии записей не очищаются (это означало бы удаление 99% БД, всей истории данных);
подлежащие очистке (xmax > 0, срок годности завершен) - не замораживаются. Поскольку они будут когда-то очищены безопасным для соседних операций образом, их заморозку посчитали бессмысленной.

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

Параметр	Назначение	По умолчанию
`vacuum_freeze_min_age`	Минимальный возраст транзакции xmin, с которого версию строки можно замораживать. Версии младше этого возраста VACUUM без ключа FREEZE не замораживает. Внутренний порог - до половины autovacuum_freeze_max_age.	50 миллионов транзакций
`vacuum_freeze_table_age`	Возраст транзакции - порога заморозки таблицы pg_class.relfrozenxid, при котором очистка игнорирует карту видимости и проводит заморозку по каждому блоку таблицы (агрессивный режим). Фактический максимум для параметра равен 0.95 * autovacuum_freeze_max_age.	150 миллионов транзакций
`vacuum_multixact_freeze_min_age`	Минимальный возраст мультитранзакции, с которого идентификаторы мультитранзакций будут заменяться новыми идентификаторами транзакций или мультитранзакций. Внутренний порог - до половины autovacuum_multixact_freeze_max_age.	5 миллионов мультитранзакций
`vacuum_multixact_freeze_table_age`	Возраст транзакции - порога заморозки мультитранзакций таблицы pg_class.relminmxid, при котором игнорируется карта видимости и проводится полная (агрессивная) заморозка. Фактический максимум: 0.95% * autovacuum_multixact_freeze_max_age.	150 миллионов мультитранзакций

Третья пара параметров - пороги автоматической агрессивной заморозки таблицы autovacuum_freeze_max_age и autovacuum_multixact_freeze_max_age - относится к автоочистке для предотвращения зацикливания счетчиков транзакций и мультитранзакций. Подробнее о них – в разделе Автоочистка для предотвращения зацикливания счетчиков.

Внутренние пороги и максимумы параметров введены для того, чтобы дать шанс autovacuum или командам VACUUM исправить ситуацию по "устаревающей" таблице до того, как ей потребуется принудительная автоочистка.

В Pangolin различаются два режима заморозки, второй из которых существует для коррекции недостатка первого.

«Ленивый» режим – во время очистки

Алгоритм в общем виде:

Выбрать старейшую активную транзакцию в системе, OldestXmin. Например, если в системе активны транзакции 51 000 000, 51 000 001, 51 000 002 - выберите OldestXmin = 51 000 000.
Отступите от нее на vacuum_freeze_min_age транзакций в прошлое и получите порог заморозки: freezeLimit_txid = (OldestXmin − vacuum_freeze_min_age).

Например, для старейшей активной транзакции 51 000 000 и отступа по умолчанию vacuum_freeze_min_age = 50 000 000 транзакций порог такой: freezeLimit_txid = (51 000 000 - 50 000 000) = 1 000 000.
Если блок не помечен в карте видимости как полностью замороженный, то в цикле по записям блока, если xmin < freezeLimit_txid, установите признак заморозки.
Завершите цикл по записям.
Если заморожены все записи в блоке, то установите в карте видимости признак «полностью замороженной» страницы.

Ленивая заморозка выполняется во время обычной очистки, которая пропускает полностью видимые блоки согласно карте видимости. Целые страницы полностью видимых данных постоянно остаются без ее внимания, если не содержат устаревшие версии (dead tuples).

Второй режим заморозки периодически компенсирует этот недостаток.

«Агрессивный» режим – полная заморозка таблицы

В этом режиме выполняется полная заморозка всей таблицы, кроме тех страниц, которые уже полностью заморожены (второй бит в Карте видимости).

После полной заморозки обновляется порог заморозки таблицы - pg_class.relfrozenxid. Сюда вносится новое значение xid самой старой замороженной транзакции таблицы. Обновление поля происходит только в этом режиме (при полной заморозке), поскольку в ленивом режиме неизвестно, какие номера могли бы использоваться в пропущенных страницах.

Во время полных заморозок таблиц постоянно обновляется и порог заморозки содержащей их базы, pg_database.datfrozenxid. Представляет из себя старейший из порогов заморозки таблиц по базе.

Критерий принятия решения о полной заморозке таблицы - слишком большое «отставание» порога relfrozenxid по таблице от последней транзакции.

Примерный алгоритм:

Выберите старейшую активную транзакцию в системе, OldestXmin.
Отступите от нее на vacuum_freeze_min_age транзакций назад и получите целевой порог заморозки таблицы: freezeLimit_txid = (OldestXmin − vacuum_freeze_min_age)
Сравните фактический порог заморозки таблицы, pg_class.relfrozenxid, с целью: не старше vacuum_freeze_table_age от текущей транзакции. Если pg_class.relfrozenxid < (OldestXmin − vacuum_freeze_table_age), то в цикле по страницам, не отмеченным в карте видимости как «полностью замороженные» и цикле по версиям записей, если xmin < freezeLimit_txid, то установите признак заморозки.
Завершите цикл по записям.
Если удалось заморозить все записи блока, то установите в карте видимости признак «полностью заморожен».
Завершите цикл по страницам.
Обновите pg_class.relfrozenxid – поместите сюда номер старейшего из замороженных идентификаторов (скорее всего, freezeLimit_txid).
Если минимальный из pg_class.relfrozenxid в результате изменился (сейчас удалось заморозить старейшую таблицу) - обновите pg_database.datfrozenxid.
По возможности, удалите из clog страницы и файлы старше тех, которые содержат pg_database.datfrozenxid.

Поскольку в Ленивом режиме пороги заморозки таблиц и базы не обновляются, время агрессивной заморозки наступает примерно раз в (vacuum_freeze_table_age - vacuum_freeze_min_age) транзакций.

По умолчанию – примерно раз в 100 млн транзакций.

Заморозку можно принудительно выполнить в агрессивном режиме: VACUUM FREEZE. На время этой операции пороги vacuum_freeze_min_age и vacuum_freeze_table_age будут установлены в 0, а в Карте видимости таблицы не будут учитываться признаки видимости страниц – только признаки заморозки.

Использование карты видимости можно отключить и полностью: VACUUM (DISABLE_PAGE_SKIPPING) t. Такой режим полезен для пересоздания разрушенной карты.

Внимание!

В любом из режимов заморозки нельзя заморозить версию записи, открытую любой из активных транзакций по всему кластеру баз Pangolin (серверу).

Примечание:

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

функция vacuum_rel();

функция table_relation_vacuum().

«Вежливый» режим VACUUM

VACUUM по крупной, значительно измененной таблице - длительная операция с плотным использованием дисковых и процессорных ресурсов. Есть возможность запустить очистку так, чтобы она периодически приостанавливалась, пропуская к ресурсам пользовательские сессии.

Этот режим включается установкой параметра vacuum_cost_delay в ненулевое значение. Параметр задает продолжительность паузы после обработки очередной порции данных. Размерность - астрономическое время. Если единица измерения не указана. то понимается как миллисекунды. По умолчанию - 0 (паузы выключены). Теоретически, здесь можно указывать и доли миллисекунд, практически - не все платформы обеспечивают точный микросекундный таймер.

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

Параметр	Назначение	По умолчанию
`vacuum_cost_page_hit`	Стоимость просмотра 1 блока, обнаруженного в кэше shared_buffers	1
`vacuum_cost_page_miss`	Стоимость просмотра 1 блока при чтении его с диска	10
`vacuum_cost_page_dirty`	Стоимость изменения 1 блока, включая вытеснение его из кэша на диск	20
`vacuum_cost_limit`	Накопленная стоимость операций, после которой выдерживается пауза	200

Для примера рассчитаем максимальную скорость VACUUM в настройках по умолчанию при vacuum_cost_delay = 2ms.

Максимальный размер порции будет достигнут при просмотре таблицы в кэше и составит (200 баллов/1 балл/блок ) = 200 блоков по 8 КБ = 1600 КБ. Пусть обработка порции занимает пренебрежимо малое время, тогда при паузе в 2 мс за секунду будет обработано (1000 мс/2 мс) = 500 порций, что составит 1600 КБ * 500 = 800000 КБ, примерно 781 МБ.

Если все блоки придется прочитать с диска, то порция сократится до 160 КБ (200 баллов/10 баллов за блок), а максимальная скорость снизится до 78.1 МБ в секунду.

Изменение тех блоков, которые уже считаны с диска в память, будет проходить со скоростью до 39.05 МБ/с.

Ручные команды VACUUM очень редко выполняются в "вежливом" режиме, а вот автоматическая очистка работает в нем почти всегда (см. ниже).

Блокировки во время VACUUM

На уровне таблицы VACUUM без ключа FULL ограничивается блокировкой уровня SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock). В этом режиме таблица защищена от параллельного изменения схемы и запуска любого другого процесса VACUUM. Не допускаются параллельные CREATE INDEX (с CONCURRENTLY и без), ANALYZE, CREATE STATISTICS, COMMENT, REINDEX CONCURRENTLY, CREATE TRIGGER, большинство ALTER TABLE, REFRESH MATERIALIZED VIEW (с CONCURRENTLY и без), DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL.

Подробнее - в документации, раздел «13.3. Явные блокировки».

Сессия, которая пытается выполнить DDL по таблице во время VACUUM, будет на время VACUUM заблокирована. За блокированным DDL постепенно будут вставать в очередь (блокироваться) и остальные сессии, пытающиеся выполнить по той же таблице DML. Инцидент может развиваться вплоть до переполнения пулов соединений и приостановки приложения. В отличие от ручного VACUUM, autovacuum избегает подобных инцидентов автоматически (подробнее - ниже).

Дополнительно, каждая страница (блок объекта в памяти) на время очистки и заморозки закрывается на легковесную блокировку (LWLock) типа content_lock в исключительном режиме. Эта блокировка гарантирует согласованность служебных структур внутри блока: xmin, xmax и флагов infomask записей (включая признак заморозки), порядка записей, списка указателей на них. Поэтому VACUUM (и очистка, и заморозка) конфликтует на уровне страницы с UPDATE (включая внутриблоковые обновления HOT) и INSERT, запрещая параллельные изменения страницы до завершения ее обработки. Это обычно не вызывает видимых проблем.

Демон автоочистки autovacuum

Постоянное повторное выполнение команды VACUUM по всем таблицам - не лучший способ обслуживать СУБД. Поэтому разработчики для текущего обслуживания объектов добавили в PostgreSQL набор фоновых процессов autovacuum.

Эти процессы запускаются при запуске сервера, когда установлен параметр autovacuum = on. Запускается координатор (launcher) и максимум autovacuum_max_workers обработчиков, каждый из которых для обслуживания выделенной ему таблицы подключается к одной из баз кластера. Если баз больше, чем обработчиков, то свободный обработчик будет переключаться на базу, в которой находится обслуживаемая таблица.

После запуска координатор постоянно пробуждается по таймеру, который настраивается параметром autovacuum_naptime и составляет (autovacuum_naptime/кол-во баз в кластере). По умолчанию autovacuum_naptime установлен в 1 минуту, при 3 базах таймер будет установлен на 60/3 = 20 секунд.

Процесс autovacuum обслуживает только те таблицы, которые существенно изменились со времени последнего обслуживания. Для этого ему необходимы данные о количестве модификаций таблицы, которые собирает другой фоновый процесс - сборщик статистики. Чтобы эта информация непрерывно собиралась, должен быть включен параметр track_counts = on. Без сборщика информации autovacuum не сможет выбирать среди таблиц кандидатуры для обработки.

Для autovacuum в Pangolin настраивается при установке объем буфера в памяти – параметр сервера autovacuum_work_mem. При инсталляции параметр будет установлен в 1/48 от объема физической памяти сервера. Если параметр установить в -1, то будет использовано значение параметра maintenance_work_mem.

Отладка процесса включается параметром log_autovacuum_min_duration. Если autovacuum обрабатывает одну из таблиц дольше указанного времени, то в протокол сервера попадает подробная информация, которая помогает настроить параметры по реальной нагрузке.

На autovacuum возложены те же обязанности, что и на команду VACUUM: очистка полностью устаревших версий записей в таблицах и индексах, заморозка записей "доисторических" транзакций, поддержка карт FSM и VM, обновление статистики ANALYZE, поддержка массива статусов clog в памяти и на диске.

Демон автоочистки проверяет статистику баз и таблиц через заданный интервал времени и выдает процессам-обработчикам команды VACUUM и ANALYZE на таблицы.

Хотя сам autovacuum и запускается по таймеру, было бы неверно на каждый запуск повторять в нем очистку и обновление статистики при малейших изменениях данных. Чтобы избежать слишком частых обращений к таблице, настраиваются пороги минимального количества изменений со времени последней очистки, после которого таблица поступает на следующую очистку. Применяется одновременно абсолютный и относительный порог, который может быть настроен по VACUUM и по ANALYZE раздельно.

Из-за специфики MVCC, по VACUUM пороги задаются отдельно для накопления изменений (UPDATE + DELETE) и отдельно – для накопления INSERT. Дело здесь в том, что таблица, по которой выполняются только INSERT, формально вообще не нуждается в очистке, поскольку не содержит ни одной устаревшей версии записей. Однако, она нуждается в заморозке даже больше остальных таблиц (все действующие записи должны быть заморожены рано или поздно). Поскольку автоматическая заморозка - часть VACUUM/autovacuum, отдельно от очистки выполнить ее не получится – она выполняется в процессе autovacuum. Только не с каждой очисткой, а несколько реже.

Параметры сервера:

Автоматическая операция - характер изменений таблицы	Абсолютный порог - значение по умолчанию	Относительный порог, доля от таблицы - значение по умолчанию
Очистка, VACUUM UPDATE + DELETE	autovacuum_vacuum_threshold 50 записей	autovacuum_vacuum_scale_factor 0.2 (20% общего числа записей таблицы)
Очистка, VACUUM INSERT	autovacuum_vacuum_insert_threshold 1000 записей	autovacuum_vacuum_insert_scale_factor 0.2 (20% таблицы)
Обновление статистики, ANALYZE UPDATE + DELETE + INSERT	autovacuum_analyze_threshold 50 записей	autovacuum_analyze_scale_factor 0.1 (10% таблицы)

Например, таблицу из 500 записей настроенный по умолчанию autovacuum сможет выбрать для:

очистки, VACUUM: при изменении от 50 + 0.2 * 500 = 150 записей или при добавлении от 1000 + 0.2 * 500 = 1100 записей;
обновления статистики, ANALYZE: при изменении или добавлении от 50 + 0.1 * 500 = 100 записей.

Эти параметры могут быть настроены только на уровне сервера (postgresql.conf для одиночного сервера, DCS и postgres.yml - для кластера). Применяются для всех баз кластера. Однако, возможна их тонкая настройка для каждой из таблиц. Подробности см. в разделе «Настройки и правила перекрытия параметров».

Просмотреть счетчики модификаций таблиц с момента запуска сервера или сброса статистики можно так:

SELECT n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as "deletes", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples" FROM pg_stat_user_tables
WHERE schemaname = 'sch1';

Имеет смысл для небольших, часто модифицируемых таблиц установить более высокий абсолютный порог и поднять относительный, чтобы не повторять очистку и сбор статистики слишком часто. А для объемных, редко модифицируемых - отказаться от относительного порога (обнулить его), использовать только абсолютный порог порядка нескольких сотен тысяч записей.

Если бы автоматическая очистка выполнялась только по порогам количества модификаций, то в некоторых таблицах после завершения фазы активных изменений могли бы накопиться версии записей, относящиеся к очень старым, но еще не замороженным транзакциям. Содержание таких версий в таблице не позволяло бы освободить идентификаторы транзакций или мультитранзакций, могло бы повлечь «раздувание» массивов clog/multixact и постепенно привести к обороту счетчика. Подобные инциденты предотвращает автоматическая заморозка для предотвращения зацикливания (см. раздел «Автоочистка для предотвращения зацикливания счетчиков». В этом же разделе подробно описаны параметры autovacuum_freeze_max_age, autovacuum_multixact_freeze_max_age – пороги этой заморозки).

Блокировки во время autovacuum

На уровне таблицы и страниц объектов блокировки autovacuum – как у VACUUM без ключа FULL.

Есть одно важное отличие от VACUUM: autovacuum автоматически предотвращает инциденты по исключительной блокировке таблицы. Для этого процессы autovacuum непрерывно проверяют, не оказались ли они причиной блокировки любой из пользовательских сессий. Если за ними занял очередь на блокировке хотя бы один процесс, то autovacuum по заблокированной таблице немедленно отменяется. Побочный эффект: приложение, постоянно выполняющее DDL между блоками транзакционной нагрузки, может постоянно прерывать autovacuum до завершения обработки затронутых DDL таблиц.

«Вежливый» режим autovacuum

Фоновую очистку почти всегда необходимо выполнять так, чтобы она периодически приостанавливалась, пропуская пользовательские сессии к физическим ресурсам.

Здесь «вежливый» режим работает так же, как редко применяемый «вежливый» режим команды VACUUM. Использует те же баллы относительной стоимости операций обработки блока vacuum_cost_page_hit, vacuum_cost_page_miss и vacuum_cost_page_dirty - см. таблицу параметров.

Объем порции и пауза после ее обработки могут быть настроены для VACUUM и autovacuum как раздельно, так и одновременно. Параметры:

Параметр	Назначение	По умолчанию	Значение при -1
`autovacuum_vacuum_cost_limit`	Накопленная стоимость операций, после которой выдерживается пауза	-1	vacuum_cost_limit
`autovacuum_vacuum_cost_delay`	Продолжительность паузы после обработки очередной порции данных	2ms	vacuum_cost_delay

Здесь, если параметр autovacuum установлен в значение -1, то в качестве его значения применяется соответствующий параметр VACUUM из столбца Значение при -1.

«Вежливый» режим - это постоянный, ожидаемый режим для автоматической очистки.

Скорость автоматической очистки по умолчанию при обработке таблицы в памяти - до 781 МБ/с, при чтении с диска - до 78.1 МБ/с, при изменении данных - до 39 МБ/с (расчеты - в том же описании для VACUUM). Часто полностью кэшированная таблица обслуживается очень быстро, а вот скорость обработки значительно измененной таблицы на диске оставляет желать лучшего. Если это так, лучше пересмотреть баллы стоимости операций и/или размер порции по фактической пропускной способности диска. Настройки по умолчанию лучше описывают шпиндельные HDD, а на SSD или Flash стоит понизить стоимости page_miss и page_dirty минимум в 2-3 раза и/или увеличить порцию, например, до размера физической страницы контроллера памяти.

Особенность параллельного «вежливого» autovacuum

Когда запущено несколько параллельных обработчиков autovacuum, стоимость порции операций autovacuum_vacuum_cost_limit распределяется пропорционально среди всех работающих процессов. Сумма ограничений всех процессов не превосходит заданный предел. А вот паузы cost_delay действуют в каждом процессе без изменений. Поэтому после увеличения мощности autovacuum с повышением параметра autovacuum_max_workers можно получить парадоксальную ситуацию: обработчиков запущено больше и все они активны, но большие таблицы обрабатываются существенно дольше.

Дело в том, что autovacuum при увеличении количества процессов кратно понижает скорость каждого из них, а таблицы между потоками не передаются.

Чтобы получить ожидаемое поведение, следует пропорционально увеличивать autovacuum_vacuum_cost_limit вслед за autovacuum_max_workers.

При увеличении autovacuum_max_workers следует учесть, что буфер в памяти размером autovacuum_work_mem будет выделяться каждому из процессов. Этот объем между процессами не делится, поэтому на сервере может вырасти использование памяти.

Автоочистка для предотвращения зацикливания счетчиков

Сервер автоматически предупреждает развитие ситуации, которая могла бы привести к обороту (зацикливанию) счетчика транзакций. Таблицы с истекающим максимальным «сроком хранения» записей будут принудительно заморожены, даже если автоочистка выключена на уровне сервера или таблицы. Пороги здесь измеряются в абсолютных единицах счетчиков транзакций:

Параметр	Назначение	По умолчанию
`autovacuum_freeze_max_age`	Максимальный возраст среди транзакций таблицы, pg_class.relfrozenxid	200 миллионов транзакций
`autovacuum_multixact_freeze_max_age`	Максимальный возраст среди мультитранзакций таблицы, pg_class.relminmxid	400 миллионов мультитранзакций

Эти два вида принудительной автоочистки будут запущены postmaster в любом случае. Для предотвращения инцидента сервер в этом случае не подчинится ни параметру autovacuum = off, ни соответствующим точным настройкам таблицы/TOAST.

Таблица на время принудительной заморозки будет полностью заблокирована для чтения и записи.

Оба параметра могут быть настроены только на уровне сервера для всех баз кластера. Их также можно перекрыть на уровне каждой из таблиц в параметрах хранения.

Пороги по умолчанию выбраны достаточно «тесными» - менее 10% и 20% от общего пространства транзакций и мультитранзакций соответственно, 231 значений. Это сделано для того, чтобы урезать хранимый в памяти и на диске массив статусов транзакций clog. Производительность каждой операции поиска в этом массиве зависит от его объема, а эти операции выполняются при каждом выяснении видимости записи по незавершенным транзакциям.

Несмотря на важную цель этой очистки и максимальный уровень блокировки затронутой таблицы, принудительная заморозка подчиняется настройкам «вежливого» режима autovacuum всегда, вплоть до версий ядра PostgreSQL 15 (Pangolin 6.0+).

Команды VACUUM FULL и CLUSTER

Команда VACUUM FULL удаляет все бесполезные записи (dead tuples) и полностью дефрагментирует действующие записи по всем файлам, где есть данные. Она выбирает действительное содержимое таблицы в новые файлы на диске, не содержащие ничего лишнего, что позволяет возвратить неиспользованное пространство операционной системе.

Несмотря на то, что алгоритм VACUUM FULL по коду имеет много общего с VACUUM (общие параметры и настройки, права, код работы с транзакциями), они делают с данными и объектами совершенно разные вещи.

Используя VACUUM FULL, следует всегда помнить о двух моментах:

никто не может ни прочитать, ни записать данные таблицы в продолжение всего времени работы VACUUM FULL;
на время работы команды требуется временное дисковое пространство, вплоть до полного объема таблицы с индексами, с учетом размещения объектов по табличным пространствам.

Алгоритм VACUUM FULL в общем виде:

В цикле по каждой из таблиц:
1. Получите на таблицу блокировку AccessExclusiveLock.
2. Создайте новый, промежуточный файл данных таблицы (+ TOAST, если требуется).
3. В цикле по каждой из действующих записей таблицы:
  1. Скопируйте запись в промежуточный файл таблицы.
  2. Если необходимо, заморозьте запись.
4. Завершите цикл по записям.
5. В описании таблицы обменяйте местами файлы данных, ранее существующие и промежуточные.
6. Перестройте все индексы на таблице.
7. Обновите карту свободного пространства и карту видимости по таблице.
8. Обновите статистику и некоторые служебные объекты.
9. Удалите старые файлы таблицы.
10. Освободите блокировку AccessExclusiveLock.
Завершите цикл по таблицам.
Если возможно, удалите старые файлы и страницы clog.

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

VACUUM FULL всегда выполняет агрессивную заморозку записей. Можно считать, что на время ее работы параметры vacuum_freeze_min_age и vacuum_freeze_table_age установлены в 0. Как если бы в обычной команде VACUUM использовался ключ FREEZE.

VACUUM FULL всегда сопровождается полной переиндексацией таблицы, поэтому параметр INDEX_CLEANUP тоже игнорируется. Всегда пересоздается и таблица хранения сверхбольших атрибутов TOAST.

VACUUM FULL не может выполняться в параллельном режиме.

VACUUM FULL организует манипуляции с каталогами по каждой из таблиц в отдельную транзакцию и требует выделения стольких транзакций, сколько таблиц очищает.

Длительный VACUUM FULL способен, как и любая длительная транзакция, вызвать инцидент с переходом сервера в состояние «только для чтения» по исчерпанию номеров транзакций. См. раздел «Автоочистка для предотвращения зацикливания счетчиков».

Внимание!

Будьте осторожны - планируйте VACUUM FULL по объемным таблицам на время минимальной транзакционной нагрузки!

Команду CLUSTER можно понимать как вариант VACUUM FULL, который в процессе переноса данных дополнительно упорядочивает таблицу в порядке сортировки указанного индекса, что повышает производительность запросов с выборкой и сортировкой по ключу индекса. Точнее, это VACUUM FULL реализован как вариант CLUSTER с отключенным упорядочиванием данных.

Подходит для:

очистки таблицы после постоянного изменения ее назначения в архитектуре приложения - например, переноса большей части данных из таблицы в архивное хранилище;
очистки таблицы, раздутой в результате пика аномальной нагрузки и/или существенного отставания autovacuum;
полной дефрагментации таблицы;
принудительного применения изменений параметров хранения таблицы к хранимым в таблице данным.

Не подходит для:

периодической, регламентной очистки устаревших данных - рекомендуется для постоянной работы настроить autovacuum, а в масштабных трансформациях данных сразу выполнять VACUUM, лучше с ключом ANALYZE;
предотвращения оборота счетчика транзакций (wraparound) - VACUUM FULL будет отнимать почти закончившиеся номера транзакций, здесь рекомендуется VACUUM по проблемным таблицам, можно с агрессивной заморозкой (FREEZE).

Блокировка во время VACUUM FULL

На уровне таблицы VACUUM FULL работает с постоянной блокировкой наивысшего уровня ACCESS EXCLUSIVE (AccessExclusiveLock). В этом режиме недопустимы любые параллельные операции с таблицей.

Этот уровень блокировки - единственный, на котором заблокирован даже оператор SELECT без FOR UPDATE/SHARE.

Подробнее - в документации, 13.3. «Явные блокировки».

Настройки и правила перекрытия параметров

Внимание!

Параметры сервера maintenance_work_mem и autovacuum_work_mem очень важны для очистки. От них напрямую зависит количество циклов операции очистки при большом объеме модификаций таблицы.

В Pangolin при установке автоматически применяются значения:

maintenance_work_mem = (Объем физической памяти сервера)/48;

autovacuum_work_mem = (Объем физической памяти сервера)/24.

Параметры одиночной команды VACUUM имеют контекст user. Это означает, что они без перезапуска могут быть настроены на уровне сервера, базы, роли, сессии. Прав суперпользователя не требуется. Параметры описаны в разделах стандартной документации:

Если переустанавливать параметры сессии командой SET, то изменения будут сразу использованы в следующих командах VACUUM той же сессии.

Параметры автоматической очистки описаны в разделе «20.10. Автоматическая очистка» документации PostgreSQL. Они имеют контекст postmaster или sighup. Для перенастройки требуются права АБД. Применение нового значения потребует, в зависимости от контекста, перезапуска сервера или pg_reload_conf().

Большинство параметров автоочистки может быть перекрыто на уровне таблицы, а параметр уровня таблицы может быть перекрыт на уровне ее TOAST-таблицы. Для сохранения настроек уровня таблиц используются параметры хранения, которые настраиваются по команде ALTER TABLE .. SET (параметр = значение). Потребуются права владельца таблицы или суперпользователя. Список перекрывающих параметров есть в документации, раздел Параметры хранения описания CREATE TABLE.

Правила перекрытия параметров автоочистки:

Параметр autovacuum	Параметр таблицы	Параметр TOAST	Примечания
`autovacuum`	autovacuum_enabled	toast.autovacuum_enabled	Принудительная заморозка для предотвращения зацикливания выполняется всегда
`autovacuum_vacuum_threshold`	Да	Да
`autovacuum_vacuum_scale_factor`	Да	Да
`autovacuum_vacuum_insert_threshold`	Да	Да	-1 в параметрах таблицы или TOAST отключает очистку при накоплении INSERT
`autovacuum_vacuum_insert_scale_factor`	Да	Да
`autovacuum_analyze_threshold`	Да	Нет
`autovacuum_analyze_scale_factor`	Да	Нет
`autovacuum_vacuum_cost_delay`	Да	Да
`autovacuum_vacuum_cost_limit`	Да	Да
`vacuum_freeze_min_age`	autovacuum_freeze_min_age	toast.autovacuum_freeze_min_age	Порог: 1/2 системного autovacuum_freeze_max_age
`autovacuum_freeze_max_age`	Да	Да	Порог: системный autovacuum_freeze_max_age
`vacuum_freeze_table_age`	autovacuum_freeze_table_age	toast.autovacuum_freeze_table_age
`vacuum_multixact_freeze_min_age`	autovacuum_multixact_freeze_min_age	toast.autovacuum_multixact_freeze_min_age	Порог: 1/2 системного autovacuum_multixact_freeze_max_age
`autovacuum_multixact_freeze_max_age`	Да	Да	Порог: системный autovacuum_multixact_freeze_max_age
`vacuum_multixact_freeze_table_age`	autovacuum_multixact_freeze_table_age	toast.autovacuum_multixact_freeze_table_age
`log_autovacuum_min_duration`	Да	Да
`autovacuum_max_workers`	Нет	Нет
`autovacuum_naptime`	Нет	Нет
`autovacuum_work_mem`	Нет	Нет
`vacuum_defer_cleanup_age`	Нет	Нет
`vacuum_cleanup_index_scale_factor`	Нет	Нет	Устаревший параметр, в Pangolin недействителен.

«Да» в ячейке означает, что параметр для таблицы называется так же, как параметр autovacuum, а для TOAST-таблицы – toast.<параметр>. «Нет» - параметр не может быть перекрыт. Иначе явно указано название параметра.

Например, системный параметр autovacuum_vacuum_threshold может быть настроен с перекрытием на уровне таблицы и TOAST-таблицы вот так:

First_db=# ALTER TABLE sch1.table1 SET ( autovacuum_vacuum_threshold = 150, toast.autovacuum_vacuum_threshold = 25 );
ALTER TABLE

Просмотр настройки параметров таблицы:

First_db=# select relnamespace::regnamespace, relname, reloptions from pg_class
where oid in (  'sch1.table1'::regclass,
( select reltoastrelid from pg_class where oid = 'sch1.table1'::regclass::oid )
);
relnamespace |    relname     |            reloptions
-------------+----------------+-----------------------------------
sch1         | table1         | {autovacuum_vacuum_threshold=150}
pg_toast     | pg_toast_23080 | {autovacuum_vacuum_threshold=25}
(2 rows)
First_db=# \d+ sch1.table1
Table "sch1.table1"
Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
-------+---------+-----------+----------+---------+----------+--------------+-------------
f1     | integer |           |          |         | plain    |              |
f2     | text    |           |          |         | extended |              |
Indexes:
"table1_index_f1" UNIQUE, btree (f1)
Access method: heap
Options: autovacuum_vacuum_threshold=150, toast.autovacuum_vacuum_threshold=25

Два параметра таблицы, vacuum_index_cleanup и vacuum_truncate, позволяют выключить для таблицы необязательные блоки алгоритма VACUUM - очистку индексов и усечение файла таблицы на последний занятый блок. По умолчанию – на новой таблице – оба параметра установлены и оба действия разрешены.

Таблица по умолчанию	TOAST-таблица по умолчанию	Примечание	Ключ VACUUM для перекрытия
vacuum_index_cleanup true	toast.vacuum_index_cleanup true	Выключение имеет смысл при большом объеме/кол-ве индексов.	INDEX_CLEANUP
vacuum_truncate true	toast.vacuum_truncate true	Выключение имеет смысл при очень высокой конкуренции в нагрузке, чтобы исключить блокировку AccessExclusiveLock при усечении файла отношения.	TRUNCATE

Если необязательные операции для части таблиц выключены, то команда VACUUM с ключами позволяет проигнорировать тонкие настройки и принудительно выполнить операции. К примеру, во время окна обслуживания или пониженной нагрузки.

Просмотр параметров хранения на уровне таблиц:

First_db=# SELECT relnamespace::regnamespace, relname, reloptions FROM pg_class WHERE reloptions is not null;
relnamespace |     relname      |            reloptions
-------------+------------------+-----------------------------------
pg_catalog   | pg_stats         | {security_barrier=true}
pg_catalog   | pg_stats_ext     | {security_barrier=true}
sch1         | table1           | {autovacuum_vacuum_threshold=150}
public       | pgbench_accounts | {fillfactor=100}
public       | pgbench_branches | {fillfactor=100}
public       | pgbench_tellers  | {fillfactor=100}
pg_toast     | pg_toast_23080   | {autovacuum_vacuum_threshold=25}
(7 rows)

Мониторинг и разрешение известных проблем

Упреждающий мониторинг

В промышленной среде рекомендуется отслеживать:

количество активных процессов autovacuum, по операциям принудительной автоочистки - отдельно;
пороги заморозки баз в pg_database: datfrozenxid, datminmxid и:
- количество транзакций до начала принудительной автоочистки,
- количество транзакций до переполнения счетчиков и остановки СУБД;
количество операций VACUUM и autovacuum, выполненных в системе;
длительные и бездействующие транзакции.

Детальную картину по операциям, которые выполняются прямо сейчас, показывает

по операциям VACUUM/autovacuum – представление 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.

Обычные проблемы и их устранение

Autovacuum не запускается периодически по большой таблице

Симптомы: таблица содержит очень много записей (сотни миллионов). Изменяется постоянно, но небольшими порциями. В выборке видно, что последняя очистка выполнялась очень давно, с тех пор прошло много дней:

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).

Autovacuum (или VACUUM) работает слишком медленно

Симптомы: операция autovacuum или VACUUM по одной и той же таблице продолжается десятки минут, иногда – часы. В pg_stat_activity видны запущенные и постоянно активные процессы autovacuum, в pg_stat_progress_vacuum видна длительная обработка одной таблицы с нарастанием счетчиков.

Возможные причины:

Autovacuum слишком «вежлив», не использует ресурсы в полной мере.
Слишком мало процессов-обработчиков autovacuum.
Не хватает кэша разделяемых буферов.
Мало памяти 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).

Ни autovacuum, ни VACUUM не очищают устаревшие записи

Симптомы: таблица раздувается, несмотря на постоянно активный autovacuum.

Возможные причины:

Очистка подчиняется правилам MVCC и сохраняет нужные для других транзакций/сессий/других объектов версии записей.
Процесс autovacuum постоянно прерывается, отменяется и перезапускается заново.

Решения:

Найдите и удалите те проблемные объекты 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;
```
Если Patroni забыли в Maintenance mode, то переведите кластер в обычный режим (patronictl resume). Если слоты к Patroni не относятся, то используйте 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.

Предупреждение повторных инцидентов

Исключите длительные и бездействующие транзакции на стороне приложений.

Настройте скорость autovacuum на 50 .. 70% от максимальной скорости. Максимальную скорость можно определить, выполняя VACUUM (DISABLE_PAGE_SKIPPING, VERBOSE) по специально созданной большой таблице.

На стороне приложений пересмотрите код трансформаций объемных массивов данных так, чтобы трансформация завершалась выделенной командой VACUUM, а лучше – VACUUM ANALYZE.

Подсказки для настройки параметров autovacuum

Параметр	Рекомендуется
Autovacuum срабатывает недостаточно часто
`autovacuum_vacuum_scale_factor`	Снизить порог, чтобы чаще запускать очистку. Полезно для больших таблиц с большим количеством обновлений/удалений
`autovacuum_vacuum_insert_scale_factor`	Снизить порог, чтобы чаще запускать очистку для больших таблиц с большим количеством вставок
Очистка происходит слишком медленно
`autovacuum_vacuum_cost_delay`	Уменьшить продолжительность паузы «вежливого» режима, чтобы ускорить очистку
`autovacuum_vacuum_cost_limit`	Увеличить объем порции «вежливого» режима до наступления паузы, уменьшая частоту пауз и ускоряя очистку
`autovacuum_max_workers`	Увеличить, чтобы запускать больше параллельных обработчиков автоочистки при необходимости
`shared_buffers`	Увеличить объем кэша в разделяемой памяти, если есть ресурсы. Кэширование блоков ускорит очистку
`autovacuum_work_mem`	Увеличить, чтобы расширить в каждом обработчике autovacuum буфер в памяти для хранения списка устаревших записей. -1 – использовать maintenance_work_mem
`maintenance_work_mem`	Задает размер буфера для списка устаревших записей, если autovacuum_work_mem = -1
`max_parallel_maintenance_workers`	Увеличить количество индексов, одновременно обрабатываемых командой VACUUM в параллельном режиме (не autovacuum)
Очистка не очищает устаревшие записи
`statement_timeout`	Настроить, чтобы автоматически завершать длительно выполняемые запросы по таймауту
`idle_in_transaction_session_timeout`	Настроить, чтобы автоматически завершать по таймауту бездействующие сессии с открытой транзакцией
`hot_standby_feedback`	В Pangolin по умолчанию «on» - реплика отправляет лидеру отзывы о выполняемых запросах, обновляет отметку для удержания версий. Если реплика не относится к Patroni и часто отстает, можно рассмотреть выключение hot_ standby_ feedback на этой реплике
`vacuum_defer_cleanup_age`	Настроить на лидере, чтобы отложить очистку версий строк до тех пор, пока не пройдет указанное количество транзакций. Позволяет увеличить окно выполнения без возникновения конфликтов для запросов на реплике при отключенном hot_ standby_ feedback
`old_snapshot_threshold`	Настроить, чтобы разрешить очистку версий записей, которые могли бы понадобиться слишком старым снимкам (старше указанного времени)

Особенности Pangolin в сравнении с Oracle Database

Счетчик транзакций

Емкость счетчика номеров-идентификаторов транзакций в Pangolin - всего 32 бита, поэтому количество номеров отслеживаемых транзакций ограничено. Доступно примерно четыре миллиарда значений, порядка двух миллиардов транзакций до текущего номера и порядка двух миллиардов - после. Если не «переносить в историю» активности транзакций, то сервер примет примерно два миллиарда транзакций и перейдет в READ ONLY по всему кластеру баз.

Процесс «перехода в историю» транзакций - freezing - описан в разделе Заморозка (перенос в историю) транзакций. Его цель – высвободить номер транзакции, делая старый идентификатор доступным для новых транзакций.

Не получится «заморозить» любую из транзакций в «горизонте событий». Пока есть хотя бы одна транзакция старше рассматриваемой, требуется в остальных транзакциях отслеживать видимость данных, измененных рассматриваемой транзакцией. Старшие транзакции должны видеть данные «до» нее, а младшие - «после». Для отслеживания видимости данных номера должны сохраняться при всех транзакциях горизонта вне зависимости от статуса – активны они, бездействуют, приняты или отменены.

Поскольку номера транзакций - ценный ресурс, рекомендуется всегда избегать длительных и бездействующих транзакций. Крайне рекомендуется исключить длительные транзакции в те пиковые моменты, когда выполняется большое количество транзакций, быстро меняющих данные. Самая старая из активных транзакций, пока она существует, удерживает нижнюю границу горизонта событий базы, «растягивает» пространство используемых идентификаторов. Ни одна из транзакций младше нее не может «уйти в историю» (быть заморожена). Накопление двух миллиардов не замороженных транзакций приводит к переполнению счетчика транзакций («оборот» или wraparound). По базе в состоянии wraparound невозможно добавить транзакцию, она доступна только на чтение.

Борьба с продолжительными транзакциями

В Pangolin и PostgreSQL нет знакомого по Oracle отдельного пространства для хранения истории модификаций, UNDO tablespace. Механизмы, похожие на Automatic Undo Management (AUM) - намного сложнее. Единый параметр, подобный undo_retention – есть, но действует он по-другому.

Зато в руках АБД Pangolin есть четыре параметра, которые помогают бороться с длительными и бездействующими транзакциями, а также с длительными запросами. Помогают они в том случае, когда разработка приложения помочь не в состоянии.

old_snapshot_threshold - максимальное астрономическое время удержания снимка операции, в часах, минутах, секундах. Если снимок существует дольше указанного времени, то сервер считает приемлемой очистку тех версий истории записей, которые могли бы понадобиться этому снимку. Если длительная транзакция все-таки попробует к ним обратиться, то она получит ошибку snapshot too old. Причины и эффект этой ошибки будут такими же, как у знакомой по Oracle ORA-01555. Поэтому параметр очень похож на undo_retention - и по размерности (время), и по эффектам.
idle_in_transaction_session_timeout - максимальная продолжительность бездействия транзакции. По истечению заданного времени транзакция будет отменена.
statement_timeout - максимальная длительность выполнения оператора, при превышении которой оператор будет прерван.
lock_timeout - максимальная длительность ожидания получения блокировки таблицы, индекса, строки или другого объекта базы данных, при превышении которой оператор будет прерван.

Индексы

Индексы в Pangolin не хранят информацию о сроке действия и видимости записей таблицы. В них записаны равноправные ссылки на каждую из версий записи, хранимых в таблице. Среди этих версий есть как действующие, так и устаревшие, причем различить их можно только при посещении таблицы. Частный случай - когда вся страница (блок) таблицы отмечена в карте видимости таблицы как полностью видимая, тогда по ссылкам на эту страницу все записи точно действительны. Эта особенность используется для строгого индексного сканирования таблицы.

А где же Flashback?

Архитектурное решение, выбранное в PostgreSQL и Pangolin для хранения снимков операций, отодвигает на границы возможного реализации знакомых по Oracle технологий семейства Flashback. Эти технологии позволяют осмотреть историю версий данных по транзакциям (Flashback Transaction Query), получить снимок произвольно выбранной по номеру транзакции и выполнить "с ее точки зрения" произвольный запрос (Flashback Query), восстановить историческое состояние на любую транзакцию для таблицы (Flashback Table) и для БД (Flashback Database). Здесь, даже если история версий данных на последний миллиард транзакций еще доступна, крайне сложно осмотреть массив снимков (они хранятся вместе с транзакциями в процессах-сессиях) и сконструировать снимок данных с точки зрения произвольной транзакции (не хранится история списков активных транзакций на произвольный момент в прошлом).

В широком доступе реализации возможностей Flashback для PostgreSQL неизвестны.

Есть два исключения из общего правила.

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

Однако, в обоих случаях копируются заранее подготовленные снимки. Появление возможности для получения снимков с точки зрения произвольных транзакций – возможное направление для развития PostgreSQL.

Идентификация транзакций

В отличие от Oracle, Pangolin не имеет подобного SCN единого идентификатора транзакции, действительного одновременно в памяти экземпляра, в объектах на диске и в журнале повтора (предзаписи). Здесь номер транзакции XID никак не связан с адресом изменений транзакции в журнале WAL. Указатель на позицию в журнале WAL - другой идентификатор, Log Sequence Number или LSN, он же – последовательный номер транзакции в журнале. Искусственно связать идентификатор xid (транзакция MVCC) с LSN (запись журнала WAL) можно только при запуске логической репликации.

XID не связан и с астрономическим временем. Постоянной таблицы соответствия между XID и временем в секундах тоже нет.

Есть параметр сервера track_commit_timestamp, который включает запись астрономического времени. Однако, записывается здесь только момент фиксации транзакции. См. раздел «20.6. Репликация» в стандартной документации.

Только при включенном track_commit_timestamp можно использовать аналоги функций SCN_TO_TIMESTAMP() и TIMESTAMP_TO_SCN():

pg_xact_commit_timestamp ( xid ) → timestamp with time zone – выдает время фиксации транзакции.
pg_last_committed_xact () → record ( xid xid, timestamp timestamp with time zone ) – выдает идентификатор и время фиксации транзакции, зафиксированной последней.

Вторая функция может показать только последнюю зафиксированную транзакцию – произвольный момент времени не указать.

Устаревшие параметры

Параметр vacuum_cleanup_index_scale_factor появился в ядре PostgreSQL 12 для того, чтобы при текущих очистках таблицы откладывать ресурсоемкую операцию очистки индексов до модификации определенной доли записей в процентах. Отслеживание модификаций таблицы оказалось плохо совместимым с поддержкой порога autovacuum_vacuum_insert_threshold. Поскольку последний механизм работает лучше, vacuum_cleanup_index_scale_factor выключен с PostgreSQL 13.3 и во всех Pangolin.

Перспективные возможности для Pangolin 6.0+ (ядро 15+)

Команда COPY FREEZE

Запрашивает копирование данных с уже замороженными строками – так, как будто после выполнения COPY сразу была выполнена команда VACUUM FREEZE. При начальном добавлении данных исключается повторный просмотр таблицы, снимается нагрузка с автоочистки. Строки могут быть заморожены только тогда, когда:

загружаемая таблица была создана или опустошена в текущей подтранзакции,
по ней нет открытых курсоров,
в данной транзакции нет других снимков,
таблица не является секционированной.

Аномалия MVCC

Новая аномалия MVCC: соседние сессии увидят все загруженные COPY FREEZE данные немедленно после успешной загрузки, вне зависимости от момента принятия или отмены транзакции. Действительно, признак заморозки - указание принять запись как видимую вне зависимости от номера открывшей ее транзакции.

Такое поведение отличается от ожидаемого, придется учесть его при разработке приложений.

Агрессивная автоочистка для предотвращения зацикливания счетчиков

Новый параметр vacuum_failsafe_age (integer) задает порог возраста relfrozenxid для таблицы, при достижении которого механизм автоочистки для предотвращения зацикливания счетчиков выключает паузы «вежливого» режима и пропускает не обязательные для заморозки блоки алгоритма VACUUM (например, очистку индекса).

По умолчанию составляет 1.6 миллиарда транзакций. Допустимы значения от нуля до 2.1 миллиарда, но эффективное ограничение снизу – минимум 105% от autovacuum_freeze_max_age, чтобы не уходить в агрессивный режим до попыток предотвратить зацикливание обычным образом.

Ссылки по теме

Введение​

Изоляция транзакций и история записей в MVCC Pangolin: подробности​

Горизонты событий и снимки​

Мультитранзакции​

Подготовленные транзакции​

Реплики и слоты репликации​

Особенности Pangolin​

Механизмы очистки объектов​

Общие объекты и параметры для всех видов очистки​

Массив статусов транзакций clog или xact​

Карта видимости таблицы​

Карта свободного пространства​

Фактор заполнения​

Особенности Pangolin: дисциплина организации доступных блоков​

Команда VACUUM и утилита vacuumdb​

Заморозка (перенос в историю) транзакций​

«Ленивый» режим – во время очистки​

«Агрессивный» режим – полная заморозка таблицы​

«Вежливый» режим VACUUM​

Блокировки во время VACUUM​

Демон автоочистки autovacuum​

Блокировки во время autovacuum​

«Вежливый» режим autovacuum​

Особенность параллельного «вежливого» autovacuum​

Автоочистка для предотвращения зацикливания счетчиков​

Команды VACUUM FULL и CLUSTER​

Блокировка во время VACUUM FULL​

Настройки и правила перекрытия параметров​

Мониторинг и разрешение известных проблем​

Упреждающий мониторинг​

Обычные проблемы и их устранение​

Autovacuum не запускается периодически по большой таблице​

Autovacuum (или VACUUM) работает слишком медленно​

Ни autovacuum, ни VACUUM не очищают устаревшие записи​

Предупреждение повторных инцидентов​

Подсказки для настройки параметров autovacuum​

Особенности Pangolin в сравнении с Oracle Database​

Счетчик транзакций​

Борьба с продолжительными транзакциями​

Индексы​

А где же Flashback?​

Идентификация транзакций​

Устаревшие параметры​

Перспективные возможности для Pangolin 6.0+ (ядро 15+)​

Команда COPY FREEZE​

Агрессивная автоочистка для предотвращения зацикливания счетчиков​

Ссылки по теме​

Стандартная документация PostgreSQL 15​

Hironobu SUZUKI - The Internals of PostgreSQL​

Другие статьи и исследования​