Pg_stat_statements Extension Installation Step description

I. Installation pg_stat_statements

1. Install the plugin pg_stat_statements

[Email protected] soft]# CD postgresql-9.3.0

[[email protected] 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

[Email protected] postgresql-9.3.0]# CD contrib/

[[email protected] 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

[Email protected] contrib]# CD pg_stat_statements/

[[email protected] 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

[[email protected] 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

[[email protected] 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/'

[Email protected] pg_stat_statements]#

2. Modify the configuration file

VI postgresql.conf

shared_preload_libraries = ' pg_stat_statements '

Pg_stat_statements.max = 10000

Pg_stat_statements.track = All

3. Restart the database

Second, the test

1. Set up a test database bench

[Email protected] data]$ createdb Bench

2. Reset Count

Psql Bench

bench=# SELECT Pg_stat_statements_reset ();

Pg_stat_statements_reset

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

(1 row)

3. Initialize the test library

[Email protected] 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.

[Email protected] data]$

4. Load the extension

Psql Bench

bench=# CREATE EXTENSION pg_stat_statements;

Uninstalling the module method

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. Perform the test

[Email protected] 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)

[Email protected] data]$

6. Performance Query

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;

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1721988

Pg_stat_statements Extension Installation Step description