记一次Postgres CPU爆满故障

问题描述

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

问题排查

服务器配置不够

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

查看服务器负载

  • free -m

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

  • top

数据库占用CPU过高

连接数过多

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

SELECT 
  COUNT(*) 
FROM 
  pg_stat_activity 
WHERE 
  STATE NOT LIKE '%idle';

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

慢SQL

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

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掉他们,使业务先恢复。

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的方式来优化查询效率。

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

参考

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

posted @ 2020-11-23 20:21  同上一闪一闪  阅读(301)  评论(0编辑  收藏  举报