Vacuum使用心得

最近在优化PG,在vacuum过程中发现了  (to prevent wrap)。于是花了一周实践深入了解并实践了一把。下面把这一周的心得分享一下,以备遇到同样情况的 朋友以及将来的自己 使用。

数据库总是不断地在执行删除,更新等操作。良好的空间管理非常重要,能够对性能带来大幅提高。在postgresql中用于维护数据库磁盘空间的工具是VACUUM,其重要的作用是删除那些已经标示为删除的数据并释放空间。

基础知识前述:

在PostgreSQL中,每个事务都有一个唯一的事务ID,被称为XID。注意:除了被BEGIN -COMMIT/ROLLBACK包裹的一组语句会被当作一个事务对待外,不显示指定BEGIN – COMMIT/ROLLBACK的单条语句也是一个事务。

数据库中的事务ID递增。可通过txid_current()函数获取当前事务的ID。

隐藏多版本标记字段

PostgreSQL中,对于每一行数据(称为一个tuple),包含有4个隐藏字段。这四个字段是隐藏的,但可直接访问。

  • xmin 在创建(insert)记录(tuple)时,记录此值为插入tuple的事务ID
  • xmax 默认值为0.在删除tuple时,记录此值(即若tuple被删除,则xmax被设置为删除tuple的事务的ID).即 未被删除时xmax为0
  • cmin和cmax 标识在同一个事务中多个语句命令的序列值,从0开始,用于同一个事务中实现版本可见性判断。(即在上一个语句的基础上加1)

因为PostgreSQL的更新操作并非真正更新数据,而是将旧数据标记为删除,并插入新数据,所以“更新的事务ID”也就是“创建记录的事务ID”。

dead tuple:

PG的MVCC说起,PG为了实现多版本并发控制,当PG在更新数据时, 是不直接删除老数据的,一个update操作执行后,被更改的数据的旧版本也被保留下来,当PG进行删除数据时,也不是直接删除而是标记删除,那些旧版本数据被称为dead tuple。

PG提供了autovacuum,vaccum操作,当对表做vacuum操作的时候,才考虑回收,旧版本不及时回收就会造成表膨胀。

PostgreSQL中的MVCC优势

使用MVCC,读操作不会阻塞写,写操作也不会阻塞读,提高了并发访问下的性能

事务的回滚可立即完成,无论事务进行了多少操作

数据可以进行大量更新,不段像MySQL和Innodb引擎和Oracle那样需要保证回滚段不会被耗尽

PostgreSQL中的MVCC缺点

1、事务ID个数有限制

事务ID由32位数保存,而事务ID递增,当事务ID用完时,会出现wraparound问题。

PostgreSQL通过VACUUM机制来解决该问题。对于事务ID,PostgreSQL有三个事务ID有特殊意义:

  • 0代表invalid事务号
  • 1代表bootstrap事务号
  • 2代表frozon事务。frozon transaction id比任何事务都要老

可用的有效最小事务ID为3。VACUUM时将所有已提交的事务ID均设置为2,即frozon。之后所有的事务都比frozon事务新,因此VACUUM之前的所有已提交的数据都对之后的事务可见。PostgreSQL通过这种方式实现了事务ID的循环利用。

2、大量过期数据占用磁盘并降低查询性能

PostgreSQL更新数据并非真正更改记录值,而是通过将旧数据标记为删除,再插入新的数据来实现。对于更新或删除频繁的表,会累积大量过期数据,占用大量磁盘,并且由于需要扫描更多数据,使得查询性能降低。

PostgreSQL解决该问题的方式也是VACUUM机制。从释放磁盘的角度,VACUUM分为两种

  • VACUUM 该操作并不要求获得排它锁,因此它可以和其它的读写表操作并行进行。同时它只是简单的将dead tuple对应的磁盘空间标记为可用状态,新的数据可以重用这部分磁盘空间。但是这部分磁盘并不会被真正释放,也即不会被交还给操作系统,因此不能被系统中其它程序所使用,并且可能会产生磁盘碎片。
  • VACUUM FULL 需要获得排它锁,它通过“标记-复制”的方式将所有有效数据(非dead tuple)复制到新的磁盘文件中,并将原数据文件全部删除,并将未使用的磁盘空间还给操作系统,因此系统中其它进程可使用该空间,并且不会因此产生磁盘碎片。

VACUUM语法结构:

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, …] ) ] ]

Vacuum

vacuum只是将删除状态的空间释放掉,转换到能够重新使用的状态。该操作并不要求获得排它锁,因此它可以和其它的读写表操作并行进行。同时它只是简单的将dead tuple对应的磁盘空间标记为可用状态,新的数据可以重用这部分磁盘空间。但是这部分磁盘并不会被真正释放,也即不会被交还给操作系统,因此不能被系统中其它程序所使用,并且可能会产生磁盘碎片。

Vacuum Full

将会使空间释放的信息表现在系统级别,其实质是将当前删除记录后面的数据进行移动,使得整体的记录连贯起来。需要获得排它锁,它通过“标记-复制”的方式将所有有效数据(非dead tuple)复制到新的磁盘文件中,并将原数据文件全部删除,并将未使用的磁盘空间还给操作系统,因此系统中其它进程可使用该空间,并且不会因此产生磁盘碎片。

Vacuum analyze

analyze的功能是更新统计信息,使得优化器能够选择更好的方案执行sql。oracle中同样也有analyze,作用也相同,目前更多的使用的是dbms_stats包。统计信息收集和更新对于系统性能来说非常重要,与oracle维护类似,通常可以通过采用手动或者定制任务的方式。也有不同,oracle在进行imp后自动的对相应数据对象进行统计信息的收集和更新,而postgresql的恢复过程还没有集成到里面,需要手动去执行。

Vacuum Freeze  (to prevent wrap)

vacuum freeze 命令表示强制对表或数据库进行freeze 操作。freeze 操作是为了保证整个数据库的最老最新事务差不能超过20亿,从而防止事务ID 发生回绕。执行 vacuum freeze table后需要修改 pg_class.relfrozenxid的值。当表的age大于 autovacuum_freeze_max_age时,autovacuum 进程回自动对该表进行 freeze。freeze后,还可以清除掉比 整个集群的最老事务号早的clog文件。freeze 是全表扫描的,所以不要在高峰时期运行。

在PostgreSQL 中,XID 是用32位无符号数来表示的,很容易发生溢出的情况,造成事务可见性混乱。为了解决这个问题,PostgreSQL:

PostgreSQL中是使用2^31取模的方法来进行事务的比较

同一个数据库中,存在的最旧和最新两个事务之间的年龄最多是2^31,即20亿

freeze 操作会将表中过老的元组的xmin 赋值为特殊的XID FrozenTransactionId(3)。FrozenTransactionId为t_xmin的元组将会被其他所有的事务可见,这样该元组原来对应的XID 相当于被回收了,经过不断的处理,就可以控制一个数据库的最老的事务和最新的事务的年龄不超过20亿。

vacuum freeze table:将表的xmax的值变为2.

freeze vacuum的机制,当表的最大xid大于等于autovacuum_freeze_max_age时,PostgreSQL会将表中的对应row的xmax的值设置为2,被认为是最老的事务,这样新的事务都能看到该row,完成freeze vacuum的目的。

创建表是对于该表来说是最小的xid,那么relfrozenxid的值就是创建表的xid,不论后面更新插入删除,relfrozenxid都不会变,当age(relfrozenxid)>阀值时,就是强制调用vacuum freeze table;其实age()只是相对数,就是当前的事务id-表的最小事务id,随着当前事务id的增大,表被freeze后,relfrozenxid也会变大,只是中间的间隔变小。

而对于行来说,如果是vacuum table时,只要自己行的xid与最新的事务id相距不超过vacuum_freeze_min_age阀值时,就不会发生变化,如果超过这个阀值,行的xid就会变为freezeid(2),但是表的relfrozenxid取得的是除了2之外的最小值;就是有点难理解

运维逻辑详见 [与vacuum freeze相关的参数主要有三个] 部分

vacuum所有相关参数详细说明如下:

参考 https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html

autovacuum:

默认为on,表示是否开起autovacuum。默认开起。特别的,当需要冻结xid时,尽管此值为off,PG也会进行vacuum。

autovacuum_naptime:

下一次vacuum的时间,默认1min。 这个naptime会被vacuum launcher分配到每个DB上。autovacuum_naptime/num of db。

log_autovacuum_min_duration:

默认值为-1,关闭vacuum的日志记录,配置为0表示记录autovacuum的所有log。参数设置为正整数表示对于在此时间内完成的vacuum操作不进行log记录,如果没能完成,则记录超出时间内的log。该参数对于了解对象执行vacuum操作的时间非常有用。

autovacuum_max_workers:

最大同时运行的worker数量,不包含launcher本身。

autovacuum_work_mem    :

每个worker可使用的最大内存数。

autovacuum_vacuum_threshold    :

默认50。与autovacuum_vacuum_scale_factor配合使用, autovacuum_vacuum_scale_factor默认值为20%。当update,delete的tuples数量超过autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold时,进行vacuum。如果要使vacuum工作勤奋点,则将此值改小。

参数表示执行autovacuum操作之前,对单个表中记录执行DML操作的最少行数。达到该行数时自动激活autovacuum操作。该参数针对数据库中的所有表,还可以通过对单个表配置不同的值来改变相应表的autovacuum操作。

autovacuum_vacuum_scale_factor:

该参数采用百分比的方式设定阀值。默认值为20%,当DML涉及的数据量大于某个表的20%时,自动触发autovacuum操作。同样可以通过对单个表进行阀值设定。

autovacuum_analyze_scale_factor:机制与上面相同,到达阀值是自动激活analyze操作。同样可以通过对单个表进行阀值设定。

autovacuum_analyze_threshold        :

默认50。与autovacuum_analyze_scale_factor配合使用。

autovacuum_analyze_scale_factor    :

默认10%。当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold时,进行analyze。

autovacuum_freeze_max_age:

200 million。离下一次进行xid冻结的最大事务数。

autovacuum_multixact_freeze_max_age:

400 million。离下一次进行xid冻结的最大事务数。

autovacuum_vacuum_cost_delay    :

autovacuum进程的时间延迟限制,默认值是20ms。对于单个表同样适用。如果为-1,取vacuum_cost_delay值。

autovacuum_vacuum_cost_limit       :

autovacuum进程的开销延迟限制,默认值是-1,表示不进行开销限制,系统将会直接依据vacuum_cost_limit参数管理vacuum的开销。对于单个表同样适用。如果为-1,到vacuum_cost_limit的值,这个值是所有worker的累加值。

autovacuum_work_mem

指定每个autovacuum工作进程使用的最大内存量。它默认为-1,表示 应该使用maintenance_work_mem的值。在其他上下文中运行时,该设置对VACUUM的行为没有影响。

maintenance_work_mem

指定维护操作要使用的最大内存量,例如VACUUM,CREATE INDEX和ALTER TABLE ADD FOREIGN KEY。默认为64兆字节(64MB)。由于数据库会话一次只能执行其中一个操作,并且安装通常没有多个并发运行,因此将此值设置为远大于work_mem是安全的。较大的设置可能会提高清理和恢复数据库转储的性能。

请注意,当autovacuum运行时,最多 可以分配autovacuum_max_workers时间,因此请注意不要将默认值设置得太高。通过单独设置autovacuum_work_mem来控制它可能是有用的。

可以对 单表设定不同的以上参数

表上的以下参数可以单独设置

autovacuum_vacuum_threshold

autovacuum_analyze_threshold

autovacuum_vacuum_scale_factor

autovacuum_analyez_scale_factor

autovacuum_freeze_max_age

autovacumm_multixact_freeze_max_age

autovacuum_vacuum_cost_delay

autovacuum_vacuum_cost_limit

比如    alter table ta set  (autovacuum_freeze_max_age=xxxx);

执行VACUUM 和ANALYZE命令时,因为它们会消耗大量的CPU与IO资源,而且执行一次要花很长时间,这样会干扰系统执行应用程序发出的SQL命令。为了解决这个问题,VACUUM 和ANALYZE命令执行一段时间后,系统会暂时终止它们的运行,过一段时间后再继续执行这两个命令。这个特性在默认的情况下是关闭的。将参数vacuum_cost_delay设为一个非零的正整数就可以打开这个特性。

用户通常只需要设置参数vacuum_cost_delay和vacuum_cost_limit,其它的参数使用默认值即可。VACUUM 和ANALYZE命令在执行过程中,系统会计算它们执行消耗的资源,资源的数量用一个正整数表示,如果资源的数量超过vacuum_cost_limit,则执行命令的进程会进入睡眠状态,睡眠的时间长度是是vacuum_cost_delay。vacuum_cost_limit的值越大,VACUUM 和ANALYZE命令在执行的过程中,睡眠的次数就越少,反之,vacuum_cost_limit的值越小,VACUUM 和ANALYZE命令在执行的过程中,睡眠的次数就越多。

vacuum_cost_delay (integer)

这个参数可以在任何时候被设置。默认值是0。它决定执行VACUUM 和ANALYZE命令的进程的睡眠时间。单位是毫秒。它的值最好是10的整数,如果不是10的整数,系统会自动将它设为比该值大的并且最接近该值的是10的倍数的整数。如果值是0,VACUUM 和ANALYZE命令在执行过程中不会主动进入睡眠状态,会一直执行下去直到结束。

vacuum_cost_page_hit (integer)

这个参数可以在任何时候被设置。默认值是1。锁住缓冲池,查找共享的散列表以及扫描页面的内容的开销(credits)。

vacuum_cost_page_miss (integer)

这个参数可以在任何时候被设置。默认值是10。表示锁住缓冲池,查找共享散列表,从磁盘读取需要的数据块以及扫描它的内容的开销。

vacuum_cost_page_dirty (integer)

这个参数可以在任何时候被设置。默认值是20。如果清理修改一个原先是干净的块的预计开销。它需要一个把脏的磁盘块再次冲刷到磁盘上的额外开销。

vacuum_cost_limit (integer)

这个参数可以在任何时候被设置。默认值是200。导致清理进程休眠的积累开销。

autovacuum进程进行 vacuum 触发条件

表上(update,delte 记录) >= autovacuum_vacuum_scale_factor*

reltuples(表上记录数) + autovacuum_vacuum_threshold

通过以上默认参数的计算,

当一张表上update,delte记录次数达到 0.2 * 表记录数 + 50 的时候,触发autovacuum。

autovacuum进程进行 analyze 触发条件

表上(insert,update,delte 记录) >= autovacuum_analyze_scale_factor*

reltuples(表上记录数) + autovacuum_analyze_threshold

通过以上默认参数的计算,

当一张表上insert,update,delte 记录次数达到 0.1 * 表记录数 + 50 的时候,触发autoanalyze。

autovacuum_freeze_max_age (integer)

指定表上事务的最大年龄,默认为2亿,达到这个阀值将触发 autovacuum进程,从而避免 wraparound。

表上的事务年龄可以通过 pg_class.relfrozenxid 查询。所有表的该值是一样的。

select relname,age(relfrozenxid) from pg_class where relname=’test01′;

基于代价的vacuum参数:

vacuum_cost_delay :计算每个毫秒级别所允许消耗的最大IO,vacuum_cost_limit/vacuum_cost_dely。 默认vacuum_cost_delay为20毫秒。

vacuum_cost_page_hit :vacuum时,page在buffer中命中时,所花的代价。默认值为1。

vacuum_cost_page_miss:vacuum时,page不在buffer中,需要从磁盘中读入时的代价默认为10。 vacuum_cost_page_dirty:当vacuum时,修改了clean的page。这说明需要额外的IO去刷脏块到磁盘。默认值为20。

vacuum_cost_limit:当超过此值时,vacuum会sleep。默认值为200。

把上面每

—-来自官方

为了跟踪数据库中最早解冻的XID 的age, VACUUM将XID统计信息存储在系统表pg_class和pg_database中。特别是,表的pg_class行的relfrozenxid列包含该表的最后一个激进的VACUUM使用的冻结截止XID 。

由XID早于此截止XID的事务插入的所有行都保证已被冻结。类似地,数据库的pg_database行的 datfrozenxid列是该数据库中 出现的未freeze XID的下限 – 它只是每个表relfrozenxid中的最小值数据库中的值。

检查此信息的便捷方法是执行以下查询:

SELECT c.oid::regclass as table_name,

       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age

FROM pg_class c

LEFT JOIN pg_class t ON c.reltoastrelid = t.oid

WHERE c.relkind IN (‘r’, ‘m’);

SELECT datname, age(datfrozenxid) FROM pg_database;

—-来自官方

与vacuum freeze相关的参数主要有三个:

vacuum_freeze_min_age

vacuum_freeze_table_age

autovacuum_freeze_max_age

vacuum_freeze_min_age 

表示表中每个元组需要freeze的最小age。这里值得一提的是每次表被freeze 之后,会更新pg_class 中的relfrozenxid 列为本次freeze的XID。

表age就是当前的最新的XID 与relfrozenxid的差值,而元组age可以理解为每个元组的t_xmin与relfrozenxid的差值。所以,这个参数也可以被简单理解为每个元组两次被freeze之间的XID 差值的一个最小值。

增大该参数可以避免一些无用的freeze 操作,减小该参数可以使得在表必须被强制清理之前保留更多的XID 空间。该参数最大值为20亿,最小值为2亿。

普通的vacuum 使用visibility map来快速定位哪些数据页需要被扫描,只会扫描那些脏页(会跳过没有dead row的page,即使这些page有旧的xid值),其他的数据页即使其中元组对应的xmin非常旧也不会被扫描。而在freeze的过程中,我们是需要对所有可见且未被all-frozen的数据页进行扫描,这个扫描过程PostgreSQL 称为aggressive vacuum。每次vacuum都去扫描每个表所有符合条件的数据页显然是不现实的,所以我们要选择合理的aggressive vacuum周期。PostgreSQL 引入了参数vacuum_freeze_table_age来决定这个周期。

vacuum_freeze_table_age

控制什么时候需要扫描全表,并freeze所有行的xid。表示表的age大于该值时,会进行aggressive vacuum,即扫描表中可见且未被all-frozen的数据页。该参数最大值为20亿,最小值为1.5亿。

即 如果事务数大于(vacuum_freeze_table_age-vacuum_freeze_min_age),那么就扫描所有可见的没有冻结的page如果该值为0,则每次扫描表都进行aggressive vacuum。

可以看出:

当表的age超过vacuum_freeze_table_age则会aggressive vacuum

当元组的age超过vacuum_freeze_min_age后可以进行freeze

为了保证整个数据库的最老最新事务差不能超过20亿的原则,两次aggressive vacuum之间的新老事务差不能超过20亿,即两次aggressive vacuum之间表的age增长(vacuum_freeze_table_age)不能超过20亿减去vacuum_freeze_min_age(只有元组年龄超过vacuum_freeze_min_age才会被freeze)。但是看上面的参数,很明显不能绝对保证这个约束,为了解决这个问题,PostgreSQL 引入了autovacuum_freeze_max_age 参数。

autovacuum_freeze_max_age

表示如果当前最新的XID 减去元组的t_xmin 大于等于autovacuum_freeze_max_age,则元组对应的表会强制进行autovacuum,即使PostgreSQL已经关闭了autovacuum。该参数最小值为2亿,最大值为20亿。

也就是说,在经过autovacuum_freeze_max_age-vacuum_freeze_min_age的XID 增长之后,这个表肯定会被强制地进行 一次freeze。因为autovacuum_freeze_max_age最大值为20亿,所以说在两次freeze之间,XID 的增长肯定不会超过20亿,这就保证了上文中整个数据库的最老最新事务差不能超过20亿的原则。

值得一提的是:

vacuum_freeze_table_age设置的值如果比autovacuum_freeze_max_age要高,则每次vacuum_freeze_table_age生效地时候,autovacuum_freeze_max_age已经生效,起不到过滤减少数据页扫描的作用。所以默认的规则,vacuum_freeze_table_age要设置的比autovacuum_freeze_max_age小。但是也不能太小,太小的话会造成频繁的aggressive vacuum。

另外通过分析源码可知,vacuum_freeze_table_age在最后应用时,会去取min(vacuum_freeze_table_age,0.95 autovacuum_freeze_max_age)。所以官方文档推荐vacuum_freeze_table_age=0.95 autovacuum_freeze_max_age。

freeze 操作会消耗大量的IO,对于不经常更新的表,可以合理地增大autovacuum_freeze_max_age和vacuum_freeze_min_age的差值。

但是如果设置autovacuum_freeze_max_age 和vacuum_freeze_table_age过大,因为需要存储更多的事务提交信息,会造成pg_xact 和 pg_commit 目录占用更多的空间。例如,我们把autovacuum_freeze_max_age设置为最大值20亿,pg_xact大约占500MB,pg_commit_ts大约是20GB(一个事务的提交状态占2位)。如果是对存储比较敏感的用户,也要考虑这点影响。

而减小vacuum_freeze_min_age则会造成vacuum 做很多无用的工作,因为当数据库freeze 了符合条件的row后,这个row很可能接着会被改变。理想的状态就是,当该行不会被改变,才去freeze 这行。

增加autovacuum_freeze_max_age(以及vacuum_freeze_table_age)的唯一缺点是数据库集群的 pg_clog子目录将占用更多空间;

减少vacuum_freeze_min_age的一个缺点是它可能导致 VACUUM做无用的工作:如果此后很快修改行(导致它获取新的XID),冻结行版本会浪费时间。因此,设置应该足够大,以至于行不会被冻结,直到它们不再可能更改为止。

—来自网摘 云栖社区

autovacuum 的freeze操作是不能主动预测的,只能被动出发。如果要主动去做 vacuum freeze操作运维

1、

查询当前所有表的age

SELECT c.oid::regclass as table_name,

       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age

FROM pg_class c

LEFT JOIN pg_class t ON c.reltoastrelid = t.oid

WHERE c.relkind IN (‘r’, ‘m’);

比如 查看表超过指定大小的最老ID

SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size

FROM pg_class WHERE relkind = ‘r’ and pg_table_size(oid) > 1073741824

ORDER BY age(relfrozenxid) DESC LIMIT 10;

这个命令是查询按照最老的XID排序,查看大于1G而且是排名前20的表。

2、 查询数据库的age

select datname,age(datfrozenxid) from pg_database;

3、设置 vacuum_cost_delay 为一个比较高的数值(30ms),这样以便减少普通vacuum操作对正常数据查询的影响;

4、设置 vacuum_freeze_table_age=0.5*autovacuum_freeze_max_age, vacuum_freeze_min_age为原来值的0.1倍;

5、最后对表 vacuum freeze操作即可;

flexible-freeze.py 是一个阿里推荐的 vacuum freeze监控工具 (自动对具有最老XID的表进行 vacuum freeze);

————————————————
版权声明:本文为CSDN博主「Captain_Felix」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/zhaowenzhong/article/details/83753402

Leave a Reply