转载

PostgreSQL模板 pg_stat_statements的官方介绍/简单安装步骤

链接：http://blog.itpub.net/28602568/viewspace-2077893/

标题：PostgreSQL模板 pg_stat_statements的官方介绍/简单安装步骤

PostgreSQL 9.3.1 官方中文手册介绍pg_stat_statements

F.28. pg_stat_statements

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

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

F.28.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
query	text		有代表性的语句的文本 (多达 track_activity_query_size 字节)
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模块加载到服务器中时，统计跟踪该服务器中的所有数据库，不管该视图是否存在。

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

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

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

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

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

F.28.2. 函数

pg_stat_statements_reset() returns void 【执行select pg_stat_statements_reset(); 相当于清空pg_stat_statements系统视图】

pg_stat_statements_reset抛弃所有pg_stat_statements 到目前为止收集的统计。缺省的，这个函数只能被超级用户执行。

F.28.3. 配置参数

pg_stat_statements.max (integer)

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

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 * track_activity_query_size字节【10000*1024/1024/1024＝9.7M】。请注意，这个内存在该模块加载时被消耗，即使pg_stat_statements.track 设置为none。

track_activity_query_size (integer)指定跟踪当前执行命令的预留字节数，为了每个活动会话，以及pg_stat_activity.query字段。默认值是1024。这个参数只能在服务器启动时设置。

这些参数必须在postgresql.conf中设置。典型的用法是：

# postgresql.conf shared_preload_libraries = 'pg_stat_statements'      # (change requires restart) pg_stat_statements.max = 10000 pg_stat_statements.track = all

F.28.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 1; -[ RECORD 1 ]--------------------------------------------------------------------- query       | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; calls       | 3000 total_time  | 9609.00100000002 rows        | 2836 hit_percent | 99.9778970000200936

简单安装步骤：

1、编写$PGDATA/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 1000

pg_stat_statements.track = all

2、编译安装pg_stat_statements模块

进入postgresql的源码目录：

cd ...postgresql-9.1.3/contrib/pg_stat_statements 【find / -name 找下...路径】

make

make install

#如果..../share/extension目录下存在pg_stat_statements--1.*.sql，说明安装成功了

3、加载pg_stat_statements模块

#启动postgresql服务

pg_ctl start

#加载sql文件

$ psql -f pg_stat_statements--1.2.sql

Use "CREATE EXTENSION pg_stat_statements" to load this file.

#查看版本

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
pg_stat_statements | 1.2 | 1.2 | track execution statistics of all SQL statements executed

#进入DB创建系统视图：

postgres=# create extension pg_stat_statements;

CREATE EXTENSION

postgres=# select * from pg_stat_statements; --不报错就可以使用了，随意DDL/DML下~再查看pg_stat_statements会有跟踪记录

4、本人使用pg_stat_statements用来监控是否有drop index的情况.和慢sql...等，如下是查看drop index 情况；

SELECT PA.ROLNAME,PD.DATNAME,P.QUERY,CALLS,TOTAL_TIME,ROWS
FROM PG_STAT_STATEMENTS P
JOIN PG_AUTHID PA ON PA.OID = P.USERID
JOIN PG_DATABASE PD ON PD.OID = P.DBID
-- AND DATNAME LIKE 'BI_EDW_SHOES%'
AND UPPER(QUERY) LIKE '%DROP %INDEX%'

-- 过滤掉存储/函数中包含的drop index关键字

and UPPER(QUERY) not like '%=%DROP %INDEX%' ;

源码介绍参考： http://www.cnblogs.com/gaojian/archive/2013/08/16/3261568.html

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 PostgreSQL 分类目录。将固定连接加入收藏夹。

正文到此结束