| PostgreSQL 9.5.3 中文手册 | |||
|---|---|---|---|
| 上一页 | 上一级 | 附录 F. 额外提供的模块 | 下一页 |
pg_stat_statements 模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息。
该模块必须通过在postgresql.conf的shared_preload_libraries 中增加pg_stat_statements来载入，因为它需要额外的共享内存。 这意味着增加或移除该模块需要一次服务器重启。
当加载pg_stat_statements时，
它跟踪服务器所有数据库的统计信息。为了访问和操纵这些统计，
该模块提供了一个视图pg_stat_statements和工具函数
pg_stat_statements_reset和pg_stat_statements。
这些不是全局可用的，但可以使用CREATE EXTENSION pg_stat_statements
为特定数据库启用。
该模块收集的统计信息通过一个名为 pg_stat_statements的系统视图提供。 这个视图为每个不同的数据库 ID、用户 ID 和查询 ID （最多到该模块可以追踪的不同语句的最大数量）包含一行。该视图的列显示在 表 F-20中。
表 F-20. pg_stat_statements列
| 名称 | 类型 | 引用 | 描述 |
|---|---|---|---|
| userid | oid | pg_authid.oid | 执行该语句的用户的 OID |
| dbid | oid | pg_database.oid | 在其中执行该语句的数据库的 OID |
| queryid | bigint | 内部哈希码，从语句的解析树计算得来 | |
| query | text | 语句的文本形式 | |
| calls | bigint | 被执行的次数 | |
| total_time | double precision | 在该语句中花费的总时间，以毫秒计 | |
| min_time | double precision | 在语句中花费的最短时间，以毫秒为单位 | |
| max_time | double precision | 在语句中花费的最长时间，以毫秒为单位 | |
| mean_time | double precision | 在语句中花费的平均时间，以毫秒为单位 | |
| stddev_time | double precision | 在语句中花费的人口标准偏差时间，以毫秒为单位 | |
| rows | bigint | 该语句检索或影响的行总数 | |
| shared_blks_hit | bigint | 该语句造成的共享块缓冲命中总数 | |
| shared_blks_read | bigint | 该语句读取的共享块的总数 | |
| shared_blks_dirtied | bigint | 该语句弄脏的共享块的总数 | |
| shared_blks_written | bigint | 该语句写入的共享块的总数 | |
| local_blks_hit | bigint | 该语句造成的本地块缓冲命中总数 | |
| local_blks_read | bigint | 该语句读取的本地块的总数 | |
| local_blks_dirtied | bigint | 该语句弄脏的本地块的总数 | |
| local_blks_written | bigint | 该语句写入的本地块的总数 | |
| temp_blks_read | bigint | 该语句读取的临时块的总数 | |
| temp_blks_written | bigint | 该语句写入的临时块的总数 | |
| blk_read_time | double precision | 该语句花在读取块上的总时间，以毫秒计（如果track_io_timing 被启用，否则为零） | |
| blk_write_time | double precision | 该语句花在写入块上的总时间，以毫秒计（如果track_io_timing 被启用，否则为零） |
出于安全原因，不允许非超级用户查看其它用户执行的 SQL 文本或者 queryid。 不过，如果视图已经安装到他们的数据库中，他们就可以查看统计信息。
可规划的查询（即SELECT、INSERT、UPDATE 以及DELETE）根据一种内部哈希计算具有相同的查询结构时， 它们就会被组合到一个单一的pg_stat_statements项。通常， 对于这里的目的，如果两个查询除了查询中的文本常量值之外在语义上等效， 它们将会被认为是相同的。不过，工具命令（即所有其他命令） 会严格地基于它们的文本查询字符串进行比较。
当为了把一个查询与其他查询匹配而忽略常数值时，在pg_stat_statements 显示中它会被一个?所替换。查询文本的剩余部分就是具有与该 pg_stat_statements项相关的特定queryid 哈希值的第一个查询的文本。
在某些情况中，具有明显不同文本的查询可能会被融合到一个单一的 pg_stat_statements项。通常这只会发生在语义等价的查询上， 但是也有很小的机会因为哈希冲突的原因导致无关的查询被融合到一个项中（不过， 对于属于不同用户或数据库的查询来说不会发生这种情况）。
由于queryid哈希值是根据查询被解析和分析后的表达计算的， 对立的情况也可能存在：如果具有相同文本的查询由于参数 （如不同的search_path设置）的原因而具有不同的含义， 它们就可能作为不同的项存在。
pg_stat_statements的使用者可能希望为每个项使用queryid （也许会与dbid和userid组合） 作为一个比查询文本更稳定和可靠的标识符。但是，有一点很重要的是， 对于queryid哈希值的稳定性只有有限的保障。 因为该标识符是从解析分析后的树得来的，它的值是以这种形式出现的内部对象标识符的函数。 这有一些违背直觉的含义。例如，如果有两个查询引用了同一个表， 但是该表在两次查询之间被删除并且重建，显然这两个查询是完全一致的， 但是pg_stat_statements将把它们认为是不同的。 哈希处理也对机器架构以及平台的其他方面的差别很敏感。更进一步， 认为PostgreSQL的不同主版本之间queryid 将会保持稳定是不安全的。
根据经验，只有在底层服务器版本以及目录元数据细节保持完全相同时， queryid值才能被假定为稳定并且可比。 两台参与到基于物理 WAL 重放的复制中的服务器会对相同的查询给出一样的 queryid值。但是， 逻辑复制模式并不保证在所有相关细节上都保持完全一样的复制， 因此在一组逻辑复制之间计算代价时，queryid并非是一个有用的标识符。 如果有疑问，推荐直接进行测试。
代表性查询文本保存在外部磁盘文件中，并且不消耗共享内存。因此， 即使非常冗长的查询文本也可以成功存储。然而，如果许多长的查询文本被累积， 则外部文件可能变得不可管理地大。如果发生这种情况，作为恢复方法， pg_stat_statements可以选择丢弃查询文本，其中 pg_stat_statements视图中的所有现有条目将显示空的 query字段，尽管与每个queryid 相关联的统计信息被保留。如果发生这种情况，请考虑减少 pg_stat_statements.max以防止重现。
pg_stat_statements_reset() returns void
pg_stat_statements_reset抛弃目前由pg_stat_statements
收集的所有统计信息。默认情况下，这个函数只能被超级用户执行。
pg_stat_statements(showtext boolean) returns setof record
pg_stat_statements视图按照一个也叫
pg_stat_statements的函数来定义。客户端可以直接调用
pg_stat_statements函数，并且通过指定
showtext := false来忽略查询文本（即，对应于视图的
query列的OUT参数将返回空值）。
这个特性是为了支持不想重复接收长度不定的查询文本的外部工具而设计的。
这类工具可以转而自行缓存第一个观察到的查询文本，因为这就是
pg_stat_statements自己所做的全部工作，并且只在需要的时候检索查询文本。
因为服务器会把查询文本存储在一个文件中，这种方法可以降低重复检查
pg_stat_statements数据的物理 I/O。
pg_stat_statements.max是该模块跟踪的语句的最大数目 （即pg_stat_statements视图中行的最大数量）。 如果观测到的可区分的语句超过这个数量，最少被执行的语句的信息将会被丢弃。 默认值为 5000。这个参数只能在服务器启动时设置。
pg_stat_statements.track控制哪些语句会被该模块计数。 指定top可以跟踪顶层语句（那些直接由客户端发出的语句）， 指定all还可以跟踪嵌套的语句（例如在函数中调用的语句）， 指定none可以禁用语句统计信息收集。默认值是top。 只有超级用户能够改变这个设置。
pg_stat_statements.track_utility控制该模块是否会跟踪工具命令。 工具命令是除了SELECT、INSERT、 UPDATE和DELETE之外所有的其他命令。默认值是on。 只有超级用户能够改变这个设置。
pg_stat_statements.save 指定在服务器关闭之后是否还保存语句统计信息。如果被设置为off， 那么关闭后不保存统计信息并且在服务器启动时也不会重新载入统计信息。 默认值为on。这个参数只能在postgresql.conf 文件中或者在服务器命令行上设置。
该模块要求与pg_stat_statements.max成比例的额外共享内存。 注意只要该模块被载入就会消耗这么多的内存，即便 pg_stat_statements.track被设置为none。
这些参数必须在postgresql.conf中设置。典型的用法可能是：
# postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all
bench=# SELECT pg_stat_statements_reset();
$ pgbench -i bench
$ pgbench -c10 -t300 bench
bench=# \x
bench=# 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;
-[ RECORD 1 ]---------------------------------------------------------------------
query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
calls | 3000
total_time | 9609.00100000002
rows | 2836
hit_percent | 99.9778970000200936
-[ RECORD 2 ]---------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
calls | 3000
total_time | 8015.156
rows | 2990
hit_percent | 99.9731126579631345
-[ RECORD 3 ]---------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_time | 310.624
rows | 100000
hit_percent | 0.30395136778115501520
-[ RECORD 4 ]---------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?;
calls | 3000
total_time | 271.741999999997
rows | 3000
hit_percent | 93.7968855088209426
-[ RECORD 5 ]---------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_time | 81.42
rows | 0
hit_percent | 34.4947735191637631 Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>。Peter Geoghegan <peter@2ndquadrant.com>为它加入了查询正规化的功能。