postgresql模块——pg_stat_statements详解和安装测试

longpan

　　其实很简单，最近可能需要对postgresql进行监控，所以接触了很多相关的监控命令和工具，这边文章主要是记录下工作过程，怕之后会忘记。
　　
　　转载注明出处：http://blog.csdn.net/lengzijian/article/details/8133471
　　
　　我想要的功能：记录每条sql的执行时间，能够查询每天执行最慢的top10。
　　

　　下面先介绍下pg_stat_statements：(翻译)
　　引文原文地址：http://www.postgresql.org/docs/9.0/interactive/pgstatstatements.html
　　

　　pg_stat_statements模块提供了一种跟踪执行的所有SQL语句的统计信息的方法。
　　这个模块必须改写配置文件postgresql.conf中的shared_preload_libraries变量(之后讲解如何配置)，这是因为他需要额外的共享内存。同时也意味着需要重启服务。
　　
　　1-先看下pg_stat_statements视图

字段名	类型	引用	说明
userid	oid	pg_authid.oid	执行者id
dbid	oid	pg_database.oid	执行数据库id
query	text		执行的语句
calls	bigint		执行次数
total_time	double precision		执行总时间 (平均值=total_time/calls )
rows	bigint		影响的总行数
shared_blks_hit	bigint		共享块命中数量
shared_blks_read	bigint		共享块读数量
shared_blks_written	bigint		共享块写数量
local_blks_hit	bigint		本地块命中数量
local_blks_read	bigint		本地块读数量
local_blks_written	bigint		本地块写数量

　　如上视图和函数pg_stat_statements_reset只有在数据库已经正确安装，并且已经执行pg_stat_statements.sql脚本后才会生效。只要pg_stat_statements成功添加，就会跟踪服务器上所有的数据库操作。
　　
　　处于安全的原因，普通用户不允许查看其他用户执行的语句信息(query)，如果视图安装到他的数据库，那么就可以查看相关的统计信息(子健做的实验实在超级用户下：postgres用户postgres库)
　　
　　注意，如果语句信息(query)一样，不论任何out-of-line变量的值被使用，都会认为这几条声明是相同的。使用out-of-line变量有助于组织语句并且可能回事统计数据更加有用

　　2­­-函数
　　pg_stat_statements_reset() returns void
　　pg_stat_statements_reset丢弃目前由pg_stat_statements统计的所有信息，默认情况下，这个函数只能运行在超级用户下。
　　
　　3-配置变量
　　pg_stat_statements.max(integer)
　　pg_stat_statements.max是最大追踪的统计数据数量(即，视图中的最大行数)。如果数据量大于最大值，那么执行最少的语句将会被丢弃(本人测试，如果语句执行次数都为1时，其次是时间久的数据被丢弃)，这个值默认是1000，这个变量在服务启动前设置。
　　
　　pg_stat_statements.track(enum)
　　 pg_stat_statements.track控制统计数据规则，top用于追踪top-level statement(直接由客户端方发送的)，all还会追踪嵌套的statements(例如在函数中调用的statements)
　　
　　pg_stat_statements.track_utility(boolen)
　　pg_stat_statements.track_utility控制是否跟踪公共程序命令(utility commands)，公共程序命令是SELECT/INSERT/UPDATE/DELETE以外的命令，默认值是开启，只有超级用户可以更改此设置。
　　
　　pg_stat_statements.save(boolean)
　　pg_stat_statements.save指定在服务器关闭时，是否保存统计信息。如果设置off，服务关闭时，统计信息将不会保存。默认值是on。这个值只能够在postgresql.conf中或者命令行设置。
　　
　　该模块需要额外的共享内存，内存大小大致为pg_stat_statements.max* track_activity_query_size。要注意的是，一旦模块被加载，即使pg_stat_statements.track设置为none，共享内存都会被消耗。
　　
　　上面的都是一些需要掌握的知识，下面开始真正配置pg_stat_statements并且运行
　　首先要编写postgresql.conf

#postgresql.conf 　　#------------------------------------------------------------------------------ 　　# PG_STAT_STATEMENTS OPTIONS 　　#------------------------------------------------------------------------------ 　　shared_preload_libraries = 'pg_stat_statements' 　　custom_variable_classes = 'pg_stat_statements' 　　pg_stat_statements.max = 1000 　　pg_stat_statements.track = all

　　4-编译安装pg_stat_statements模块
　　进入postgresql的源码目录：

cd /home/proxy_pg/postgresql-9.1.3/contrib/pg_stat_statements 　　make 　　make install 　　#如果$pgpath/share/extension目录下存在pg_stat_statements--1.0.sql，说明安装成功了

　　
　　5-加载pg_stat_statements模块

#启动postgresql服务 　　bin/pg_ctl start -D stat_date/ 　　#加载sql文件 　　[postgres@slave2 pgsql]$ bin/psql -f share/extension/pg_stat_statements--1.0.sql -p 5499 　　Use "CREATE EXTENSION pg_stat_statements" to load this file. 　　#进入数据库做，如下操作： 　　[postgres@slave2 pgsql]$ bin/psql -p 5499 　　psql (9.1.3) 　　Type "help" for help. 　　 　　postgres=# create extension pg_stat_statements;#创建pg_stat_statements 　　CREATE EXTENSION 　　postgres=# SELECT pg_stat_statements_reset();#清空pg_stat_statements(可以不做) 　　pg_stat_statements_reset 　　-------------------------- 　　 　　(1 row) 　　#我们手动插入10条数据：(这里是自己写的脚本，可以通过www下载) 　　./a.out 1 10 　　#执行如下命令 　　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; 　　 　　#可以看到最耗时的5条数据，最后一列表示命中率

　　
　　虽然成功了，但这也是监控系统中很小的一部分，接下来我的挑战是把各种监控系统融合在意思，做出可视化界面，使监控更友好。