PostgreSQL克服vacuum wraparoud

 

当VACUUM进程跟不上数据库活动，就会发生事务id wraparound，PostgreSQL服务被迫关闭。

更专业的说法是:当多版本并发控制(MVCC)的语义失败，且唯一事务ID的值达到其最大值(大约20亿)时，就会发生事务id wraparound。

导致这种情况的原因是，由autovacuum worker或用户交互(手动)管理的VACUUM进程跟不上DML操作。

事务id wraround可以由以下一种或多种情况的组合引起:

1.autovacuum被关闭了

2.长时间运行的事务

3.数据库执行逻辑dump(流复制)

4.许多带有锁的会话连接

5.密集的dml操作强制取消了autovacuum

 

事务wraparound会导致PostgreSQL为了保证数据完整性而关闭。

PostgreSQL在任何时候都有许多事务，这些事务由一个唯一的ID跟踪。每隔一段时间，这个数字就会达到可以注册的上限，例如，默认的20亿，然后就会被重新编号。但是，如果唯一事务id的数量达到了它的最大事务限制，即TXID wraparound，Postgresql将强制关闭以保护数据。

 

以下是工作原理：

·40亿事务，即2^32，是Postgres中使用的数据类型的整数上限

·20亿，即2的31次方，是PostgreSQL在强制关闭前允许的上限

·在距离上限1000万事务时，会给出警告信息

在距离上限100万事务时，PostgreSQL进入只读模式。

警告标志

在autovacuum守护进程在整个数据集群中出现滞后的情况下，请检查你的监控解决方案，以便确定这些指标的趋势:

·IO等增加 ·CPU负载的增加 ·SQL性能降低

 

缓解措施包括:

·检查内部Postgres监控指标并确认正在清理表。 ·查看Postgres日志，查找被取消的autovacuum工作进程。 ·查看视图"pg_stat_activity "，并寻找一个查询字符串：“preventing transaction id wraparound”。实际上，这是一条正常的信息。但是，人们不应该看到autovacuum运行的目的仅仅是减轻wraparound。

以下是一些示例错误消息，你可以在Postgres日志中发现，当由于wraparound而受到关机威胁时:

#
# When less than 10 million transactions remain before shutdown
#
WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

 

以下查询可以用于检查是否有事务id wraparound的风险：

    --
    -- Database query for transaction age per database
    --  and as a percentage of maximum permitted transactions
    --
SELECT  datname,
       age(datfrozenxid),
       (age(datfrozenxid)::numeric/1000000000*100)::numeric(4,2) as "% WRAPAROUND RISK"
FROM pg_database ORDER BY 2 DESC;

 

--
-- Database query for transaction age per table
--
SELECT  
c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as "TXID age",
(greatest(age(c.relfrozenxid),age(t.relfrozenxid))::numeric/1000000000*100)::numeric(4,2) as "%WRAPAROUND RISK"
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 2 DESC;

 

--
-- Other vacuum runtime parameters of interest
--  returning TXID age
--
SELECT  name, setting
FROM pg_settings
WHERE name ~ 'vacuum'
AND name ~ '_age$'
ORDER BY 1 ASC;

 

防止事务id wraparound

首先，确保所有的表都有正常的vacuum。一般正确配置autovacuum进程就没事。否则，你需要考虑设置一个手动vacuum策略。

 

以下只是一些建议，毕竟每个环境都具有主观性。

如果你时间充足，运行以下命令调用vacuumdb。-j参数可以根据cpu的个数调整，-a参数表示按照字母的顺序处理数据库。

vacuumdb -F -a -z -j 10 -v

如果你看到有些个别数据库需要紧急处理，可以写个脚本单独数据某个数据库：

vacuumdb -z -j 10 -v <mydatabase>

 

紧急操作：距离上限小于1000万个事务时处理wraparound

以下是即将进行事务wraparound时要采用的一组操作。请记住，你是在跟时间赛跑：你必须在剩余可用事务id下降到100万个事务之前vacuum整个数据集群。

操作：

·尽可能快地对数据库执行vacuum操作

·使用vacuumdb命令

·尽可能多的使用并发数量

·以verbose模式运行vacuum，将信息输出到日志

·监控日志输出内容

·对单个数据执行执行vacuumdb，如果需要，还可以单独对表执行

·避免使用参数-a

 

脚本：仅供参考

1.找出数据库最老的TXID

2.生成一个表的列表，以便将最老的TXID变成最新的

3.将这些表写入一个脚本，以便调用vacuumdb和vacuum

 

脚本的秘诀是：xargs 。它使人们能够合理地利用尽可能多的CPU。下面的一对bash脚本针对一系列表调用vacuumdb。 当然，有不止一种方法可以做到这一点。

脚本一在选定的数据库中生成一个表列表并调用脚本二，脚本二它分别在每个表上执行 VACUUM。

脚本1：(go1_highspeed_vacuum.sh)

#!/bin/bash
#
# INVOCATION
# EX: ./go1_highspeed_vacuum.sh
#
​
########################################################
# EDIT AS REQUIRED
export CPU=4
export PAGER=less PGUSER=postgres PGPASSWORD=mypassword PGDATABASE=db01 PGOPTIONS='-c statement_timeout=0'
########################################################
​
SQL1="
with a as (select  c.oid::regclass as table_name,
           greatest(age(c.relfrozenxid),age(t.relfrozenxid))
          from pg_class c
          left join pg_class t on c.reltoastrelid = t.oid
          where c.relkind in ('r', 'm')
            order by 2 desc)
select table_name from a
"
​
LIST="$(echo "$SQL1" | psql -t)"
​
# the 'P' sets the number of CPU to use simultaneously
xargs -t -n 1 -P $CPU ./go2_highspeed_vacuum.sh $PGDATABASE<<<$LIST
​
echo "$(date): DONE"

脚本2：(go2_highspeed_vacuum.sh)

#!/bin/bash
​
########################################################
# EDIT AS REQUIRED
export PAGER=less PGUSER=postgres PGPASSWORD=mypassword PGOPTIONS='-c statement_timeout=0'
export DB=$1
​
########################################################
​
vacuumdb --verbose ${DB} > ${DB}.log 2>&1

提示：

·按照reverse字母顺序对数库执行vacuumdb，以避免与按forward字母顺序进行的autovacuum工作进程发生冲突。

·查询表“pg_stat_activity”。

·始终监视 autovacuum 进程的工作位置。

·避免autovacuum进程和当前正在vacuumdb进程在同一张表上工作。

·使用autovacuum工作进程作为仍有待处理的数据库的指标。

·在与手动vacuum发生冲突时杀死活跃的autovacuum进程以加快速度。

 

紧急操作：因为事务id wraparound，PostgreSQL服务已经关闭

因为事务id waparound，postgresql服务已经被强制关闭的话，需要在单用户模式下执行postgresql cluster级别的vacuum。

登录服务器，以postgres用户执行：

    # it is understood that environment 
    # variable PGDATA points to the data cluster
    #
    postgres --single -D $PGDATA postgres <<< 'vacuum analyze'

建议将其写成脚本，因为你需要逐个登录数据库并执行vacuum操作。

找出所有的数据库：

postgres --single -D $PGDATA postgres <<< 'select datname from pg_database' \
    | grep '"' | cut -d '"' -f 2 > list_db

这里是个示例：用到了上面生成的list_db

#
# it is understood the database list has 
#  been edited before invoking this code snippet
#
for u in $(cat list_db)
do
    postgres --single -D $PGDATA $u <<< 'vacuum analyze'
done

事务id wraparound是很麻烦的，好在很少发生。