pg_stat_statements扩展安装步骤说明

大佬教程收集整理的这篇文章主要介绍了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

@H_270_0@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 | scheR_871_11845@a | 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,@R_821_10586@l_time,rows,100.0 * shared_blks_hit /

NULLIF(shared_blks_hit + shared_blks_read,0) AS hit_percent

FROM pg_stat_statements ORDER BY @R_821_10586@l_time DESC liMIT 5;

大佬总结

以上是大佬教程为你收集整理的pg_stat_statements扩展安装步骤说明全部内容，希望文章能够帮你解决pg_stat_statements扩展安装步骤说明所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。