查詢指令
https://blog.csdn.net/jackgo73/article/details/122700702
SELECT n.nspname as "Schema", c.relname as "Name", c.relfrozenxid,age(c.relfrozenxid)
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY c.relfrozenxid::text::bigint DESC;
select datname,age(datfrozenxid),2^31-age(datfrozenxid) age_remain from pg_database order by age(datfrozenxid) desc;
select current_database(),rolname,nspname,relkind,relname,age(relfrozenxid),2^31-age(relfrozenxid) age_remain from pg_authid t1 join pg_class t2 on t1.oid=t2.relowner join pg_namespace t3 on t2.relnamespace=t3.oid where t2.relkind in ($$t$$,$$r$$) order by age(relfrozenxid) desc limit 5;
select datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from pg_stat_activity where state<>$$idle$$ and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval $$30 min$$ order by xact_start;
select name,statement,prepare_time,now()-prepare_time,parameter_types,from_sql from pg_prepared_statements where now()-prepare_time > interval $$30 min$$ order by prepare_time; 複制
關鍵參數
系統級
# （final模式預設兩億，把回收xid位點limit設為oldestXmin，然後把limit之前的全部freeze，同時開始告警）
autovacuum_freeze_max_age = 200000000 # 如果表的事務ID年齡大于該值, 即使未開啟autovacuum也會強制觸發FREEZE, 并告警Preventing Transaction ID Wraparound Failures
# （lazy模式預設五千萬，把回收xid位點limit設為oldestXmin減五千萬，回收垃圾元組的同時順便freeze）
vacuum_freeze_min_age = 50000000 # 手動或自動垃圾回收時, 如果記錄的事務ID年齡大于該值, 将被FREEZE
# （eager模式預設一億五千萬，把回收xid位點limit設為oldestXmin減一億五千萬，按vm按需掃頁面然後freeze）
vacuum_freeze_table_age = 150000000 # 手動垃圾回收時, 如果表的事務ID年齡大于該值, 将觸發FREEZE. 該參數的上限值為 %95 autovacuum_freeze_max_age
# 組合事務ID限制，同上述配置。
autovacuum_multixact_freeze_max_age = 400000000 # 如果表的并行事務ID年齡大于該值, 即使未開啟autovacuum也會強制觸發FREEZE, 并告警Preventing Transaction ID Wraparound Failures
vacuum_multixact_freeze_min_age = 5000000 # 手動或自動垃圾回收時, 如果記錄的并行事務ID年齡大于該值, 将被FREEZE
vacuum_multixact_freeze_table_age = 150000000 # 手動垃圾回收時, 如果表的并行事務ID年齡大于該值, 将觸發FREEZE. 該參數的上限值為 %95 autovacuum_multixact_freeze_max_age
# 其他配置
autovacuum_vacuum_cost_delay # 自動垃圾回收時, 每輪回收周期後的一個休息時間, 主要防止垃圾回收太耗資源. -1 表示沿用vacuum_cost_delay的設定
autovacuum_vacuum_cost_limit # 自動垃圾回收時, 每輪回收周期設多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty參數以及周期内的操作決定. -1 表示沿用vacuum_cost_limit的設定
vacuum_cost_delay # 手動垃圾回收時, 每輪回收周期後的一個休息時間, 主要防止垃圾回收太耗資源.
vacuum_cost_limit # 手動垃圾回收時, 每輪回收周期設多大限制, 限制由vacuum_cost_page_hit,vacuum_cost_page_missvacuum_cost_page_dirty參數以及周期内的操作決定. 複制
表級
# 系統級：autovacuum_freeze_max_age = 500000000
# 表級分段配置：
alter table t set (autovacuum_freeze_max_age=210000000);
alter table t set (autovacuum_freeze_max_age=220000000);
alter table t set (autovacuum_freeze_max_age=230000000);
.. 複制
計算cutoff位點
起始freeze的位點
void
vacuum_set_xid_limits(Relation rel,
int freeze_min_age,
int freeze_table_age,
int multixact_freeze_min_age,
int multixact_freeze_table_age,
TransactionId *oldestXmin,
TransactionId *freezeLimit,
TransactionId *xidFullScanLimit,
MultiXactId *multiXactCutoff,
MultiXactId *mxactFullScanLimit)
{
int freezemin;
int mxid_freezemin;
int effective_multixact_freeze_max_age;
TransactionId limit;
TransactionId safeLimit;
MultiXactId oldestMxact;
MultiXactId mxactLimit;
MultiXactId safeMxactLimit;
/*
* We can always ignore processes running lazy vacuum. This is because we
* use these values only for deciding which tuples we must keep in the
* tables. Since lazy vacuum doesn't write its XID anywhere, it's safe to
* ignore it. In theory it could be problematic to ignore lazy vacuums in
* a full vacuum, but keep in mind that only one vacuum process can be
* working on a particular table at any time, and that each vacuum is
* always an independent transaction.
*/
*oldestXmin =
TransactionIdLimitedForOldSnapshots(GetOldestXmin(rel, PROCARRAY_FLAGS_VACUUM), rel);
Assert(TransactionIdIsNormal(*oldestXmin));
/*
* Determine the minimum freeze age to use: as specified by the caller, or
* vacuum_freeze_min_age, but in any case not more than half
* autovacuum_freeze_max_age, so that autovacuums to prevent XID
* wraparound won't occur too frequently.
*/
// vacuum_freeze_min_age這個值太大了會被置為autovacuum_freeze_max_age的1/2。
// 配的太大了會頻繁的做freeze
freezemin = freeze_min_age;
if (freezemin < 0)
freezemin = vacuum_freeze_min_age;
freezemin = Min(freezemin, autovacuum_freeze_max_age / 2);
Assert(freezemin >= 0);
/*
* Compute the cutoff XID, being careful not to generate a "permanent" XID
*/
limit = *oldestXmin - freezemin;
if (!TransactionIdIsNormal(limit))
limit = FirstNormalTransactionId;
/*
* If oldestXmin is very far back (in practice, more than
* autovacuum_freeze_max_age / 2 XIDs old), complain and force a minimum
* freeze age of zero.
*/
safeLimit = ReadNewTransactionId() - autovacuum_freeze_max_age;
if (!TransactionIdIsNormal(safeLimit))
safeLimit = FirstNormalTransactionId; 複制
下面判斷邏輯會根據情況調整freeze起始位點
if (TransactionIdPrecedes(limit, safeLimit))
{
ereport(WARNING,
(errmsg("oldest xmin is far in the past"),
errhint("Close open transactions soon to avoid wraparound problems.")));
limit = *oldestXmin;
}
*freezeLimit = limit;
/*
* Compute the multixact age for which freezing is urgent. This is
* normally autovacuum_multixact_freeze_max_age, but may be less if we are
* short of multixact member space.
*/
effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold();
/*
* Determine the minimum multixact freeze age to use: as specified by
* caller, or vacuum_multixact_freeze_min_age, but in any case not more
* than half effective_multixact_freeze_max_age, so that autovacuums to
* prevent MultiXact wraparound won't occur too frequently.
*/
mxid_freezemin = multixact_freeze_min_age;
if (mxid_freezemin < 0)
mxid_freezemin = vacuum_multixact_freeze_min_age;
mxid_freezemin = Min(mxid_freezemin,
effective_multixact_freeze_max_age / 2);
Assert(mxid_freezemin >= 0);
/* compute the cutoff multi, being careful to generate a valid value */
oldestMxact = GetOldestMultiXactId();
mxactLimit = oldestMxact - mxid_freezemin;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
safeMxactLimit =
ReadNextMultiXactId() - effective_multixact_freeze_max_age;
if (safeMxactLimit < FirstMultiXactId)
safeMxactLimit = FirstMultiXactId;
if (MultiXactIdPrecedes(mxactLimit, safeMxactLimit))
{
ereport(WARNING,
(errmsg("oldest multixact is far in the past"),
errhint("Close open transactions with multixacts soon to avoid wraparound problems.")));
/* Use the safe limit, unless an older mxact is still running */
if (MultiXactIdPrecedes(oldestMxact, safeMxactLimit))
mxactLimit = oldestMxact;
else
mxactLimit = safeMxactLimit;
}
*multiXactCutoff = mxactLimit;
if (xidFullScanLimit != NULL)
{
int freezetable;
Assert(mxactFullScanLimit != NULL);
/*
* Determine the table freeze age to use: as specified by the caller,
* or vacuum_freeze_table_age, but in any case not more than
* autovacuum_freeze_max_age * 0.95, so that if you have e.g nightly
* VACUUM schedule, the nightly VACUUM gets a chance to freeze tuples
* before anti-wraparound autovacuum is launched.
*/
freezetable = freeze_table_age;
if (freezetable < 0)
freezetable = vacuum_freeze_table_age;
freezetable = Min(freezetable, autovacuum_freeze_max_age * 0.95);
Assert(freezetable >= 0);
/*
* Compute XID limit causing a full-table vacuum, being careful not to
* generate a "permanent" XID.
*/
limit = ReadNewTransactionId() - freezetable;
if (!TransactionIdIsNormal(limit))
limit = FirstNormalTransactionId;
*xidFullScanLimit = limit;
/*
* Similar to the above, determine the table freeze age to use for
* multixacts: as specified by the caller, or
* vacuum_multixact_freeze_table_age, but in any case not more than
* autovacuum_multixact_freeze_table_age * 0.95, so that if you have
* e.g. nightly VACUUM schedule, the nightly VACUUM gets a chance to
* freeze multixacts before anti-wraparound autovacuum is launched.
*/
freezetable = multixact_freeze_table_age;
if (freezetable < 0)
freezetable = vacuum_multixact_freeze_table_age;
freezetable = Min(freezetable,
effective_multixact_freeze_max_age * 0.95);
Assert(freezetable >= 0);
/*
* Compute MultiXact limit causing a full-table vacuum, being careful
* to generate a valid MultiXact value.
*/
mxactLimit = ReadNextMultiXactId() - freezetable;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
*mxactFullScanLimit = mxactLimit;
}
else
{
Assert(mxactFullScanLimit == NULL);
}
} 複制
驗證
實測
select txid_current(); # 14076185
# 建立表時relfrozenxid就是目前的事務ID，age=0（currentxid - 建立表時relfrozenxid就是目前的事務ID，age）
create table t9(i int);
select age(relfrozenxid),relfrozenxid,relname,pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relname='t9';
age | relfrozenxid | relname | pg_size_pretty
-----+--------------+---------+----------------
1 | 14076186 | t9 | 0 bytes
insert into t9 values (1);
insert into t9 values (2);
postgres=# select t_xmin,t_xmax,t_infomask2,t_infomask from heap_page_items(get_raw_page('t9', 0));
t_xmin | t_xmax | t_infomask2 | t_infomask
----------+--------+-------------+------------
14076187 | 0 | 1 | 2048
14076188 | 0 | 1 | 2048
postgres=# select * from pg_stat_user_tables where relname='t9';
-[ RECORD 1 ]-------+-------
relid | 135092
schemaname | public
relname | t9
seq_scan | 0
seq_tup_read | 0
idx_scan |
idx_tup_fetch |
n_tup_ins | 2
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 2
n_dead_tup | 0
n_mod_since_analyze | 2
last_vacuum | （執行了，不管清理沒清理，都會更新時間）
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count |
autoanalyze_count | 0
【1】啟動長事務
【2】啟動消耗事務ID：pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 10
# （1）到vacuum_freeze_min_age=5000W時，因為長事務存在，不能freeze。
# （2）不會有報錯
# （3）但會提示最老事務的事務ID：oldest xmin: 14076189（就是那個長事務）
vacuum (freeze,verbose) t9;
INFO: vacuuming "public.t9"
INFO: "t9": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 14076189
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# select age(relfrozenxid),relfrozenxid,relname,pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relname='t9';
age | relfrozenxid | relname | pg_size_pretty
----------+--------------+---------+----------------
59056502 | 14076189 | t9 | 40 kB
# （1）到vacuum_freeze_table_age=15000W時，因為長事務存在，不能freeze。
# （2）不會有報錯
# （3）但會提示最老事務的事務ID：oldest xmin: 14076189（就是那個長事務）
postgres=# vacuum (freeze,verbose) t9;
vacuum (freeze,verbose) t9;
INFO: vacuuming "public.t9"
INFO: "t9": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 14076189
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# select age(relfrozenxid),relfrozenxid,relname,pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relname='t9';
age | relfrozenxid | relname | pg_size_pretty
-----------+--------------+---------+----------------
153257008 | 14076189 | t9 | 40 kB
# （1）到autovacuum_freeze_max_age=20000W時，因為長事務存在，不能freeze。
# （2）開始報警，但是沒報錯
# （3）但會提示最老事務的事務ID：oldest xmin: 14076189（就是那個長事務）
postgres=# vacuum (freeze,verbose) t9;
WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
INFO: vacuuming "public.t9"
INFO: "t9": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 14076189
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# select age(relfrozenxid),relfrozenxid,relname,pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relname='t9';
age | relfrozenxid | relname | pg_size_pretty
-----------+--------------+---------+----------------
213075933 | 14076189 | t9 | 40 kB 複制