Postgresql源码(35)vacuum freeze起始位点逻辑分析

2023-10-19 12:10

本文主要是介绍Postgresql源码(35)vacuum freeze起始位点逻辑分析,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

查询指令

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。// 配的太大了会频繁的做freezefreezemin = 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;elsemxactLimit = 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 bytesinsert 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 |       204814076188 |      0 |           1 |       2048postgres=# 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.
VACUUMpostgres=# 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.
VACUUMpostgres=# 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

这篇关于Postgresql源码(35)vacuum freeze起始位点逻辑分析的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/239655

相关文章

Go标准库常见错误分析和解决办法

《Go标准库常见错误分析和解决办法》Go语言的标准库为开发者提供了丰富且高效的工具,涵盖了从网络编程到文件操作等各个方面,然而,标准库虽好,使用不当却可能适得其反,正所谓工欲善其事,必先利其器,本文将... 目录1. 使用了错误的time.Duration2. time.After导致的内存泄漏3. jsO

Python实现无痛修改第三方库源码的方法详解

《Python实现无痛修改第三方库源码的方法详解》很多时候,我们下载的第三方库是不会有需求不满足的情况,但也有极少的情况,第三方库没有兼顾到需求,本文将介绍几个修改源码的操作,大家可以根据需求进行选择... 目录需求不符合模拟示例 1. 修改源文件2. 继承修改3. 猴子补丁4. 追踪局部变量需求不符合很

Spring事务中@Transactional注解不生效的原因分析与解决

《Spring事务中@Transactional注解不生效的原因分析与解决》在Spring框架中,@Transactional注解是管理数据库事务的核心方式,本文将深入分析事务自调用的底层原理,解释为... 目录1. 引言2. 事务自调用问题重现2.1 示例代码2.2 问题现象3. 为什么事务自调用会失效3

找不到Anaconda prompt终端的原因分析及解决方案

《找不到Anacondaprompt终端的原因分析及解决方案》因为anaconda还没有初始化,在安装anaconda的过程中,有一行是否要添加anaconda到菜单目录中,由于没有勾选,导致没有菜... 目录问题原因问http://www.chinasem.cn题解决安装了 Anaconda 却找不到 An

Spring定时任务只执行一次的原因分析与解决方案

《Spring定时任务只执行一次的原因分析与解决方案》在使用Spring的@Scheduled定时任务时,你是否遇到过任务只执行一次,后续不再触发的情况?这种情况可能由多种原因导致,如未启用调度、线程... 目录1. 问题背景2. Spring定时任务的基本用法3. 为什么定时任务只执行一次?3.1 未启用

C++ 各种map特点对比分析

《C++各种map特点对比分析》文章比较了C++中不同类型的map(如std::map,std::unordered_map,std::multimap,std::unordered_multima... 目录特点比较C++ 示例代码 ​​​​​​代码解释特点比较1. std::map底层实现:基于红黑

最新Spring Security实战教程之表单登录定制到处理逻辑的深度改造(最新推荐)

《最新SpringSecurity实战教程之表单登录定制到处理逻辑的深度改造(最新推荐)》本章节介绍了如何通过SpringSecurity实现从配置自定义登录页面、表单登录处理逻辑的配置,并简单模拟... 目录前言改造准备开始登录页改造自定义用户名密码登陆成功失败跳转问题自定义登出前后端分离适配方案结语前言

Spring、Spring Boot、Spring Cloud 的区别与联系分析

《Spring、SpringBoot、SpringCloud的区别与联系分析》Spring、SpringBoot和SpringCloud是Java开发中常用的框架,分别针对企业级应用开发、快速开... 目录1. Spring 框架2. Spring Boot3. Spring Cloud总结1. Sprin

Spring 中 BeanFactoryPostProcessor 的作用和示例源码分析

《Spring中BeanFactoryPostProcessor的作用和示例源码分析》Spring的BeanFactoryPostProcessor是容器初始化的扩展接口,允许在Bean实例化前... 目录一、概览1. 核心定位2. 核心功能详解3. 关键特性二、Spring 内置的 BeanFactory

Java逻辑运算符之&&、|| 与&、 |的区别及应用

《Java逻辑运算符之&&、||与&、|的区别及应用》:本文主要介绍Java逻辑运算符之&&、||与&、|的区别及应用的相关资料,分别是&&、||与&、|,并探讨了它们在不同应用场景中... 目录前言一、基本概念与运算符介绍二、短路与与非短路与:&& 与 & 的区别1. &&:短路与(AND)2. &:非短