使用查詢存放區監視效能

適用於:適用於 PostgreSQL 的 Azure 資料庫 - 單一伺服器

重要

適用於 PostgreSQL 的 Azure 資料庫 - 單一伺服器位於淘汰路徑上。 強烈建議您升級至 適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器。 如需移轉至 適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器的詳細資訊,請參閱單一伺服器適用於 PostgreSQL 的 Azure 資料庫發生什麼事

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

重要

請勿修改 azure_sys 資料庫或其結構描述。 這樣做會造成查詢存放區與相關的效能功能無法正確運作。

啟用查詢存放區

查詢存放區是選擇加入的功能,因此預設不會在伺服器上啟用。 存放區是針對指定伺服器上的所有資料庫全域啟用或停用的,並無法個別開啟或關閉資料庫。

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

  1. 登入 Azure 入口網站,然後選取適用於 PostgreSQL 的 Azure 資料庫伺服器。
  2. 在功能表的 [設定] 區段中,選取 [伺服器參數]。
  3. 搜尋 pg_qs.query_capture_mode 參數。
  4. 將值設定為 TOP,然後選取 [儲存]。

若要啟用查詢存放區中的等候統計資料:

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

或者,您可以使用 Azure CLI 設定這些參數。

az postgres server configuration set --name pg_qs.query_capture_mode --resource-group myresourcegroup --server mydemoserver --value TOP
az postgres server configuration set --name pgms_wait_sampling.query_capture_mode --resource-group myresourcegroup --server mydemoserver --value ALL

請等候 20 分鐘,以讓第一批資料保存在 azure_sys 資料庫中。

查詢存放區中的資訊

查詢存放區有兩個存放區:

  • 執行階段統計資料存放區,用於保存查詢執行統計資料資訊。
  • 等候統計資料存放區,用於保存等候統計資料資訊。

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

  • 判斷查詢在指定時間範圍內的執行次數
  • 跨時間範圍比較查詢的平均值行時間,查看大幅差異
  • 識別在過去 X 小時中執行最久的查詢
  • 識別等候資源的前 N 項查詢
  • 了解特定查詢的等候性質

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

存取查詢存放區資訊

查詢存放區資料會儲存在 Postgres 伺服器上的 azure_sys 資料庫中。

下列查詢會傳回查詢存放區中的相關資訊:

SELECT * FROM query_store.qs_view; 

或者,此查詢可取得等候統計資料:

SELECT * FROM query_store.pgms_wait_sampling_view;

尋找等候查詢

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

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

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

設定選項

啟用查詢存放區時,會以每 15 分鐘的彙總時間範圍儲存資料一次,每個範圍內最多可有 500 個相異的查詢。

下列選項可用於設定查詢存放區參數。

參數 說明 預設值 Range
pg_qs.query_capture_mode 設定追蹤哪些陳述式。 none、top、all
pg_qs.max_query_text_length 設定可以儲存的最大查詢長度。 較長的查詢會遭截斷。 6000 100 - 10K
pg_qs.retention_period_in_days 設定保留期限。 7 1 - 30
pg_qs.track_utility 設定是否要追蹤公用程式命令 on on、off

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

參數 說明 預設值 Range
pgms_wait_sampling.query_capture_mode 設定追蹤等候統計資料的哪些陳述式。 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 入口網站Azure CLI,為參數取得或設定不同的值。

檢視和函式

使用下列檢視和函式來檢視和管理查詢存放區。 PostgreSQL 公用角色中的任何人都可以使用這些檢視,查看查詢存放區中的資料。 這些檢視僅適用於 azure_sys 資料庫。

移除常值和常數之後,查看查詢結構,其會呈現標準化。 如果兩個查詢完全相同 (但常值除外),則兩者會有相同的雜湊碼。

query_store.qs_view

此檢視會傳回查詢存放區中的查詢文字資料。 不同的 query_text 都會自成一資料列。 資料無法透過入口網站、API 或 CLI 中的 [智慧型效能] 區段來取得 - 但可以透過連線至 azure_sys 並查詢 'query_store.query_texts_view' 來找到。

名稱 型別 參考 說明
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) 代表性陳述式的文字。 結構相同的不同查詢會群集在一起;此文字就式叢集中第一個查詢的文字。
plan_id BIGINT 對應到此查詢的方案識別碼,尚無法使用
start_time timestamp 依時間貯體彙總的查詢 - 根據預設,貯體的時間範圍為 15 分鐘。 這是和此查詢的時間貯體對應的開始時間。
end_time timestamp 和此查詢的時間貯體對應的結束時間。
calls BIGINT 查詢執行的次數
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,否則為零)

query_store.query_texts_view

此檢視會傳回查詢存放區中的查詢文字資料。 不同的 query_text 都會自成一資料列。

名稱 類型 說明
query_text_id BIGINT query_texts 資料表識別碼
query_sql_text Varchar(10000) 代表性陳述式的文字。 結構相同的不同查詢會群集在一起;此文字就式叢集中第一個查詢的文字。

query_store.pgms_wait_sampling_view

此檢視會傳回查詢存放區中的查詢文字資料。 不同的 query_text 都會自成一資料列。 資料無法透過入口網站、API 或 CLI 中的 [智慧型效能] 區段來取得 - 但可以透過連線至 azure_sys 並查詢 'query_store.query_texts_view' 來找到。

名稱 型別 參考 說明
user_id oid pg_authid.oid 執行陳述式的使用者物件識別 (OID)
db_id oid pg_database.oid 在其中執行陳述式的資料庫物件識別 (OID)
query_id BIGINT 從陳述式的剖析樹狀結構計算的內部雜湊碼
event_type text 後端等候中事件的類型
event text 如果後端目前正在等候,為該等候事件的名稱
calls 整數 擷取到相同事件的次數

函式

Query_store.qs_reset() 傳回 void

qs_reset 捨棄查詢存放區至今收集到的所有統計資料。 只有伺服器管理員角色可以執行此函式。

Query_store.staging_data_reset() 傳回 void

staging_data_reset 捨棄查詢存放區在記憶體中收集的統計資料 (亦即,記憶體中的資料尚未排清至資料庫)。 只有伺服器管理員角色可以執行此函式。

Azure 監視器

適用於 PostgreSQL 的 Azure 資料庫會與 Azure 監視器診斷設定整合。 診斷設定可讓您以 JSON 格式將 Postgres 記錄傳送至 Azure 監視器記錄,以進行分析和警示、傳送至事件中樞以進行串流,以及傳送至 Azure 儲存體以進行封存。

重要

此診斷功能僅適用於一般用途和記憶體最佳化定價層。

設定診斷設定

您可以使用 Azure 入口網站、CLI、REST API 和 PowerShell,來啟用 Postgres 伺服器的診斷設定。 要設定的記錄類別是 QueryStoreRuntimeStatisticsQueryStoreWaitStatistics

若要使用 Azure 入口網站啟用資源記錄:

  1. 在入口網站中,移至 Postgres 伺服器導覽功能表中的 [診斷設定]。
  2. 選取 [新增診斷設定]。
  3. 命名此設定。
  4. 選取您慣用的端點 (儲存體帳戶、事件中樞、記錄分析)。
  5. 選取記錄類型 QueryStoreRuntimeStatisticsQueryStoreWaitStatistics
  6. 儲存您的設定。

若要使用 PowerShell、CLI 或 REST API 啟用此設定,請造訪診斷設定文章

JSON 記錄格式

下列表格描述兩種記錄類型的欄位。 視您選擇的輸出端點而定,所含欄位及其出現順序可能會有所不同。

QueryStoreRuntimeStatistics

欄位 說明
TimeGenerated [UTC] 以 UTC 記錄記錄時的時間戳記
ResourceId Postgres 伺服器的 Azure 資源 URI
類別 QueryStoreRuntimeStatistics
OperationName QueryStoreRuntimeStatisticsEvent
LogicalServerName_s Postgres 伺服器名稱
runtime_stats_entry_id_s 來自 runtime_stats_entries 資料表的識別碼
user_id_s 執行陳述式的使用者物件識別 (OID)
db_id_s 在其中執行陳述式的資料庫物件識別 (OID)
query_id_s 從陳述式的剖析樹狀結構計算的內部雜湊碼
end_time_s 和此查詢的時間貯體對應的結束時間
calls_s 查詢執行的次數
total_time_s 查詢總執行時間 (以毫秒為單位)
min_time_s 查詢最短執行時間 (以毫秒為單位)
max_time_s 查詢最長執行時間 (以毫秒為單位)
mean_time_s 查詢平均執行時間 (以毫秒為單位)
ResourceGroup 資源群組
SubscriptionId 您的訂用帳戶識別碼
ResourceProvider Microsoft.DBForPostgreSQL
資源 Postgres 伺服器名稱
ResourceType Servers

QueryStoreWaitStatistics

欄位 說明
TimeGenerated [UTC] 以 UTC 記錄記錄時的時間戳記
ResourceId Postgres 伺服器的 Azure 資源 URI
類別 QueryStoreWaitStatistics
OperationName QueryStoreWaitEvent
user_id_s 執行陳述式的使用者物件識別 (OID)
db_id_s 在其中執行陳述式的資料庫物件識別 (OID)
query_id_s 查詢的內部雜湊碼
calls_s 擷取到相同事件的次數
event_type_s 後端等候中事件的類型
event_s 如果後端目前正在等候,則為等候事件名稱
start_time_t 事件開始時間
end_time_s 事件結束時間
LogicalServerName_s Postgres 伺服器名稱
ResourceGroup 資源群組
SubscriptionId 您的訂用帳戶識別碼
ResourceProvider Microsoft.DBForPostgreSQL
資源 Postgres 伺服器名稱
ResourceType Servers

限制與已知問題

  • 如果 PostgreSQL 伺服器開啟 default_transaction_read_only 參數,查詢存放區會無法擷取資料。
  • 如果遇到長時間的 Unicode 查詢 (> = 6000 個位元組),查詢存放區功能可能會中斷。
  • 讀取複本會從主要伺服器複寫查詢存放區資料。 這表示,讀取複本的查詢存放區不會提供讀取複本上執行的查詢統計資料。

下一步