记一次Postgres CPU爆满故障

问题描述

公司项目测试环境调用某些接口的时候,服务器立即崩溃,并一定时间内无法提供服务。

问题排查

服务器配置不够

第一反应是服务器需要升配啦,花钱解决一切!毕竟测试服务器配置确实不高,2CPU + 4Gib,能干啥?不过问题是今天突然发生的,而且说崩就崩。凭着严谨的态度,还是要刨根问底地找下问题。

查看服务器负载

  • free -m

内存占用并不大,忘记截图了,反正看下来不是内存过高导致的崩溃

  • top

数据库占用CPU过高

连接数过多

业务高峰活跃连接陡增,活跃的连接数是否比平时多很多

1
2
3
4
5
6
SELECT 
COUNT(*)
FROM
pg_stat_activity
WHERE
STATE NOT LIKE '%idle';

查询下来只有3个连接,所以不是连接数导致的CPU过高

慢SQL

如果活跃连接数的变化处于正常范围,则可能是当时有性能很差的SQL被大量执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
select 
datname,
usename,
client_addr,
application_name,
state,
backend_start,
xact_start,
xact_stay,
query_start,
query_stay,
replace(
query,
chr(10),
' '
) as query
from
(
select
pgsa.datname as datname,
pgsa.usename as usename,
pgsa.client_addr client_addr,
pgsa.application_name as application_name,
pgsa.state as state,
pgsa.backend_start as backend_start,
pgsa.xact_start as xact_start,
extract(
epoch
from
(now() - pgsa.xact_start)
) as xact_stay,
pgsa.query_start as query_start,
extract(
epoch
from
(now() - pgsa.query_start)
) as query_stay,
pgsa.query as query
from
pg_stat_activity as pgsa
where
pgsa.state != 'idle'
and pgsa.state != 'idle in transaction'
and pgsa.state != 'idle in transaction (aborted)'
) idleconnections
order by
query_stay desc
limit
5;

可以看到,确实有一条慢SQL,而且属于奇慢无比,执行了接近1分钟还没执行完毕,基本可以定位,是慢SQL导致的CPU占用陡增。

问题解决

对于上面的方法查出来的慢SQL,首先需要做的是Kill掉他们,使业务先恢复。

1
2
select pg_cancel_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();
select pg_terminate_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();

如果这些SQL确实是业务上必需的,则需要对他们做如下优化:

  1. 对查询涉及的表,执行ANALYZE <table>VACUUM ANZLYZE <table>,更新表的统计信息,使查询计划更准确。为避免对业务影响,最好在业务低峰执行。
  2. 执行explain <query text>explain (buffers true, analyze true, verbose true) <query text>命令,查看SQL的执行计划(前者不会实际执行SQL,后者会实际执行而且能得到详细的执行信息),对其中的Table Scan涉及的表,建立索引。
  3. 重新编写SQL,去除掉不必要的子查询、改写UNION ALL、使用JOIN CLAUSE固定连接顺序等,都是进一步深度优化SQL的手段,这里不再深入说明。

总结

在查询语句中,尽量减少不必要的子查询,公司使用的ORM框架是Spring JPA,针对一些特别慢的HQL,可以采用直接执行SQL的方式来优化查询效率。

1
2
@Query(value = "select count(*) from example_table where example_id = :exampleId", nativeQuery = true)
int exampleNativeQuery(@Param("exampleId") Long exampleId);

参考

PostgreSQL/PPAS CPU使用率高的原因及解决办法