怎么使用PostgreSQL的pg_stat_statements
短信预约 -IT技能 免费直播动态提醒
本篇内容主要讲解“怎么使用PostgreSQL的pg_stat_statements”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么使用PostgreSQL的pg_stat_statements”吧!
pg_stat_statements提供了一种跟踪所有SQL语句的手段。
进入pg_stat_statements目录,执行make/make install
[pg12@localhost pg_stat_statements]$ ls
expected pg_stat_statements--1.4--1.5.sql pg_stat_statements.control
Makefile pg_stat_statements--1.4.sql pg_stat_statements.o
pg_stat_statements--1.0--1.1.sql pg_stat_statements--1.5--1.6.sql pg_stat_statements.so
pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.6--1.7.sql pg_stat_statements--unpackaged--1.0.sql
pg_stat_statements--1.2--1.3.sql pg_stat_statements.c sql
pg_stat_statements--1.3--1.4.sql pg_stat_statements.conf
[pg12@localhost pg_stat_statements]$ make
make -C ../../class="lazy" data-src/backend generated-headers
make[1]: Entering directory `/data/source/postgresql-12beta1/class="lazy" data-src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/class="lazy" data-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/class="lazy" data-src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/class="lazy" data-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/class="lazy" data-src/backend/utils'
make[1]: Leaving directory `/data/source/postgresql-12beta1/class="lazy" data-src/backend'
[pg12@localhost pg_stat_statements]$ make install
make -C ../../class="lazy" data-src/backend generated-headers
make[1]: Entering directory `/data/source/postgresql-12beta1/class="lazy" data-src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/class="lazy" data-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/class="lazy" data-src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/data/source/postgresql-12beta1/class="lazy" data-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/class="lazy" data-src/backend/utils'
make[1]: Leaving directory `/data/source/postgresql-12beta1/class="lazy" data-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 EXTENSION
testdb=# 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=# \x
Expanded display is on.
testdb=# select * from pg_stat_statements;
-[ RECORD 1 ]-------+---------------------------------
userid | 10
dbid | 16384
queryid | 6343734953611294604
query | select * from pg_stat_statements
calls | 2
total_time | 0.618297
min_time | 0.303231
max_time | 0.315066
mean_time | 0.3091485
stddev_time | 0.00591750000000002
rows | 2
shared_blks_hit | 0
shared_blks_read | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
blk_read_time | 0
blk_write_time | 0
-[ RECORD 2 ]-------+---------------------------------
userid | 10
dbid | 16384
queryid | -2568307067200732111
query | select * from t1
calls | 1
total_time | 876.219199
min_time | 876.219199
max_time | 876.219199
mean_time | 876.219199
stddev_time | 0
rows | 1100000
shared_blks_hit | 0
shared_blks_read | 5946
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
blk_read_time | 0
blk_write_time | 0查询汇总统计信息
testdb=# SELECT query,
(total_time / 1000 / 60) as total,
(total_time/calls) as avg
FROM 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 avg
testdb-# 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”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/24.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
