2020-03-04 PG崩溃 - 一个查询sql把硬盘打满了_pgsql_tmp硬盘撑爆了

崩溃现象

3月3日晚，加班生产的同事们报告系统登录不上去，IT的同事检查监控，数据库CPU打爆、I/O报警，但是还没查出原因数据库自行恢复了。
3月4日一早，再次发生相同问题，直到中午也没有自行恢复，重启数据库解决。同时发现，数据库临时文件不停增长，直到把硬盘占满，数据库无法访问。重启后临时文件没了，之后又暴涨至崩溃，只能临时看着数据库硬盘，快满的时候重启一下数据库避免死机。

调查经过

崩溃原因查明，数据库临时文件把硬盘占满导致。那么又是什么原因导致临时文件突然暴涨呢。
PG临时文件如下：

total 550597824
-rw------- 1 postgres postgres 3178496 Mar  4 13:09 pgsql_tmp126504.0
-rw------- 1 postgres postgres 3137536 Mar  4 13:09 pgsql_tmp126504.1
-rw------- 1 postgres postgres 3194880 Mar  4 13:09 pgsql_tmp126504.10
-rw------- 1 postgres postgres 3129344 Mar  4 13:09 pgsql_tmp126504.100
-rw------- 1 postgres postgres 3203072 Mar  4 13:09 pgsql_tmp126504.1000
-rw------- 1 postgres postgres 3145728 Mar  4 13:09 pgsql_tmp126504.1001
-rw------- 1 postgres postgres 3186688 Mar  4 13:09 pgsql_tmp126504.1002
-rw------- 1 postgres postgres 3186688 Mar  4 13:09 pgsql_tmp126504.1003
-rw------- 1 postgres postgres 3145728 Mar  4 13:09 pgsql_tmp126504.1004
-rw------- 1 postgres postgres 3153920 Mar  4 13:09 pgsql_tmp126504.1005
-rw------- 1 postgres postgres 3170304 Mar  4 13:09 pgsql_tmp126504.1006
-rw------- 1 postgres postgres 3153920 Mar  4 13:09 pgsql_tmp126504.1007
1
2
3
4
5
6
7
8
9
10
11
12
13

文件名tmp后数字为生成该临时文件sql的PID。最后发现所有的PID出自下面这个sql。

SELECT 
 N.NODE_ID AS NODE_ID,
 ST_ASTEXT(N.GEO) AS N_GEO_TXT,
 EDITVER,
 CHANGE_TYPE,
 CHANGESET_ID,
 ISDEL,
 CONFIRM_TIMESTAMP AS UPDATETIME,
 CONFIRM_UID AS UID,
 AUTOCHECK,
 MANUALCHECK 
 FROM TB_BGNODE AS N LEFT JOIN TB_BACKGROUND_NODE AS WN ON N.NODE_ID = WN.NODE_ID 
 WHERE WN.BG_ID IN (#{ids}); -- 此处id为300个
1
2
3
4
5
6
7
8
9
10
11
12
13

调出该sql在后台执行发现，查询非常慢，并且产生大量临时文件，分析sql结果如下：

PostgreSQL 使用 hash join 进行了顺序扫描(seq scan)。tb_bgnode有1.9亿多条26G数据，PG进行了顺序扫描加载到内存HASH表，内存不足时写入硬盘。单个sql写入26G，这个sql又是被频繁调用，因此几分钟就能把硬盘占满。
初步解决办法，将300个id改为100个id，经测试执行计划改变，使用index scan，20ms即可执行完。

修改代码后问题解决。

解决后的思考

由于前一天并没有修改代码，问题是突然出现，生产近段时间项目会造成tb_bgnode数据暴增，分析可能是数据量的变化导致的PG选择了一个不合理的执行计划。
在网上查到这篇文章《一个单一的 PostgreSQL 配置如何将缓慢的查询性能提升了 50 倍》

如上文所说，由于我们使用的是固态硬盘，默认数据库配置是针对机械硬盘进行调整的。
修改random_page_const为1，重试300个id的查询，变为使用index scan，快了9000倍，并且不再生成大量临时文件。