pg_stat_statement

介绍

        pg_stat_statements模块提供追踪服务器所执行的所有SQL语句的执行统计信息。

        该模块必须通过在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。这意味着增加或移除该模块需要一次服务器重启。

        当pg_stat_statements被载入时，它会跟踪该服务器的所有数据库的统计信息。该模块提供了一个视图 pg_stat_statements和两个函数pg_stat_statements_reset、pg_stat_statements用于访问和操纵这些统计信息。这些视图 和函数不是全局可用的，但是可以用CREATE EXTENSION pg_stat_statements为特定数据库启用它们。

函数介绍

• pg_stat_statements_reset() 返回 void
  pg_stat_statements_reset清空由pg_stat_statements函数收集的所有统计信息。默认情况下，这个函数只能被超级用户执行。
• pg_stat_statements(showtext boolean) returns setof record
  pg_stat_statements视图按照一个也叫 pg_stat_statements的函数来定义。客户端可以直接调用 pg_stat_statements函数，并且通过指定 showtext := false来忽略查询文本（即，对应于视图的 query列的OUT参数将返回空值）。 这个特性是为了支持不想重复接收长度不定的查询文本的外部工具而设计的。 这类工具可以转而自行缓存第一个观察到的查询文本，因为这就是 pg_stat_statements自己所做的全部工作，并且只在需要的 时候检索查询文本。因为服务器会把查询文本存储在一个文件中，这种方法可 以降低重复检查pg_stat_statements数据的 物理 I/O。

配置参数

模块加载参数：

• shared_preload_libraries='pg_stat_statements'
  用于加载pg_stat_statements模块，需要重启postgres服务。
• track_io_timing = on
  如果要跟踪IO消耗的时间，需要打开如上参数。
• track_activity_query_size = 2048
  设置单条SQL的最长长度，超过被截断显示（默认1024）。

 

模块采样参数：

• pg_stat_statements.max (integer)
  pg_stat_statements.max是由该模块跟踪的语句的最大数目（即pg_stat_statements视图中行的最大数量）。如果观测到的可区分的语句超过这个数量，最少被执行的语句的信息将会被丢弃。默认值为 5000。这个参数只能在服务器启动时设置。
• pg_stat_statements.track (enum)
  pg_stat_statements.track控制哪些语句会被该模块计数。指定top可以跟踪顶层语句（那些直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none可以禁用语句统计信息收集。默认值是top。 只有超级用户能够改变这个设置。
• pg_stat_statements.track_utility (boolean)
  pg_stat_statements.track_utility控制该模块是否会跟踪工具命令。工具命令是除了SELECT、INSERT、 UPDATE和DELETE之外所有的其他命令。默认值是on。 只有超级用户能够改变这个设置。
• pg_stat_statements.save (boolean)
  pg_stat_statements.save指定是否在服务器关闭之后还保存语句统计信息。如果被设置为off，那么关闭后不保存统计信息并且在服务器启动时也不会重新载入统计信息。默认值为on。这个参数只能在postgresql.conf文件中或者在服务器命令行上设置。

该模块要求与pg_stat_statements.max成比例的额外共享内存。注意只要该模块被载入就会消耗这么多的内存，即便pg_stat_statements.track被设置为none。

 

参数调整示例：

[postgres]# vi $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

[postgres]# pg_ctl retart -m fast

启用pg_stat_statements

 配置参数

[postgres]# vi $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

重启PG

[postgres]# pg_ctl restart -m fast

创建模块

postgres=# create extension pg_stat_statements;
CREATE EXTENSION

postgres=# \d
               List of relations
 Schema |        Name        | Type  |  Owner   
--------+--------------------+-------+----------
 public | pg_stat_statements | view  | postgres
 
postgres=# \d pg_stat_statements;
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers 
---------------------+------------------+-----------
 userid              | oid              | 
 dbid                | oid              | 
 queryid             | bigint           | 
 query               | text             | 
 calls               | bigint           | 
 total_time          | double precision | 
 min_time            | double precision | 
 max_time            | double precision | 
 mean_time           | double precision | 
 stddev_time         | double precision | 
 rows                | bigint           | 
 shared_blks_hit     | bigint           | 
 shared_blks_read    | bigint           | 
 shared_blks_dirtied | bigint           | 
 shared_blks_written | bigint           | 
 local_blks_hit      | bigint           | 
 local_blks_read     | bigint           | 
 local_blks_dirtied  | bigint           | 
 local_blks_written  | bigint           | 
 temp_blks_read      | bigint           | 
 temp_blks_written   | bigint           | 
 blk_read_time       | double precision | 
 blk_write_time      | double precision | 

注释：

名称	类型	引用	描述
userid	oid	pg_authid.oid	执行该语句的用户的 OID
dbid	oid	pg_database.oid	在其中执行该语句的数据库的 OID
queryid	bigint		内部哈希码，从语句的解析树计算得来
query	text		语句的文本形式
calls	bigint		被执行的次数
total_time	double precision		在该语句中花费的总时间，以毫秒计
min_time	double precision		在该语句中花费的最小时间，以毫秒计
max_time	double precision		在该语句中花费的最大时间，以毫秒计
mean_time	double precision		在该语句中花费的平均时间，以毫秒计
stddev_time	double precision		在该语句中花费时间的总体标准偏差，以毫秒计
rows	bigint		该语句检索或影响的行总数
shared_blks_hit	bigint		该语句造成的共享块缓冲命中总数
shared_blks_read	bigint		该语句读取的共享块的总数
shared_blks_dirtied	bigint		该语句弄脏的共享块的总数
shared_blks_written	bigint		该语句写入的共享块的总数
local_blks_hit	bigint		该语句造成的本地块缓冲命中总数
local_blks_read	bigint		该语句读取的本地块的总数
local_blks_dirtied	bigint		该语句弄脏的本地块的总数
local_blks_written	bigint		该语句写入的本地块的总数
temp_blks_read	bigint		该语句读取的临时块的总数
temp_blks_written	bigint		该语句写入的临时块的总数
blk_read_time	double precision		该语句花在读取块上的总时间，以毫秒计（如果track_io_timing被启用，否则为零）
blk_write_time	double precision		该语句花在写入块上的总时间，以毫秒计（如果track_io_timing被启用，否则为零）

查询资源消耗多的SQL

调用次数较多的SQL

select * from pg_stat_statements stat order by calls desc limit 10;

总执行时间较长的SQL

select * from pg_stat_statements order by total_time desc limit 10;

平均执行时间较长的SQL

select * from pg_stat_statements order by mean_time desc limit 10;

在读/写块上总执行时间最多的SQL

select * from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 10;

在读/写块上平均执行时间最多的SQL

select * from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 10;

查看时间抖动严重的SQL

select * from pg_stat_statements order by stddev_time desc limit 10;

消耗共享内存较多的SQL

select * from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;

使用临时块较多的SQL

select * from pg_stat_statements order by temp_blks_written desc limit 10;

缓冲池命中率较低的SQL

SELECT *,cast(100.0*shared_blks_hit/nullif(shared_blks_hit + shared_blks_read,0) as decimal(10,2)) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;