pg_stat_statements

PostgreSQL 9.4.4 中文手册
上一页 | 上一级 | 附录 F. 额外提供的模块 | 下一页

# F.29. pg_stat_statements

`pg_stat_statements`模块提供一种跟踪执行统计服务器执行的所有SQL语句的手段。

该模块必须通过在`postgresql.conf`中添加`pg_stat_statements` 到shared_preload_libraries来加载，因为它需要额外的共享内存。 这意味着添加或删除这个模块都需要重启服务器。

## F.29.1. `pg_stat_statements` 视图

该模块收集的统计通过一个名为`pg_stat_statements`的系统视图使其可用。 这个模块为每个不同的查询、数据库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` |  | 该语句花费的总时间，以毫秒计
`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，否则为0）
`blk_write_time` | `double precision` |  | 该语句写入块花费的总时间，以毫秒计 （如果启用了track_io_timing，否则为0）

这个视图和函数`pg_stat_statements_reset`和 `pg_stat_statements`，只有在通过安装 `pg_stat_statements`扩展特别安装到的数据库中可用。 不过，当`pg_stat_statements`模块加载到服务器中时， 统计跟踪该服务器中的所有数据库，不管该视图是否存在。

为了安全起见，不允许非超级用户查看其它用户执行的查询的SQL文本或`queryid`。 不过，如果视图已经安装到他们的数据库中，那么他们可以看到统计。

可计划的查询（也就是，`SELECT`, `INSERT`, `UPDATE`, 和 `DELETE`）组合成为一个`pg_stat_statements`， 当它们根据一个内部哈希计算有相同的查询结构时。典型的，如果两个查询语义上相等， 除了查询中字面常量的值之外，我们认为这两个查询相同。工具命令（也就是，所有其他命令） 是直接基于它们的文本查询字符串比较的。

当一个常量的值为了匹配其他查询而忽略时，该常量在`pg_stat_statements` 的显示中被`?`替代。查询文本的剩余部分是第一个查询特定 `queryid`散列值与`pg_stat_statements`相关条目。

在一些情况下，带有明显不同文本的查询可能合并到一个`pg_stat_statements`。 通常这只在语义相等的查询上发生，但是有很小的可能哈希冲突导致不相关的查询被合并到一个条目。 （不过，这对于属于不同用户或数据库的查询来说是不会发生的。）

因为哈希值是基于分析查询的表示法之后来计算的，相反的也是可能的： 带有相同文本的查询可能表现为单独的条目，如果它们因为一个因素的结果有不同的含义， 比如不同的`search_path`设置。

`pg_stat_statements`的消耗者可能希望将`queryid` （可能和`dbid`和`userid`结合使用） 用作每个条目更稳定可靠的标识符而不是它的查询文本。 但是，重要的是要理解`queryid`哈希值的稳定性保证是有限的。 因为标识符来源于post-parse-analysis树， 它的值是在其他事物之上，出现在这个表示法中的内部对象标识符的一个函数。 这有一些违背常理。例如，如果在两个查询的执行期间它们引用的表删除之后又重新创建了， 那么`pg_stat_statements`将认为这两个看起来相同的查询是不同的。 哈希过程对机器架构和平台其他方面的不同也是敏感的。 另外，对`queryid`跨PostgreSQL 的主要版本还是稳定的这种假设是不安全的。

根据经验，只要底层服务器版本和目录元数据细节保持完全相同， `queryid`的值就可以假设为稳定和可比较的。 参与到基于物理WAL重放复制中的两个服务器，对于相同的查询可以预计为有不同的 `queryid`值。不过，逻辑复制模式并不保证复制品在所有相关细节上都相同， 所以`queryid`对于累加一系列逻辑复制的消耗来说不是一个有用的标识符。 如果有怀疑，建议直接做测试。

## F.29.2. 函数

`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。

## F.29.3. 配置参数

`pg_stat_statements.max` (`integer`)

  `pg_stat_statements.max`是该模块追踪语句的最大值 （也就是，`pg_stat_statements`视图中的最大行数）。 如果观察了比这更多的不同的语句，则会抛弃执行最少的语句的信息。 缺省值是5000。这个参数只能在服务器启动时设置。

`pg_stat_statements.track` (`enum`)

  `pg_stat_statements.track`控制哪个语句可以被该模块计数。 声明`top`来跟踪顶级的语句（直接通过客户端发出的语句）。`all` 也跟踪嵌套的语句（比如包含在函数中的语句），或`none`禁用语句状态收集。 缺省值是`top`。只有超级用户可以更改这个设置。

`pg_stat_statements.track_utility` (`boolean`)

  `pg_stat_statements.track_utility`控制该模块是否追踪工具命令。 工具命令是除了`SELECT`, `INSERT`, `UPDATE` 和 `DELETE`的所有命令。缺省值是`on`。只有超级用户可以更改这个设置。

`pg_stat_statements.save` (`boolean`)

  `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

## F.29.4. 示例输出

    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

## F.29.5. 作者

Takahiro Itagaki `<itagaki.takahiro@oss.ntt.co.jp>`。 Peter Geoghegan `<peter@2ndquadrant.com>`添加了查询正常化。

上一页 | 起始页 | 下一页
pgrowlocks | 上一级 | pgstattuple