使用查詢存放區監視效能Monitor performance with the Query Store

適用範圍: Azure Database for PostgreSQL-9.6] 和 [10 的單一伺服器Applies to: Azure Database for PostgreSQL - Single Server 9.6 and 10

適用於 PostgreSQL 的 Azure 資料庫查詢存放區功能提供的方法可追蹤一段時間的查詢效能。The Query Store feature in Azure Database for PostgreSQL provides a way to track query performance over time. 查詢存放區可協助您快速找到執行時間最長又最耗資源的查詢,簡化效能疑難排解。Query Store simplifies performance troubleshooting by helping you quickly find the longest running and most resource-intensive queries. 查詢存放區會自動擷取查詢的歷程記錄和執行階段統計資料,並予以保留以供您檢閱。Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. 依時間範圍區分資料,以便查看資料庫使用模式。It separates data by time windows so that you can see database usage patterns. 所有使用者、資料庫及查詢的資料都會儲存在適用於 PostgreSQL 的 Azure 資料庫執行個體中名為 azure_sys 的資料庫。Data for all users, databases, and queries is stored in a database named azure_sys in the Azure Database for PostgreSQL instance.

重要

請勿修改 azure_sys 資料庫或其結構描述。Do not modify the azure_sys database or its schemas. 這樣做會造成查詢存放區與相關的效能功能無法正確運作。Doing so will prevent Query Store and related performance features from functioning correctly.

啟用查詢存放區Enabling Query Store

查詢存放區是選擇加入的功能,因此預設不會在伺服器上啟用。Query Store is an opt-in feature, so it isn't active by default on a server. 存放區是針對指定伺服器上的所有資料庫全域啟用或停用的,並無法個別開啟或關閉資料庫。The store is enabled or disabled globally for all the databases on a given server and cannot be turned on or off per database.

使用 Azure 入口網站啟用查詢存放區Enable Query Store using the Azure portal

  1. 登入 Azure 入口網站,然後選取適用於 PostgreSQL 的 Azure 資料庫伺服器。Sign in to the Azure portal and select your Azure Database for PostgreSQL server.
  2. 在功能表的 [設定] 區段中,選取 [伺服器參數] 。Select Server Parameters in the Settings section of the menu.
  3. 搜尋 pg_qs.query_capture_mode 參數。Search for the pg_qs.query_capture_mode parameter.
  4. 將值設為TOP儲存Set the value to TOP and Save.

若要啟用查詢存放區中的等候統計資料:To enable wait statistics in your Query Store:

  1. 搜尋 pgms_wait_sampling.query_capture_mode 參數。Search for the pgms_wait_sampling.query_capture_mode parameter.
  2. 將值設為ALL儲存Set the value to ALL and Save.

或者,您可以設定這些參數,使用 Azure CLI。Alternatively you can set these parameters using the 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 資料庫中。Allow up to 20 minutes for the first batch of data to persist in the azure_sys database.

查詢存放區中的資訊Information in Query Store

查詢存放區有兩個存放區:Query Store has two stores:

  • 執行階段統計資料存放區,用於保存查詢執行統計資料資訊。A runtime stats store for persisting the query execution statistics information.
  • 等候統計資料存放區,用於保存等候統計資料資訊。A wait stats store for persisting wait statistics information.

使用查詢存放區的常見案例包括:Common scenarios for using Query Store include:

  • 判斷查詢在指定時間範圍內的執行次數Determining the number of times a query was executed in a given time window
  • 跨時間範圍比較查詢的平均值行時間,查看大幅差異Comparing the average execution time of a query across time windows to see large deltas
  • 識別在過去 X 小時中執行最久的查詢Identifying longest running queries in the past X hours
  • 識別等候資源的前 N 項查詢Identifying top N queries that are waiting on resources
  • 了解特定查詢的等候性質Understanding wait nature for a particular query

為了讓空間使用量降到最低,會經過一段固定且可設定的時間範圍,才彙總執行階段統計資料存放區的執行階段執行統計資料。To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed, configurable time window. 查詢這些查詢存放區檢視,即可看到這些存放區中的資訊。The information in these stores is visible by querying the query store views.

下列查詢會傳回查詢存放區中的相關資訊:The following query returns information about queries in Query Store:

SELECT * FROM query_store.qs_view; 

或者,此查詢可取得等候統計資料:Or this query for wait stats:

SELECT * FROM query_store.pgms_wait_sampling_view;

尋找等候查詢Finding wait queries

等候事件類型會依相似性,將不同的等候事件結合成貯體。Wait event types combine different wait events into buckets by similarity. 查詢存放區提供等候事件類型、特定的等候事件名稱,以及有問題的查詢。Query Store provides the wait event type, specific wait event name, and the query in question. 能夠將此等候資訊與查詢執行階段相互關聯,表示您可以更深入了解查詢效能特性從何而來。Being able to correlate this wait information with the query runtime statistics means you can gain a deeper understanding of what contributes to query performance characteristics.

以下是如何查詢存放區中的等候統計資料,以更多深入了解工作負載的一些範例:Here are some examples of how you can gain more insights into your workload using the wait statistics in Query Store:

觀測Observation 動作Action
高鎖定等候數High Lock waits 查看受影響查詢的查詢文字,並找出目標實體。Check the query texts for the affected queries and identify the target entities. 查看查詢存放區,針對經常執行和/或持續時間很長的實體,尋找修改同一實體的其他查詢。Look in Query Store for other queries modifying the same entity, which is executed frequently and/or have high duration. 找出這些查詢之後,請考慮變更應用程式邏輯,改善並行存取,或使用限制較少的隔離等級。After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
高緩衝區 IO 等候數High Buffer IO waits 在查詢存放區中尋找實體讀取次數高的查詢。Find the queries with a high number of physical reads in Query Store. 如果與高 IO 等候數的查詢相符,請考慮對基礎實體引進索引,以執行搜尋,而不是掃描。If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans. 這可將查詢的 IO 額外負荷降到最低。This would minimize the IO overhead of the queries. 請在入口網站檢查伺服器的效能建議,以查看是否有此伺服器的索引建議,可供將查詢最佳化。Check the Performance Recommendations for your server in the portal to see if there are index recommendations for this server that would optimize the queries.
高記憶體等候數High Memory waits 找出查詢存放區中記憶體耗用量名列前茅的查詢。Find the top memory consuming queries in Query Store. 這些查詢可能會進一步延遲受影響查詢的進度。These queries are probably delaying further progress of the affected queries. 請在入口網站檢查伺服器的效能建議,以查看是否有索引建議,可供將這些查詢最佳化。Check the Performance Recommendations for your server in the portal to see if there are index recommendations that would optimize these queries.

組態選項Configuration options

啟用查詢存放區時,會以每 15 分鐘的彙總時間範圍儲存資料一次,每個範圍內最多可有 500 個相異的查詢。When Query Store is enabled it saves data in 15-minute aggregation windows, up to 500 distinct queries per window.

下列選項可用於設定查詢存放區參數。The following options are available for configuring Query Store parameters.

參數Parameter 描述Description 預設值Default RangeRange
pg_qs.query_capture_modepg_qs.query_capture_mode 設定追蹤哪些陳述式。Sets which statements are tracked. Nonenone none、top、allnone, top, all
pg_qs.max_query_text_lengthpg_qs.max_query_text_length 設定可以儲存的最大查詢長度。Sets the maximum query length that can be saved. 較長的查詢會遭截斷。Longer queries will be truncated. 60006000 100 - 10K100 - 10K
pg_qs.retention_period_in_dayspg_qs.retention_period_in_days 設定保留期限。Sets the retention period. 77 1 - 301 - 30
pg_qs.track_utilitypg_qs.track_utility 設定是否要追蹤公用程式命令Sets whether utility commands are tracked onon on、offon, off

下列選項特別適用於等候統計資料。The following options apply specifically to wait statistics.

參數Parameter 描述Description 預設值Default RangeRange
pgms_wait_sampling.query_capture_modepgms_wait_sampling.query_capture_mode 設定追蹤等候統計資料的哪些陳述式。Sets which statements are tracked for wait stats. Nonenone none、allnone, all
Pgms_wait_sampling.history_periodPgms_wait_sampling.history_period 設定以毫秒為單位的等候事件取樣頻率。Set the frequency, in milliseconds, at which wait events are sampled. 100100 1-6000001-600000

注意

pg_qs.query_capture_mode 已取代 pgms_wait_sampling.query_capture_modepg_qs.query_capture_mode supersedes pgms_wait_sampling.query_capture_mode. 若 pg_qs.query_capture_mode 是 NONE,則 pgms_wait_sampling.query_capture_mode 設定沒有影響。If pg_qs.query_capture_mode is NONE, the pgms_wait_sampling.query_capture_mode setting has no effect.

使用 Azure 入口網站Azure CLI,為參數取得或設定不同的值。Use the Azure portal or Azure CLI to get or set a different value for a parameter.

檢視和函式Views and functions

使用下列檢視和函式來檢視和管理查詢存放區。View and manage Query Store using the following views and functions. PostgreSQL 公用角色中的任何人都可以使用這些檢視,查看查詢存放區中的資料。Anyone in the PostgreSQL public role can use these views to see the data in Query Store. 這些檢視僅適用於 azure_sys 資料庫。These views are only available in the azure_sys database.

移除常值和常數之後,查看查詢結構,其會呈現標準化。Queries are normalized by looking at their structure after removing literals and constants. 如果兩個查詢完全相同 (但常值除外),則兩者會有相同的雜湊碼。If two queries are identical except for literal values, they will have the same hash.

query_store.qs_viewquery_store.qs_view

此檢視會傳回查詢存放區中的所有資料。This view returns all the data in Query Store. 不同的資料庫識別碼、使用者識別碼及查詢識別碼都會自成一資料列。There is one row for each distinct database ID, user ID, and query ID.

名稱Name 型別Type 參考References 描述Description
runtime_stats_entry_idruntime_stats_entry_id bigintbigint 來自 runtime_stats_entries 資料表的識別碼ID from the runtime_stats_entries table
user_iduser_id oidoid pg_authid.oidpg_authid.oid 執行陳述式的使用者物件識別 (OID)OID of user who executed the statement
db_iddb_id oidoid pg_database.oidpg_database.oid 在其中執行陳述式的資料庫物件識別 (OID)OID of database in which the statement was executed
query_idquery_id bigintbigint   從陳述式的剖析樹狀結構計算的內部雜湊碼Internal hash code, computed from the statement's parse tree
query_sql_textquery_sql_text Varchar(10000)Varchar(10000)   代表性陳述式的文字。Text of a representative statement. 結構相同的不同查詢會群集在一起;此文字就式叢集中第一個查詢的文字。Different queries with the same structure are clustered together; this text is the text for the first of the queries in the cluster.
plan_idplan_id bigintbigint 對應到此查詢的方案識別碼,尚無法使用ID of the plan corresponding to this query, not available yet
start_timestart_time timestamptimestamp 依時間貯體彙總的查詢 - 根據預設,貯體的時間範圍為 15 分鐘。Queries are aggregated by time buckets - the time span of a bucket is 15 minutes by default. 這是和此查詢的時間貯體對應的開始時間。This is the start time corresponding to the time bucket for this entry.
end_timeend_time timestamptimestamp 和此查詢的時間貯體對應的結束時間。End time corresponding to the time bucket for this entry.
callscalls bigintbigint   查詢執行的次數Number of times the query executed
total_timetotal_time 雙精度double precision   查詢總執行時間 (以毫秒為單位)Total query execution time, in milliseconds
min_timemin_time 雙精度double precision 查詢最短執行時間 (以毫秒為單位)Minimum query execution time, in milliseconds
max_timemax_time 雙精度double precision 查詢最長執行時間 (以毫秒為單位)Maximum query execution time, in milliseconds
mean_timemean_time 雙精度double precision 查詢平均執行時間 (以毫秒為單位)Mean query execution time, in milliseconds
stddev_timestddev_time 雙精度double precision 查詢執行時間標準差 (以毫秒為單位)Standard deviation of the query execution time, in milliseconds
rowsrows bigintbigint   由陳述式擷取或受其影響的資料列總數Total number of rows retrieved or affected by the statement
shared_blks_hitshared_blks_hit bigintbigint   依據陳述式的共用區塊快取點擊總次數Total number of shared block cache hits by the statement
shared_blks_readshared_blks_read bigintbigint 由陳述式讀取的共用區塊總數Total number of shared blocks read by the statement
shared_blks_dirtiedshared_blks_dirtied bigintbigint   由陳述式變動的共用區塊總數Total number of shared blocks dirtied by the statement
shared_blks_writtenshared_blks_written bigintbigint   由陳述式寫入的共用區塊總數Total number of shared blocks written by the statement
local_blks_hitlocal_blks_hit bigintbigint 依據陳述式的本機區塊快取點擊總次數Total number of local block cache hits by the statement
local_blks_readlocal_blks_read bigintbigint   由陳述式讀取的本機區塊總數Total number of local blocks read by the statement
local_blks_dirtiedlocal_blks_dirtied bigintbigint   由陳述式變動的本機區塊總數Total number of local blocks dirtied by the statement
local_blks_writtenlocal_blks_written bigintbigint   由陳述式寫入的本機區塊總數Total number of local blocks written by the statement
temp_blks_readtemp_blks_read bigintbigint   由陳述式讀取的暫存區塊總數Total number of temp blocks read by the statement
temp_blks_writtentemp_blks_written bigintbigint   由陳述式寫入的暫存區塊總數Total number of temp blocks written by the statement
blk_read_timeblk_read_time 雙精度double precision   陳述式讀取區塊花費的總時間 (以毫秒為單位) (若已啟用 track_io_timing 的話,否則為零)Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_timeblk_write_time 雙精度double precision   陳述式寫入區塊花費的總時間 (以毫秒為單位) (若已啟用 track_io_timing,否則為零)Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

query_store.query_texts_viewquery_store.query_texts_view

此檢視會傳回查詢存放區中的查詢文字資料。This view returns query text data in Query Store. 不同的 query_text 都會自成一資料列。There is one row for each distinct query_text.

名稱Name 型別Type 描述Description
query_text_idquery_text_id bigintbigint query_texts 資料表識別碼ID for the query_texts table
query_sql_textquery_sql_text Varchar(10000)Varchar(10000)   代表性陳述式的文字。Text of a representative statement. 結構相同的不同查詢會群集在一起;此文字就式叢集中第一個查詢的文字。Different queries with the same structure are clustered together; this text is the text for the first of the queries in the cluster.

query_store.pgms_wait_sampling_viewquery_store.pgms_wait_sampling_view

此檢視會傳回查詢存放區中的等候事件資料。This view returns wait events data in Query Store. 不同的資料庫識別碼、使用者識別碼、查詢識別碼及事件都會自成一資料列。There is one row for each distinct database ID, user ID, query ID, and event.

名稱Name 型別Type 參考References 描述Description
user_iduser_id oidoid pg_authid.oidpg_authid.oid 執行陳述式的使用者物件識別 (OID)OID of user who executed the statement
db_iddb_id oidoid pg_database.oidpg_database.oid 在其中執行陳述式的資料庫物件識別 (OID)OID of database in which the statement was executed
query_idquery_id bigintbigint   從陳述式的剖析樹狀結構計算的內部雜湊碼Internal hash code, computed from the statement's parse tree
event_typeevent_type texttext   後端等候中事件的類型The type of event for which the backend is waiting
事件event texttext 如果後端目前正在等候,為該等候事件的名稱The wait event name if backend is currently waiting
callscalls 整數Integer 擷取到相同事件的次數Number of the same event captured

函式Functions

Query_store.qs_reset() 傳回 voidQuery_store.qs_reset() returns void

qs_reset 捨棄查詢存放區至今收集到的所有統計資料。qs_reset discards all statistics gathered so far by Query Store. 只有伺服器管理員角色可以執行此函式。This function can only be executed by the server admin role.

Query_store.staging_data_reset() 傳回 voidQuery_store.staging_data_reset() returns void

staging_data_reset 捨棄查詢存放區在記憶體中收集的統計資料 (亦即,記憶體中的資料尚未排清至資料庫)。staging_data_reset discards all statistics gathered in memory by Query Store (that is, the data in memory that has not been flushed yet to the database). 只有伺服器管理員角色可以執行此函式。This function can only be executed by the server admin role.

限制與已知問題Limitations and known issues

  • 如果 PostgreSQL 伺服器開啟 default_transaction_read_only 參數,查詢存放區會無法擷取資料。If a PostgreSQL server has the parameter default_transaction_read_only on, Query Store cannot capture data.
  • 如果遇到長時間的 Unicode 查詢 (> = 6000 個位元組),查詢存放區功能可能會中斷。Query Store functionality can be interrupted if it encounters long Unicode queries (>= 6000 bytes).

後續步驟Next steps