标签
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. 数据块的读写时间。
| 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();