使用pg_stat_statements插件

简介

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

更多信息，请参见pg_stat_statements官方文档。

支持的版本说明

PostgreSQL 10及以上版本的最新小版本支持该插件。

可通过以下SQL语句查询当前实例是否支持该插件：

SELECT * FROM pg_available_extension_versions WHERE name  = 'pg_stat_statements';

如果不支持，可通过升级内核小版本或者使用转储与还原升级大版本使用该插件。

RDS for PostgreSQL实例支持的插件，具体请参见支持的插件列表。

插件安装与卸载

查看当前数据库是否有安装pg_stat_statements插件，执行如下SQL：

select * from pg_extension  where extname = 'pg_stat_statements';

如果显示结果为空说明未安装该插件，如果显示插件的信息说明已安装该插件。

pg_stat_statements已默认预加载在shared_preload_libraries参数中，直接通过如下方式进行插件安装。

• 安装插件

  SELECT control_extension('create', 'pg_stat_statements');

• 删除插件

  SELECT control_extension('drop', 'pg_stat_statements');

更多信息，请参见通过界面安装和卸载插件和通过SQL命令安装和卸载插件。

基本使用

1. pg_stat_statements插件安装好之后，需要开启对应参数。默认参数配置如下，可根据不同业务进行调整。

   表1 参数说明

   参数名称	是否需要重启	值	允许值	描述
   pg_stat_statements.max	是	5000	100~5,000,000	设置pg_stat_statements跟踪的最大语句数。
   pg_stat_statements.save	否	on	on,off	保存服务器关闭期间的pg_stat_statements统计信息。
   pg_stat_statements.track	否	top	top,all,none	控制哪个语句可以被该模块计数。
   pg_stat_statements.track_planning	否	off	on,off	选择是否由pg_stat_statements跟踪计划持续时间。
   pg_stat_statements.track_utility	否	on	on,off	选择是否由pg_stat_statements跟踪实用程序命令。

2. 此时，可以通过查询pg_stat_statements视图，获取统计信息。

   select * from pg_stat_statements;

3. 查询最耗IO的SQL。

   --消耗IO的前五条SQL
   select userid::regrole, dbid, query
   from pg_stat_statements
   order by (blk_read_time+blk_write_time) desc limit 5;

4. 查询最耗共享内存的SQL。

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

5. 重置统计信息。

   select pg_stat_statements_reset();

进阶使用

使用pg_stat_statements排查CPU过高问题。

1. 为了方便排查CPU过高的问题，需要重置pg_stat_statements的计数器。

   select pg_stat_statements_reset();

   等待一段时间，使pg_stat_statements能够统计到足够的信息。

2. 获取最耗时的SQL。

   select * from pg_stat_statements order by total_exec_time desc limit 10;

   此步骤获取到的SQL会长时间占用用户态CPU时间，把这些SQL取出来分析。

3. 获取读取Buffer次数最多的SQL。

   select * from pg_stat_statements order by shared_blks_hit + shared_blks_read desc limit 10;

   此步骤获取到的SQL可能由于缺少查询对应的索引，导致过多的buffer读，从而消耗大量CPU。

4. 获取执行次数最多的SQL。

   select * from pg_stat_statements order by calls desc limit 10;

   有些比较简单的SQL单独执行耗时较低，但是在某些情况下（例如：在事务中循环执行、大量的并发执行）也会导致CPU的消耗增高。