数据库年龄,精准诊断新模式

PostgreSQL(后简称PG)中,每张表都是有年龄的。那么就像人一样,上了年纪是容易出事的。

数据库年龄的含义

PG数据库年龄其实指的是数据库的事务ID(后面简称xid),PG的MVCC事务依赖于比较xid值的大小来实现:如果一个行版本的插入xid大于当前事务的xid,它就是"属于未来的",那么就不应该对当前事务可见。

如上图,xid类型是uint32,可以理解为一个没有端点的环。一个长时间(超过40亿个事务)运行的集簇会遭受到事务ID回卷问题,即xid计数器回卷到0(注)。

一旦一个行版本创建时被分配了一个特定的普通 XID,该行版本将成为接下来20亿个事务的"过去"(与我们谈论的具体哪个普通 XID 无关)。如果在20亿个事务之后该行版本仍然存在,它将突然变得好像在未来。

为了避免发生这种情况,PostgreSQL会在数据库年龄离20亿还差100w的时候就会自动变成只读,只允许超级用户以单用户模式登录进来执行清理操作。所以,有必要提前清理每个数据库中的表。

这个清理动作由autovacuum进程来周期性地实施。VACUUM会把行标记为冻结,这表示它们是被一个在足够远的过去提交的事务所插入,从MVCC的角度来看,效果就是该插入事务对所有当前和未来事务来说当然都是可见的。

注:事实上,0,1和2是PG保留的3个xid,普通事务ID是从3开始的,回卷也是回到3,为了便于理解,PG手册中没有过多说明。

初步分析

告警的意思是当前业务库的年龄已经超过了5亿。这个不合理,PG的autovacuum_freeze_max_age参数,控制需要被冻结的行的最大年龄,默认值是2亿,即对于年龄超过2亿的行,PG的autovacuum进程(自动回收清理垃圾的进程)会自动扫描全表,把老的行给冻结掉。所以生产库运行的半年来,年龄一直维持在2亿附近,那么为什么突然autovacuum就没有效果了呢?继续分析。

定位高龄表

登录生产环境的业务库,从系统表pg_class中查看当前年龄最大的表有哪些。从下面的结果来看,年龄超过5亿的表有3张,而且名字一样,从命名方式来看,应该是临时表。进一步查看表定义,通过schema确认,这3张表都是临时表。

确认异常发生时点

找到问题点之后,回到监控,查看数据年龄是从什么时间开始出现异常的。查看zabbix中数据库年龄的监控,发现数据库的年龄是从12月21号凌晨3点左右开始逐渐升高,到告警发生已经持续升高了4天。照这个速度,如果不解决问题,20天左右就会耗尽xid,导致PG强制关闭。

临时表问题

那么问题就集中在这个临时表上了,临时表比较特殊,PG的autovacuum进程并不会回收临时表的垃圾,也不会收集临时表的统计信息。

正常情况下,临时表用完就会随着连接的断开而被销毁掉。而这3张表应该是3个不同的连接创建的,且已经保留了4天,那么有可能是应用使用的连接池,或者连接泄漏了。在数据库确认了下,确实有一些比较古老的连接存在。

这些临时表应该是其中某几个连接创建的,与项目组确认之后,他们没有使用连接池,并且这些临时表已经没人使用了,于是决定drop掉这些临时表。从监控看到,临时表drop掉之后,数据库年龄就恢复正常了。

根本原因

我们分析,有下面2个可能的原因:

应用SQL中使用完临时表没有及时清理

应用程序存在连接泄露的bug

解决方案

创建临时表时加上on commit drop,即临时表在事务提交后就会被drop掉(注)

应用程序需要检查有无连接泄漏的问题

注:PG临时表的生命周期默认情况下,是会话级的,即连接断开时才会清理表定义。

临时表在一个事务块结束时的行为有三种选项:

PRESERVE ROWS在事务结束时不采取特殊的动作。这是默认行为。

DELETE ROWS在每一个事务块结束时将删除临时表中的所有行。实质上,在每一次提交时会完成一次自动的TRUNCATE。

PRESERVE ROWS在事务结束时不采取特殊的动作。这是默认行为。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180927A0M2CC00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码关注腾讯云开发者

领取腾讯云代金券