本篇內(nèi)容主要講解“怎么使用PostgreSQL的pg_stat_statements”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓小編來帶大家學(xué)習(xí)“怎么使用PostgreSQL的pg_stat_statements”吧!
為五河等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù),及五河網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為做網(wǎng)站、網(wǎng)站建設(shè)、五河網(wǎng)站設(shè)計,以傳統(tǒng)方式定制建設(shè)網(wǎng)站,并提供域名空間備案等一條龍服務(wù),秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達到每一位用戶的要求,就會得到認可,從而選擇與我們長期合作。這樣,我們也可以走得更遠!pg_stat_statements提供了一種跟蹤所有SQL語句的手段。
進入pg_stat_statements目錄,執(zhí)行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 ../../src/backend generated-headers make[1]: Entering directory `/data/source/postgresql-12beta1/src/backend' make -C catalog distprep generated-header-symlinks make[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-symlinks make[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 install make -C ../../src/backend generated-headers make[1]: Entering directory `/data/source/postgresql-12beta1/src/backend' make -C catalog distprep generated-header-symlinks make[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-symlinks make[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]$
使用前需在配置文件中添加預(yù)加載共享鏈接庫,否則會報錯:
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參數(shù):
[pg12@localhost pg12db1]$ grep 'shared_preload' postgresql.conf shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
注意:修改該參數(shù)后需要重啟數(shù)據(jù)庫實例.
下面是簡單的使用:
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
查詢匯總統(tǒng)計信息
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”有了更深的了解,不妨來實際操作一番吧!這里是創(chuàng)新互聯(lián)網(wǎng)站,更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
本文名稱:怎么使用PostgreSQL的pg_stat_statements-創(chuàng)新互聯(lián)
鏈接分享:http://aaarwkj.com/article48/cojdep.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站內(nèi)鏈、App開發(fā)、網(wǎng)站維護、網(wǎng)站設(shè)計公司、建站公司、網(wǎng)站策劃
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內(nèi)容