PostgreSQL数据库服务器CPU使用率100% 处理

        数据库运维过程中,一个常见而又紧急的问题就是突发的CPU使用率100%,造成业务停顿,下面是一些主要的分析、诊断方法:


一、查看连接数变化,结合监控,查看出现问题时的活动连接数变化

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


二、追踪慢sql
如果活跃连接数的变化处于正常范围,则很大概率可能是当时有性能很差的SQL被大量执行导致。打开PostgreSQL慢SQL日志,我们可以通过这个日志,定位到当时比较耗时的SQL来进一步做分析。但通常问题发生时,整个系统都处于停滞状态,所有SQL都慢下来,当时记录的慢SQL可能非常多,并不容易排查罪魁祸首。介绍几种在问题发生时,即介入追查慢SQL的方法。

1. 第一种方法是使用pg_stat_statements插件定位慢SQL,步骤如下:

1.1. 如果没有创建这个插件,需要手动创建。我们要利用插件和数据库系统里面的计数信息(如SQL执行时间累积等),而这些信息是不断累积的,包含了历史信息。为了更方便的排查当前的CPU满问题,我们要先重置计数器。
create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();
1.2. 等待一段时间(例如1分钟),使计数器积累足够的信息。
1.3. 查询最耗时的SQL(一般就是导致问题的直接原因)。
select * from pg_stat_statements order by total_time desc limit 5;
1.4. 查询读取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对应造成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)的信息开始查起,查找缺失索引的表。数据表如果缺失索引,大部分热数据又都在内存时,此时数据库只能使用表扫描,并需要处理已在内存中的大量的无关记录,而耗费大量CPU。特别是对于表记录数超100的表,一次表扫描占用大量CPU(基本把一个CPU占满),多个连接并发(例如上百连接),把所有CPU占满。


3.1. 通过下面的查询,查出使用表扫描最多的表:

select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;
3.2. 查询当前正在运行的访问到上述表的慢查询:

select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';
3.3. 也可以通过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,首先需要做的可能是Cancel或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 或explain (buffers true, analyze true, verbose true) 命令,查看SQL的执行计划(注意,前者不会实际执行SQL,后者会实际执行而且能得到详细的执行信息),对其中的Table Scan涉及的表,建立索引。

3. 重新编写SQL,去除掉不必要的子查询、改写UNION ALL、使用JOIN CLAUSE固定连接顺序等到,都是进一步深度优化SQL的手段,这里不再深入说明。

上一篇:读 perf 笔记 简写


下一篇:Linux C如何判断文件类型?