最耗時的有效查詢統計資料

「最耗時的有效查詢」，又稱為「執行時間最長的查詢」，是一份在資料庫中有效的查詢清單，並按照查詢執行時間排序。深入瞭解這些查詢，有助於在發生系統延遲和 CPU 使用率偏高的情況時，找出原因。

Spanner 提供內建資料表 SPANNER_SYS.OLDEST_ACTIVE_QUERIES，列出執行中的查詢 (包括包含 DML 陳述式的查詢)，並依開始時間排序，以升冪排序。但不包含變更資料流查詢。

如果執行大量查詢，由於系統會對這類資料的收集作業強制執行記憶體限制，因此結果可能會限於部分查詢。因此，Spanner 提供額外的資料表 SPANNER_SYS.ACTIVE_QUERIES_SUMMARY，顯示所有有效查詢的摘要統計資料 (變更串流查詢除外)。您可以使用 SQL 陳述式，從這兩個內建資料表擷取資訊。

在本文件中，我們將說明這兩個資料表，並提供一些使用這些資料表的查詢範例，最後示範如何使用這些資料表，以協助減輕由活動查詢造成的問題。

可用性

SPANNER_SYS 資料只能透過 SQL 介面取得，例如：

• Google Cloud 控制台中資料庫的 Spanner Studio 頁面

• gcloud spanner databases execute-sql 指令

• executeQuery API

Spanner 提供的其他單一讀取方法不支援 SPANNER_SYS。

OLDEST_ACTIVE_QUERIES

SPANNER_SYS.OLDEST_ACTIVE_QUERIES 會傳回一份有效查詢清單，並依開始時間排序。如果執行大量查詢，由於 Spanner 會對這類資料的收集作業強制執行記憶體限制，因此結果可能會限於部分查詢。如要查看所有執行中查詢的統計資料摘要，請參閱 ACTIVE_QUERIES_SUMMARY。

資料表結構定義

查詢範例

您可以使用用戶端程式庫、Google Cloud CLI 或 Google Cloud 控制台執行下列 SQL 陳述式範例。

列出最久執行的查詢

下列查詢會傳回一份最久未執行的查詢清單，並按照查詢的開始時間排序。

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC;

列出執行時間最久的 2 個查詢

這個範例與前一個查詢略有不同，會傳回前 2 個最久未執行的查詢，並依查詢開始時間排序。

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 2;

查詢輸出

ACTIVE_QUERIES_SUMMARY

顧名思義，內建表格 SPANNER_SYS.ACTIVE_QUERIES_SUMMARY 會顯示所有有效查詢的摘要統計資料。如以下結構定義所示，系統會依據查詢的時間長度將查詢分為三個區塊或計數器：超過 1 秒、超過 10 秒和超過 100 秒。

資料表結構定義

一個查詢可能會被計入多個分類。舉例來說，如果某個查詢執行了 12 秒，就會計入 COUNT_OLDER_THAN_1S 和 COUNT_OLDER_THAN_10S，因為它符合兩個條件。

查詢範例

您可以使用用戶端程式庫、gcloud spanner 或 Google Cloud 主控台執行下列 SQL 陳述式範例。

擷取執行中查詢的摘要

下列查詢會傳回執行查詢的摘要統計資料。

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;

查詢輸出

限制

雖然我們希望提供最全面的洞察資料，但在某些情況下，這些資料表中傳回的資料可能不含查詢。

• 如果 DML 查詢 (UPDATE/INSERT/DELETE) 位於套用變異事件階段，則不會納入。

• 如果查詢因暫時性錯誤而重新啟動，則不會納入。

• 不會納入來自超載或無回應伺服器的查詢。

• OLDEST_ACTIVE_QUERIES 無法用於讀寫交易。即使是在唯讀交易中，也會忽略交易時間戳記，並一律在執行時傳回目前的資料。在極少數情況下，系統可能會傳回 ABORTED 錯誤，並提供部分結果；在這種情況下，請捨棄部分結果，並再次嘗試查詢。

使用執行中的查詢資料排解 CPU 使用率偏高的問題

在 Spanner 資料庫中排解延遲問題時，查詢統計資料和交易統計資料會提供實用資訊。這些工具可提供已完成查詢的相關資訊。不過，有時您需要瞭解系統中執行的內容。舉例來說，假設 CPU 使用率相當高，而您想回答下列問題。

• 目前有多少查詢正在執行？
• 這些查詢是什麼？
• 有多少查詢正在執行超過 100 秒的長時間作業？
• 哪個工作階段正在執行查詢？

根據上述問題的答案，您可以決定採取以下行動。

• 刪除執行查詢的工作階段，即可立即解決問題。
• 新增索引，改善查詢效能。
• 如果查詢與定期背景工作相關，請降低查詢頻率。
• 找出發出查詢的使用者或元件，這些使用者或元件可能未獲授權執行查詢。

在本逐步操作說明中，我們會檢查執行中的查詢，並決定是否採取任何行動。

擷取執行中查詢的摘要

在本範例情境中，我們發現 CPU 使用率高於正常值，因此決定執行下列查詢，傳回有效查詢的摘要。

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;

查詢會產生下列結果。

我們發現有一個查詢執行時間超過 100 秒。這在我們的資料庫中很不尋常，因此我們想進一步調查。

擷取執行中的查詢清單

我們在前一個步驟中判斷，有一個查詢執行時間超過 100 秒。為了進一步調查，我們執行下列查詢，以便傳回前 5 個最久未執行的查詢的更多資訊。

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id,
       query_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 5;

在這個範例中，我們在 2024 年 3 月 28 日下午約 16 點 44 分 9 秒 (美國東部時間) 執行查詢，並傳回下列結果。(您可能需要水平捲動畫面，才能查看完整輸出內容)。

最早的查詢 (指紋 = -2833175298673875968) 會在表格中醒目顯示。這是一項昂貴的 CROSS JOIN。我們決定採取行動。

取消耗時的查詢

在這個範例中，我們發現某個查詢正在執行成本高昂的 CROSS JOIN，因此決定取消該查詢。我們在上一個步驟收到的查詢結果包含 query_id。我們可以為 GoogleSQL 執行下列 CALL cancel_query(query_id) 指令，為 PostgreSQL 執行 spanner.cancel_query(query_id) 指令，取消查詢。

CALL cancel_query(query_id)

CALL spanner.cancel_query(query_id)

CALL cancel_query('37190103859320827')

您需要查詢 spanner_sys.oldest_active_queries 資料表，確認查詢已取消。

本逐步操作說明會示範如何使用 SPANNER_SYS.OLDEST_ACTIVE_QUERIES 和 SPANNER_SYS.ACTIVE_QUERIES_SUMMARY 分析執行中的查詢，並視需要針對導致 CPU 用量過高的查詢採取行動。當然，避免高成本作業並為您的用途設計正確的結構定義，一向是較為省錢的做法。如要進一步瞭解如何建構可有效執行的 SQL 陳述式，請參閱 SQL 最佳做法。

後續步驟

• 瞭解其他內省工具。
• 瞭解 Spanner 為每個資料庫儲存的其他資訊。這些資訊儲存在資料庫的資訊結構定義資料表中。
• 進一步瞭解 Spanner 的 SQL 最佳做法。