PostgreSQL pg_stat_statements

2021-09-16 16:52 更新
F.29.1. pg_stat_statements視圖
F.29.2. 函數(shù)
F.29.3. 配置參數(shù)
F.29.4. 示例輸出

pg_stat_statements模塊提供了一種跟蹤服務(wù)器執(zhí)行的所有 SQL 語句的計劃和執(zhí)行統(tǒng)計信息的方法。

該模塊必須通過在postgresql.confshared_preload_libraries中增加pg_stat_statements來載入,因為它需要額外的共享內(nèi)存。這意味著增加或移除該模塊需要一次服務(wù)器重啟。

當(dāng)pg_stat_statements被載入時,它會跟蹤該服務(wù)器 的所有數(shù)據(jù)庫的統(tǒng)計信息。該模塊提供了一個視圖 pg_stat_statements以及函數(shù)pg_stat_statements_resetpg_stat_statements用于訪問和操縱這些統(tǒng)計信息。這些視圖 和函數(shù)不是全局可用的,但是可以用CREATE EXTENSION pg_stat_statements 為特定數(shù)據(jù)庫啟用它們。

F.29.1. pg_stat_statements視圖

由該模塊收集的統(tǒng)計信息可以通過一個名為 pg_stat_statements的視圖使用。這個視圖為每 一個可區(qū)分的數(shù)據(jù)庫 ID、用戶 ID 和查詢 ID(最多到該模塊可以追 蹤的可區(qū)分語句的數(shù)量)的組合都包含一行。該視圖的列如 表 F.21中所示。

表 F.21. pg_stat_statements

列類型

描述

userid oid (references pg_authid.oid)

執(zhí)行該語句的用戶的 OID

dbid oid (references pg_database.oid)

在其中執(zhí)行該語句的數(shù)據(jù)庫的 OID

queryid bigint

內(nèi)部哈希碼,從語句的解析樹計算得來

query text

語句的文本形式

plans bigint

計劃語句的次數(shù)(如果啟用了pg_stat_statements.track_planning,否則為零)

total_plan_time double precision

計劃語句所花費的總時間,以毫秒為單位(如果啟用了pg_stat_statements.track_planning,否則為零)

min_plan_time double precision

計劃語句所花費的最短時間,以毫秒為單位(如果啟用了pg_stat_statements.track_planning,否則為零)

max_plan_time double precision

計劃語句所花費的最長時間,以毫秒為單位(如果啟用了pg_stat_statements.track_planning,否則為零)

mean_plan_time double precision

計劃語句所花費的平均時間,以毫秒為單位(如果啟用了pg_stat_statements.track_planning,否則為零)

stddev_plan_time double precision

計劃語句花費的時間的總體標(biāo)準(zhǔn)偏差,以毫秒為單位(如果啟用了pg_stat_statements.track_planning,否則為零)

calls bigint

語句被執(zhí)行的次數(shù)

total_exec_time double precision

執(zhí)行語句所花費的總時間,以毫秒為單位

min_exec_time double precision

執(zhí)行語句所花費的最短時間,以毫秒為單位

max_exec_time double precision

執(zhí)行語句所花費的最長時間,以毫秒為單位

mean_exec_time double precision

執(zhí)行語句的平均時間,以毫秒為單位

stddev_exec_time double precision

執(zhí)行語句花費的時間的總體標(biāo)準(zhǔn)偏差,以毫秒為單位

rows bigint

語句檢索或影響的總行數(shù)

shared_blks_hit bigint

語句的共享塊緩存命中總數(shù)

shared_blks_read bigint

語句讀取的共享塊總數(shù)

shared_blks_dirtied bigint

被語句弄臟的共享塊總數(shù)

shared_blks_written bigint

語句寫入的共享塊總數(shù)

local_blks_hit bigint

語句的本地塊緩存命中總數(shù)

local_blks_read bigint

語句讀取的本地塊總數(shù)

local_blks_dirtied bigint

被語句弄臟的本地塊總數(shù)

local_blks_written bigint

語句寫入的本地塊總數(shù)

temp_blks_read bigint

語句讀取的臨時塊總數(shù)

temp_blks_written bigint

語句寫入的臨時塊總數(shù)

blk_read_time double precision

語句讀取塊所花費的總時間,以毫秒為單位(如果啟用了track_io_timing,否則為零)

blk_write_time double precision

語句寫入塊所花費的總時間,以毫秒為單位(如果啟用了track_io_timing,否則為零)

wal_records bigint

語句生成的 WAL 記錄總數(shù)

wal_fpi bigint

語句生成的 WAL 整頁圖像總數(shù)

wal_bytes numeric

語句生成的 WAL 字節(jié)總數(shù)


由于安全性原因,只有超級用戶和pg_read_all_stats 角色的成員被允許看到其他用戶執(zhí)行的查詢 的 SQL 文本或者queryid。 不過,如果該視圖被安裝在其他用戶的數(shù)據(jù)庫中,那么他們就能夠看見統(tǒng) 計信息。

只要可規(guī)劃的查詢(即SELECT、INSERT、UPDATE以及DELETE)根據(jù)一種內(nèi)部哈希計算具有相同的查詢結(jié)構(gòu),它們就會被組合到一個單一的pg_stat_statements項。通常,對于這里的目的,如果兩個查詢除了查詢中的文本常量值之外在語義上等效,它們將會被認(rèn)為是相同的。不過,功能性命令(即所有其他命令)會嚴(yán)格地以它們的文本查詢字符串為基礎(chǔ)進(jìn)行比較。

當(dāng)為了把一個查詢與其他查詢匹配,常數(shù)值會被忽略, 在pg_stat_statements顯示中它會被一個參數(shù)符號, 比如$1所替換。查詢文本的剩余部分就是具有與該pg_stat_statements項相關(guān)的特定queryid哈希值的第一個查詢的文本。

在某些情況中,具有明顯不同文本的查詢可能會被融合到一個單一的pg_stat_statements項。通常這只會發(fā)生在語義等價的查詢身上,但是也有很小的機(jī)會因為哈希碰撞的原因?qū)е聼o關(guān)的查詢被融合到一個項中(不過,對于屬于不同用戶或數(shù)據(jù)庫的查詢來說不會發(fā)生這種情況)。

由于queryid哈希值是根據(jù)查詢被解析和分析后的表達(dá)計算的,對立的情況也可能存在:如果具有相同文本的查詢由于參數(shù)(如不同的search_path設(shè)置)的原因而具有不同的含義,它們就可能作為不同的項存在。

pg_stat_statements的使用者可能希望使用 queryid(也許會與dbiduserid組合)作為一個項比查詢文本更穩(wěn)定和可靠的標(biāo)識符。但是,有一點很重要的是,對于queryid哈希值穩(wěn)定性只有有限的保障。因為該標(biāo)識符是從解析分析后的樹得來的,它的值是以這種形式出現(xiàn)的內(nèi)部對象標(biāo)識符的函數(shù)。這有一些違背直覺的含義。例如,如果有兩個查詢引用了同一個表,但是該表在兩次查詢之間被刪除并且重建,顯然這兩個查詢是完全一致的,但是 pg_stat_statements將把它們認(rèn)為是不同的。哈希處理也對機(jī)器架構(gòu)以及平臺的其他方面的差別很敏感。更進(jìn)一步,認(rèn)為PostgreSQL的不同主版本之間queryid將會保持穩(wěn)定是不安全的。

根據(jù)經(jīng)驗,只有在底層服務(wù)器版本以及目錄元數(shù)據(jù)細(xì)節(jié)保持完全相同時,queryid值才能被假定為穩(wěn)定并且可比。兩臺參與到基于物理 WAL 重放的復(fù)制中的服務(wù)器會對相同的查詢給出一樣的queryid值。但是,邏輯復(fù)制模式并不保證在所有相關(guān)細(xì)節(jié)上都保持完全一樣的復(fù)制,因此在邏輯復(fù)制機(jī)之間計算代價時,queryid并非是一個有用的標(biāo)識符。如果有疑問,推薦直接進(jìn)行測試。

代表性查詢文本中用于替換常量的參數(shù)符號從原始查詢文本中最高的 $n參數(shù)之后的下一個數(shù)字開始, 如果沒有則為$1。值得注意的是,在某些情況下, 可能存在影響編號的隱藏參數(shù)符號。例如,PL/pgSQL 使用隱藏參數(shù)符號將函數(shù)局部變量的值插入到查詢中,以便像 SELECT i + 1 INTO jPL/pgSQL 語句將具有像SELECT i + $2這樣的代表性文本。

有代表性的查詢文本被保存在一個外部磁盤文件中,并且不會消耗共享內(nèi)存。 因此,即便是很長的查詢文本也能被成功的存儲下來。不過,如果累積了很多 長的查詢文本,該外部文件也會增長到很大。作為一種恢復(fù)方法,如果這樣的 情況發(fā)生,pg_stat_statements可能會選擇丟棄這些查詢文本, 于是pg_stat_statements視圖中的所有現(xiàn)有項將會顯示空的 query域,不過與每個 queryid相關(guān)聯(lián)的 統(tǒng)計信息會被保留下來。如果發(fā)生這種情況,可以考慮減小 pg_stat_statements.max來防止復(fù)發(fā)。

planscalls并不總是匹配的, 因為計劃和執(zhí)行統(tǒng)計信息在它們各自的結(jié)束階段更新,并且僅適用于成功的操作。 例如,如果一條語句計劃成功但在執(zhí)行階段失敗,則只會更新其計劃統(tǒng)計信息。 如果因為使用了緩存計劃而跳過計劃,則只會更新其執(zhí)行統(tǒng)計信息。

F.29.2. 函數(shù)

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void

pg_stat_statements_reset丟棄到目前為止與指定的userid, dbidqueryid相對應(yīng)的pg_stat_statements收集的統(tǒng)計信息。 如果有任何參數(shù)未被指定,那么將對這些參數(shù)使用默認(rèn)值 0(無效),并且將重置與其他參數(shù)匹配的統(tǒng)計信息。 如果未指定任何參數(shù),或者所有指定的參數(shù)均為0(無效),則它將丟棄所有統(tǒng)計信息。 默認(rèn)情況下,此功能只能由超級用戶執(zhí)行??梢允褂?code class="command">GRANT授予其他人訪問權(quán)限。

pg_stat_statements(showtext boolean) returns setof record

pg_stat_statements視圖按照一個也叫 pg_stat_statements的函數(shù)來定義??蛻舳丝梢灾苯诱{(diào)用 pg_stat_statements函數(shù),并且通過指定 showtext := false來忽略查詢文本(即,對應(yīng)于視圖的 query列的 OUT參數(shù)將返回空值)。 這個特性是為了支持不想重復(fù)接收長度不定的查詢文本的外部工具而設(shè)計的。 這類工具可以轉(zhuǎn)而自行緩存第一個觀察到的查詢文本,因為這就是 pg_stat_statements自己所做的全部工作,并且只在需要的 時候檢索查詢文本。因為服務(wù)器會把查詢文本存儲在一個文件中,這種方法可 以降低重復(fù)檢查pg_stat_statements數(shù)據(jù)的 物理 I/O。

F.29.3. 配置參數(shù)

pg_stat_statements.max (integer)

pg_stat_statements.max是由該模塊跟蹤的語句的最大數(shù)目(即pg_stat_statements視圖中行的最大數(shù)量)。如果觀測到的可區(qū)分的語句超過這個數(shù)量,最少被執(zhí)行的語句的信息將會被丟棄。默認(rèn)值為 5000。這個參數(shù)只能在服務(wù)器啟動時設(shè)置。

pg_stat_statements.track (enum)

pg_stat_statements.track控制哪些語句會被該模塊計數(shù)。指定top可以跟蹤頂層語句(那些直接由客戶端發(fā)出的語句),指定all還可以跟蹤嵌套的語句(例如在函數(shù)中調(diào)用的語句),指定none可以禁用語句統(tǒng)計信息收集。默認(rèn)值是top。 只有超級用戶能夠改變這個設(shè)置。

pg_stat_statements.track_utility (boolean)

pg_stat_statements.track_utility控制該模塊是否會跟蹤工具命令。工具命令是除了SELECT、INSERT、 UPDATEDELETE之外所有的其他命令。默認(rèn)值是on。 只有超級用戶能夠改變這個設(shè)置。

pg_stat_statements.track_planning (boolean)

pg_stat_statements.track_planning控制模塊是否跟蹤計劃操作和持續(xù)時間。 啟用此參數(shù)可能會導(dǎo)致明顯的性能損失,尤其是在許多并發(fā)連接上執(zhí)行較少種類的查詢時。 默認(rèn)值為off。只有超級用戶才能更改此設(shè)置。

pg_stat_statements.save (boolean)

pg_stat_statements.save指定是否在服務(wù)器關(guān)閉之后還保存語句統(tǒng)計信息。如果被設(shè)置為off,那么關(guān)閉后不保存統(tǒng)計信息并且在服務(wù)器啟動時也不會重新載入統(tǒng)計信息。默認(rèn)值為on。這個參數(shù)只能在postgresql.conf文件中或者在服務(wù)器命令行上設(shè)置。

該模塊要求與pg_stat_statements.max成比例的額外共享內(nèi)存。注意只要該模塊被載入就會消耗這么多的內(nèi)存,即便pg_stat_statements.track被設(shè)置為none。

這些參數(shù)必須在postgresql.conf中設(shè)置。典型的用法可能是:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

F.29.4. 示例輸出

bench=# SELECT pg_stat_statements_reset();

$ pgbench -i bench
$ pgbench -c10 -t300 bench

bench=# \x
bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
calls           | 3000
total_exec_time | 25565.855387
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 20756.669379
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------?------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 291.865911
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 4 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 271.232977
rows            | 3000
hit_percent     | 98.8454011741682975
-[ RECORD 5 ]---+--------------------------------------------------?------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 160.588563
rows            | 0
hit_percent     | 100.0000000000000000


bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
            WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';

bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 20756.669379
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------?------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 291.865911
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 271.232977
rows            | 3000
hit_percent     | 98.8454011741682975
-[ RECORD 4 ]---+--------------------------------------------------?------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 160.588563
rows            | 0
hit_percent     | 100.0000000000000000
-[ RECORD 5 ]---+--------------------------------------------------?------------------
query           | vacuum analyze pgbench_accounts
calls           | 1
total_exec_time | 136.448116
rows            | 0
hit_percent     | 99.9201915403032721

bench=# SELECT pg_stat_statements_reset(0,0,0);

bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------?---------------------------
query           | SELECT pg_stat_statements_reset(0,0,0)
calls           | 1
total_exec_time | 0.189497
rows            | 1
hit_percent     | 
-[ RECORD 2 ]---+--------------------------------------------------?---------------------------
query           | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit /          +
                |                nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+
                |           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3
calls           | 0
total_exec_time | 0
rows            | 0
hit_percent     | 

 

以上內(nèi)容是否對您有幫助:
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號
微信公眾號

編程獅公眾號