Postgresql разделенный на авто-вакуумный стол
PostgreSQL 9.5.2 RDS в AWS
select name,setting from pg_settings
where name like '%vacuum%'
order by name;
name | setting
-------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.05
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 450000000
autovacuum_max_workers | 3
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 30
autovacuum_vacuum_cost_delay | 20
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.1
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
log_autovacuum_min_duration | 0
rds.force_autovacuum_logging_level | log
vacuum_cost_delay | 0
vacuum_cost_limit | 300
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit | 1
vacuum_cost_page_miss | 10
vacuum_defer_cleanup_age | 0
vacuum_freeze_min_age | 50000000
vacuum_freeze_table_age | 250000000
vacuum_multixact_freeze_min_age | 5000000
vacuum_multixact_freeze_table_age | 150000000
Я пытался выяснить, как работает автоматическая очистка в двух базах данных Postgres. Базы данных идентичны по размеру, параметрам и структуре. (Это два хранилища данных для одного и того же приложения - разные местоположения и разные шаблоны данных).
Мы используем разделы для некоторых из наших очень больших таблиц. Я заметил, что старые (статические) разделы регулярно очищаются автоматически. Я понимаю, что идентификаторы XID заморожены, но для отношения требуется периодическая очистка, чтобы найти новые XID.
Я использовал этот запрос, чтобы найти отношения, которые потребуют очистки, чтобы избежать обтекания XID:
SELECT 'Relation Name',age(c.relfrozenxid) c_age, age(t.relfrozenxid) t_age,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
order by age desc limit 5;
?column? | c_age | t_age | age
---------------+-----------+-----------+-----------
Relation Name | 461544753 | | 461544753
Relation Name | 461544753 | | 461544753
Relation Name | 461544753 | | 461544753
Relation Name | 461544753 | | 461544753
Relation Name | 461544753 | 310800517 | 461544753
Все перечисленные отношения являются старыми стабильными разделами. Столбец relfrozenxid определен как: "Все идентификаторы транзакций до этого были заменены постоянным (" замороженным ") идентификатором транзакции в этой таблице. Это используется для отслеживания необходимости вакуумирования таблицы во избежание обхода идентификатора транзакции. или позволить сжать pg_clog."
Из любопытства я посмотрел на relfrozenxid для всех разделов определенной таблицы:
SELECT c.oid::regclass as table_name,age(c.relfrozenxid) as age , c.reltuples::int, n_live_tup, n_dead_tup,
date_trunc('day',last_autovacuum)
FROM pg_class c
JOIN pg_stat_user_tables u on c.relname = u.relname
WHERE c.relkind IN ('r', 'm')
and c.relname like 'tablename%'
table_name | age | reltuples | n_live_tup | n_dead_tup | date_trunc
-------------------------------------+-----------+-----------+------------+------------+------------------------
schema_partition.tablename_201202 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201306 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201204 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201110 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201111 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201112 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201201 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201203 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201109 | 460250527 | 0 | 0 | 0 | 2018-09-23 00:00:00+00
schema_partition.tablename_201801 | 435086084 | 37970232 | 37970230 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201307 | 433975635 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201107 | 433975635 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201312 | 433975635 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201311 | 433975635 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201401 | 433975635 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201310 | 423675180 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201704 | 423222113 | 43842668 | 43842669 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201612 | 423222113 | 65700844 | 65700845 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201705 | 423221655 | 46847336 | 46847338 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201702 | 423171142 | 50701032 | 50701031 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_overflow | 423171142 | 754 | 769 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201106 | 421207271 | 1 | 1 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201309 | 421207271 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201108 | 421207271 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201308 | 421207271 | 0 | 0 | 0 | 2018-09-25 00:00:00+00
schema_partition.tablename_201806 | 374122782 | 44626756 | 44626757 | 0 | 2018-09-26 00:00:00+00
schema.tablename | 360135561 | 0 | 0 | 0 | 2018-09-27 00:00:00+00
Я почти уверен, что не совсем понимаю, как работает relfrozenxid, но похоже, что на таблицы разделов влияет родительская таблица (что может повлиять на значение relfrozenxid для многораздельной таблицы). Я не могу найти никаких документов по этому поводу. Я думаю, что для статических таблиц relfrozenxid будет оставаться статическим, пока не возникнет вакуум.
Кроме того, у меня есть несколько отношений, которые имеют статические данные, которые, по-видимому, никогда не были автоматически очищены (last_autovacuum имеет значение null). Может ли это быть результатом операции VACUUM FREEZE?
Я новичок в Postgres и с готовностью признаю, что не полностью понимаю процессы автоматического вакуума.
Я не вижу и проблем с производительностью, которые я могу определить.
Редактировать:
Я настроил запрос для запуска каждые 4 часа для одной многораздельной таблицы:
SELECT c.oid::regclass as table_name,age(c.relfrozenxid) as age , c.reltuples::int, n_live_tup, n_dead_tup,
date_trunc('day',last_autovacuum)
FROM pg_class c
JOIN pg_stat_user_tables u on c.relname = u.relname
WHERE c.relkind IN ('r', 'm')
and c.relname like 'sometable%'
order by age desc;
Глядя на два разных раздела, мы видим результаты за последние 20 часов:
schemaname.sometable_201812 | 206286536 | 0 | 0 | 0 |
schemaname.sometable_201812 | 206286537 | 0 | 0 | 0 |
schemaname.sometable_201812 | 225465100 | 0 | 0 | 0 |
schemaname.sometable_201812 | 225465162 | 0 | 0 | 0 |
schemaname.sometable_201812 | 225465342 | 0 | 0 | 0 |
schemaname.sometable_201812 | 236408374 | 0 | 0 | 0 |
-bash-4.2$ grep 201610 test1.out
schemaname.sometable_201610 | 449974426 | 31348368 | 31348369 | 0 | 2018-09-22 00:00:00+00
schemaname.sometable_201610 | 449974427 | 31348368 | 31348369 | 0 | 2018-09-22 00:00:00+00
schemaname.sometable_201610 | 469152990 | 31348368 | 31348369 | 0 | 2018-09-22 00:00:00+00
schemaname.sometable_201610 | 50000051 | 31348368 | 31348369 | 0 | 2018-10-10 00:00:00+00
schemaname.sometable_201610 | 50000231 | 31348368 | 31348369 | 0 | 2018-10-10 00:00:00+00
schemaname.sometable_201610 | 60943263 | 31348368 | 31348369 | 0 | 2018-10-10 00:00:00+00
Relfrozenxid разделов изменяется, хотя прямого раздела DML для раздела нет. Я бы предположил, что вставки в базовую таблицу каким-то образом изменяют relfrozenxid разделов.
Раздел sometable_201610 имеет 31 миллион строк, но является статическим. Когда я смотрю на файлы журналов, автоматическая очистка этого типа раздела занимает 20-30 минут. Я не знаю, если это проблема с производительностью или нет, но это кажется дорогим. Глядя на автовакуум в файлах журналов, можно увидеть, что, как правило, каждый из этих больших разделов автоматически убирается каждую ночь. (Есть также много разделов с нулевыми кортежами, которые автоматически откачиваются, но они занимают очень мало времени).