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

Внимание!

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

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

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

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

• «20.4. Потребление ресурсов»;
• «19.11. Параметры клиентских сеансов по умолчанию».

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

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

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

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

Параметр автоочистки	Параметр таблицы	Параметр 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)