请选择 进入手机版 | 继续访问电脑版

PostgreSQL CPU占用高分析

[复制链接]
菜鸡 发表于 2021-1-1 18:32:19 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
查看当前活跃的DB session 正在运行的SQL语句(运行时间凌驾10秒)

  1. 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;
复制代码
按 total_time 列出查询,并查看哪个查询在数据库中耗费的时间最多

  1. SELECT round(total_time*1000)/1000 AS total_time,query        FROM pg_stat_statements        ORDER BY total_time DESC limit 5;
复制代码
查看哪些table未实时做vacuum,以及未实时收集统计信息

  1. 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;
复制代码
查看有没有被锁的session

  1. 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;
复制代码
推荐创建如下session

pg_stat_tables
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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;
复制代码
要启用 pg_stat_statements,请修改现有的自定义参数组并设置以下值:
  1. shared_preload_libraries = pg_stat_statementstrack_activity_query_size = 2048pg_stat_statements.track = ALLpg_stat_statements.max = 10000
复制代码
选择立刻应用,然后重启数据库实例。然后,在要监控的数据库上运行与以下内容类似的下令:
注意:以下示例在“demo”数据库中安装扩展。
  1. demo=> select current_database();current_database------------------demo(1 row)
复制代码
  1. demo=> CREATE EXTENSION pg_stat_statements;
复制代码
设置 pg_stat_statements 后,您可以使用以下方法之一监控输出:
按 total_time 列出查询,并查看哪个查询在数据库中耗费的时间最多:
  1. SELECT round(total_time*1000)/1000 AS total_time,queryFROM pg_stat_statementsORDER BY total_time DESC limit 2;
复制代码
列出查询及调用总数、总行数和返回的行数:
  1. SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percentFROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
复制代码
基于每次执行列出查询,以便随时间对查询举行抽样:
  1. SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_writtenFROM pg_stat_statementsWHERE calls != 0ORDER BY total_time DESC LIMIT 5;
复制代码
确定导致高 CPU 使用率的查询后,可以使用以下方法进一步低沉 CPU 使用率:
如果有时机举行调解,请使用 EXPLAIN 和 EXPLAIN ANALYZE 来识别告诫。有关更多信息,请参阅有关 EXPLAIN 的 PostgreSQL 文档。
如果有重复运行的查询,请使用准备的语句来低沉 CPU 的压力。
  https://aws.amazon.com/cn/premiumsupport/knowledge-center/rds-aurora-postgresql-high-cpu/
https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-query-logging/

来源:https://blog.csdn.net/sunny_day_day/article/details/112001481
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )