Shulou(Shulou.com)05/31 Report--

This article focuses on "how to use PostgreSQL's pg_stat_statements". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use PostgreSQL's pg_stat_statements.

Pg_stat_statements provides a means to track all SQL statements.

Go to the pg_stat_statements directory and execute make/make install

[pg12@localhost pg_stat_statements] $lsexpected pg_stat_statements--1.4--1.5.sql pg_stat_statements.controlMakefile pg_stat_statements--1.4.sql pg_stat_statements.opg_stat_statements--1.0--1.1.sql pg_stat_statements--1.5--1.6.sql pg_stat_statements .sopg _ stat_statements--1.1--1.2.sql pg_stat_statements--1.6--1.7.sql pg_stat_statements--unpackaged--1.0.sqlpg_stat_statements--1.2--1.3.sql pg_stat_statements.c sqlpg_stat_statements--1.3--1.4.sql pg_stat_ statements.confs [pg12 @ localhost pg_stat_statements] $makemake-C.. / .. / src/backend generated-headersmake [1]: Entering directory `/ data/source/postgresql-12beta1/src/backend'make-C catalog distprep generated-header-symlinksmake [2]: Entering directory` / data/source/postgresql-12beta1/src/backend/catalog'make [2]: Nothing to be done for `distprep'.make [2]: Nothing to be done for `generated-header-symlinks'.make [2]: Leaving directory `/ data/source/postgresql-12beta1/src/backend/catalog'make-C utils distprep generated -header-symlinksmake [2]: Entering directory `/ data/source/postgresql-12beta1/src/backend/utils'make [2]: Nothing to be done for `distprep'.make [2]: Nothing to be done for `generated-header-symlinks'.make [2]: Leaving directory` / data/source/postgresql-12beta1/src/backend/utils'make [1]: Leaving directory `/ data/source/postgresql-12beta1/src/backend' [pg12@localhost pg_stat_statements] $make installmake-C.. /. / src/backend generated-headersmake [1]: Entering directory `/ data/source/postgresql-12beta1/src/backend'make-C catalog distprep generated-header-symlinksmake [2]: Entering directory` / data/source/postgresql-12beta1/src/backend/catalog'make [2]: Nothing to be done for `distprep'.make [2]: Nothing to be done for `generated-header-symlinks'.make [2]: Leaving directory `/ data/source/postgresql-12beta1/src/backend/catalog'make-C utils distprep generated-header -symlinksmake [2]: Entering directory `/ data/source/postgresql-12beta1/src/backend/utils'make [2]: Nothing to be done for `distprep'.make [2]: Nothing to be done for `generated-header-symlinks'.make [2]: Leaving directory` / data/source/postgresql-12beta1/src/backend/utils'make [1]: Leaving directory `/ data/source/postgresql-12beta1/src/backend'/usr/bin/mkdir-p' / appdb/xdb/pg12beta1/lib/postgresql'/usr / bin/mkdir-p'/ appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir-p'/ appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/install-c-m 755 pg_stat_statements.so'/ appdb/xdb/pg12beta1/lib/postgresql/pg_stat_statements.so'/usr/bin/install-c-m 644. / pg_stat_statements.control'/ appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/ Install-c-m 644. / pg_stat_statements--1.4.sql. / pg_stat_statements--1.6--1.7.sql. / pg_stat_statements--1.5--1.6.sql. / pg_stat_statements--1.4--1.5.sql. / pg_stat_statements--1.3--1.4.sql. / pg_stat_statements--1.2--1.3.sql. / pg_stat _ statements--1.1--1.2.sql. / pg_stat_statements--1.0--1.1.sql. / pg_stat_statements--unpackaged--1.0.sql'/ appdb/xdb/pg12beta1/share/postgresql/extension/' [pg12@localhost pg_stat_statements] $

You need to add a preloaded shared link library to the configuration file before using it, otherwise an error will be reported:

Testdb=# create extension pg_stat_statements;CREATE EXTENSIONtestdb=# select * from pg_stat_statements;psql: ERROR: pg_stat_statements must be loaded via shared_preload_libraries

Modify the shared_preload_libraries parameter in the postgresql.conf file:

[pg12@localhost pg12db1] $grep 'shared_preload' postgresql.conf shared_preload_libraries =' pg_stat_statements' # (change requires restart)

Note: after modifying this parameter, you need to restart the database instance.

Here is a simple use:

Testdb=#\ xExpanded display is on.testdb=# select * from pg_stat_statements -[RECORD 1]-+-- userid | 10dbid | 16384queryid | 6343734953611294604query | select * from pg_stat_statementscalls | 2total_time | 0.618297min_time | 0.303231max_time | | 0.315066mean_time | 0.3091485stddev_time | 0.00591750000000002rows | 2shared_blks_hit | 0shared_blks_read | 0shared_blks_dirtied | 0shared_blks_written | 0local_blks_hit | 0local_blks_read | 0local_blks_dirtied | 0local_blks_written | 0temp_blks_read | 0temp_blks_written | 0blk_read_time | 0blk_write | _ time | 0-[RECORD 2]-+-userid | 10dbid | 16384queryid |-2568307067200732111query | select * from t1calls | 1total_time | 876.219199min_time | 876. 219199max_time | 876.219199mean_time | 876.219199stddev_time | 0rows | 1100000shared_blks_hit | 0shared_blks_read | 5946shared_blks_dirtied | 0shared_blks_written | 0local_blks_hit | 0local_blks_read | 0local_blks_dirtied | 0local_blks_written | 0temp_blks_read | 0temp_blks_written | 0blk_read_time | 0blk_write_time | 0

Query summary statistics

Testdb=# SELECT query, (total_time/ 1000 / 60) as total, (total_time/calls) as avgFROM pg_stat_statements ORDER BY 1 DESC LIMIT 100 Query | total | avg-- + SELECT + | 1.5928199999999998e-05 | 0.318564 (total_time / $1 / $2) as total + | | (total_time/calls) as avg + | | query + | | FROM pg_stat_statements + | | ORDER BY 1 DESC + | | LIMIT $3 | | | SELECT + | 4.9146500000000005e-06 | 0.294879 (total_time / $1 / $2) as total | + | | (total_time/calls) as avg + | | FROM pg_stat_statements + | | ORDER BY 1 DESC + | | LIMIT $3 | | select * From T1 | 0.014603653316666666 | 876.219199 select * from pg_stat_statements | 1.5696116666666667e-05 | 0.313922333333336 (4 rows)... testdb=# testdb=# SELECT testdb-# query Testdb-# round ((total_time/ 1000 / 60):: numeric,4) as total, testdb-# round ((total_time/calls):: numeric,4) as avgtestdb-# FROM pg_stat_statements testdb-# WHERE query like'% t1%'testdb-# ORDER BY 1 DESC testdb-# LIMIT 100 Query | total | avg-+-select * from T1 where id = $1 | 0.0022 | 129.9684 select * from T1 where id < $1 | 0.0020 | 121.3334 select * from T1 | 0.0146 | 876.2192 (3 rows)

Id = xx in the query condition, and the actual value is replaced with a placeholder ($x).

At this point, I believe you have a deeper understanding of "how to use PostgreSQL's pg_stat_statements". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.