PostgreSQL/PPAS CPU使用率高的排查及解决办法

PostgreSQL/PPAS CPU使用率高的排查及解决办法

一、问题描述

RDS for PostgreSQL/PPAS使用过程中,可能会遇到CPU使用率过高甚至达到100%的情况。本文将介绍造成该状况的常见原因以及解决方法,并通过CPU使用率为100%的典型场景,来分析引起该状况的排查及其相应的解决方案。

二、解决方案

如果您对实例或数据有修改、变更等风险操作,务必注意实例的容灾、容错能力,确保数据安全。
如果您对实例(包括但不限于ECS、RDS)等进行配置与数据修改,建议提前创建快照或开启RDS日志备份等功能。
如果您在阿里云平台授权或者提交过登录账号、密码等安全信息,建议您及时修改。

CPU使用率到达100%,首先检查是不是业务高峰活跃连接陡增,而数据库预留的资源不足。需要查看问题发生时,活跃的连接数是否比平时多很多。对于RDS for PostgreSQL/PPAS,数据库上的连接数变化,可以从控制台的监控信息中看到。而当前活跃的连接数,可以直接连接数据库,使用下列查询语句得到。

select count( * ) from pg_stat_activity where state not like '%idle';

三、追踪慢SQL

如果活跃连接数的变化处于正常范围,则可能是当时有性能很差的SQL被大量执行。由于RDS有慢SQL日志,可以通过这个日志,定位到当时比较耗时的SQL来进一步做分析。但通常问题发生时,整个系统都处于停滞状态,所有SQL都慢下来,当时记录的慢SQL可能非常多,并不容易找到目标。这里介绍几种追查慢SQL的方法。

1、第一种方法是使用pg_stat_statements插件定位慢SQL,仅适用于PostgreSQL,步骤如下。
i、如果没有pg_stat_statements插件,需要先手动创建。要利用插件和数据库系统里面的计数信息(如SQL执行时间累积等),而这些信息是不断累积的,包含了历史信息。为了更方便的排查当前的CPU过高问题,要先使用以下命令重置计数器。

create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();

ii、等待一段时间(例如1分钟),使计数器积累足够的信息。
iii、参考以下命令查询最耗时的SQL,一般就是导致问题的直接原因。

select * from pg_stat_statements order by total_time desc limit 5;

iv、参考以下SQL语句,查询读取Buffer次数最多的SQL,这些SQL可能由于所查询的数据没有索引,而导致了过多的Buffer读,也同时大量消耗了CPU。

select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

2、第二种方法是直接通过pg_stat_activity视图,参考以下查询SQL语句,查看当前长时间执行,一直不结束的SQL。这些SQL也可能造成CPU过高。

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;

3、第3种方法是从数据表上表扫描(Table Scan)的信息开始查起,查找缺失索引的表。数据表如果缺失索引,大部分热数据又都在内存时(例如内存8G,热数据6G),此时数据库只能使用表扫描,并需要处理已在内存中的大量无关记录,导致耗费大量CPU。特别是对于表记录数超过100的表,一次表扫描占用大量CPU(基本把一个CPU占满)和多个连接并发(例如上百连接)。
i、参考以下SQL语句,查出使用表扫描最多的表。

select * from pg_stat_user_tables 
where n_live_tup > 100000 and seq_scan > 0 
order by seq_tup_read desc limit 10;

ii、参考以下SQL语句,查询当前正在运行的访问到上述表的慢查询。

select * from pg_stat_activity 
where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';

说明:也可以通过pg_stat_statements插件定位涉及到这些表的查询,如下所示。

select * from pg_stat_statements 
where query ilike '%<table>%'
order by shared_blks_hit+shared_blks_read desc 
limit 3;

四、处理慢SQL

对于上面的方法查出来的慢SQL,如下所示,首先需要做的是结束掉它们,使业务先恢复。

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]语句,更新表的统计信息,使查询计划更准确。为避免对业务影响,最好在业务低峰执行。
说明:[$Table]为查询涉及的表。
2、选择一条如下SQL语句执行,查看SQL的执行计划,第一条SQL语句不会实际执行SQL语句,第二条SQL语句会实际执行而且能得到详细的执行信息,对其中的Table Scan涉及的表,建立索引。

explain [$Query_Text]
explain (buffers true, analyze true, verbose true) [$Query_Text]

说明:[$Query_Text]为SQL文件或语句。
3、重新编写SQL语句,去除掉不必要的子查询、改写UNION ALL、使用JOIN CLAUSE固定连接顺序等,都是进一步深度优化SQL语句的手段,这里不再深入说明。

.
.
.
.
.
来自:https://help.aliyun.com/knowledge_detail/43562.html
.
.

  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值