1 问题现象

服务器的cpu利用率持续高于85%，客户频繁收到告警

客户的监控平台显示，数据库进程占用了大量cpu资源

2 排查步骤

2.1 连接数的变化

cpu利用率猛增，首先怀疑是不是业务高峰期活跃连接陡增，而数据库预留资源不足造成的结果

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

使用以上的sql语句排查数据库的活跃连接

从查询结果来看，并不是业务高峰导致的cpu利用率高

2.2 查看数据库线程的cpu占用情况

只有活跃的CPU才会使CPU的使用增高

查看具体哪个会话占用高资源，使用top查看cpu使用率高的进程

查看数据库进程的pid

top

可以看到数据库进程的pid为2105610，cpu利用率高达310.6%

根据数据库进程的pid，查询各线程的资源占用情况

top -H -p 2105610

查询到有4个线程的cpu利用率达到70%以上，是造成此次异常的原因，这里显示的pid：338211、338212、338213、338214是对应的线程id

2.3 在数据库中查询对应的会话

根据上一步我们查到的线程id，我们可以在数据库中查询这些线程对应的会话，使用如下的sql语句（适用于opengasuss系的数据库）：

--标红部分替换为实际的线程id

select psa.* from pg_stat_activity psa join pg_os_threads pot on psa.pid=pot.pid where pot.lwpid in ('338211','338212','338213','338214');

查询到如下会话

找到引发异常的会话及其对应的SQL语句

2.4 查看数据库日志

观察数据库的运行日志，其中存在大量报错的语句：

比较之后，发现其IP、端口、sql语句等都与上一步定位到的会话一致

将这部分语句发送给客户，让客户内部排查

2.5 慢sql

如果活跃连接数的变化处于正常范围，则很大概率是当时有性能很差的SQL被大量执行所致。这里所说的性能差，并不一定指的是SQL的执行时间长，有时看似执行速度很快的SQL大量执行时也会造成CPU的升高。

pg_stat_statements

--创建插件，已有可忽略
create extension pg_stat_statements;
--重置统计信息
select pg_stat_statements_reset();

等待一段时间，使计数器积累足够的信息

查询最耗时的SQL

select * from pg_stat_statements order by total_exec_time desc limit 5;

查询读取buffer次数最多的SQL，这类SQL可能由于所查询的数据没有索引，而导致了过多的buffer读，也同时消耗了大量的CPU。

稍后我们会介绍为什么全表扫描会引起cpu利用率的升高

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

pg_stat_activity

查看当前长时间执行，一直不结束的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;

表扫描TableScan

查询数据表上的表扫描，数据表如果缺失索引，大部分热数据又都在内存时，此时数据库只能使用表扫描，并需要处理已在内存中的大量无关记录，耗费大量CPU。

查询使用表扫描最多的表：

注意：每次重启数据库时，pg_stat_user_tables的n_live_tup等信息需要重新收集

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

查询当前正在运行的上述表上的慢查询：

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;

3 为什么全表扫描会耗费大量CPU资源

数据读取：在全表扫描过程中，数据库需要从磁盘读取整个表的所有数据。如果表非常大，这将涉及到大量的磁盘I/O操作，而磁盘I/O通常是数据库操作中的瓶颈之一。频繁的磁盘读取会消耗大量的CPU资源，因为CPU需要等待磁盘操作完成。
数据处理：全表扫描意味着数据库需要检查表中的每一行数据，以确定哪些行符合查询条件。对于每一行数据，CPU都需要执行比较操作，这在数据量大时会非常耗时。此外，如果查询涉及到计算（如函数、表达式等），CPU的负担会更重。
内存使用：数据库通常会将读取的数据缓存到内存中，以提高访问速度。全表扫描会占用大量的内存资源，这可能会导致内存不足，从而触发更多的磁盘交换（swap）操作，进一步增加CPU的负担。
锁和并发：全表扫描可能会锁定整个表，阻止其他事务对表的并发访问。这不仅会降低数据库的并发性能，还可能导致更多的事务等待锁释放，从而增加CPU的上下文切换开销。
排序和聚合操作：如果查询涉及到排序（ORDER BY）或聚合（GROUP BY、SUM、COUNT等），全表扫描后的数据需要在内存中进行排序或聚合计算，这同样会消耗大量的CPU资源。
查询优化器的选择：数据库的查询优化器在决定是否使用索引时，会根据统计信息和成本估算来选择最优的执行计划。如果查询优化器认为全表扫描的成本低于使用索引（例如，当表中的数据量很小，或者索引的选择性不高时），它可能会选择全表扫描，从而导致CPU资源的浪费。

为了减少全表扫描，提高数据库性能，通常需要：