PolarDB PostgreSQL版在使用过程中,可能会出现CPU使用率异常升高甚至达到满载的情况。本文将介绍造成这种情况的常见原因和排查方法,以及对应的解决方案。
问题原因
业务量上涨
当CPU使用率上升时,最有可能的情况是业务量的上涨导致数据库使用的计算资源增多。
排查方法
需要排查目前数据库的活跃连接数是否比平时多,您可以通过以下两种方法进行排查:
如果数据库配备了监控系统,则活跃连接数的变化情况可以通过图表的形式观察到。
如果数据库没有配备监控系统,则需要连接到数据库,执行以下命令来获取当前活跃连接数。
SELECT COUNT(*) FROM pg_stat_activity WHERE state NOT LIKE 'idle';
说明其中:
pg_stat_activity
是PolarDB PostgreSQL版的内置系统视图,该视图返回的每一行都是一个正在运行中的PolarDB PostgreSQL版进程。state
表示进程当前的状态。取值如下:active
:进程正在执行查询。idle
:进程空闲,正在等待新的客户端命令。idle in transaction
:进程处于事务中,但目前暂未执行查询。idle in transaction (aborted)
:进程处于事务中,且有一条语句发生过错误。fastpath function call
:进程正在执行一个fast-path函数。disabled
:进程的状态采集功能被关闭。
上述命令可以查询到所有非空闲状态的进程数,即可能占用CPU的活跃连接数。如果活跃连接数较平时更多,则CPU使用率上升是由于业务量上涨导致。
问题原因
慢查询
如果CPU使用率上升,而活跃连接数的变化范围处在正常范围内,则有可能出现了较多性能较差的慢查询。这些慢查询可能在很长一段时间里占用了较多的CPU,导致CPU使用率上升。
PolarDB PostgreSQL版提供了慢查询日志的功能,执行时间高于log_min_duration_statement
的SQL将会被记录到慢查询日志中。然而当CPU占用率接近满载时,将会导致整个系统的停滞,所有SQL的执行可能都变慢,所以慢查询日志中记录的信息可能非常多,不易排查。
排查方法
定位执行时间较长的慢查询
pg_stat_statements
插件能够记录数据库服务器上所有SQL语句在优化和执行阶段的统计信息。由于该插件需要使用共享内存,因此插件名已经被配置在shared_preload_libraries
参数中。
如果当前数据库中没有创建pg_stat_statements
插件,执行以下命令,创建该插件。该过程将会注册插件提供的函数及视图:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
pg_stat_statements
插件和数据库系统本身都会不断累积统计信息。为了排查CPU异常升高后这段时间内的问题,需要将数据库和插件中留存的统计信息清空,然后开始收集从当前时刻开始的统计信息:-- 清空当前数据库的统计信息 SELECT pg_stat_reset(); -- 清空 pg_stat_statements 插件截止目前收集的统计信息 SELECT pg_stat_statements_reset();
等待1~2分钟,使数据库和插件充分采集这段时间内的统计信息。
统计信息收集完毕后,参考以下命令查询执行时间最长的5条SQL。
SELECT * FROM pg_stat_statements ORDER BY total_plan_time DESC LIMIT 5; SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
定位读取Buffer数量较多的慢查询
当一张表缺少索引,而对该表的查询基本上都是点查时,数据库将不得不使用全表扫描,并在内存中使用过滤条件来过滤掉大量的无效记录,导致CPU使用率大幅上升。
通过pg_stat_statements
插件的统计信息,参考以下命令,查询截止目前读取Buffer数量最多的5条SQL。
SELECT * FROM pg_stat_statements
ORDER BY shared_blks_hit + shared_blks_read DESC
LIMIT 5;
通过PolarDB PostgreSQL版内置系统视图pg_stat_user_tables
中的统计信息,也可以统计出使用全表扫描时扫描次数最多的表。
参考以下命令,查询具备一定规模数据量(元组约为10万个)且使用全表扫描获取到的元组数量最多的5张表。
SELECT * FROM pg_stat_user_tables
WHERE n_live_tup > 100000 AND seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 5;
定位长时间执行不结束的慢查询
通过系统内置视图pg_stat_activity
,可以查询出长时间执行不结束的SQL,这些SQL有极大可能造成CPU使用率过高。
参考以下命令,查询执行时间最长,且当前还未退出的5条SQL。
SELECT
*,
extract(epoch FROM (NOW() - xact_start)) AS xact_stay,
extract(epoch FROM (NOW() - query_start)) AS query_stay
FROM pg_stat_activity
WHERE state NOT LIKE 'idle%'
ORDER BY query_stay DESC
LIMIT 5;
结合前一步中排查到的使用全表扫描次数最多的表,参考以下命令,获取在该表上执行时间超过一定阈值(例如10s)的慢查询。
SELECT * FROM pg_stat_activity
WHERE
state NOT LIKE 'idle%' AND
query ILIKE '%表名%' AND
NOW() - query_start > interval '10s';
解决方案
对于异常占用CPU较高的SQL,如果仅有个别非预期SQL,则可以通过给后端进程发送信号的方式,先让SQL执行中断,使CPU使用率恢复正常。参考以下命令,以慢查询执行所使用的进程pid(
pg_stat_activity
视图的pid
列)作为参数,中止相应的进程的执行。SELECT pg_cancel_backend(pid); SELECT pg_terminate_backend(pid);
如果执行较慢的SQL是业务上必要的SQL,则需要对它进行调优。可以对SQL涉及到的表进行采样,更新其统计信息,使优化器能够产生更加准确的执行计划。
说明采样需要占用一定的CPU,最好在业务低谷期进行。
ANALYZE 表名;
对于全表扫描较多的表,可以在常用的过滤列上创建索引,使用索引扫描,减少全表扫描在内存中过滤不符合条件的记录所造成的CPU浪费。