PHP实战中知识总结 / PgSQL - pg_stat_statements（系统表）
pg_stat_statements视图提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息，包括SQL语句的执行时间、执行次数等
一、前置条件
（1）需要安装pg_stat_statements扩展
（2）需要在postgresql.conf的shared_preload_libraries配置中增加pg_stat_statements来载入，因为它需要额外的共享内存，由于修改了配置文件，因此需要重启数据库服务才会生效
// 安装扩展
[root@izwz91quxhnlkan8kjak5hz postgresql-13.0]# cd contrib/
[root@izwz91quxhnlkan8kjak5hz contrib]# ls
adminpack btree_gist earthdistance intarray Makefile pg_prewarm postgres_fdw tablefunc vacuumlo
amcheck citext file_fdw isn oid2name pgrowlocks README tcn xml2
auth_delay contrib-global.mk fuzzystrmatch jsonb_plperl pageinspect pg_standby seg test_decoding
auto_explain cube hstore jsonb_plpython passwordcheck pg_stat_statements sepgsql tsm_system_rows
bloom dblink hstore_plperl lo pg_buffercache pgstattuple spi tsm_system_time
bool_plperl dict_int hstore_plpython ltree pgcrypto pg_trgm sslinfo unaccent
btree_gin dict_xsyn intagg ltree_plpython pg_freespacemap pg_visibility start-scripts uuid-ossp
[root@izwz91quxhnlkan8kjak5hz contrib]# cd pg_stat_statements/
[root@izwz91quxhnlkan8kjak5hz pg_stat_statements]# make && make install
make -C ../../src/backend generated-headers
...
/usr/bin/install -c -m 755 pg_stat_statements.so '/www/server/postgresql/lib/pg_stat_statements.so'
/usr/bin/install -c -m 644 ./pg_stat_statements.control '/www/server/postgresql/share/extension/'
/usr/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.7--1.8.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql '/www/server/postgresql/share/extension/'
[postgres@izwz91quxhnlkan8kjak5hz postgres]$ psql
psql (13.0)
Type "help" for help.
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
// 修改配置文件postgresql.conf
[postgres@izwz91quxhnlkan8kjak5hz postgres]# vim postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
// 重启服务
[postgres@izwz91quxhnlkan8kjak5hz postgres]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2021-05-11 19:57:21.920 CST [11604] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2021-05-11 19:57:21.920 CST [11604] LOG: listening on IPv4 address "127.0.0.1", port 5432
2021-05-11 19:57:21.920 CST [11604] LOG: could not bind IPv6 address "::1": 无法指定被请求的地址
2021-05-11 19:57:21.920 CST [11604] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2021-05-11 19:57:21.925 CST [11604] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-05-11 19:57:21.940 CST [11606] LOG: database system was shut down at 2021-05-11 19:57:21 CST
2021-05-11 19:57:22.013 CST [11604] LOG: database system is ready to accept connections
done
server started
二、表字段说明
| 列名 | 示例值 | 说明 |
|---|---|---|
| userid | 10 | 用户ID |
| dbida | 12917 | 数据库OID |
| queryid | 4390283800491518311 | SQL进行归一化后的HASH值 |
| query | select version() | SQL归一化后的内容 |
| plans | 24 | SQL被解析生成执行计划的次数 |
| calls | 1 | 执行次数 |
| total_plan_time | 0.208 | SQL总共的执行时间 |
| min_plan_time | 0.208 | SQL最小的执行时间 |
| max_plan_time | 0.208 | SQL最大的执行时间 |
| mean_plan_time | 0.208 | SQL平均的执行时间 |
| stddev_plan_time | 0 | 在该语句中花费时间的总体标准偏差，以毫秒计 |
| rows | 1 | SQL返回或者影响的行数 |
| shared_blks_hit | 0 | SQL在shared_buffer中命中的块数 |
| shared_blks_read | 0 | 从磁盘中读取的块数 |
| shared_blks_dirtied | 0 | SQL语句弄脏的shared_buffer的块数 |
| shared_blks_written | 0 | SQL语句写入的块数 |
| local_blks_hit | 0 | 临时表中命中的块数 |
| local_blks_read | 0 | 临时表需要读的块数 |
| local_blks_dirtied | 0 | 临时表弄脏的块数 |
| local_blks_written | 0 | 临时表写入的块数 |
| temp_blks_read | 0 | 从临时文件读取的块数 |
| temp_blks_written | 0 | 从临时文件写入的数据块数 |
| blk_read_time | 0 | 从磁盘读取花费的时间 |
| blk_write_time | 0 | 从磁盘写入花费的时间 |
三、函数
（1）pg_stat_statements(showtext boolean)：pg_stat_statements视图按照一个也叫pg_stat_statements的函数来定义。客户端可以直接调用pg_stat_statements函数，并且通过指定showtext := false来忽略查询文本（即，对应于视图的query列的OUT参数将返回空值）
（2）pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint)：该函数可用于清除指定的userid，dbid和queryid相对应的pg_stat_statements收集的统计信息。如果未指定任何参数，或者所有指定的参数均为0（无效），则它将删除所有统计信息。 默认情况下，此功能只能由超级用户执行。可以使用GRANT授予其他人访问权限
四、配置参数
（1）pg_stat_statements.max(integer)：由该模块跟踪的语句的最大数目（即pg_stat_statements视图中行的最大数量）。如果观测到的可区分的语句超过这个数量，最少被执行的语句的信息将会被丢弃。默认值为 5000。这个参数只能在服务器启动时设置
（2）pg_stat_statements.track(enum)：该配置可控制哪些语句会被该模块计数。指定top可以跟踪顶层语句（那些直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none可以禁用语句统计信息收集。默认值是top。 只有超级用户能够改变这个设置
（3）pg_stat_statements.track_utility(boolean)：该配置可用于控制该模块是否会跟踪工具命令。工具命令是除了SELECT、INSERT、UPDATE和DELETE之外所有的其他命令。默认值是on。 只有超级用户能够改变这个设置。
（4）pg_stat_statements.save(boolean)：指定是否在服务器关闭之后还保存语句统计信息。如果被设置为off，那么关闭后不保存统计信息并且在服务器启动时也不会重新载入统计信息。默认值为on。这个参数只能在postgresql.conf文件中或者在服务器命令行上设置
// 示例配置
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
// 应用示例
// 最耗时 SQL，单次调用最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by mean_exec_time desc limit 5;
// 最耗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
select userid::regrole, dbid, query from pg\stat_statements order by stddev_exec_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;