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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!