CPU等待事件持续较多,出现CPU瓶颈,需进行具体查询分析。
可观察polar_stat_activity_rt中哪类进程CPU比较高,数值单位是CPU使用率。
SELECT
backend_type,SUM(cpu_sys cpu_user) AS cpu_rate
FROM polar_stat_activity_rt
GROUP BY backend_type
ORDER BY cpu_rate desc; \watch 1
如果CPU等待事件的数量持续大于CPU核数,说明并发数量过高。可通过polar_stat_activity查看是哪些SQL造成CPU过高。
SELECT
query, COUNT(*) AS wait_count
FROM polar_stat_activity
WHERE state='active' AND backend_type='client backend' AND wait_event_type IS NULL
GROUP BY query
ORDER BY wait_count DESC; \watch 1
也可通过pg_stat_database中的 xact_commit xact_rollback 增量判断是否TPS超过预期,或者是通过 polar_stat_query_count增量判断是否QPS超过预期。
确定哪些SQL造成CPU高后,可以通过观察pg_stat_statements和polar_stat_sql视图来获取SQL的统计信息。