PostgreSQL 之 pg_stat_statements

pg_stat_statements 模块提供了一种跟踪执行的所有 SQL 语句的统计信息的方法。

安装插件

ubuntu 16.04 安装：

apt-get -y update && apt-get install -y postgresql-contrib-9.6 postgresql-plpython-9.6

如果 pgsql 是通过容器部署可以更新基础镜像：

FROM postgres:9.6

RUN apt-get -y update && apt-get install -y --force-yes postgresql-contrib-9.6 postgresql-plpython-9.6

加载模块

shared_preload_libraries = 'pg_stat_statements'    # 加载 pg_stat_statements 模块

以下可选参数：

track_io_timing = on                      # 跟踪 IO 消耗的时间
track_activity_query_size = 2048          # 单条 sql 的最长长度，超过截断显示

pg_stat_statements.save = on              # 重启后是否保留统计信息 
pg_stat_statements.max = 10000            # 最多保留统计信息条数，通过 LRU 算法来覆盖老的记录。  
pg_stat_statements.track = all            # all：所有 sql 包括函数内嵌套的 sql 、 top：直接执行的 sql 不包括函数内嵌套的、 none：不跟踪  
pg_stat_statements.track_utility = off    # 是否跟踪非 DML 语句 (例如 DDL、DCL)， on 表示跟踪, off 表示不跟踪

重启数据库

docker restart pgsql

加载扩展

加载扩展模块

create extension pg_stat_statements;

卸载扩展模块

drop extension pg_stat_statements;

常用分析语句

单次调用最耗时

select query from pg_stat_statements order by mean_time desc limit 1;

总最耗时

select query from pg_stat_statements order by total_time desc limit 1;

响应时间抖动最严重

select query from pg_stat_statements order by stddev_time desc limit 1;

单次调用最耗 IO

select query from pg_stat_statements order by (blk_read_time + blk_write_time) /calls desc limit 1;

总最耗IO

select query from pg_stat_statements order by (blk_read_time + blk_write_time) desc limit 1;

最耗共享内存

select query from pg_stat_statements order by (shared_blks_hit + shared_blks_dirtied) desc limit 1;

最耗临时空间

select query from pg_stat_statements order by temp_blks_written desc limit 1;

重置统计信息

select pg_stat_statements_reset();

参考资料

• https://www.postgresql.org/docs/9.6/pgstatstatements.html
• https://yq.aliyun.com/articles/74421