SRE文档1.安装
cd postgresql-12.2/contrib/pg_stat_statements
make && make installcd postgresql-12.2/contrib/pg_stat_statements
make && make install- 配置参数
bash
#修改postgresql.conf
################ IO #####################
shared_preload_libraries = 'pg_stat_statements'
custom_variable_classes = 'pg_stat_statements'
track_io_timing = on
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 # 重启后是否保留统计信息#修改postgresql.conf
################ IO #####################
shared_preload_libraries = 'pg_stat_statements'
custom_variable_classes = 'pg_stat_statements'
track_io_timing = on
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 # 重启后是否保留统计信息- 重启数据库
2.创建扩展
sql
在需要查询TOP SQL的数据库中，创建extension
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)在需要查询TOP SQL的数据库中，创建extension
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)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;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;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;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;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;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;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;select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;3.重置统计信息
select pg_stat_statements_reset();select pg_stat_statements_reset();3.1查询慢查执行时间前5的SQL
postgres=# SELECT query, calls, total_time, rows, 100.0 \* shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;postgres=# SELECT query, calls, total_time, rows, 100.0 \* shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;- 慢SQL、TOP SQL优化
explain (analyze,verbose,timing,costs,buffers,timing) SQL; -- SQL代替为要分析的SQLexplain (analyze,verbose,timing,costs,buffers,timing) SQL; -- SQL代替为要分析的SQL