使用進階查詢洞察功能監控查詢效能

選取文件版本： 18.4.0

• 目前 (18.4.0)
• 18.4.0
• 17.10.0
• 16.14.0
• 15.18.0

進階查詢洞察功能可讓您偵測、診斷及預防 AlloyDB Omni 資料庫的查詢效能問題。這項功能會擷取資料庫遙測資料，包括標準化查詢統計資料、等待事件、用戶端 IP 位址、標記和執行個體負載，並保留 30 天的歷來指標。

您可以使用進階查詢洞察執行下列工作：

• 分析執行時間、I/O 時間、等待時間、共用緩衝區活動和資料列計數，找出高延遲和耗用大量資源的查詢。
• 將緩慢查詢與等待事件和等待事件類別相互關聯，找出效能瓶頸並解決問題。
• 使用 SQLCommenter 中繼資料標記查詢，追蹤特定應用程式元件的查詢效能。
• 即時監控進行中的查詢和開啟的交易，並停止有問題的工作階段。

事前準備

注意： 啟用 google_insights_v2 擴充功能以取得進階查詢洞察，可能會導致資料庫執行個體效能負擔增加最多 5%。

啟用進階查詢洞察前，請確認符合下列需求：

• 您正在容器中執行獨立 AlloyDB Omni 執行個體。

  注意： 請勿在高可用性 (HA) 執行個體上使用進階查詢洞察功能。

• 您擁有資料庫執行個體的 postgres 超級使用者存取權，以及重新啟動容器的管理員權限。

• 執行個體中有 alloydbadmin 資料庫。AlloyDB Omni 會在初始化期間預設建立這個資料庫。

啟用進階查詢洞察

如要啟用進階查詢洞察，請按照下列步驟操作：

1. 以 postgres 超級使用者身分連線至 postgres 資料庫：

   Docker

   docker exec -it CONTAINER_NAME psql -U postgres -d postgres
   

   Docker

   docker exec -it CONTAINER_NAME psql -U postgres -d postgres
   

   Podman

   podman exec -it CONTAINER_NAME psql -U postgres -d postgres
   

   Podman

   podman exec -it CONTAINER_NAME psql -U postgres -d postgres
   

   將 CONTAINER_NAME 替換為 AlloyDB Omni 容器名稱，例如 my-omni-1。

2. 在 shared_preload_libraries 中檢查已載入的程式庫，將進階查詢洞察新增至預先載入清單，啟用擴充功能，然後結束 psql：

   SHOW shared_preload_libraries;
   ALTER SYSTEM SET shared_preload_libraries = 'LIBRARY_1','LIBRARY_2','google_insights_v2';
   ALTER SYSTEM SET google_insights_v2.enabled = on;
   \q
   

   將 LIBRARY_1、LIBRARY_2 和任何其他現有程式庫，替換為 SHOW shared_preload_libraries 傳回的程式庫名稱。請用單引號括住每個程式庫項目 (例如 'g_stats','google_columnar_engine','google_insights_v2')，並加入 AlloyDB Omni 所需的所有現有程式庫，以免覆寫。

3. 重新啟動 AlloyDB Omni 伺服器，套用預先載入設定：

   Docker

   docker restart CONTAINER_NAME
   

   Docker

   docker restart CONTAINER_NAME
   

   Podman

   podman restart CONTAINER_NAME
   

   Podman

   podman restart CONTAINER_NAME
   

4. 以 postgres 超級使用者身分連線至 alloydbadmin 資料庫：

   Docker

   docker exec -it CONTAINER_NAME psql -U postgres -d alloydbadmin
   

   Docker

   docker exec -it CONTAINER_NAME psql -U postgres -d alloydbadmin
   

   Podman

   podman exec -it CONTAINER_NAME psql -U postgres -d alloydbadmin
   

   Podman

   podman exec -it CONTAINER_NAME psql -U postgres -d alloydbadmin
   

5. 在 alloydbadmin 資料庫中建立進階查詢洞察擴充功能：

   CREATE EXTENSION IF NOT EXISTS google_insights_v2;
   

   注意： 請務必在 alloydbadmin 中建立擴充功能，而非在應用程式資料庫中建立。這項擴充功能會擷取執行個體上所有資料庫的活動，並將所有遙測資料寫入 alloydbadmin。

6. 確認程式庫已載入、擴充功能已啟用，且 google_insights_v2 擴充功能已安裝在 alloydbadmin 中：

   SHOW shared_preload_libraries;
   SHOW google_insights_v2.enabled;
   SELECT extname, extversion FROM pg_extension WHERE extname = 'google_insights_v2';
   

   確認 shared_preload_libraries 包含 google_insights_v2、google_insights_v2.enabled 傳回 on，以及 pg_extension 傳回 google_insights_v2 的資料列。

尺寸和功能參數

在 AlloyDB Omni 中，進階查詢洞察會將資料庫遙測記錄到 alloydbadmin 資料庫的 SQL 資料表、檢視區塊和函式中，涵蓋下列層面和功能：

• 查詢：查詢延遲時間增加時，可用於找出緩慢或耗用大量資源的 SQL 陳述式。
• 等待事件類型：用於判斷 CPU、磁碟 I/O 或鎖定爭用是否導致工作負載瓶頸。
• 等候事件：用於找出導致查詢執行延遲的特定 PostgreSQL 等候事件，例如 DataFileRead 或 WALWrite。
• 資料庫：用於比較邏輯資料庫之間的資源消耗量，並隔離共用執行個體上的高負載資料庫。
• 使用者和用戶端 IP 位址：用於將查詢負載歸因於特定資料庫角色或用戶端 IP 位址，並找出有問題的應用程式用戶端。
• 標記：用於將資料庫負載與特定應用程式服務、路徑或以 SQLCommenter 標記的 ORM 動作建立關聯。
• 執行中的查詢：用於即時診斷即時效能高峰、遭封鎖的交易，以及長時間執行的工作階段。

將 google_insights_v2.enabled 設為 on 時，AlloyDB Omni 會自動收集查詢、等待事件類型、等待事件、資料庫和使用者的指標。

如要在執行階段更新任何 google_insights_v2.* 參數，而不必重新啟動資料庫伺服器，請執行 ALTER SYSTEM SET 並重新載入設定：

ALTER SYSTEM SET PARAMETER_NAME = VALUE;
SELECT pg_reload_conf();

更改下列內容：

• PARAMETER_NAME：要設定的進階查詢洞察參數名稱，例如 google_insights_v2.qi_track_active_queries、google_insights_v2.qi_track_tag 或 google_insights_v2.qi_track_client_address。
• VALUE：參數的新值，例如 on。

如需各項參數、對應觀測能力檢視畫面，以及各檢視畫面傳回的指標和資料欄說明，請參閱「進階查詢洞察參考資料」。

時間精細程度

每個歷來 observability_service_* 檢視畫面都有三種時間精細程度：

• 基本檢視 (*_view)：清除間隔為 15 秒。您可以使用這些檢視畫面，調查過去 1 到 6 小時內發生的近期尖峰或事件。
• 粗略層級 1 檢視畫面 (*_coarse_1_view)：前 2,000 項查詢的 5 分鐘匯總間隔。您可以透過這些檢視畫面，分析 6 到 24 小時內的成效趨勢。
• 粗略層級 2 檢視畫面 (*_coarse_2_view)：前 2,000 個查詢的 30 分鐘匯總間隔。您可以使用這些檢視畫面，分析多日趨勢 (最多 30 天，這是保留期限)。

歷來觀測能力檢視畫面會以微秒為單位，儲存所有執行、等待和 I/O 時間欄，包括 total_execution_time、total_wait_time、total_read_time、total_write_time 和 time_spent。

查詢歷來成效指標

連線至 alloydbadmin 資料庫，針對歷來可觀測性檢視畫面執行診斷查詢。詳情請參閱「執行及連線至 AlloyDB Omni」。

本節的 SQL 陳述式是範例查詢，適用於各維度的常見疑難排解情境。您可以自訂這些範例，例如變更 recorded_on 時間範圍、依特定資料庫或角色篩選，或查詢 *_coarse_1_view 和 *_coarse_2_view 進行多日分析，以符合工作負載。

找出延遲時間長的查詢

如要找出過去一小時內總執行時間最長的前 10 項正規化查詢，請對 google_insights_v2.observability_service_query_view 執行下列範例查詢：

SELECT
  query_id,
  database,
  username,
  left(query, 100) AS normalized_query,
  sum(count) AS total_executions,
  sum(total_execution_time) AS total_execution_time_us,
  round((sum(total_execution_time)::numeric / NULLIF(sum(count), 0)), 2) AS avg_execution_time_us,
  sum(total_rows) AS total_rows,
  sum(total_shared_blocks_hit) AS shared_blocks_hit,
  sum(total_shared_blocks_read) AS shared_blocks_read
FROM google_insights_v2.observability_service_query_view
WHERE recorded_on >= now() - INTERVAL '1 hour'
GROUP BY query_id, database, username, left(query, 100)
ORDER BY total_execution_time_us DESC
LIMIT 10;

依查詢分析等待事件

如要找出哪些等待事件類別 (wait_class) 和精細等待事件 (wait_event) 對特定 query_id 造成的等待時間最長，請對 google_insights_v2.observability_service_wait_event_stats_by_query_view 執行下列範例查詢：

SELECT
  query_id,
  database,
  username,
  wait_class,
  wait_event,
  sum(count) AS wait_occurrences,
  sum(time_spent) AS total_wait_time_us
FROM google_insights_v2.observability_service_wait_event_stats_by_query_view
WHERE recorded_on >= now() - INTERVAL '1 hour'
  AND query_id = QUERY_ID
GROUP BY query_id, database, username, wait_class, wait_event
ORDER BY total_wait_time_us DESC;

將 QUERY_ID 替換為 observability_service_query_view 傳回的 query_id 值。

分析資料庫、使用者和用戶端 IP 負載

如要比較過去一小時內，各個邏輯資料庫和資料庫角色的總執行時間、等待時間和查詢量，請對 google_insights_v2.observability_service_db_load_conn_view 執行下列範例查詢：

SELECT
  database,
  username,
  sum(count) AS total_executions,
  sum(total_execution_time) AS total_execution_time_us,
  sum(total_wait_time) AS total_wait_time_us
FROM google_insights_v2.observability_service_db_load_conn_view
WHERE recorded_on >= now() - INTERVAL '1 hour'
GROUP BY database, username
ORDER BY total_execution_time_us DESC;

google_insights_v2.qi_track_client_address 啟用後，您也可以使用下列針對 google_insights_v2.observability_service_client_address_stats_by_query_view 的查詢範例，找出哪些連線用戶端 IP 位址產生的查詢時間最長：

SELECT
  client_address,
  username,
  database,
  query_id,
  sum(count) AS total_executions,
  sum(time_spent) AS total_time_spent_us
FROM google_insights_v2.observability_service_client_address_stats_by_query_view
WHERE recorded_on >= now() - INTERVAL '1 hour'
GROUP BY client_address, username, database, query_id
ORDER BY total_time_spent_us DESC
LIMIT 10;

依標記追蹤查詢效能

當 google_insights_v2.qi_track_tag 設為 on，且查詢包含 SQLCommenter 註解時，請執行下列範例查詢，使用 google_insights_v2.observability_service_tag_view 依應用程式標記中繼資料匯總執行時間：

SELECT
  application,
  framework_name,
  framework_controller,
  framework_action,
  framework_route,
  database,
  username,
  sum(count) AS total_executions,
  sum(total_execution_time) AS total_execution_time_us,
  round((sum(total_execution_time)::numeric / NULLIF(sum(count), 0)), 2) AS avg_execution_time_us
FROM google_insights_v2.observability_service_tag_view
WHERE recorded_on >= now() - INTERVAL '1 hour'
GROUP BY
  application,
  framework_name,
  framework_controller,
  framework_action,
  framework_route,
  database,
  username
ORDER BY total_execution_time_us DESC
LIMIT 10;

監控執行中的查詢

將 google_insights_v2.qi_track_active_queries 設為 on 時，您可以查詢 alloydbadmin 資料庫中的 google_insights_v2.stat_activity 和 google_insights_v2.stat_activity_v3，即時監控執行中的查詢和開啟的交易。

這些檢視畫面會顯示有效的用戶端連線、工作階段和交易持續時間，以及目前的等待事件，同時排除背景工作人員和 alloydbadmin 連線。

查詢有效工作階段和交易時間長度

如要找出目前在執行個體中執行的查詢，以及執行時間最長的交易，請在 alloydbadmin 中執行下列範例查詢：

SELECT
  pid AS "Process ID",
  left(normalized_query, 40) AS "Query",
  state AS "Status",
  round(EXTRACT(EPOCH FROM (clock_timestamp() - backend_start))::numeric, 2) AS "Session duration (secs)",
  CASE
    WHEN xact_start IS NOT NULL
    THEN round(EXTRACT(EPOCH FROM (clock_timestamp() - xact_start))::numeric, 2)
    ELSE NULL
  END AS "Transaction duration (secs)",
  CASE
    WHEN query_start IS NOT NULL
    THEN round(EXTRACT(EPOCH FROM (clock_timestamp() - query_start))::numeric, 2)
    ELSE NULL
  END AS "Query duration (secs)",
  COALESCE(wait_event_type, '') AS "Wait event type",
  COALESCE(wait_event, '') AS "Wait event",
  database AS "Database",
  COALESCE(application_name, '') AS "Application name",
  username AS "Username",
  COALESCE(client_addr::text, '') AS "Client address"
FROM google_insights_v2.stat_activity_v3
WHERE state = 'active'
ORDER BY query_start ASC;

停止長時間執行的查詢或交易

如要停止在 google_insights_v2.stat_activity_v3 中識別出的長時間執行查詢或遭封鎖的交易，請將其程序 ID 傳遞至 pg_terminate_backend：

SELECT pg_terminate_backend(PROCESS_ID);

將 PROCESS_ID 替換為 google_insights_v2.stat_activity_v3 傳回的 Process ID (pid) 值。

停用進階查詢洞察

如要停止收集遙測資料，而不重新啟動 AlloyDB Omni 執行個體，請將 google_insights_v2.enabled 設為 off，然後重新載入設定：

ALTER SYSTEM SET google_insights_v2.enabled = off;
SELECT pg_reload_conf();

如要從 alloydbadmin 資料庫中移除擴充功能物件和儲存的歷來資料，請連線至 alloydbadmin 並捨棄擴充功能：

DROP EXTENSION IF EXISTS google_insights_v2 CASCADE;

後續步驟

• 進階查詢洞察參考資料
• 選擇及安裝監控工具