数据库年龄增长问题定位及处理方法

指标异常说明（影响）

同一个数据库中，存在的最旧和最新两个事务之间的年龄最多是2^31，即20亿，当表的年龄大于autovacuum_freeze_max_age时（云上默认是4亿），autovacuum进程会自动对表进行freeze。

数据库年龄一旦超过20亿，数据库会宕机不可用，则不会再接受新事物写入。需要单用户模式执行vacuum full才能修复。

问题排查思路

数据库年龄增大的原因大概有以下几种：

1. autovacuum进程不起作用
2. 写入数据量过大
3. 临时表长时间未释放
4. 只读长事务

图1 排查思路

排查方法

• autovacuum进程不起作用

  数据库中存在大量的死元祖，没有进行vacuum操作，排查方法如下：

  1. 确认数据库上的autovacuum参数是否开启。
  2. 查看autovacuum_freeze_max_age参数值，云上数据库默认值是4亿，客户如果修改了参数值超过10亿，建议改小参数值再观察。
  3. 确认是否达到autovacuum的触发条件：

     autovacuum_vacuum_threshold这个参数主要是指定表中变动的tuple数，超过这个数字会触发autovacuum对这个表进行整理。

     autovacuum_vacuum_scale_factor这个参数主要指定表的变动行占整体表的百分之几，超过这个占用的比率会触发autovacuum。

  4. 确认autovacuum进程状态是否正常，可使用如下SQL：

     select * from pg_stat_activity where backend_type like '%vacuu%';

• 写入的数据量太大
  1. 查看数据库年龄。

     select datname, age(datfrozenxid) from pg_database where datname <> 'template1' and datname <> 'template0' order by age desc;

  2. 查看autovacuum的参数 查看autovacuum相关参数配置是否合理，对比参数模板的配置值。

     SELECT name, setting FROM pg_settings WHERE name like '%vacuum%';

  3. 查询数据库年龄大的5张表。

     select relname, relfrozenxid,  age(relfrozenxid) aa from pg_class where relfrozenxid != 0 order by aa desc limit 5;

  4. 查询这些年龄大的表的autovacuum情况。

     SELECT schemaname, relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_all_tables WHERE relname='pg_toast_1335431529';

  5. 查询这些年龄大表的大小情况。

     select pg_size_pretty(pg_relation_size(pg_toast_1335431536));

  6. 查询autovacuum执行情况，如下命令执行2次，分别查看2次执行结果中的heap_blks_scanned 字段值是否在稳定增加，正常增加可以判断vacuum在正常执行。

     select * from pg_stat_progress_vacuum;

     如果autovacuum在正常执行，那么此时我们需要结合监控指标来看，查看近7天的磁盘读写吞吐量和IOPS相关的指标，如果长时间处于打满状态，此时可以确认是由于磁盘IO过高，导致autovacuum清理进程速度赶不上事务ID生成速度，从而使数据库年龄增长。

     磁盘类型以及最大吞吐量可参考数据库实例存储类型性能对比。

     图2 查看磁盘读写吞吐量
     
     图3 查看IOPS
     
• 临时表长时间未释放
  1. 具体排查方法请参考1~3，如果查询的结果中年龄最大的表是“tmp_%”开头的表，如果不确认是否是临时表，可以查看表的属性。
  2. 查看年龄最大的表的属性，如果“relpersistence”字段为“t”，表明该表是临时表。

     select * from pg_class where relname ='tmp_table_pu';

     

     在数据库中，临时表不会vacuum，但其生命周期一般不会长。

     连接一旦释放，临时表就会被回收。

     因此，需要排查数据库中是否存在长连接，可通过如下语句进行排查：

     select (now()-backend_start) duration, *from pg_stat_activity  where backend_type = 'client backend' order by duration desc nulls  last;

     排查出长连接后，需要释放客户端的长连接，进而释放临时表，从而观察数据库年龄是否有下降。

• 长事务导致数据库年龄增加

  长事务也是导致数据库年龄增长的一个原因，在PG数据库中，可通过云监控平台或者使用SQL语句查询长事务。

  1. 可通过如下语句查询是否有长事务。

     select * from pg_stat_activity where state <> 'idle' order by xact_start;

  2. 或者可以通过云监控平台，查看最长事务存活时长，判断是否有长事务。
     

     监控只能判断是否存在长事务，不能查看到具体长事务的信息，建议使用SQL语句结合监控共同判断。

     图4 查看最长事务存活时长
     
  3. 如果存在长事务，可通过如下SQL取消长事务。

     -- 推荐使用如下命令
     select pg_cancel_backend($PID);
     -- 若pg_cancel_backend语句无效，再使用本语句
     select pg_terminate_backend($PID);

  4. 取消长事务后，对数据库年龄最大的表执行vacuum操作。

     vacuum "Test20231127";

  5. 清理完表空间后，在数据库执行如下SQL进行验证，如果“n_dead_tup”归零或者仅数值很小，则说明恢复完成。

     SELECT schemaname, relname, n_live_tup, n_dead_tup,
     FROM pg_stat_all_tables WHERE relname = 'Test20231127';

解决方法

• autovacuum进程不起作用
  1. 确认客户数据库上的autovacuum参数是否开启，如果参数未开启，需要开启autovacuum参数，等待观察数据库年龄恢复情况。
  2. 查看autovacuum_freeze_max_age参数值，云上数据库默认值是4亿，如果修改了参数值超过10亿，建议改小参数值，等待观察数据库年龄恢复情况。

• 写入数据量太大

  如果磁盘吞吐量达到性能上限，需要更换磁盘类型。

  需要手动执行vacuum清理年龄大的表。

• 临时表长时间未释放

  临时表不会执行autovacuum，如果是临时表导致数据库年龄增长，需要释放客户端连接使临时表被回收。

• 长事务导致数据库年龄增加

  如果是长事务，先取消长事务，再对数据库年龄大的表执行vacuum。

  1. 取消长事务

     select pg_cancel_backend($PID);

  2. 清理表

     vacuum 表名;

参考资料

PostgreSQL官方文档