共用方式為

使用查詢存放區監視效能

  • 發行項

適用於： 適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

查詢存放區是 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器中的一項功能，可提供一段時間追蹤查詢效能的方式。 查詢存放區可協助您快速找出執行時間最長且耗用最多資源的查詢，來簡化效能問題的疑難解答。 查詢存放區會自動擷取查詢和運行時間統計數據的歷程記錄，並保留它們以供檢閱。 它會依時間切分資料，以便您查看時態性使用模式。 所有使用者、資料庫和查詢的數據都會儲存在 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器實例中名為 azure_sys 的資料庫。

啟用查詢存放區

查詢存放區不需額外費用即可使用。 這是加入功能，因此預設不會在伺服器上啟用此功能。 查詢存放區可以針對給定伺服器上的所有資料庫全域予以啟用或停用，但無法根據每個資料庫予以開啟或關閉。

重要

請勿在高載定價層上啟用查詢存放區，因為這會造成效能影響。

在 Azure 入口網站 中啟用查詢存放區

  1. 登入 Azure 入口網站，然後選取適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體。
  2. 在功能表的 [設定] 區段中，選取 [伺服器參數]。
  3. 搜尋 pg_qs.query_capture_mode 參數。
  4. 將值設定為 top 或 all，視您是否要追蹤最上層查詢或巢狀查詢而定，或是在函式或程式內執行的值，然後選取 [ 儲存]。 允許最多 20 分鐘，讓第一批數據保存在 azure_sys 資料庫中。

啟用查詢存放區等候取樣

  1. 搜尋 pgms_wait_sampling.query_capture_mode 參數。
  2. 將值設定為 all，然後選取 [儲存]。

查詢存放區中的資訊

查詢存放區包含兩個存放區：

  1. 執行階段統計資料存放區，用於保存查詢執行統計資料資訊。
  2. 等候統計資料存放區，用於保存等候統計資料資訊。

使用查詢存放區的常見案例包括：

  • 判斷查詢在給定時間範圍內的執行次數。
  • 比較跨時間範圍查詢的平均運行時間，以查看大型變化。
  • 識別過去幾小時中執行最久的查詢。
  • 識別前 N 項等候資源的查詢。
  • 瞭解等候特定查詢的本質。

為了讓空間使用量降到最低，會經過一段固定且可設定的時間範圍，才彙總執行階段統計資料存放區的執行階段執行統計資料。 這些存放區中的資訊可以使用檢視來查詢。

存取查詢存放區資訊

查詢存放區數據會儲存在 azure_sys 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器實例上的資料庫中。 下列查詢會傳回查詢存放區中記錄之查詢的相關信息：

SELECT * FROM  query_store.qs_view;

此查詢會傳回等候統計資料的相關信息：

SELECT * FROM  query_store.pgms_wait_sampling_view;

尋找等候查詢

等候事件類型會依相似性，將不同的等候事件結合成貯體。 查詢存放區會提供等候事件類型、特定的等候事件名稱，以及有問題的查詢。 能夠將此等候資訊與查詢執行階段相互關聯，表示您可以更深入了解查詢效能特性從何而來。

以下是如何使用查詢存放區中的等候統計數據，深入瞭解工作負載的一些範例：

觀測 動作
高鎖定等候 查看受影響查詢的查詢文字，並找出目標實體。 查看查詢存放區中是否有經常執行且/或持續時間較高的其他查詢，並正在修改相同的實體。 找出這些查詢之後，請考慮變更應用程式邏輯，改善並行存取，或使用限制較少的隔離等級。
高緩衝區 IO 等候 尋找查詢存放區中具有大量實體讀取的查詢。 如果它們符合高 IO 等候的查詢，請考慮啟用 自動化索引微調 功能，以查看它是否建議建立一些索引，這可能會減少這些查詢的實體讀取數目。
高記憶體等候 尋找查詢存放區中耗用查詢的最上層記憶體。 這些查詢可能會進一步延遲受影響查詢的進度。

設定選項

啟用查詢存放區時，它會將數據儲存在由 pg_qs.interval_length_minutes 伺服器參數決定的匯總時段（預設值為 15 分鐘）。 針對每個視窗，每個視窗最多會儲存500個不同的查詢。 區分每個查詢唯一性的屬性user_id（執行查詢的使用者標識碼）、db_id（查詢執行內容的資料庫標識符），以及query_id（可唯一識別所執行查詢的整數值）。 如果在設定的間隔期間，相異查詢的數目達到500個，則會解除分配所記錄的5%查詢，以騰出更多空間。 第一個解除分配的，就是執行次數最少的次數。

下列選項適用於設定查詢存放區參數：

參數 說明 Default 範圍
pg_qs.interval_length_minutes (*) 查詢存放區的擷取間隔以分鐘為單位。 定義數據持續性的頻率。 15 1 - 30
pg_qs.is_enabled_fs 僅限內部使用：此參數會當做功能覆寫參數使用。 如果顯示為關閉，則會停用查詢存放區，儘管已設定的值。pg_qs.query_capture_mode on on, off
pg_qs.max_plan_size 查詢存放區從查詢計劃文字儲存的最大位元組數目;較長的計劃會被截斷。 7500 100 - 10000
pg_qs.max_query_text_length 可儲存的查詢長度上限;較長的查詢會被截斷。 6000 100 - 10000
pg_qs.parameters_capture_mode 是否和何時擷取查詢位置參數。 capture_parameterless_only capture_parameterless_only, capture_first_sample
pg_qs.query_capture_mode 要追蹤的語句。 none none、 、 topall
pg_qs.retention_period_in_days 查詢存放區的保留期間時段為天數。 較舊的數據會自動刪除。 7 1 - 30
pg_qs.store_query_plans 查詢計劃是否應該儲存在查詢存放區中。 off on, off
pg_qs.track_utility 查詢存放區是否必須追蹤公用程式命令。 on on, off

(*) 靜態伺服器參數，其需要重新啟動伺服器，其值的變更才會生效。

下列選項特別適用於等候統計資料：

參數 說明 Default 範圍
pgms_wait_sampling.history_period 頻率，以毫秒為單位，用來取樣等候事件。 100 1 - 600000
pgms_wait_sampling.is_enabled_fs 僅限內部使用：此參數會當做功能覆寫參數使用。 如果顯示為 off，則會停用等候取樣，儘管已設定的值 pgms_wait_sampling.query_capture_mode。 on on, off
pgms_wait_sampling.query_capture_mode 延伸模組 pgms_wait_sampling 必須追蹤的語句。 none none, all

注意

pg_qs.query_capture_modepgms_wait_sampling.query_capture_mode取代 。 如果 pg_qs.query_capture_mode 為 none，則 pgms_wait_sampling.query_capture_mode 設定沒有作用。

使用 Azure 入口網站為參數取得或設定不同的值。

檢視和函式

您可以查詢存放區所記錄的資訊，或使用資料庫架構azure_sys中query_store提供的一些檢視和函式加以刪除。 PostgreSQL 公用角色中的任何人都可以使用這些檢視來查看查詢存放區中的數據。 這些檢視僅適用於 azure_sys 資料庫。

查詢的正規化方式是查看其結構，並忽略任何語意不重要的項目，例如常值、常數、別名或大小寫差異。

如果兩個查詢的語意相同，則即使它們針對相同的參考資料行和資料表使用不同的別名，還是會以相同的 query_id 予以識別。 如果兩個查詢只有使用的常值不同，則也會以相同的 query_id 予以識別。 針對以相同query_id識別的查詢，其sql_query_text是查詢存放區開始錄製活動後先執行的查詢，或自上次捨棄保存的數據后，因為 函式已執行 query_store.qs_reset 。

查詢正規化的運作方式

以下是嘗試說明此正規化運作方式的一些範例：

假設您使用下列陳述式來建立資料表：

create table tableOne (columnOne int, columnTwo int);

您會啟用查詢存放區資料收集，而且單一或多個使用者會依此確切順序來執行下列查詢：

select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";

所有先前的查詢都會共用相同的 query_id。 而且，查詢存放區所保留的文字就是啟用資料收集之後第一個所執行查詢的文字。 因此，其會是 select * from tableOne;。

下列一組查詢在正規化之後就會與先前的一組查詢不符，因為 WHERE 子句會使其語意不同：

select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;

不過，這個最後一組中的所有查詢都會共用相同的 query_id，而且用來識別這些查詢的文字全都是批次 select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1; 中第一個查詢的文字。

最後，在以下尋找一些查詢不符合上一個批次中這些查詢的query_id，以及它們不相符的原因：

查詢：

select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;

不相符的原因：數據行清單是指相同的兩個數據行（columnOne 和 ColumnTwo），但參考的順序會反轉，從 columnOne, ColumnTwo 上一個批次 ColumnTwo, columnOne 到此查詢中的順序。

查詢：

select * from tableOne where columnTwo = 25 and columnOne = 25;

不相符的原因：WHERE 子句中所評估運算式的參照順序會從上一個批次中的 columnOne = ? and ColumnTwo = ? 反轉為此查詢中的 ColumnTwo = ? and columnOne = ?。

查詢：

select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;

不相符的原因：數據行清單中的第一個表達式已不再columnOne，而是abs透過 （abs(columnOne)） 評估函columnOne式，這在語意上不相等。

查詢：

select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;

不相符的原因：WHERE 子句中的第一個表達式不會再評估與常值相等 columnOne ，但函式的結果 ceiling 在常值上評估，這在語意上不相等。

檢視

query_store.qs_view

此檢視會傳回保存於查詢存放區支持資料表中的所有數據。 目前使用中時間範圍仍在記錄記憶體中的數據，直到時間範圍結束，而且其記憶體內部揮發性數據會收集並保存至儲存在磁碟上的數據表為止。 此檢視會針對每個相異資料庫 (db_id)、使用者 (user_id) 和查詢 (query_id) 傳回不同的資料列。

名稱 類型 參考 說明
runtime_stats_entry_id bigint 來自 runtime_stats_entries 資料表的識別碼。
user_id oid pg_authid.oid 已執行陳述式的使用者物件識別 (OID)。
db_id oid pg_database.oid 在其中執行陳述式的資料庫物件識別 (OID)。
query_id bigint 從陳述式的剖析樹狀結構計算的內部雜湊碼。
query_sql_text varchar(10000) 代表性陳述式的文字。 結構相同的不同查詢會群集在一起；此文字就式叢集中第一個查詢的文字。 查詢文字長度上限的預設值為 6000，而且可以使用查詢存放區參數 pg_qs.max_query_text_length 進行修改。 如果查詢的文字超過這個最大值，則會截斷為第一 pg_qs.max_query_text_length 個字元。
plan_id bigint 對應至此查詢的計畫識別碼。
start_time timestamp 查詢會依時間範圍匯總。 伺服器參數 pg_qs.interval_length_minutes 會定義這些視窗的時間範圍（預設值為15分鐘）。 此資料行會對應至記錄此專案之視窗的開始時間。
end_time timestamp 對應至此項目時間範圍的結束時間。
calls bigint 此時間範圍中的查詢執行次數。 請注意，針對平行查詢，每個執行的呼叫數目會對應至 1，用於驅動查詢執行的後端進程，以及每個後端背景工作進程啟動的許多其他單位，以執行執行樹狀結構的平行分支。
total_time 雙精度 查詢總執行時間 (以毫秒為單位)。
min_time 雙精度 查詢最短執行時間 (以毫秒為單位)。
max_time 雙精度 查詢最長執行時間 (以毫秒為單位)。
mean_time 雙精度 查詢平均執行時間 (以毫秒為單位)。
stddev_time 雙精度 查詢執行時間標準差 (以毫秒為單位)。
rows bigint 陳述式所擷取或影響的資料列總數。 請注意，對於平行查詢，每個執行的數據列數目會對應至驅動查詢執行之後端進程傳回給客戶端的數據列數目，加上每個後端背景工作進程啟動以共同作業執行執行樹狀結構的平行分支的所有數據列總和，會傳回驅動查詢執行的後端進程。
shared_blks_hit bigint 陳述式的共用區塊快取點擊總數。
shared_blks_read bigint 陳述式所讀取的共用區塊總數。
shared_blks_dirtied bigint 陳述式所變動的共用區塊總數。
shared_blks_written bigint 陳述式所寫入的共用區塊總數。
local_blks_hit bigint 陳述式的本機區塊快取點擊總數。
local_blks_read bigint 陳述式所讀取的本機區塊總數。
local_blks_dirtied bigint 陳述式所變動的本機區塊總數。
local_blks_written bigint 陳述式所寫入的本機區塊總數。
temp_blks_read bigint 陳述式所讀取的暫存區塊總數。
temp_blks_written bigint 陳述式所寫入的暫存區塊總數。
blk_read_time 雙精度 陳述式讀取區塊所花費的總時間 (以毫秒為單位) (如果啟用 track_io_timing，否則為零)。
blk_write_time 雙精度 陳述式寫入區塊所花費的總時間 (以毫秒為單位) (如果啟用 track_io_timing，否則為零)。
is_system_query boolean 判斷具有 user_id = 10 的角色是否執行查詢。 該使用者具有超級用戶許可權，並用來執行控制平面作業。 因為此服務是受控 PaaS 服務，所以只有 Microsoft 是該超級使用者角色的一部分。
query_type text 查詢所代表的作業類型。 可能的值為 unknown、select、update、insert、delete、merge、utility、nothing、undefined。
search_path text search_path擷取查詢時所設定的值。
query_parameters text JSON 物件的文字表示，其值會傳遞至參數化查詢的位置參數。 此數據行只會在兩種情況下填入其值：1） 表示非參數化查詢。 2） 針對參數化查詢，當 pg_qs.parameters_capture_mode 設為 capture_first_sample時，如果查詢存放區可以在運行時間擷取查詢參數的值。
parameters_capture_status text 查詢所代表的作業類型。 可能的值為 succeeded （查詢未參數化，或是已成功擷取參數化查詢和值）、（查詢已參數化，但未擷取參數，因為pg_qs.parameters_capture_mode已設定為 capture_parameterless_only）， disabled too_long_to_capture （查詢已參數化，但參數未擷取，因為產生的 JSON 長度會在此檢視的數據行中query_parameters浮出水面，因此會被視為長時間保存查詢存放區），因此不會擷取參數。 too_many_to_capture （查詢已參數化，但未擷取參數，因為參數總數，被視為過度保存查詢存放區）， serialization_failed （查詢已參數化，但至少其中一個作為參數傳遞的值無法串行化為文字）。

query_store.query_texts_view

此檢視會傳回查詢存放區中的查詢文字資料。 每個相異 query_sql_text 都有一個資料列。

名稱 類型 說明
query_text_id bigint query_texts 資料表識別碼
query_sql_text varchar(10000) 代表性陳述式的文字。 結構相同的不同查詢會群集在一起；此文字就式叢集中第一個查詢的文字。
query_type smallint 查詢所代表的作業類型。 在 PostgreSQL <= 14 的版本中，可能的值為 0 (未知)、1 (選取)、2 (更新)、3 (插入)、4 (刪除)、5 (公用程式)、6 (無)。 在 PostgreSQL >= 15 的版本中，可能的值為 0 (未知)、1 (選取)、2 (更新)、3 (插入)、4 (刪除)、5 (合併)、6 (公用程式)、7 (無)。

query_store.pgms_wait_sampling_view

此檢視會傳回查詢存放區中的等候事件資料。 此檢視會針對每個相異資料庫 (db_id)、使用者 (user_id)、查詢 (query_id) 和事件 (event) 傳回不同的資料列。

名稱 類型 參考 說明
start_time timestamp 查詢會依時間範圍匯總。 伺服器參數 pg_qs.interval_length_minutes 會定義這些視窗的時間範圍（預設值為15分鐘）。 此資料行會對應至記錄此專案之視窗的開始時間。
end_time timestamp 對應至此項目時間範圍的結束時間。
user_id oid pg_authid.oid 執行語句之用戶的物件識別碼。
db_id oid pg_database.oid 執行語句之資料庫的物件標識碼。
query_id bigint 從陳述式的剖析樹狀結構計算的內部雜湊碼。
event_type text 後端正在等候的事件類型。
event text 如果後端目前正在等候，則是等候事件名稱。
calls 整數 擷取相同事件的次數。

注意

如需檢視和event_type event query_store.pgms_wait_sampling_view數據行中可能值的清單，請參閱pg_stat_activity的官方檔，並尋找參考相同名稱之數據行的資訊。

query_store.query_plans_view

此檢視會傳回用來執行查詢的查詢計劃。 每個相異資料庫識別碼和查詢識別碼都會各有一個資料列。 查詢存放區只會記錄非使用量查詢的查詢計劃。

名稱 類型 參考 說明
plan_id bigint 來自 EXPLAIN 所產生正規化查詢計畫的雜湊值。 其格式為正規化，因為它會排除計劃節點的估計成本，以及緩衝區的使用方式。
db_id oid pg_database.oid 在其中執行陳述式的資料庫物件識別 (OID)。
query_id bigint 從陳述式的剖析樹狀結構計算的內部雜湊碼。
plan_text varchar(10000) 陳述式在 costs=false、buffers=false 且 format=text 時的執行計畫。 與 EXPLAIN 所產生的輸出完全相同。

函式

query_store.qs_reset

此函式會捨棄查詢存放區到目前為止收集的所有統計數據。 它會捨棄已關閉時間時段的統計數據，這些統計數據已保存至磁碟上的數據表。 它也會捨棄目前時間範圍的統計數據，該時間範圍只存在於記憶體中。 只有伺服器管理員角色的成員 （azure_pg_admin） 才能執行此函式。

query_store.staging_data_reset

此函式會捨棄查詢存放區收集記憶體中收集的所有統計數據（也就是說，記憶體中的數據尚未排清到磁碟數據表上，以支持查詢存放區所收集數據的持續性）。 只有伺服器管理員角色的成員 （azure_pg_admin） 才能執行此函式。

唯讀模式

當 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器的實例處於只讀模式時，例如default_transaction_read_only當 參數設定on為 時，或因為達到儲存容量而自動啟用只讀模式時，查詢存放區不會擷取任何數據。

在具有 讀取複本的伺服器上啟用查詢存放區，不會自動在任何讀取複本上啟用查詢存放區。 即使您在任何讀取複本上啟用它，查詢存放區也不會記錄在任何讀取複本上執行的查詢，因為它們會在只讀模式中運作，直到您將查詢升階為主要複本為止。

相關內容