資訊專欄INFORMATION COLUMN
PostgreSQL中高耗SQL的獲取可以使用pg_stat_statements模塊來獲取，pg_stat_statements模塊提供執(zhí)行SQL語句的執(zhí)行統(tǒng)計信息。
該模塊必須在postgresql.conf的shared_preload_libraries中增加pg_stat_statements來載入，因?yàn)樗枰~外的共享內(nèi)存。增加或移除該模塊需要將數(shù)據(jù)庫重啟。
當(dāng)pg_stat_statements被載入時，它會跟蹤該服務(wù)器的所有數(shù)據(jù)庫的統(tǒng)計信息。該模塊提供了視圖pg_stat_statements以及函數(shù)pg_stat_statements_reset用于訪問和操縱這些統(tǒng)計信息。這些視圖和函數(shù)不是全局可用的，但是可以在指定數(shù)據(jù)庫創(chuàng)建該擴(kuò)展。
創(chuàng)建extension模塊
postgres=# CREATE EXTENSION pg_stat_statements; CREATE EXTENSION |
修改數(shù)據(jù)庫PG_HOME下的postgresql.conf文件
shared_preload_libraries= pg_stat_statements
pg_stat_statements.max= 10000 #pg_stat_statements中記錄的最大的SQL條目數(shù)，默認(rèn)為5000
pg_stat_statements.track= all#記錄pg_stat_statements中的
pg_stat_satements.saveon #用來控制數(shù)據(jù)庫在關(guān)閉的時候，是否將SQL信息保存到文件中。默認(rèn)打開
pg_stat_satements.track_utilityon #追蹤SQL命令：DQLDDL 以及DQL,DDL以外的其他SQL命令(off只記錄DQLDDL)
如果沒有配置postgresql.conf文件中的shared_preload_libraries,那么將會提示如下報錯：
ERROR:pg_stat_statements must be loaded via shared_preload_libraries
使用pg_ctl重新啟動數(shù)據(jù)庫，使擴(kuò)展生效。
pg_ctl start -D $PGDATA -l /tmp/pg_rotate_logfile() |
進(jìn)入數(shù)據(jù)庫，查看pg_stat_statements視圖，有數(shù)據(jù)則安裝成功。
psql -U postgres -d pgtestdb select * from pg_stat_statements; |
該視圖的結(jié)構(gòu)信息如下：
由于安全性原因，只有超級用戶和pg_read_all_stats角色的成員被允許看到其他用戶執(zhí)行的查詢的SQL文本或者queryid。
log_min_duration_statement這個參數(shù)可以控制閾值的時間，如果查詢花費(fèi)的時間長于此閾值時間，則會記錄該SQL。默認(rèn)為1s。可以使用
ALTER SYSTEM SETlog_min_duration_statement = 1000;
更改閾值記錄，單位為ms。
我們可以在數(shù)據(jù)庫中看到平均運(yùn)行時間最高的查詢，如下所示：
SELECT total_time, min_time,(total_time/calls) as avg_time, max_time, mean_time, calls, rows,query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; |
返回結(jié)果如下：
其中各項(xiàng)的涵義：
total_time：返回查詢的總運(yùn)行時間（以毫秒為單位）。
min_time、avg_time和max_time：返回查詢的最小、平均和最大運(yùn)行時間。
mean_time：使用total_time/調(diào)用返回查詢的平均運(yùn)行時間（以毫秒為單位）。
Calls (調(diào)用)：返回查詢運(yùn)行的總數(shù)。
Rows(行數(shù))：返回由于查詢而返回或受影響的行總數(shù)。
Query(查詢)：返回正在運(yùn)行的查詢。默認(rèn)情況下，最多顯示1024個查詢字節(jié)?？梢允褂胻rack_activity_query_size參數(shù)更改此值。
pg_stat_statements所獲得的統(tǒng)計數(shù)據(jù)一直累積到重置。
可以使用以下腳本進(jìn)行按天備份。
備份完成后可以通過具有超級用戶權(quán)限的用戶連接到數(shù)據(jù)庫以重置統(tǒng)計數(shù)據(jù)來運(yùn)行重置：
SELECTpg_stat_statements_reset();
#!/bin/bash # this script is aimed to delete the expired data; # and use the vacummdb command to clean up databases. # Copyright(c) 2016--2016 yuxiangli All Copyright reserved. echo "-----------------------------------------------------" echo `date +%Y%m%d%H%M%S` dates= `date +%Y%m%d` psql -U hbdx_xxx -h 133.0.xxx.xx -d testdb -p xxx << EOF create table public.pg_stat_statements_$dates as select * from public.pg_stat_statements; SELECT pg_stat_statements_reset(); q EOF echo "-----------------------------------------------------" echo `date +%Y%m%d%H%M%S` echo "-----------------------------------------------------" |
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/130094.html
摘要：作者譚峰張文升出版日期年月頁數(shù)頁定價元本書特色中國開源軟件推進(jìn)聯(lián)盟分會特聘專家撰寫，國內(nèi)多位開源數(shù)據(jù)庫專家鼎力推薦。張文升中國開源軟件推進(jìn)聯(lián)盟分會核心成員之一。 很高興《PostgreSQL實(shí)戰(zhàn)》一書終于出版，本書大體上系統(tǒng)總結(jié)了筆者 PostgreSQL DBA 職業(yè)生涯的經(jīng)驗(yàn)總結(jié)，本書的另一位作者張文升擁有豐富的PostgreSQL運(yùn)維經(jīng)驗(yàn)，目前就職于探探科技任首席PostgreS...
摘要：近日，正式發(fā)布了版本，該版本進(jìn)行了大量的修復(fù)和功能改進(jìn)。事實(shí)上，開發(fā)者獲得的所有標(biāo)準(zhǔn)會使它更加強(qiáng)大一個最好的例子是來自近年來的索引，它給提供了極大的性能提升。在最新發(fā)布的版本中，在中的輸出也更具可讀性。 【編者按】作為一款開源的對象—關(guān)系數(shù)據(jù)庫，Postgres 一直得到許多開發(fā)者喜愛。近日，Postgres 正式發(fā)布了9.5版本，該版本進(jìn)行了大量的修復(fù)和功能改進(jìn)。而本文將分享10個 ...
摘要：旨在記錄自己的學(xué)習(xí)過程，方便日后遇到問題是及時查閱復(fù)習(xí)，另一方面也希望能幫助像筆者一樣從來沒使用過的人快速熟悉。 這篇文章主要記錄的是本人學(xué)習(xí)使用IntelliJ IDEA的筆記，可能不是特別的詳細(xì)。旨在記錄自己的學(xué)習(xí)過程，方便日后遇到問題是及時查閱復(fù)習(xí)，另一方面也希望能幫助像筆者一樣從來沒使用過IDEA的人快速熟悉IDEA。文章錯誤之處還請各位大佬批評指正。（文末有本人的微信公眾號，...
摘要：上有主節(jié)點(diǎn)和從節(jié)點(diǎn)兩部分，兩者主要的功能是生成查詢計劃并派發(fā)，以及協(xié)調(diào)并行計算，同時在上保存著，這個全局目錄存著一組數(shù)據(jù)庫系統(tǒng)本身所具有的元數(shù)據(jù)的系統(tǒng)表。 前言：近年來，互聯(lián)網(wǎng)的快速發(fā)展積累了海量大數(shù)據(jù)，而在這些大數(shù)據(jù)的處理上，不同技術(shù)棧所具備的性能也有所不同，如何快速有效地處理這些龐大的數(shù)據(jù)倉，成為很多運(yùn)營者為之苦惱的問題！隨著Greenplum的異軍突起，以往大數(shù)據(jù)倉庫所面臨的很多...
閱讀 1356·2023-01-11 13:20
閱讀 1707·2023-01-11 13:20
閱讀 1215·2023-01-11 13:20
閱讀 1906·2023-01-11 13:20
閱讀 4165·2023-01-11 13:20
閱讀 2757·2023-01-11 13:20
閱讀 1402·2023-01-11 13:20
閱讀 3671·2023-01-11 13:20
