pgsql调优转载

持续更新 —————————–

官方文档

根据进程可以查询命令执行的时间和效率

  • 查询耗时
SELECT procpid, START, now() - START AS lap, current_query  FROM ( SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid,
pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query  FROM (SELECT
pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>'  and procpid=58762  ORDER BY lap DESC;
  • 查询连接 查询进程
SELECT * FROM pg_stat_activity  where pid==?? and  query =?? and 
  • pg_stat_statements
修改postgresql.conf的shared_preload_libraries增加pg_stat_statements来载入

③执行SQL启动插件:create extension pg_stat_statements;
  1. 重置计数器(可选操作)。

    select pg_stat_reset();
    select pg_stat_statements_reset();
  2. 使用命令查看最耗时的SQL

    select * from pg_stat_statements order by total_time desc limit 5;
  3. 查询读取Buffer次数最多的SQL,buffer次数多,可能是因为没有索引,也同时导致了cpu高。

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

获取后面的

select to_char(trigger_start_time,'YYYY-MM-DD 00:00:00')
        as timePoint,sum(trigger_count) as totalCount from network_attack
        where
        trigger_start_time >= to_timestamp($1,'yyyy-MM-dd hh24:mi:ss')
        and trigger_start_time <  to_timestamp($2,'yyyy-MM-dd hh24:mi:ss')
        group by timePoint
        order by timePoint desc
relid    oid    一个表的 OID
schemaname    name    这个表所在的模式的名称
relname    name    这个表的名称
seq_scan    bigint    在这个表上发起的顺序扫描的次数
seq_tup_read    bigint    被顺序扫描取得的活着的行的数量
idx_scan    bigint    在这个表上发起的索引扫描的次数
idx_tup_fetch    bigint    被索引扫描取得的活着的行的数量
n_tup_ins    bigint    被插入的行数
n_tup_upd    bigint    被更新的行数(包括 HOT 更新的行)
n_tup_del    bigint    被删除的行数
n_tup_hot_upd    bigint    被更新的 HOT 行数(即不要求独立索引更新的行更新)
n_live_tup    bigint    活着的行的估计数量
n_dead_tup    bigint    死亡行的估计数量
n_mod_since_analyze    bigint    从这个表最后一次被分析后备修改的行的估计数量
last_vacuum    timestamp with time zone    上次这个表被手动清理的时间(不统计VACUUM FULL)
last_autovacuum    timestamp with time zone    上次这个表被自动清理守护进程清理的时间
last_analyze    timestamp with time zone    上次这个表被手动分析的时间
last_autoanalyze    timestamp with time zone    上次这个表被自动清理守护进程分析的时间
vacuum_count    bigint    这个表已被手工清理的次数(不统计VACUUM FULL)
autovacuum_count    bigint    这个表已被自动清理守护进程清理的次数
analyze_count    bigint    这个表已被手工分析的次数
autoanalyze_count    bigint    这个表已被自动清理守护进程分析的次数

seq_tup_read 远大于 idx_tup_fetch 说明索引建的有问题

n_tup_upd 说明有频繁更新的动作