Создадим таблицу с 1000 строками:
=> CREATE DATABASE mvcc_autovacuum;
CREATE DATABASE
=> \c mvcc_autovacuum
You are now connected to database "mvcc_autovacuum" as user "postgres".
=> CREATE TABLE tvac(id serial, n numeric);
CREATE TABLE
=> INSERT INTO tvac(n) SELECT 1 FROM generate_series(1,1000);
INSERT 0 1000
Выставим настройки автоочистки.
Небольшое время ожидания, чтобы сразу видеть результат:
=> ALTER SYSTEM SET autovacuum_naptime = 1;
ALTER SYSTEM
Один процент строк:
=> ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.01;
ALTER SYSTEM
Нулевой порог:
=> ALTER SYSTEM SET autovacuum_vacuum_threshold = 0;
ALTER SYSTEM
Выставим настройки автоанализа.
Два процента строк:
=> ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;
ALTER SYSTEM
Нулевой порог:
=> ALTER SYSTEM SET autovacuum_analyze_threshold = 0;
ALTER SYSTEM
Перечитаем настройки:
=> SELECT pg_reload_conf();
pg_reload_conf ---------------- t (1 row)
И подождем немного, чтобы сработал автоанализ.
Создадим представление, показывающее, нуждается ли наша таблица в очистке.
=> CREATE VIEW vacuum_v AS WITH params AS ( SELECT (SELECT setting::integer FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') AS vacuum_threshold, (SELECT setting::float FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') AS vacuum_scale_factor ) SELECT st.relname, st.n_dead_tup dead_tup, (p.vacuum_threshold + p.vacuum_scale_factor*c.reltuples)::integer max_dead_tup, st.n_dead_tup > (p.vacuum_threshold + p.vacuum_scale_factor*c.reltuples)::integer need_vacuum, st.last_autovacuum FROM pg_stat_all_tables st, pg_class c, params p WHERE c.oid = st.relid AND c.relname = 'tvac';
CREATE VIEW
Проверим.
=> SELECT * FROM vacuum_v;
relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+----------------- tvac | 0 | 10 | f | (1 row)
Таблица не требует очистки (0 ненужных версий) и ни разу не очищалась.
Можно создать аналогичное представление и для анализа:
=> CREATE VIEW analyze_v AS WITH params AS ( SELECT (SELECT setting::integer FROM pg_settings WHERE name = 'autovacuum_analyze_threshold') as analyze_threshold, (SELECT setting::float FROM pg_settings WHERE name = 'autovacuum_analyze_scale_factor') as analyze_scale_factor ) SELECT st.relname, st.n_mod_since_analyze mod_tup, (p.analyze_threshold + p.analyze_scale_factor*c.reltuples)::integer max_mod_tup, st.n_mod_since_analyze > (p.analyze_threshold + p.analyze_scale_factor*c.reltuples)::integer need_analyze, st.last_autoanalyze FROM pg_stat_all_tables st, pg_class c, params p WHERE c.oid = st.relid AND c.relname = 'tvac';
CREATE VIEW
Проверим.
=> SELECT * FROM analyze_v;
relname | mod_tup | max_mod_tup | need_analyze | last_autoanalyze ---------+---------+-------------+--------------+------------------------------- tvac | 0 | 20 | f | 2019-08-12 17:15:26.825879+03 (1 row)
Таблица не требует анализа; автоанализ уже был выполнен.
Отключим автоочистку на уровне таблицы и изменим 11 строк (больше 1 %):
=> ALTER TABLE tvac SET (autovacuum_enabled = off);
ALTER TABLE
=> UPDATE tvac SET n = n + 1 WHERE id <= 11;
UPDATE 11
=> SELECT * FROM vacuum_v;
relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+----------------- tvac | 11 | 10 | t | (1 row)
=> SELECT * FROM analyze_v;
relname | mod_tup | max_mod_tup | need_analyze | last_autoanalyze ---------+---------+-------------+--------------+------------------------------- tvac | 11 | 20 | f | 2019-08-12 17:15:26.825879+03 (1 row)
Как видно, таблице требуется автоочистка.
Включим автоочистку для таблицы и подождем несколько секунд...
=> ALTER TABLE tvac SET (autovacuum_enabled = on);
ALTER TABLE
=> SELECT * FROM vacuum_v;
relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+------------------------------- tvac | 0 | 10 | f | 2019-08-12 17:15:32.258085+03 (1 row)
=> SELECT * FROM analyze_v;
relname | mod_tup | max_mod_tup | need_analyze | last_autoanalyze ---------+---------+-------------+--------------+------------------------------- tvac | 11 | 20 | f | 2019-08-12 17:15:26.825879+03 (1 row)
Автоочистка пришла и обработала таблицу. Число ненужных версий снова равно нулю. При этом автоанализ не выполнялся.
Изменим еще 11 строк:
=> ALTER TABLE tvac SET (autovacuum_enabled = off);
ALTER TABLE
=> UPDATE tvac SET n = n + 1 WHERE id <= 11;
UPDATE 11
=> SELECT * FROM vacuum_v;
relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+------------------------------- tvac | 11 | 10 | t | 2019-08-12 17:15:32.258085+03 (1 row)
=> SELECT * FROM analyze_v;
relname | mod_tup | max_mod_tup | need_analyze | last_autoanalyze ---------+---------+-------------+--------------+------------------------------- tvac | 22 | 20 | t | 2019-08-12 17:15:26.825879+03 (1 row)
Теперь должна отработать и автоочистка, и автоанализ.
Проверим это.
=> ALTER TABLE tvac SET (autovacuum_enabled = on);
ALTER TABLE
Несколько секунд ожидания...
=> SELECT * FROM vacuum_v;
relname | dead_tup | max_dead_tup | need_vacuum | last_autovacuum ---------+----------+--------------+-------------+------------------------------- tvac | 0 | 10 | f | 2019-08-12 17:15:38.606581+03 (1 row)
=> SELECT * FROM analyze_v;
relname | mod_tup | max_mod_tup | need_analyze | last_autoanalyze ---------+---------+-------------+--------------+------------------------------- tvac | 0 | 20 | f | 2019-08-12 17:15:38.607967+03 (1 row)
Все правильно, отработали оба процесса.
Показанные представления можно использовать для мониторинга очереди таблиц, ожидающих очистку и анализ, убрав условие на имя таблицы. Для полноты картины в них требуется учесть параметры хранения на уровне отдельных таблиц.
=> ALTER TABLE tvac SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE
=> SELECT unnest(reloptions) FROM pg_class WHERE relname = 'tvac';
unnest
-------------------------------------
autovacuum_enabled=on
autovacuum_vacuum_scale_factor=0.01
(2 rows)
Конец демонстрации.