一.安装 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;
