Postgresql 模块插件之pg_stat_statements

相关链接：
pgsql编译安装
pgBouncer连接池

一、说明

pg_stat_statements 提供了跟踪服务器执行的所有 SQL 语句的规划和执行统计信息的方法。当 pg_stat_statements 处于活动状态时，它会跟踪服务器上所有数据库的统计信息。该模块收集到的统计数据可以通过一个名为 pg_stat_statements 的视图进行访问。

官方文档：pg_stat_statements文档

二、插件安装启动

（一）编译安装

源码在pgsql包里就提供了，可以在编译pgsql的时候就安装，也可以随后编译

1.进入到pgsql安装包目录

只需要编译这个组件就行，进入到解压的pgsql安装包目录

cd /usr/local/postgresql-xxx
1

该插件是自带的

cd contrib/pg_stat_statements
1

2.编译安装

make && make install
1

（二）修改配置文件

1.编辑postgresql.conf文件

默认编译安装后的目录为下面的，如果不是用的默认，使用find查找

vi /var/pgsqldata/postgresql.conf
1

找到 Add settings for extensions here，在下面写插件的参数

2.添加以下内容

具体每条有说明

## 加载模块
shared_preload_libraries='pg_stat_statements'
 
## 跟踪IO消耗的时间
track_io_timing = on

## 单条SQL的最长长度
track_activity_query_size = 2048

## 最多保留多少条信息
pg_stat_statements.max = 10000           

## 嵌套sql设置
## 参数值：all - (所有SQL), top-函数内的sql不被跟踪, none - (不跟踪)
pg_stat_statements.track = all           

## 是否跟踪非DML语句
pg_stat_statements.track_utility = off   

## 重启后是否保留统计信息  
pg_stat_statements.save = on             
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.重启pgsql

systemctl restart pgsql
1

（三）启用插件并检查

1.在需要的数据库中启用

使用pgsql管理工具或者在psql命令行里输入

create extension pg_stat_statements; 
1

2.检查

上面的命令没有错误即说明没问题

三、应用

启用后，需要在sql中查询结果

1.最耗IO的sql统计

最耗IO的20条

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 20;  
1

2.最耗时的sql统计

最耗时的20条

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 20;  
1

3.最耗共享内存的sql统计

最耗内存的20条

select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 20;  
1

4.最耗空间的sql统计

最耗空间的20条

select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 20;
1