PostgreSQL:SQL信息统计拓展

概述

pg_stat_statements是PostgreSQL数据库的一个拓展插件，用于收集数据库中的SQL运行信息，例如SQL的总执行时间，调用次数，共享内存命中率等信息。用于监控数据库的性能，是数据库性能监控的重要拓展模块
pg_stat_statements 默认在PG的源码包中就有，无需下载，但是需要手动配置安装一下，下面就来演示一次

下载安装

如果你是编译安装，直接去源码包中的contrib/pg_stat_statements目录，执行编译和安装动作即可
（如果你是yum安装，则需要下载源码包去找到拓展，最好是与你安装的数据库同版本的）
pg的源码包中有一些自带的功能插件

[root@stephen contrib]# pwd
/opt/postgresql-11.4/contrib
[root@stephen contrib]# ls pg_stat_statements
expected pg_stat_statements--1.3--1.4.sql pg_stat_statements.conf
Makefile pg_stat_statements--1.4--1.5.sql pg_stat_statements.control
pg_stat_statements--1.0--1.1.sql pg_stat_statements--1.4.sql pg_stat_statements--unpackaged--1.0.sql
pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.5--1.6.sql sql
pg_stat_statements--1.2--1.3.sql pg_stat_statements.c

编译安装

make && make install

创建拓展

postgres=# create extension pg_stat_statements ;
CREATE EXTENSION

设置参数
安装完成之后，在PG的配置文件中的postgres.conf文件中添加以下参数

shared_preload_libraries='pg_stat_statements'     
track_io_timing = on
track_activity_query_size = 2048
pg_stat_statements.max = 1000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

参数介绍

• shared_preload_libraries：动态库的加载，可设置多个共享库，多个之间逗号风格。如果设置了不支持的共享库，数据库会重启报错且无法启动
• pg_stat_statements.max ：设置记录最大 的SQL数，默认5000条，如果达到设置值，执行频率最小的SQL会被丢弃
• pg_stat_statements.track：设置哪类SQL记录，top指的是最外层的SQL，all包含函数中涉及的SQL
• pg_stat_statements.track_utility：设置是否记录select，update，delete，insert以外的SQL命令
• pg_stat_statements.save = on：当数据库关闭时是否将SQL信息记录到文件中，一般为on

全部安装完成之后，就在数据库下可以看到一个视图

postgres=# \d
List of relations
-[ RECORD 1 ]--------------
Schema | public
Name | pg_stat_statements
Type | view
Owner | postgres

这就是 pg_stat_statements 记录SQL执行情况的视图

使用

开启之后，任何执行在数据库中的语句都会被记录下来

如，我在数据库中做几次查询

stephen=# select * from datax_test limit 10;

然后就可以在pg_stat_statements这个视图中看到关于这个SQL执行的情况

stephen=# select * from pg_stat_statements where queryid = 2362179633810610809;
-[ RECORD 1 ]-------+----------------------------------
userid        | 10        #执行此SQL的用户oid
dbid        | 16384        #此SQL执行的数据库的oid
queryid        | 2362179633810610809        #此SQL的编号
query        | select * from datax_test limit $1        #此SQL的内容
calls        | 6        #此SQL的调用次数
total_time        | 17.551195        #执行总时间（ms）
min_time        | 0.030288        #最小时间        
max_time        | 17.390669        #最大时间
mean_time        | 2.92519916666667        #平均时间
stddev_time        | 6.46915489671277        #SQL花费时间的表中偏差
rows        | 60        #返回的行数
shared_blks_hit        | 5        #命中的共享内存数据块数
shared_blks_read        | 1        #共享内存度
shared_blks_dirtied        | 0        #产生的共享内存脏块数量
shared_blks_written        | 0        #写入的共享内存数据块数
local_blks_hit        | 0        #临时表命中的块数
local_blks_read        | 0        # 临时表需要读的块数
local_blks_dirtied        | 0        #临时表弄脏的块数
local_blks_written        | 0        #临时表写入的块数
temp_blks_read        | 0        #临时文件读取的块数
temp_blks_written        | 0        #临时文件写入的块数
blk_read_time        | 17.332569        #从磁盘读取花费时间
blk_write_time        | 0        #从磁盘写入花费时间

重置统计
随着数据库运行，统计的pg_stat_statements 视图会越来越大可以通过pg_stat_statements_reset函数来清理已经获取的SQL

stephen=# select count(*) from pg_stat_statements;
 count 
-------
   106
(1 row)

stephen=# select pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------
 
(1 row)

stephen=# select count(*) from pg_stat_statements;
 count 
-------
     2
(1 row)