PostgreSQL 如何查找TOP SQL

一、安装pg_stat_statements

如果您使用的是云数据库，跳过安装，到create extension 部分。pg_stat_statements是PostgreSQL的核心插件之一。可以在编译PostgreSQL时安装，也可以单独安装。

编译时安装

make world    
make install-world    

单独安装

cd src/contrib/pg_stat_statements/    
make; make install    

二、加载pg_stat_statements模块

vi $PGDATA/postgresql.conf    
    
shared_preload_libraries='pg_stat_statements'    

如果要跟踪IO消耗的时间，还需要打开如下参数

track_io_timing = on    

设置单条SQL的最长长度，超过被截断显示（可选）

track_activity_query_size = 2048    

三、配置pg_stat_statements采样参数

vi $PGDATA/postgresql.conf    
    
pg_stat_statements.max = 10000           # 在pg_stat_statements中最多保留多少条统计信息，通过LRU算法，覆盖老的记录。    
pg_stat_statements.track = all           # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)    
pg_stat_statements.track_utility = off   # 是否跟踪非DML语句 (例如DDL，DCL)， on表示跟踪, off表示不跟踪    
pg_stat_statements.save = on             # 重启后是否保留统计信息    

重启数据库

pg_ctl restart -m fast    

四、创建pg_stat_statements extension

在需要查询TOP SQL的数据库中，创建extension

create extension pg_stat_statements;    

如果您使用的是阿里云RDS PPAS数据库，请使用管理函数创建这个插件。

https://help.aliyun.com/document_detail/43600.html

  举例：
  1 创建插件 dblink
      select rds_manage_extension('create','dblink');
  2 删除插件 dblink
      select rds_manage_extension('drop','dblink');

五、分析TOP SQL

pg_stat_statements输出内容介绍

查询pg_stat_statements视图，可以得到统计信息。SQL语句中的一些过滤条件在pg_stat_statements中会被替换成变量，减少重复显示的问题。

pg_stat_statements视图包含了一些重要的信息，例如：

• SQL的调用次数，总耗时，最快/慢执行时间，平均执行时间，执行时间的方差（看出抖动），总共扫描或返回或处理了多少行
• shared buffer的使用情况，命中，未命中，产生脏块，驱逐脏块
• local buffer的使用情况，命中，未命中，产生脏块，驱逐脏块
• temp buffer的使用情况，读了多少脏块，驱逐脏块
• 数据块的读写时间

Name	Type	References	Description
userid	oid	pg_authid.oid	OID of user who executed the statement
dbid	oid	pg_database.oid	OID of database in which the statement was executed
queryid	bigint	-	Internal hash code, computed from the statement's parse tree
query	text	-	Text of a representative statement
calls	bigint	-	Number of times executed
total_time	double precision	-	Total time spent in the statement, in milliseconds
min_time	double precision	-	Minimum time spent in the statement, in milliseconds
max_time	double precision	-	Maximum time spent in the statement, in milliseconds
mean_time	double precision	-	Mean time spent in the statement, in milliseconds
stddev_time	double precision	-	Population standard deviation of time spent in the statement, in milliseconds
rows	bigint	-	Total number of rows retrieved or affected by the statement
shared_blks_hit	bigint	-	Total number of shared block cache hits by the statement
shared_blks_read	bigint	-	Total number of shared blocks read by the statement
shared_blks_dirtied	bigint	-	Total number of shared blocks dirtied by the statement
shared_blks_written	bigint	-	Total number of shared blocks written by the statement
local_blks_hit	bigint	-	Total number of local block cache hits by the statement
local_blks_read	bigint	-	Total number of local blocks read by the statement
local_blks_dirtied	bigint	-	Total number of local blocks dirtied by the statement
local_blks_written	bigint	-	Total number of local blocks written by the statement
temp_blks_read	bigint	-	Total number of temp blocks read by the statement
temp_blks_written	bigint	-	Total number of temp blocks written by the statement
blk_read_time	double precision	-	Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_time	double precision	-	Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

 

最耗IO SQL

平均最耗IO SQL TOP 5

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

总最耗IO SQL TOP 5

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

最耗时 SQL

平均最耗时 SQL TOP 5

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

总最耗时 SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;    

响应时间抖动最严重 SQL

select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;    

最耗共享内存 SQL

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

最耗临时空间 SQL

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

 

六、重置统计信息

pg_stat_statements是累积的统计，如果要查看某个时间段的统计，需要打快照，建议参考《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》

用户也可以调用如下SQL定期清理历史的统计信息

select pg_stat_statements_reset();    

 

参考

https://github.com/digoal/blog/blob/master/201704/20170424_06.md