postgresql安装pg_stat_statements 模块监控运行状态

一、编译安装pg_stat_statements 模块
[root@localhost ~]# cd /usr/local/src/postgresql-9.1.3/contrib/pg_stat_statements/
[root@localhost pg_stat_statements]# make
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o pg_stat_statements.o pg_stat_statements.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o pg_stat_statements.so pg_stat_statements.o -L../../src/port  -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags  
[root@localhost pg_stat_statements]# make install
/bin/mkdir -p '/usr/local/pgsql/lib'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/sh ../../config/install-sh -c -m 755  pg_stat_statements.so '/usr/local/pgsql/lib/pg_stat_statements.so'
/bin/sh ../../config/install-sh -c -m 644 ./pg_stat_statements.control '/usr/local/pgsql/share/extension/'
/bin/sh ../../config/install-sh -c -m 644 ./pg_stat_statements--1.0.sql ./pg_stat_statements--unpackaged--1.0.sql  '/usr/local/pgsql/share/extension/'
[root@localhost pg_stat_statements]# 

[postgres@localhost ~]$ psql 
psql (9.1.3)
Type "help" for help.


postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres=# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_statements | 1.0     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)


二、添加pg_stat_statement 参数


# vi 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


 # /etc/init.d/postgresql start 


三、# 生成测试数据


[root@localhost pg_stat_statements]# su - postgres
[postgres@localhost ~]$ psql 
psql (9.1.3)
Type "help" for help.


postgres=# create table t22(id int);
CREATE TABLE
postgres=# insert into t22 values(generate_series(1,13335));
INSERT 0 13335
postgres=# insert into t22 values(generate_series(1,1333599));
INSERT 0 1333599
postgres=# select count(*) from t22;
  count  
---------
 1346934
(1 row)


postgres=# select count(*)from t22 where id <10000 and id>88 ;
 count 
-------
 19822
(1 row)

postgres=# update t22 set id=88888888 where id <10000 and id>88 ;
UPDATE 19822


四、监控结果查询


# 查询慢查执行时间前5的SQL
postgres=# 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;
                         query                          | calls | total_time |  rows   |     hit_percent     
--------------------------------------------------------+-------+------------+---------+---------------------
 insert into t22 values(generate_series(1,1333599));    |     1 |  21.729554 | 1333599 | 99.6123143374028840
 update t22 set id=88888888 where id <10000 and id>88 ; |     1 |   0.530321 |   19822 | 96.5676830753559645
 select count(*) from t22;                              |     1 |   0.447886 |       1 | 75.4306265379519213
 select count(*)from t22 where id <10000 and id>88 ;    |     1 |   0.382753 |       1 | 74.6166950596252129
 insert into t22 values(generate_series(1,13335));      |     1 |   0.043362 |   13335 | 99.5923208064635683
(5 rows)


postgres=#