pg_stat_statement如何安装部署使用

pg_stat_statements模块提供一种跟踪执行统计服务器执行的所有SQL语句的手段。该模块默认是不开启的，如果需要开启需要我们手动对其进进行编译安装，修改配置文件并重启数据库，并在使用前手动载入该模块。

1、手动对pg_stat_statement模块进行编译安装

-- 进入pgsql软件的contrib目录下，查看是否有pg_stat_statements相关模块
# pwd
/usr/local/postgresql-12.2/contrib
# ls | grep pg_stat_statements
pg_stat_statements

-- 进入pg_stat_statement模块目录下进行编译安装
# make && make install

-- 检查pg_stat_statement是否安装成功
# cd /usr/local/pgsql/share/extension/
# ll | grep pg_stat_statements
-rw-r--r-- 1 root root  1246 10月 25 15:46 pg_stat_statements--1.0--1.1.sql
-rw-r--r-- 1 root root  1336 10月 25 15:46 pg_stat_statements--1.1--1.2.sql
-rw-r--r-- 1 root root  1454 10月 25 15:46 pg_stat_statements--1.2--1.3.sql
-rw-r--r-- 1 root root   345 10月 25 15:46 pg_stat_statements--1.3--1.4.sql
-rw-r--r-- 1 root root   305 10月 25 15:46 pg_stat_statements--1.4--1.5.sql
-rw-r--r-- 1 root root  1427 10月 25 15:46 pg_stat_statements--1.4.sql
-rw-r--r-- 1 root root   376 10月 25 15:46 pg_stat_statements--1.5--1.6.sql
-rw-r--r-- 1 root root   806 10月 25 15:46 pg_stat_statements--1.6--1.7.sql
-rw-r--r-- 1 root root   191 10月 25 15:46 pg_stat_statements.control
-rw-r--r-- 1 root root   449 10月 25 15:46 pg_stat_statements--unpackaged--1.0.sql

2、修改数据库配置文件postgres，允许pg_stat_statements模块加载到共享内存中

-- 修改配置文件
# vi postgres.conf
shared_preload_libraries='pg_stat_statements,pg_pathman'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

3、重启数据库，登录数据库后加载pg_stat_statement模块

-- 重启数据库
# su - postgres
$ pg_ctl -D /data/pgsql12/data restart

-- 登录数据库，载入pg_stat_statement模块
postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';               //查看可用模块
-[ RECORD 1 ]-----+----------------------------------------------------------
name              | pg_stat_statements
default_version   | 1.7
installed_version |
comment           | track execution statistics of all SQL statements executed

postgres=# create extension pg_stat_statements;         //载入模块，载入后pg_stat_statements表可正常使用
CREATE EXTENSION

4、pg_stat_statements表的使用

postgres=# select * from  pg_stat_statements limit 1;
-[ RECORD 1 ]-------+------------------------------------------------------------------------
userid              | 10                        //用户id
dbid                | 13547                     //数据库oid
queryid             | 1194713979                //查询id
query               | SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'   //查询SQL
calls               | 1                         //调用次数
total_time          | 53.363875                 //SQL总共执行时间
min_time            | 53.363875                 //SQL最小执行时间
max_time            | 53.363875                 //SQL最大执行时间
mean_time           | 53.363875                 //SQL平均执行时间
stddev_time         | 0                         //SQL花费时间的表中偏差
rows                | 1                         //SQL返回或者影响的行数
shared_blks_hit     | 1                         //SQL在在shared_buffer中命中的块数
shared_blks_read    | 0                         //SQL从page cache或者磁盘中读取的块数
shared_blks_dirtied | 0                         //SQL语句弄脏的shared_buffer的块数
shared_blks_written | 0                         //SQL语句写入的块数
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       | 0                         //从磁盘或者读取花费的时间
blk_write_time      | 0                         //从磁盘写入花费的时间