PostgreSQL每日一贴-pg_stat_statements文档

pg_stat_statements

 

      pg_stat_statements模块提供了跟踪一个数据库服务器执行的所有SQL语句的统计信息的方法。

      因为需要额外的共享内存，所以需要在postgresql.conf中把pg_stat_statements增加到shared_preload_libraries。这样才能加载这个模块。这也就意味着增加或删除该模块后必须要重启数据库。

F.29.1. pg_stat_statements视图

      可以通过系统视图pg_stat_statements来查看该模块收集的统计信息。对于每一组不同的database ID, user ID和query ID，视图中均有一行记录（直到达到可以跟踪的sql的最大值）。视图的列信息如Table F-20所示。

Table 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 block cache命中总次数
shared_blks_read	bigint		shared blocks读总次数
shared_blks_dirtied	bigint		shared blocks脏读总次数（dirtied）
shared_blks_written	bigint		shared blocks写总次数
local_blks_hit	bigint		local block cache命中总次数
local_blks_read	bigint		local blocks读总次数
local_blks_dirtied	bigint		local blocks脏读总次数（dirtied）
local_blks_written	bigint		local blocks写总次数
temp_blks_read	bigint		temp blocks读总次数
temp_blks_written	bigint		temp blocks写总次数
blk_read_time	double precision		读block总耗时，单位毫秒（只有打开track_io_timing才生效，否则为0）
blk_write_time	double precision		写block总耗时，单位毫秒（只有打开track_io_timing才生效，否则为0）

      这个视图以及函数pg_stat_statements_reset、 pg_stat_statements只能在指定安装了模块pg_stat_statements的数据库上只用。但是，一旦pg_stat_statements模块被加载到数据库服务器中，所有数据库的信息都会被统计，不论这个视图是否存在。

      因为安全的原因，非超级管理员用户不能查看其它用户执行的SQL文本和queryid。但是，如果这个视图已经安装到了他们的数据库中，他们可以查看统计信息。根据内部散列计算，认为Plannable查询(指SELECT, INSERT, UPDATE,and DELETE)有相同的查询结构时，Plannable查询会被组合到一个pg_stat_statements元祖中。典型的，如果两个sql语句语义上除了出现在查询中的文字常量不同外，其他是相同的，则这两个sql会被认为是相同的。但是Utility命令（非Plannable查询）会严格的比较文本查询字符串。
视图pg_stat_statements中的查询文本显示时，如果这个语句为了匹配另外一个查询语句而将某个常量忽略了，则这个常量会被显示为？。剩余的语句文本是与pg_stat_statements相关条目关联的特定queryid散列值对应的第一个语句文本。

      在某些实例中，有明显不同文本的查询会被合并为一个pg_stat_statements元祖。通常，合并为同一元祖这种情况只有在语义相同的语句才会出现。但是，也有小概率出现哈希碰撞，导致不相关的查询合并为同一个条目（但是，如果是在不同的数据库或使用不同用户执行的，这种情况就不会出现）。

相反的 ，由于queryid哈希值是在语句的post-parse-analysis阶段计算的，完全相同的查询语句被认为是不同的也是有可能的，比如如果他们有不同的语义因素，如不同的search_path。

      pg_stat_statements使用者可能希望使用queryid（可能是queryid，dbid和userid的组合）而不是查询文本作为一个元祖更为稳定和可靠的标识符。但是需要注意的是，系统只能在一定程度上保证queryid哈希值的稳定。因为标识符来源于post-parse-analysis树，而这个值是通过函数、内部对象标识符等等计算而来（Since the identifier isderived from the post-parse-analysis tree, its value is a function of, amongother things, the internal object identifiers appearing in this representation）。同时，有些违反直觉的场景存在。例如，如果在创建两个查询的执行过程之间，他们引用的一个表被删除并重新创建了，pg_stat_statements会认为这两个明显相同的查询是截然不同的。同时散列过程对不同平台的体系结构和其他方面也是敏感的。因此，假设同一个sql在不同PostgreSQL版本中的queryid是相同的是不安全的做法。

      一般来说，只有在底层服务器版本和系统元祖详细信息完全相同的情况下，才可以假定同样查询的queryid是相同的。基于物理WAL回放参与复制的两个服务器可以假设相同queryid值对应相同的查询。但是，逻辑复制不能保证。如果有疑问，建议直接测试。

F.29.2. 函数

pg_stat_statements_reset() returns void

该函数会丢弃到当前已经收集的所有语句的统计信息。默认该函数只能被超级管理员调用。

pg_stat_statements(showtext boolean) returns setof record

定义pg_stat_statements视图的函数名称也是pg_stat_statements。客户端可能会直觉调用函数pg_stat_statements。如果参数showtext执行为false，返回的信息中将不包含语句文本信息（也就是，对应于视图的query列的值将返回null）。此功能是为了支持希望避免反复检索不定长度语句文本开销的外部工具。

这些工具可以缓存他们发现的每个语句文本（每条语句在第一次发现时缓存），正如pg_stat_statements所做的那样，然后只在需要检索语句文本的时候再去使用。因为这些服务使用文件来存储语句文本，这种方法可能会减少反复检查pg_stat_statements视图所产生的物理I/O。

F.29.3. 配置参数

pg_stat_statements.max (integer)

pg_stat_statements.max设置的是该模块可以追踪的最大语句数量（也就是视图pg_stat_statements的最大行数）。如果实际的语句数量大于这个值，超过这个值后的那些sql语句信息会被丢弃。该值默认是5000。重新设置这个值后必须重启数据库。

pg_stat_statements.track (enum)

pg_stat_statements.track用来设置哪些语句会被统计。

top 只统计最上层的sql语句（之间通过客户端发送的那些）。

all  统计所有语句，包括那些嵌套的语句（比如存储过程中的某些sql）。

none 禁用统计功能。

参数默认值为top。只有超级管理员才可以修改这个设置。

pg_stat_statements.track_utility (boolean)

pg_stat_statements.track_utility控制是否追踪utility命令。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 ORDERBY total_time DESC LIMIT 5;
-[ RECORD 1]---------------------------------------------------------------------
query       | UPDATE pgbench_branches SETbbalance = bbalance + ? WHERE bid = ?;
calls       | 3000
total_time  |9609.00100000002
rows        | 2836
hit_percent | 99.9778970000200936
-[ RECORD 2]---------------------------------------------------------------------
query       | UPDATE pgbench_tellers SETtbalance = tbalance + ? WHERE tid = ?;
calls       | 3000
total_time  | 8015.156
rows        | 2990
hit_percent | 99.9731126579631345
-[ RECORD 3 ]---------------------------------------------------------------------
query       | copy pgbench_accounts fromstdin
calls       | 1
total_time  | 310.624
rows        | 100000
hit_percent | 0.30395136778115501520
-[ RECORD 4]---------------------------------------------------------------------
query       | UPDATE pgbench_accounts SETabalance = abalance + ? WHERE aid = ?;
calls       | 3000
total_time  | 271.741999999997
rows        | 3000
hit_percent | 93.7968855088209426
-[ RECORD 5]---------------------------------------------------------------------
query       | alter table pgbench_accountsadd 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>. Query normalization added by PeterGeoghegan <peter@2ndquadrant.com>.