怎么使用PostgreSQL的pg_stat_statements

本篇内容主要讲解"怎么使用PostgreSQL的pg_stat_statements"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"怎么使用PostgreSQL的pg_stat_statements"吧!

pg_stat_statements提供了一种跟踪所有SQL语句的手段。

进入pg_stat_statements目录,执行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.conf[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]$

使用前需在配置文件中添加预加载共享链接库,否则会报错:

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

在postgresql.conf文件中修改shared_preload_libraries参数:

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

注意:修改该参数后需要重启数据库实例.

下面是简单的使用:

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

查询汇总统计信息

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.31392233333333336(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,实际值被替换为占位符($x).

到此，相信大家对"怎么使用PostgreSQL的pg_stat_statements"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！