标簽
PostgreSQL , Freeze , 風暴
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E8%83%8C%E6%99%AF 背景
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）。
同時很多參數有庫級、表級選項。表級優先，然後是庫級，最後是執行個體級。
《PostgreSQL GUC 參數級别介紹》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 -- 是否開啟自動垃圾回收
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E8%A7%82%E6%B5%8Bsql 觀測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
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E7%BB%9F%E8%AE%A1sql 統計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 %
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E6%98%8E%E7%BB%86sql 明細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 -- 是否開啟自動垃圾回收
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E5%B0%8F%E7%BB%93 小結
通過本文提供的三個視圖，我們可以知道每個表，在什麼時候會觸發自動FREEZE，會産生多少IO。FREEZE的背景程序排程參數如何。哪些表在手動支援VACUUM時會觸發FREEZE。
本文内容涵蓋GC(垃圾回收知識)，統計學，進階SQL。等知識。
使用本文提到的SQL，你可以成為PG FREEZE風暴的先知，躲避風暴，掌握PG的垃圾回收機制，了解平滑的GC政策。
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E8%A7%86%E5%9B%BE1 視圖1：
按風暴來臨的時間順序排序，傳回每個剩餘多少事務會觸發自動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參數以及周期内的操作決定.
-- 是否開啟自動垃圾回收
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E8%A7%86%E5%9B%BE2 視圖2：
統計視圖，傳回每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參數，讓占比均勻化
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E8%A7%86%E5%9B%BE3 視圖3：
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更加平滑，不會出現集中爆發的情況。
https://github.com/digoal/blog/blob/master/201804/20180411_01.md#%E6%B5%81%E7%A8%8B%E9%99%84%E5%BD%95 流程附錄
一、自動觸發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.htmlCOPY 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'
![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)