PostgreSQL Freeze 风暴预测续 - 珍藏级SQL
背景
PostgreSQL 目前默认的存储引擎，事务可见性需要依赖行头的事务号，因为事务号是32位的，会循环使用。
在一条记录产生后，如果再次经历了20亿个事务，必须对其进行freeze，否则数据库会认为这条记录是未来事务产生的（可见性判断）。
因此FREEZE操作是数据库在32位事务号的情况下，经常要做的。
对全表进行FREEZE操作时，会扫描整表，将大于指定阈值least(autovacuum_freeze_min_age, 表级参数vacuum_freeze_min_age)年龄的记录设置为freeze。可能导致大量的读IO，写IO（主要是写数据文件,WAL日志, full page write WAL)。
一些参数决定数据库在什么时候触发FREEZE，以及触发FREEZE时，冻结哪些记录，以及是否涉及到调度（sleep）。
同时很多参数有库级、表级选项。表级优先，然后是库级，最后是实例级。
1、多久检查一次哪些表是否需要FREEZE
postgres=# show autovacuum_naptime ;
autovacuum_naptime
--------------------
1s
(1 row)
2、哪些表需要被自动FREEZE，
超过如下阈值，如果设置了表级参数则以表级参数为准，否则以系统参数为准。
表级参数
autovacuum_freeze_max_age
autovacuum_freeze_table_age
系统级参数
autovacuum_freeze_max_age
3、手工执行普通vacuum时，哪些表会被扫描全表，并freeze
超过如下阈值
系统级参数
vacuum_freeze_table_age
注意，现在PG支持VM文件里面记录一个PAGE是否需要被FREEZE，所以即使全表扫描，也会根据VM标记位，跳过一些BLOCK，所以FREEZE并不一定会产生大量读IO。根据表的情况而定。
3、触发FREEZE时，哪些记录需要被FREEZE
超过如下阈值的记录被FREEZE，如果设置了表级参数则以表级参数为准，否则以系统参数为准。
表级参数
autovacuum_freeze_min_age
系统级参数
vacuum_freeze_min_age
4、FREEZE后台进程的调度（sleep）。
current_setting('autovacuum_vacuum_cost_delay') as v7, -- 自动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源. -1 表示沿用vacuum_cost_delay的设置
current_setting('autovacuum_vacuum_cost_limit') as v8, -- 自动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定. -1 表示沿用vacuum_cost_limit的设置
current_setting('vacuum_cost_delay') as v9, -- 手动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源.
current_setting('vacuum_cost_limit') as v10, -- 手动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定.
current_setting('autovacuum') as autovacuum -- 是否开启自动垃圾回收
观测SQL
SQL里的注释，建议看懂它，可以掌握更多知识点。(这个SQL没有考虑toast相关的表级参数，一般人不会去设置它。如果要严谨一点，需要考虑进来)
create view v_freeze as
select
e.*,
a.*
from
(select
current_setting('autovacuum_freeze_max_age')::int as v1, -- 如果表的事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
current_setting('autovacuum_multixact_freeze_max_age')::int as v2, -- 如果表的并行事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
current_setting('vacuum_freeze_min_age')::int as v3, -- 手动或自动垃圾回收时, 如果记录的事务ID年龄大于该值, 将被FREEZE
current_setting('vacuum_multixact_freeze_min_age')::int as v4, -- 手动或自动垃圾回收时, 如果记录的并行事务ID年龄大于该值, 将被FREEZE
current_setting('vacuum_freeze_table_age')::int as v5, -- 手动垃圾回收时, 如果表的事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_freeze_max_age
current_setting('vacuum_multixact_freeze_table_age')::int as v6, -- 手动垃圾回收时, 如果表的并行事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_multixact_freeze_max_age
current_setting('autovacuum_vacuum_cost_delay') as v7, -- 自动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源. -1 表示沿用vacuum_cost_delay的设置
current_setting('autovacuum_vacuum_cost_limit') as v8, -- 自动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定. -1 表示沿用vacuum_cost_limit的设置
current_setting('vacuum_cost_delay') as v9, -- 手动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源.
current_setting('vacuum_cost_limit') as v10, -- 手动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定.
current_setting('autovacuum') as autovacuum -- 是否开启自动垃圾回收
) a,
LATERAL ( -- LATERAL 允许你在这个SUBQUERY中直接引用前面的table, subquery中的column
select
pg_size_pretty(pg_total_relation_size(oid)) sz, -- 表的大小(含TOAST, 索引)
oid::regclass as reloid, -- 表名(物化视图)
relkind, -- r=表, m=物化视图
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_max_age=(\d+)')::int,
substring(reloptions::text, 'autovacuum_freeze_table_age=(\d+)')::int
),
a.v1
)
-
age(case when relfrozenxid::text::int<3 then null else relfrozenxid end)
as remain_ages_xid, -- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为事务ID
coalesce(
least(
substring(reloptions::text, 'autovacuum_multixact_freeze_max_age=(\d+)')::int,
substring(reloptions::text, 'autovacuum_multixact_freeze_table_age=(\d+)')::int
),
a.v2
)
-
age(case when relminmxid::text::int<3 then null else relminmxid end)
as remain_ages_mxid, -- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为并发事务ID
coalesce(
least(
substring(reloptions::text, 'autovacuum_freeze_min_age=(\d+)')::int
),
a.v3
) as xid_lower_to_minage, -- 如果触发FREEZE, 该表的事务ID年龄会降到多少
coalesce(
least(
substring(reloptions::text, 'autovacuum_multixact_freeze_min_age=(\d+)')::int
),
a.v4
) as mxid_lower_to_minage, -- 如果触发FREEZE, 该表的并行事务ID年龄会降到多少
case
when v5 <= age(case when relfrozenxid::text::int<3 then null else relfrozenxid end) then 'YES'
else 'NOT'
end as vacuum_trigger_freeze1, -- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(事务ID年龄达到阈值)
case
when v6 <= age(case when relminmxid::text::int<3 then null else relminmxid end) then 'YES'
else 'NOT'
end as vacuum_trigger_freeze2, -- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(并行事务ID年龄达到阈值)
reloptions -- 表级参数, 优先. 例如是否开启自动垃圾回收, autovacuum_freeze_max_age, autovacuum_freeze_table_age, autovacuum_multixact_freeze_max_age, autovacuum_multixact_freeze_table_age
from pg_class
where relkind in ('r','m')
) e
order by
least(e.remain_ages_xid , e.remain_ages_mxid), -- 排在越前, 越先触发自动FREEZE, 即风暴来临的预测
pg_total_relation_size(reloid) desc -- 同样剩余年龄, 表越大, 排越前
;
并行事务ID指多会话对同一记录加共享锁，将记录并行事务ID:
《并发事务, 共享行锁管理 - pg_multixact manager for shared-row-lock implementation》
返回解释如下：
postgres=# \x
Expanded display is on.
postgres=# select * from v_freeze ;
-[ RECORD 1 ]----------+-------------------------------------------
sz | 32 kB -- 表的大小(含TOAST, 索引)
reloid | information_schema.sql_sizing_profiles -- 表名(物化视图)
relkind | r -- r=表, m=物化视图
remain_ages_xid | 199739848 -- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为事务ID
remain_ages_mxid | -- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为并发事务ID
xid_lower_to_minage | 0 -- 如果触发FREEZE, 该表的事务ID年龄会降到多少
mxid_lower_to_minage | 0 -- 如果触发FREEZE, 该表的并行事务ID年龄会降到多少
vacuum_trigger_freeze1 | YES -- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(事务ID年龄达到阈值)
vacuum_trigger_freeze2 | NOT -- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(并行事务ID年龄达到阈值)
reloptions | -- 表级参数, 优先. 例如是否开启自动垃圾回收, autovacuum_freeze_max_age, autovacuum_freeze_table_age, autovacuum_multixact_freeze_max_age, autovacuum_multixact_freeze_table_age
v1 | 200000000 -- 如果表的事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
v2 | 400000000 -- 如果表的并行事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
v3 | 0 -- 手动或自动垃圾回收时, 如果记录的事务ID年龄大于该值, 将被FREEZE
v4 | 0 -- 手动或自动垃圾回收时, 如果记录的并行事务ID年龄大于该值, 将被FREEZE
v5 | 200 -- 手动垃圾回收时, 如果表的事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_freeze_max_age
v6 | 150000000 -- 手动垃圾回收时, 如果表的并行事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_multixact_freeze_max_age
v7 | 0 -- 自动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源. -1 表示沿用vacuum_cost_delay的设置
v8 | -1 -- 自动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定. -1 表示沿用vacuum_cost_limit的设置
v9 | 0 -- 手动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源.
v10 | 200 -- 手动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定.
autovacuum | on -- 是否开启自动垃圾回收
-[ RECORD 2 ]----------+-------------------------------------------
sz | 192 kB
reloid | information_schema.sql_implementation_info
relkind | r
remain_ages_xid | 199739848
remain_ages_mxid |
xid_lower_to_minage | 0
mxid_lower_to_minage | 0
vacuum_trigger_freeze1 | YES
vacuum_trigger_freeze2 | NOT
reloptions |
v1 | 200000000
v2 | 400000000
v3 | 0
v4 | 0
v5 | 200
v6 | 150000000
v7 | 0
v8 | -1
v9 | 0
v10 | 200
autovacuum | on
-[ RECORD 3 ]----------+-------------------------------------------
sz | 192 kB
reloid | information_schema.sql_languages
relkind | r
remain_ages_xid | 199739848
remain_ages_mxid |
xid_lower_to_minage | 0
mxid_lower_to_minage | 0
vacuum_trigger_freeze1 | YES
vacuum_trigger_freeze2 | NOT
reloptions |
v1 | 200000000
v2 | 400000000
v3 | 0
v4 | 0
v5 | 200
v6 | 150000000
v7 | 0
v8 | -1
v9 | 0
v10 | 200
autovacuum | on
-[ RECORD 4 ]----------+-------------------------------------------
sz | 192 kB
reloid | information_schema.sql_packages
relkind | r
remain_ages_xid | 199739848
remain_ages_mxid |
xid_lower_to_minage | 0
mxid_lower_to_minage | 0
vacuum_trigger_freeze1 | YES
vacuum_trigger_freeze2 | NOT
reloptions |
v1 | 200000000
v2 | 400000000
v3 | 0
v4 | 0
v5 | 200
v6 | 150000000
v7 | 0
v8 | -1
v9 | 0
v10 | 200
autovacuum | on
-[ RECORD 5 ]----------+-------------------------------------------
sz | 192 kB
reloid | information_schema.sql_parts
relkind | r
remain_ages_xid | 199739848
remain_ages_mxid |
xid_lower_to_minage | 0
mxid_lower_to_minage | 0
vacuum_trigger_freeze1 | YES
vacuum_trigger_freeze2 | NOT
reloptions |
v1 | 200000000
v2 | 400000000
v3 | 0
v4 | 0
v5 | 200
v6 | 150000000
v7 | 0
v8 | -1
v9 | 0
v10 | 200
autovacuum | on
统计SQL
例如每隔100万个事务, 统计一下有多少表, 多少容量会参与FREEZE, 会导致读多少IO, 写IO多少?
create view v_freeze_stat as
select
wb, -- 第几个BATCH, 每个batch代表流逝100万个事务
cnt, -- 这个batch 有多少表
pg_size_pretty(ssz) as ssz1, -- 这个batch 这些 表+TOAST+索引 有多少容量
pg_size_pretty(ssz) as ssz2, -- 这个batch FREEZE 会导致多少读IO
pg_size_pretty(ssz*3) as ssz3, -- 这个batch FREEZE 最多可能会导致多少写IO (通常三份 : 数据文件, WAL FULL PAGE, WAL)
pg_size_pretty(min_sz) as ssz4, -- 这个batch 最小的表多大
pg_size_pretty(max_sz) as ssz5, -- 这个batch 最大的表多大
pg_size_pretty(avg_sz) as ssz6, -- 这个batch 平均表多大
pg_size_pretty(stddev_sz) as ssz7, -- 这个batch 表大小的方差, 越大, 说明表大小差异化明显
min_rest_age, -- 这个batch 距离自动FREEZE最低剩余事务数
max_rest_age, -- 这个batch 距离自动FREEZE最高剩余事务数
stddev_rest_age, -- 这个batch 距离自动FREEZE剩余事务数的方差, 越小，说明这个batch触发freeze将越平缓, 越大, 说明这个batch将有可能在某些点集中触发freeze (但是可能集中触发的都是小表)
corr_rest_age_sz, -- 表大小与距离自动freeze剩余事务数的相关性，相关性越强(值趋向1或-1) stddev_rest_age 与 sz7 说明的问题越有价值
round(100*(ssz/(sum(ssz) over ())), 2)||' %' as ratio -- 这个BATCH的容量占比，占比如果非常不均匀，说明有必要调整表级FREEZE参数，让占比均匀化
from
(
select a.*, b.* from
(
select
min(least(remain_ages_xid, remain_ages_mxid)) as v_min, -- 整个数据库中离自动FREEZE的 最小 剩余事务ID数
max(least(remain_ages_xid, remain_ages_mxid)) as v_max -- 整个数据库中离自动FREEZE的 最大 剩余事务ID数
from v_freeze
) as a,
LATERAL ( -- 高级SQL
select
width_bucket(
least(remain_ages_xid, remain_ages_mxid),
a.v_min,
a.v_max,
greatest((a.v_max-a.v_min)/1000000, 1) -- 100万个事务, 如果要更改统计例如，修改这个值即可
) as wb,
count(*) as cnt,
sum(pg_total_relation_size(reloid)) as ssz,
stddev_samp(pg_total_relation_size(reloid) order by least(remain_ages_xid, remain_ages_mxid)) as stddev_sz,
min(pg_total_relation_size(reloid)) as min_sz,
max(pg_total_relation_size(reloid)) as max_sz,
avg(pg_total_relation_size(reloid)) as avg_sz,
min(least(remain_ages_xid, remain_ages_mxid)) as min_rest_age,
max(least(remain_ages_xid, remain_ages_mxid)) as max_rest_age,
stddev_samp(least(remain_ages_xid, remain_ages_mxid) order by least(remain_ages_xid, remain_ages_mxid)) as stddev_rest_age,
corr(least(remain_ages_xid, remain_ages_mxid), pg_total_relation_size(reloid)) as corr_rest_age_sz
from v_freeze
group by wb
) as b
) t
order by wb;
返回解释如下
wb | cnt | sz1 | sz2 | sz3 | sz4 | sz5 | sz6 | sz7 | min_rest_age | max_rest_age | stddev_rest_age | corr_rest_age_sz | ratio
----+------+---------+---------+-------+--------+---------+---------+---------+--------------+--------------+-----------------+---------------------+---------
1 | 2083 | 13 GB | 13 GB | 38 GB | 32 kB | 6326 MB | 6380 kB | 184 MB | 199739848 | 199890546 | 18826.08737805 | -0.0818846238277424 | 70.96 %
2 | 2 | 5311 MB | 5311 MB | 16 GB | 224 kB | 5311 MB | 2655 MB | 3755 MB | 199999996 | 199999996 | 0 | | 29.04 %
(2 rows)
占比不均匀，有必要调整表级freeze参数。
-[ RECORD 1 ]----+--------------------
wb | 1 -- 第几个BATCH, 每个batch代表流逝100万个事务
cnt | 2083 -- 这个batch 有多少表
ssz1 | 13 GB -- 这个batch 这些 表+TOAST+索引 有多少容量
ssz2 | 13 GB -- 这个batch FREEZE 会导致多少 读IO
ssz3 | 38 GB -- 这个batch FREEZE 最多可能会导致多少 写IO (通常三份 : 数据文件, WAL FULL PAGE, WAL)
ssz4 | 32 kB -- 这个batch 最小的表多大
ssz5 | 6326 MB -- 这个batch 最大的表多大
ssz6 | 6380 kB -- 这个batch 平均表多大
ssz7 | 184 MB -- 这个batch 表大小的方差, 越大, 说明表大小差异化明显
min_rest_age | 199739846 -- 这个batch 距离自动FREEZE最低 剩余事务数
max_rest_age | 199890544 -- 这个batch 距离自动FREEZE最高 剩余事务数
stddev_rest_age | 18826.08737805 -- 这个batch 距离自动FREEZE剩余事务数的方差, 越小，说明这个batch触发freeze将越平缓, 越大, 说明这个batch将有可能在某些点集中触发freeze (但是可能集中触发的都是小表)
corr_rest_age_sz | -0.0818849525050259 -- 表大小与距离自动freeze剩余事务数的相关性，相关性越强(值趋向1或-1) stddev_rest_age 与 sz7 说明的问题越有价值
ratio | 70.96 % -- 这个BATCH的容量占比，占比如果非常不均匀，说明有必要调整表级FREEZE参数，让占比均匀化
-[ RECORD 2 ]----+--------------------
wb | 2
cnt | 2
ssz1 | 5311 MB
ssz2 | 5311 MB
ssz3 | 16 GB
ssz4 | 224 kB
ssz5 | 5311 MB
ssz6 | 2655 MB
ssz7 | 3755 MB
min_rest_age | 199999994
max_rest_age | 199999994
stddev_rest_age | 0
corr_rest_age_sz |
ratio | 29.04 %
明细SQL
观察每个BATCH内的距离freeze剩余年龄、表大小分布
该表在该batch中的容量占比
create view v_freeze_stat_detail as
select
pg_size_pretty(t.ssz) as ssz2, -- 这个batch FREEZE 会导致多少读IO (表+TOAST+索引)
pg_size_pretty(t.ssz*3) as ssz3, -- 这个batch FREEZE 最多可能会导致多少写IO (通常三份 : 数据文件, WAL FULL PAGE, WAL)
pg_size_pretty(t.ssz_sum) as ssz4, -- 所有batch 所有表的总大小 (表+TOAST+索引)
round(100*(t.ssz/t.ssz_sum), 2)||' %' as ratio_batch, -- 这个BATCH的容量占比，目标是让所有BATCH占比尽量一致
round(100*(pg_total_relation_size(t.reloid)/t.ssz), 2)||' %' as ratio_table, -- 这个表占整个batch的容量占比，大表尽量错开freeze
t.*
from
(
select a.*, b.* from
(
select
min(least(remain_ages_xid, remain_ages_mxid)) as v_min, -- 整个数据库中离自动FREEZE的 最小 剩余事务ID数
max(least(remain_ages_xid, remain_ages_mxid)) as v_max -- 整个数据库中离自动FREEZE的 最大 剩余事务ID数
from v_freeze
) as a,
LATERAL ( -- 高级SQL
select
count(*) over w as cnt, -- 这个batch 有多少表
sum(pg_total_relation_size(reloid)) over () as ssz_sum, -- 所有batch 所有表的总大小 (表+TOAST+索引)
sum(pg_total_relation_size(reloid)) over w as ssz, -- 这个batch 的表大小总和 (表+TOAST+索引)
pg_size_pretty(min(pg_total_relation_size(reloid)) over w) as min_sz, -- 这个batch 最小的表多大
pg_size_pretty(max(pg_total_relation_size(reloid)) over w) as max_sz, -- 这个batch 最大的表多大
pg_size_pretty(avg(pg_total_relation_size(reloid)) over w) as avg_sz, -- 这个batch 平均表多大
pg_size_pretty(stddev_samp(pg_total_relation_size(reloid)) over w) as stddev_sz, -- 这个batch 表大小的方差, 越大, 说明表大小差异化明显
min(least(remain_ages_xid, remain_ages_mxid)) over w as min_rest_age, -- 这个batch 距离自动FREEZE最低剩余事务数
max(least(remain_ages_xid, remain_ages_mxid)) over w as max_rest_age, -- 这个batch 距离自动FREEZE最高剩余事务数
stddev_samp(least(remain_ages_xid, remain_ages_mxid)) over w as stddev_rest_age, -- 这个batch 距离自动FREEZE剩余事务数的方差, 越小，说明这个batch触发freeze将越平缓, 越大, 说明这个batch将有可能在某些点集中触发freeze (但是可能集中触发的都是小表)
corr(least(remain_ages_xid, remain_ages_mxid), pg_total_relation_size(reloid)) over w as corr_rest_age_sz, -- 表大小与距离自动freeze剩余事务数的相关性，相关性越强(值趋向1或-1) stddev_rest_age 与 stddev_sz 说明的问题越有价值
t1.*
from
(
select
width_bucket(
least(tt.remain_ages_xid, tt.remain_ages_mxid),
a.v_min,
a.v_max,
greatest((a.v_max-a.v_min)/1000000, 1) -- 100万个事务, 如果要更改统计例如，修改这个值即可
)
as wb, -- 第几个BATCH, 每个batch代表流逝100万个事务
* from v_freeze tt
) as t1
window w as
(
partition by t1.wb
)
) as b
) t
order by
t.wb,
least(t.remain_ages_xid, t.remain_ages_mxid),
pg_total_relation_size(t.reloid) desc
;
返回解释如下
-[ RECORD 1 ]----------+-------------------------------------------
ssz2 | 13 GB -- 这个batch FREEZE 会导致多少 读IO
ssz3 | 38 GB -- 这个batch FREEZE 最多可能会导致多少 写IO (通常三份 : 数据文件, WAL FULL PAGE, WAL)
ssz4 | 18 GB -- 所有batch 所有表的总大小 (表+TOAST+索引)
ratio_batch | 70.96 % -- 这个BATCH的容量占比，目标是让所有BATCH占比尽量一致
ratio_table | 0.06 % -- 这个表占整个batch的容量占比，大表尽量错开freeze
v_min | 199739844 -- 整个数据库中离自动FREEZE的 最小 剩余事务ID数
v_max | 200000000 -- 整个数据库中离自动FREEZE的 最大 剩余事务ID数
cnt | 2085 -- 这个batch 有多少表
ssz_sum | 19177472000 -- 所有batch 所有表的总大小 (表+TOAST+索引)
ssz | 13608812544 -- =ssz2 这个batch 的表大小总和 (表+TOAST+索引)
min_sz | 32 kB -- 这个batch 最小的表多大
max_sz | 6326 MB -- 这个batch 最大的表多大
avg_sz | 6374 kB -- 这个batch 平均表多大
stddev_sz | 184 MB -- 这个batch 表大小的方差, 越大, 说明表大小差异化明显
min_rest_age | 199739844 -- 这个batch 距离自动FREEZE最低剩余事务数
max_rest_age | 199999998 -- 这个batch 距离自动FREEZE最高剩余事务数
stddev_rest_age | 19486.91462976 -- 这个batch 距离自动FREEZE剩余事务数的方差, 越小，说明这个batch触发freeze将越平缓, 越大, 说明这个batch将有可能在某些点集中触发freeze (但是可能集中触发的都是小表)
corr_rest_age_sz | -0.079332345436958 -- 表大小与距离自动freeze剩余事务数的相关性，相关性越强(值趋向1或-1) stddev_rest_age 与 stddev_sz 说明的问题越有价值
wb | 1 -- 第几个BATCH, 每个batch代表流逝100万个事务
sz | 8352 kB -- 表的大小(含TOAST, 索引)
reloid | pg_attribute -- 表名(物化视图)
relkind | r -- r=表, m=物化视图
remain_ages_xid | 199739844 -- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为事务ID
remain_ages_mxid | -- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为并发事务ID
xid_lower_to_minage | 0 -- 如果触发FREEZE, 该表的事务ID年龄会降到多少
mxid_lower_to_minage | 0 -- 如果触发FREEZE, 该表的并行事务ID年龄会降到多少
vacuum_trigger_freeze1 | YES -- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(事务ID年龄达到阈值)
vacuum_trigger_freeze2 | NOT -- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(并行事务ID年龄达到阈值)
reloptions | -- 表级参数, 优先. 例如是否开启自动垃圾回收, autovacuum_freeze_max_age, autovacuum_freeze_table_age, autovacuum_multixact_freeze_max_age, autovacuum_multixact_freeze_table_age
v1 | 200000000 -- 如果表的事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
v2 | 400000000 -- 如果表的并行事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
v3 | 0 -- 手动或自动垃圾回收时, 如果记录的事务ID年龄大于该值, 将被FREEZE
v4 | 0 -- 手动或自动垃圾回收时, 如果记录的并行事务ID年龄大于该值, 将被FREEZE
v5 | 200 -- 手动垃圾回收时, 如果表的事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_freeze_max_age
v6 | 150000000 -- 手动垃圾回收时, 如果表的并行事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_multixact_freeze_max_age
v7 | 0 -- 自动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源. -1 表示沿用vacuum_cost_delay的设置
v8 | -1 -- 自动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定. -1 表示沿用vacuum_cost_limit的设置
v9 | 0 -- 手动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源.
v10 | 200 -- 手动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定.
autovacuum | on -- 是否开启自动垃圾回收
小结
通过本文提供的三个视图，我们可以知道每个表，在什么时候会触发自动FREEZE，会产生多少IO。FREEZE的后台进程调度参数如何。哪些表在手动支持VACUUM时会触发FREEZE。
本文内容涵盖GC(垃圾回收知识)，统计学，高级SQL。等知识。
使用本文提到的SQL，你可以成为PG FREEZE风暴的先知，躲避风暴，掌握PG的垃圾回收机制，了解平滑的GC策略。
视图1 (v_freeze) ：
按风暴来临的时间顺序排序，返回每个剩余多少事务会触发自动FREEZE，详细信息如下
-- 表的大小(含TOAST, 索引)
-- 表名(物化视图)
-- r=表, m=物化视图
-- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为事务ID
-- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为并发事务ID
-- 如果触发FREEZE, 该表的事务ID年龄会降到多少
-- 如果触发FREEZE, 该表的并行事务ID年龄会降到多少
-- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(事务ID年龄达到阈值)
-- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(并行事务ID年龄达到阈值)
-- 表级参数, 优先. 例如是否开启自动垃圾回收, autovacuum_freeze_max_age, autovacuum_freeze_table_age, autovacuum_multixact_freeze_max_age, autovacuum_multixact_freeze_table_age
-- 如果表的事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
-- 如果表的并行事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
-- 手动或自动垃圾回收时, 如果记录的事务ID年龄大于该值, 将被FREEZE
-- 手动或自动垃圾回收时, 如果记录的并行事务ID年龄大于该值, 将被FREEZE
-- 手动垃圾回收时, 如果表的事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_freeze_max_age
-- 手动垃圾回收时, 如果表的并行事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_multixact_freeze_max_age
-- 自动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源. -1 表示沿用vacuum_cost_delay的设置
-- 自动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定. -1 表示沿用vacuum_cost_limit的设置
-- 手动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源.
-- 手动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定.
-- 是否开启自动垃圾回收
视图2 (v_freeze_stat) ：
统计视图，返回每N个事务为间隔，每个间隔中，有多少表要被FREEZE，表的总容量多少大。
-- 第几个BATCH, 每个batch代表流逝100万个事务
-- 这个batch 有多少表
-- 这个batch 这些 表+TOAST+索引 有多少容量
-- 这个batch FREEZE 会导致多少 读IO
-- 这个batch FREEZE 最多可能会导致多少 写IO (通常三份 : 数据文件, WAL FULL PAGE, WAL)
-- 这个batch 最小的表多大
-- 这个batch 最大的表多大
-- 这个batch 平均表多大
-- 这个batch 表大小的方差, 越大, 说明表大小差异化明显
-- 这个batch 距离自动FREEZE最低 剩余事务数
-- 这个batch 距离自动FREEZE最高 剩余事务数
-- 这个batch 距离自动FREEZE剩余事务数的方差, 越小，说明这个batch触发freeze将越平缓, 越大, 说明这个batch将有可能在某些点集中触发freeze (但是可能集中触发的都是小表)
-- 表大小与距离自动freeze剩余事务数的相关性，相关性越强(值趋向1或-1) stddev_rest_age 与 sz7 说明的问题越有价值
-- 这个BATCH的容量占比，占比如果非常不均匀，说明有必要调整表级FREEZE参数，让占比均匀化
视图3 (v_freeze_stat_detail) ：
1,2结合的明细视图，可以用于巡视哪些表的FREEZE参数需要被调整。
-- 这个batch FREEZE 会导致多少 读IO
-- 这个batch FREEZE 最多可能会导致多少 写IO (通常三份 : 数据文件, WAL FULL PAGE, WAL)
-- 所有batch 所有表的总大小 (表+TOAST+索引)
-- 这个BATCH的容量占比，目标是让所有BATCH占比尽量一致
-- 这个表占整个batch的容量占比，大表尽量错开freeze
-- 整个数据库中离自动FREEZE的 最小 剩余事务ID数
-- 整个数据库中离自动FREEZE的 最大 剩余事务ID数
-- 这个batch 有多少表
-- 所有batch 所有表的总大小 (表+TOAST+索引)
-- =ssz2 这个batch 的表大小总和 (表+TOAST+索引)
-- 这个batch 最小的表多大
-- 这个batch 最大的表多大
-- 这个batch 平均表多大
-- 这个batch 表大小的方差, 越大, 说明表大小差异化明显
-- 这个batch 距离自动FREEZE最低剩余事务数
-- 这个batch 距离自动FREEZE最高剩余事务数
-- 这个batch 距离自动FREEZE剩余事务数的方差, 越小，说明这个batch触发freeze将越平缓, 越大, 说明这个batch将有可能在某些点集中触发freeze (但是可能集中触发的都是小表)
-- 表大小与距离自动freeze剩余事务数的相关性，相关性越强(值趋向1或-1) stddev_rest_age 与 stddev_sz 说明的问题越有价值
-- 第几个BATCH, 每个batch代表流逝100万个事务
-- 表的大小(含TOAST, 索引)
-- 表名(物化视图)
-- r=表, m=物化视图
-- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为事务ID
-- 再产生多少个事务后, 自动垃圾回收会触发FREEZE, 起因为并发事务ID
-- 如果触发FREEZE, 该表的事务ID年龄会降到多少
-- 如果触发FREEZE, 该表的并行事务ID年龄会降到多少
-- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(事务ID年龄达到阈值)
-- 如果手工执行VACUUM, 是否会触发FREEZE, 触发起因(并行事务ID年龄达到阈值)
-- 表级参数, 优先. 例如是否开启自动垃圾回收, autovacuum_freeze_max_age, autovacuum_freeze_table_age, autovacuum_multixact_freeze_max_age, autovacuum_multixact_freeze_table_age
-- 如果表的事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
-- 如果表的并行事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE, 并告警Preventing Transaction ID Wraparound Failures
-- 手动或自动垃圾回收时, 如果记录的事务ID年龄大于该值, 将被FREEZE
-- 手动或自动垃圾回收时, 如果记录的并行事务ID年龄大于该值, 将被FREEZE
-- 手动垃圾回收时, 如果表的事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_freeze_max_age
-- 手动垃圾回收时, 如果表的并行事务ID年龄大于该值, 将触发FREEZE. 该参数的上限值为 %95 autovacuum_multixact_freeze_max_age
-- 自动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源. -1 表示沿用vacuum_cost_delay的设置
-- 自动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定. -1 表示沿用vacuum_cost_limit的设置
-- 手动垃圾回收时, 每轮回收周期后的一个休息时间, 主要防止垃圾回收太耗资源.
-- 手动垃圾回收时, 每轮回收周期设多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty参数以及周期内的操作决定.
-- 是否开启自动垃圾回收
PostgreSQL内核层面可以做到更加智能，比如有更好的GC调度策略，让系统的VACUUM FREEZE更加平滑，不会出现集中爆发的情况。
流程附录
一、自动触发FREEZE流程
1、 优先使用 表级参数判断是否需要强制AUTO FREEZE(prevent xid wrapped)
2、 再使用 系统级参数判断是否需要强制AUTO FREEZE(prevent xid wrapped)
3、 AUTO VACUUM FREEZE开始
4、 跳过VM中标识为已FREEZE的PAGE
5、 SLEEP 调度
autovacuum_vacuum_cost_delay = 0 # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
6、 年龄下降到（表级设定、系统级设定）
二、手动触发FREEZE流程
1、 判断当前表年龄是否已超出系统级FREEZE参数限制
2、 vacuum FREEZE
3、 跳过VM中标识为已FREEZE的PAGE
4、 SLEEP 调度
vacuum_cost_delay = 0 # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
5、 年龄下降到（表级设定、系统级设定）
三、批量导入数据时，可以选择是否自动FREEZE
https://www.postgresql.org/docs/devel/static/sql-copy.html
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
where option can be one of:
FORMAT format_name
OIDS [ boolean ]
FREEZE [ boolean ] ## 就是它
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [ boolean ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL ( column_name [, ...] )
FORCE_NULL ( column_name [, ...] )
ENCODING 'encoding_name'
参考
《PostgreSQL freeze 风暴导致的IOPS飙升 - 事后追溯》
《PostgreSQL 9.6 vacuum freeze大幅性能提升 代码浅析》
《PostgreSQL物理”备库”的哪些操作或配置，可能影响”主库”的性能、垃圾回收、IO波动》
《PostgreSQL 老湿机图解平安科技遇到的垃圾回收”坑”》
《PostgreSQL垃圾回收代码分析 - why postgresql cann’t reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》
《PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL》
https://www.postgresql.org/docs/devel/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
https://www.postgresql.org/docs/devel/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
https://www.postgresql.org/docs/devel/static/sql-createtable.html