如何找到postgres中疯狂增长的wal日志的语句

很久以前,我写过一个文章,《如何查找疯狂增长arch的进程》,讲述在oracle数据库中如何查找导致当前疯狂增长arch的session。今天,我们在postgresql数据库中也遇到了类似的问题。

在一个时间内,wal日志疯狂的增长,大约每分钟产生1G,而xlog疯狂cp去归档的结果,导致xlog来不及流复制到从库就已经切去了归档目录,进而导致了主从断开。

和开发一起诊断了这个问题之后,发现是一个update语句更新了大量的记录,每次更新1000多万记录中的200多万,这个表上14个字段中10个字段有有索引。更新时是非HOT update。这个语句每小时跑一次,每次跑的时候,有12个类似的语句。开发修改语句之后,增加了where的过滤条件后,减少了更新的数据量,从200多万减少了几行,从而解决了这个问题。

事后,我一直在想,如果没有开发人员,我们dba是否也可以从数据库本身的信息中发现问题?找到语句? 在一次偶然的机会中,和平安科技的梁海安聊天时中得到了答案。

在oracle中导致归档过多的,是过于频繁的dml语句。在pg中也是一样。只是在oracle中有v$sesstat中可以看到redo size的信息,而在pg的pg_stat_activity中只有session的信息,并没有语句的wal信息。但是由于wal的产生也是因为过多的dml引起的,我们可以从pg_catalog.pg_stat_all_tables中去找变动频繁的tuple(n_tup_ins,n_tup_upd,n_tup_del,主要是update),从而发现导致dml过多的语句。

解决方法如下:

1. 开启pg的dml审计。在postgresql.conf中设置log_statement=’mod’

2. 截取一个时间的pg_catalog.pg_stat_all_tables:

3. 截取另外一个时间的pg_catalog.pg_stat_all_tables:

4. 检查在单位时间内,那个对象的dml最多:

6. 此时我们已经得到了dml最多的对象,结合第1步的审计,就可以找到对应的语句了。

7. 清理战场,drop table orasup1; drop table orasup2;并且恢复审计粒度为log_statement=ddl


======== END ========



如果觉得文章好,欢迎打赏:
pay

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注