PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL)

标签

PostgreSQL , pg_stat_statements , TOP SQL

---

背景

数据库是较大型的应用，对于繁忙的数据库，需要消耗大量的内存、CPU、IO、网络资源。

SQL优化是数据库优化的手段之一，优化什么SQL效果最佳呢？首先要了解最耗费资源的SQL，即TOP SQL。

从哪里可以了解数据库的资源都被哪些SQL消耗掉了呢？

资源分为多个维度，CPU，内存，IO等。如何能了解各个维度层面的TOP SQL呢？

pg_stat_statements插件可以用于统计数据库的资源开销，分析TOP SQL。

一、安装pg_stat_statements

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;

五、分析TOP SQL

pg_stat_statements输出内容介绍

查询pg_stat_statements视图，可以得到统计信息

SQL语句中的一些过滤条件在pg_stat_statements中会被替换成变量，减少重复显示的问题。

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

1. SQL的调用次数，总的耗时，最快执行时间，最慢执行时间，平均执行时间，执行时间的方差（看出抖动），总共扫描或返回或处理了多少行；

2. shared buffer的使用情况，命中，未命中，产生脏块，驱逐脏块。

3. local buffer的使用情况，命中，未命中，产生脏块，驱逐脏块。

4. temp buffer的使用情况，读了多少脏块，驱逐脏块。

5. 数据块的读写时间。

NameTypeReferencesDescriptionuseridoidpg_authid.oidOID of user who executed the statementdbidoidpg_database.oidOID of database in which the statement was executedqueryidbigint-Internal hash code, computed from the statement's parse treequerytext-Text of a representative statementcallsbigint-Number of times executedtotal_timedouble precision-Total time spent in the statement, in millisecondsmin_timedouble precision-Minimum time spent in the statement, in millisecondsmax_timedouble precision-Maximum time spent in the statement, in millisecondsmean_timedouble precision-Mean time spent in the statement, in millisecondsstddev_timedouble precision-Population standard deviation of time spent in the statement, in millisecondsrowsbigint-Total number of rows retrieved or affected by the statementshared_blks_hitbigint-Total number of shared block cache hits by the statementshared_blks_readbigint-Total number of shared blocks read by the statementshared_blks_dirtiedbigint-Total number of shared blocks dirtied by the statementshared_blks_writtenbigint-Total number of shared blocks written by the statementlocal_blks_hitbigint-Total number of local block cache hits by the statementlocal_blks_readbigint-Total number of local blocks read by the statementlocal_blks_dirtiedbigint-Total number of local blocks dirtied by the statementlocal_blks_writtenbigint-Total number of local blocks written by the statementtemp_blks_readbigint-Total number of temp blocks read by the statementtemp_blks_writtenbigint-Total number of temp blocks written by the statementblk_read_timedouble precision-Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)blk_write_timedouble 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://www.postgresql.org/docs/9.6/static/pgstatstatements.html