Postgresql数据库磁盘快速增长的一个可能原因

结论

Postgresql中发现磁盘占用空间莫名快速增长时，有一种可能是因为pg_stat_activity中有异常的很久之前的查询导致的。
持续时间特别长的查询会导致Postgresql不停的维护该查询之后所有更新的数据记录，导致表大小快速的膨胀。

过程

起因

AWS上一套系统的RDS之前从Postgresql 9.5升级到了9.6。升级之后跑的好好的，突然在1月中接到警报，说磁盘不足。
很好奇,这是一个访问量不大的网站, 之前记得一百多个G空间的。怎么突然就警报空间不足了。

调查

登录AWS Console一看，吓了一跳，RDS的数据库剩余磁盘容量，2个星期内从180G下降到80G。空间下降这么快，肯定是哪边出了问题。
使用psql登录后台，查看了各表的大小

1
2
3
4
5
6
7
8
9
10
11
12
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

发现一个名为listings表的size异常的大。

首先第一反应，手动执行命令vacuum VERBOSE analyze listings来尝试清理listings表。
在输出结果中发现了奇怪的地方，xxxxxxx dead row versions cannot be removed yet.。提示有几百万条旧记录无法被删除。

解决

网上一通找，在Vacuum not removing dead tuples中找到了原因。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
There are three possibilities:
1) Active transactions:
   SELECT backend_xmin, xact_start, datname, pid, query
   FROM pg_stat_activity
   WHERE state <> 'idle'
   ORDER BY xact_start;
2) Prepared transactions:
   SELECT xid, prepared
   FROM pg_prepared_xacts;
3) Lagging replication slots:
   SELECT xmin, slot_name, active, active_pid
   FROM pg_replication_slots;
The "xid" columns will tell you which tuples VACUUM can
remove: Any tuple with "xmax" less than this are fair game.

检查pg_stat_activity中的sql，发现一个运行了长达两星期的listings查询, 为了维护该transaction，Postgresql不得不将后续所有数据的更新都保存了下来，导致了大量的磁盘消耗。
使用SELECT pg_terminate_backend PID杀死了这个查询。然后再使用vacuum VERBOSE analyze listings命名清理listings表，磁盘容量恢复到130个G。

疑问

至今还是不知道是什么原因导致一个查询可以在客户端已经terminate的情况下，在数据库端执行了两个多星期还没有被终结。即使是全表都扫一遍撑死也就几分钟的事情。

Reference

留言

▲