PostgreSQL CPU 100%问题排查

您所在的位置：网站首页 › pg数据库日志文件满了 › PostgreSQL CPU 100%问题排查

PostgreSQL CPU 100%问题排查

2024-07-08 13:43| 来源: 网络整理| 查看: 265

前言

一般来说, CPU 问题是由低效SQL引发，而造成低效SQL 的原因有：

SQL 写法不够优化

表的数据量发生了变化，但统计信息未及时更新

数据库本身的bug

分析解决

为了进一步调查这个问题，请参考如下建议：

请参考文档【1】其中提到的排查高 CPU 利用率的方法。比如：本机 PostgreSQL 视图和目录（例如 pg_stat_statements、pg_stat_activity 和 pg_stat_user_tables）查看数据库级详细信息。有关更多信息，请参阅有关监控数据库活动和 pg_stat_statements 的 PostgreSQL 文档。

PostgreSQL 提供各种日志记录参数来记录长时间运行的查询、autovacuum、锁定等待以及连接和断开连接请求。有关更多信息，请参阅如何使用 Amazon RDS for PostgreSQL 启用查询日志记录？

确定原因后，可以使用以下方法进一步降低 CPU 使用：

如果有机会进行调整，请使用 EXPLAIN 和 EXPLAIN ANALYZE 来识别警告。有关更多信息，请参阅有关 EXPLAIN 的 PostgreSQL 文档。

有用的查询

以下是一些有用的查询，请在您的环境中运行，看是否有慢查询等

1. 查看当前活跃的DB session 正在运行的SQL语句（运行时间超过10秒） SELECT now() - query_start as "runtime", usename,application_name, client_hostname, datname, state, query FROM pg_stat_activity WHERE now() - query_start > '10 seconds'::interval and state!='idle' ORDER BY runtime DESC; 2. 按 total_time 列出查询，并查看哪个查询在数据库中花费的时间最多 SELECT round(total_time*1000)/1000 AS total_time,query FROM pg_stat_statements ORDER BY total_time DESC limit 5; 3. 查看哪些table未及时做vacuum，以及未及时收集统计信息 SELECT relname, n_live_tup, n_dead_tup, trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%", to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date, to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date FROM pg_stat_all_tables ORDER BY last_autovacuum; 4. 查看有没有被锁的session select pid, usename, pg_blocking_pids(pid) as blocked_by, query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0; 5. 推荐创建如下session pg_stat_tables CREATE OR REPLACE VIEW pg_stat_tables AS WITH s AS ( SELECT *, cast((n_tup_ins + n_tup_upd + n_tup_del) AS numeric) AS total FROM pg_stat_user_tables ) SELECT s.schemaname, s.relname, s.relid, s.seq_scan, s.idx_scan, CASE WHEN s.seq_scan + s.idx_scan = 0 THEN 'NaN'::double precision ELSE round(100 * s.idx_scan/(s.seq_scan+s.idx_scan),2) END AS idx_scan_ratio, s.seq_tup_read, s.idx_tup_fetch, sio.heap_blks_read, sio.heap_blks_hit, CASE WHEN sio.heap_blks_read = 0 THEN 0.00 ELSE round(100*sio.heap_blks_hit/(sio.heap_blks_read+sio.heap_blks_hit),2) END AS hit_ratio, n_tup_ins, n_tup_upd, n_tup_del, CASE WHEN s.total = 0 THEN 0.00 ELSE round((100*cast(s.n_tup_ins AS numeric)/s.total) ,2) END AS ins_ratio, CASE WHEN s.total = 0 THEN 0.00 ELSE round((100*cast(s.n_tup_upd AS numeric)/s.total) ,2) END AS upd_ratio, CASE WHEN s.total = 0 THEN 0.00 ELSE round((100*cast(s.n_tup_del AS numeric)/s.total) ,2) END AS del_ratio, s.n_tup_hot_upd, CASE WHEN s.n_tup_upd = 0 THEN 'NaN'::double precision ELSE round(100*cast(cast(n_tup_hot_upd as numeric)/n_tup_upd as numeric), 2) END AS hot_upd_ratio, pg_size_pretty(pg_relation_size(sio.relid)) AS "table_size", pg_size_pretty(pg_total_relation_size(sio.relid)) AS "total_size", s.last_vacuum, s.last_autovacuum, s.vacuum_count, s.autovacuum_count, s.last_analyze, s.last_autoanalyze, s.analyze_count, s.autoanalyze_count FROM s, pg_statio_user_tables AS sio WHERE s.relid = sio.relid ORDER BY relname; pg_stat_indexes AS SELECT s.schemaname, s.relname, s.indexrelname, s.relid, s.idx_scan, s.idx_tup_read, s.idx_tup_fetch, sio.idx_blks_read, sio.idx_blks_hit, CASE WHEN sio.idx_blks_read + sio.idx_blks_hit = 0 THEN 'NaN'::double precision ELSE round(100 * sio.idx_blks_hit/(sio.idx_blks_read + sio.idx_blks_hit), 2) END AS idx_hit_ratio, pg_size_pretty(pg_relation_size(s.indexrelid)) AS "index_size" FROM pg_stat_user_indexes AS s, pg_statio_user_indexes AS sio WHERE s.relid = sio.relid ORDER BY relname; pg_stat_users CREATE OR REPLACE VIEW pg_stat_users AS SELECT datname, usename, pid, backend_start, (current_timestamp - backend_start)::interval(3) AS "login_time" FROM pg_stat_activity; pg_stat_queries CREATE OR REPLACE VIEW pg_stat_queries AS SELECT datname, usename, pid, (current_timestamp - xact_start)::interval(3) AS duration, waiting, query FROM pg_stat_activity WHERE pid != pg_backend_pid(); pg_stat_long_trx CREATE OR REPLACE VIEW pg_stat_long_trx AS SELECT pid, waiting, (current_timestamp - xact_start)::interval(3) AS duration, query FROM pg_stat_activity WHERE pid pg_backend_pid(); pg_stat_waiting_locks CREATE OR REPLACE VIEW pg_stat_waiting_locks AS SELECT l.locktype, c.relname, l.pid, l.mode, substring(a.query, 1, 6) AS query, (current_timestamp - xact_start)::interval(3) AS duration FROM pg_locks AS l LEFT OUTER JOIN pg_stat_activity AS a ON l.pid = a.pid LEFT OUTER JOIN pg_class AS c ON l.relation = c.oid WHERE NOT l.granted ORDER BY l.pid; 4. 请启用慢日志查询

请依据文档【2】启用如下两个参数，以将慢查询记录到日志中，方便进行分析：

可以修改以下参数，使日志记录的更详细，并将日志保存至 cloudwatch 中，这样将有利于您更好的发现问题。

log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_temp_files = 0 log_autovacuum_min_duration = 0 log_statement = 1 log_min_duration_statement = 200

[1] 如何排查 Amazon RDS 或 Amazon Aurora PostgreSQL 的高 CPU 利用率？ - https://aws.amazon.com/cn/premiumsupport/knowledge-center/rds-aurora-postgresql-high-cpu/?nc1=h_ls

[2] How do I enable query logging using Amazon RDS for PostgreSQL? - https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-query-logging/

【本文地址】

PostgreSQL CPU 100%问题排查

PostgreSQL CPU 100%问题排查

今日新闻

推荐新闻