postgresql慢查询定位-操作sql轨迹跟踪

部署插件contrib

rpm -ivh postgresql10-contrib-10.10-1PGDG.rhel7.x86_64.rpm --force --nodeps

配置postgresql.conf

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all #抓取所有sql

注： 如果不做上述操作，如下操作会报错：pg_stat_statements must be loaded via shared_preload_libraries

创建扩展对象pg_stat_statements

该对象用于存储sql执行相关执行信息，也可以理解为一张表。

create extension pg_stat_statements;
select * from pg_stat_statements;

重启pg

systemctl restart postgresql-10.service

验证查询

SELECT  query, calls, total_time, (total_time/calls) as average ,rows, 
        100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent 
FROM    pg_stat_statements 
ORDER   BY average DESC LIMIT 10;

重置查询日志，相当于清空表pg_stat_statements

select pg_stat_statements_reset() ;