共用方式為

使用查詢存放區監視效能 (部分機器翻譯)

  • 發行項

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

適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的 [查詢存放區] 功能提供方法來追蹤一段時間的查詢效能。 查詢存放區可協助您快速找到執行時間最長又最耗資源的查詢，簡化效能疑難排解。 查詢存放區會自動擷取查詢的歷程記錄和執行階段統計資料，並予以保留以供您檢閱。 它會依時間切分資料，以便您查看時態性使用模式。 所有使用者、資料庫和查詢的資料都會儲存在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體中名為 azure_sys 的資料庫。

重要

請勿修改 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 項等候資源的查詢。
  • 瞭解特定查詢的等候本質。

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

存取查詢存放區資訊

查詢存放區資料會儲存在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體的 azure_sys 資料庫中。 下列查詢會傳回查詢存放區中的相關資訊：

SELECT * FROM  query_store.qs_view;

或者，此查詢可取得等候統計資料：

SELECT * FROM  query_store.pgms_wait_sampling_view;

尋找等候查詢

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

以下是如何查詢存放區中的等候統計資料，以更多深入了解工作負載的一些範例：

觀測 動作
高鎖定等候數 查看受影響查詢的查詢文字，並找出目標實體。 查看查詢存放區，針對經常執行和/或持續時間很長的實體，尋找修改同一實體的其他查詢。 找出這些查詢之後，請考慮變更應用程式邏輯，改善並行存取，或使用限制較少的隔離等級。
高緩衝區 IO 等候數 在查詢存放區中尋找實體讀取次數高的查詢。 如果與高 IO 等候數的查詢相符，請考慮對基礎實體引進索引，以執行搜尋，而不是掃描。 這可將查詢的 IO 額外負荷降到最低。 請在入口網站檢查伺服器的效能建議，以查看是否有此伺服器的索引建議，可供將查詢最佳化。
高記憶體等候數 找出查詢存放區中記憶體耗用量名列前茅的查詢。 這些查詢可能會進一步延遲受影響查詢的進度。 請在入口網站檢查伺服器的效能建議，以查看是否有索引建議，可供將這些查詢最佳化。

設定選項

啟用查詢存放區時，其會以 pg_qs.interval_length_minutes 伺服器參數所決定的彙總時間範圍長度 (預設為 15 分鐘) 來儲存資料。 針對每個時間範圍，其會每個時間範圍都儲存 500 個相異查詢。 下列選項適用於設定查詢存放區參數：

參數 說明 Default 範圍
pg_qs.query_capture_mode 設定追蹤哪些陳述式。 none none、top、all
pg_qs.interval_length_minutes (*) 設定 pg_qs 的 query_store 擷取間隔 (以分鐘為單位) - 這是資料持續性的頻率。 15 1 - 30
pg_qs.store_query_plans (部分機器翻譯) 針對 pg_qs，開啟或關閉儲存查詢計畫。 關閉 on、off
pg_qs.max_plan_size (部分機器翻譯) 設定要為 pg_qs 的查詢計劃文字儲存的位元組數上限；較長的計劃將會截斷。 7500 100 - 10k
pg_qs.max_query_text_length (部分機器翻譯) 設定可儲存的查詢文字長度上限；將會截斷過久的查詢。 6000 100 - 10K
pg_qs.retention_period_in_days 設定 pg_qs 的保留期間長度 (天數) - 在此時間之後，將會刪除資料。 7 1 - 30
pg_qs.track_utility 設定 pg_qs 是否追蹤公用程式命令。 on on、off

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

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

參數 說明 Default 範圍
pgms_wait_sampling.query_capture_mode 選取 pgms_wait_sampling 延伸模組所追蹤的陳述式。 none none、all
Pgms_wait_sampling.history_period 設定針對等候事件進行取樣的頻率 (以毫秒為單位)。 100 1-600000

注意

pg_qs.query_capture_mode 已取代 pgms_wait_sampling.query_capture_mode。 若 pg_qs.query_capture_mode 是 NONE，則 pgms_wait_sampling.query_capture_mode 設定沒有影響。

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

檢視和函式

使用下列檢視和函式來檢視和管理查詢存放區。 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，而是透過 columnOne 評估的 abs 函數 (abs(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 對應至此項目時間範圍的結束時間。
通話 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 的角色 (azuresu) 是否已執行查詢，而此角色具有超級使用者權限，並且用來執行控制窗格作業。 因為此服務是受控 PaaS 服務，所以只有 Microsoft 是該超級使用者角色的一部分。
query_type text 查詢所代表的作業類型。 可能的值為 unknown、select、update、insert、delete、merge、utility、nothing、undefined。

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 已執行陳述式的使用者物件識別 (OID)。
db_id oid pg_database.oid 在其中執行陳述式的資料庫物件識別 (OID)。
query_id bigint 從陳述式的剖析樹狀結構計算的內部雜湊碼。
event_type text 後端正在等候的事件類型。
event text 如果後端目前正在等候，則是等候事件名稱。
通話 整數 已擷取相同事件的次數。

注意

如需 query_store.pgms_wait_sampling_view 檢視的 event_type 和 event 資料行中的可能值清單，請參閱 pg_stat_activity 的官方文件，並尋找參考同名資料行的資訊。

query_store.query_plans_view

此檢視會傳回用來執行查詢的查詢計劃。 每個相異資料庫識別碼和查詢識別碼都會各有一個資料列。 這只會儲存非公用程式查詢的查詢計畫。

plan_id db_id query_id plan_text
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) 才能執行此函數。

限制和已知問題

Azure 儲存體和查詢存放區相容性

因為相容性問題，所以您無法同時啟用 Azure 儲存體和查詢存放區延伸模組。 若要確保正常運作並避免潛在的衝突，請一次只啟用其中一個延伸模組。

若要使用 Azure 儲存體：

  • 將 pg_qs.query_capture_mode 參數設定為 NONE，以停用查詢存放區。 這是動態參數，因此您不需要重新啟動。

若要使用查詢存放區：

  1. 發出 DROP EXTENSION azure_storage;，以停用 Azure 儲存體延伸模組。
  2. 從 shared_preload_libraries 移除 Azure 儲存體。
  3. 重新啟動資料庫伺服器。

這些步驟是防止衝突並確保系統正常運作的必要步驟。 我們正努力解決這些相容性問題，並讓您隨時掌握任何更新。

唯讀模式

「適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器」執行個體處於唯讀模式時 (例如，default_transaction_read_only 參數設定為 on 時) 或唯讀模式因達到儲存體容量而自動啟用時，查詢存放區不會擷取任何資料。

相關內容