Postgresql源码（35）vacuum freeze起始位点逻辑分析

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);
..

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;

	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