pg_stat_statements扩展安装步骤说明

一.安装 pg_stat_statements

1.安装插件pg_stat_statements

[root@localhost soft]# cd postgresql-9.3.0

[root@localhost postgresql-9.3.0]# ls

aclocal.m4  config.log     configure     contrib    doc          GNUmakefile.in  INSTALL   README

config      config.status  configure.in  COPYRIGHT  GNUmakefile  HISTORY         Makefile  src

[root@localhost postgresql-9.3.0]# cd contrib/

[root@localhost contrib]# ls

adminpack          cube            hstore    pageinspect        pg_standby          pg_xlogdump    tablefunc    xml2

auth_delay         dblink          intagg    passwordcheck      pg_stat_statements  postgres_fdw   tcn

auto_explain       dict_int        intarray  pg_archivecleanup  pgstattuple         README         test_parser

btree_gin          dict_xsyn       isn       pgbench            pg_test_fsync       seg            tsearch2

btree_gist         dummy_seclabel  lo        pg_buffercache     pg_test_timing      sepgsql        unaccent

chkpass            earthdistance   ltree     pgcrypto           pg_trgm             spi            uuid-ossp

citext             file_fdw        Makefile  pg_freespacemap    pg_upgrade          sslinfo        vacuumlo

contrib-global.mk  fuzzystrmatch   oid2name  pgrowlocks         pg_upgrade_support  start-scripts  worker_spi

[root@localhost contrib]# cd pg_stat_statements/

[root@localhost pg_stat_statements]# ls

Makefile                          pg_stat_statements--1.1.sql  pg_stat_statements.control

pg_stat_statements--1.0--1.1.sql  pg_stat_statements.c         pg_stat_statements--unpackaged--1.0.sql

[root@localhost pg_stat_statements]# make 

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -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 -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o pg_stat_statements.so pg_stat_statements.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/pg/9.3/lib',--enable-new-dtags  

[root@localhost pg_stat_statements]# make install

/bin/mkdir -p '/opt/pg/9.3/lib/postgresql'

/bin/mkdir -p '/opt/pg/9.3/share/postgresql/extension'

/bin/mkdir -p '/opt/pg/9.3/share/postgresql/extension'

/usr/bin/install -c -m 755  pg_stat_statements.so '/opt/pg/9.3/lib/postgresql/pg_stat_statements.so'

/usr/bin/install -c -m 644 ./pg_stat_statements.control '/opt/pg/9.3/share/postgresql/extension/'

/usr/bin/install -c -m 644 ./pg_stat_statements--1.1.sql ./pg_stat_statements--1.0--1.1.sql ./pg_stat_statements--unpackaged--1.0.sql  '/opt/pg/9.3/share/postgresql/extension/'

[root@localhost pg_stat_statements]# 

2.修改配置文件

vi postgresql.conf

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000

pg_stat_statements.track = all

3.重新启动数据库

二、测试

1.建立测试数据库bench

[postgres@localhost data]$ createdb bench

2.重置计数

psql bench

bench=# SELECT pg_stat_statements_reset();

 pg_stat_statements_reset 

--------------------------

(1 row)

3.初始化测试库

[postgres@localhost data]$ pgbench -i bench

NOTICE:  table "pgbench_history" does not exist, skipping

NOTICE:  table "pgbench_tellers" does not exist, skipping

NOTICE:  table "pgbench_accounts" does not exist, skipping

NOTICE:  table "pgbench_branches" does not exist, skipping

creating tables...

100000 of 100000 tuples (100%) done (elapsed 0.72 s, remaining 0.00 s).

vacuum...

set primary keys...

done.

[postgres@localhost data]$ 

4.加载扩展

psql bench

bench=# CREATE EXTENSION pg_stat_statements;

卸载模块方法

bench=# DROP EXTENSION pg_stat_statements;

bench=# \dx

                                     List of installed extensions

        Name        | Version |   Schema   |                        Description     

--------------------+---------+------------+----------------------------------------

-------------------

 pg_stat_statements | 1.1     | public     | track execution statistics of all SQL statements executed

 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language

(2 rows)

bench=# 

5.执行测试

[postgres@localhost data]$ pgbench -c10 -t300 bench

starting vacuum...end.

transaction type: TPC-B (sort of)

scaling factor: 1

query mode: simple

number of clients: 10

number of threads: 1

number of transactions per client: 300

number of transactions actually processed: 3000/3000

tps = 409.872797 (including connections establishing)

tps = 411.678326 (excluding connections establishing)

[postgres@localhost data]$ 

6.性能查询

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;