本文分享自華為雲社群《【雲駐共創】GaussDB（DWS）運維利刃：TopSQL工具解析》，作者：金魚哥。
在生產環境中，難免會面臨查詢語句出現異常中斷、阻塞時間長等突發問題，如果沒能及時記錄資訊，事後就需要投入更多的人力及時間成本進行問題的定位和解決，有時還無法定位到錯誤出現的地方。為了解決這種場景，GaussDB（DWS）提供了強大的運維工具TopSQL，對執行中和執行完成的語句全方面記錄，幫助使用者定位效能問題。而TopSQL被譽為效能定位、劣化分析等的重要基石，為使用者提供覆蓋記憶體、耗時、IO、網路、空間等多方面的資訊記錄。接下來將介紹TopSQL的基本原理、能力及典型應用場景，並講解如何使用好TopSQL這把運維利刃。
一、TopSQL簡介
1. 什麼是TopSQL？
TopSQL是GaussDB（DWS）資料庫中內建的一款功能十分強大的效能分析工具。在實際生產環境中，難免會出現一些突發情況，導致查詢語句出現異常中斷、阻塞時間長等情況，如果當時沒能記錄下來，那麼事後就要投入更多的人力以及時間成本去對錯誤進行定位和解決，有時還往往定位不到錯誤出現的地方。
為了解決這樣的窘迫的情況，GaussDB(DWS)開發了TopSQL功能，對執行中的語句記錄（實時TopSQL），對執行完成的語句進行記錄（歷史TopSQL）。
TopSQL作為GaussDB(DWS)的效能診斷工具，記錄GaussDB(DWS)中各個作業、運算元級別的資源使用資料、耗時資料，包括下盤資訊、記憶體、網路、耗時、自診斷告警、基礎資訊等作業執行的資料。
2. 怎麼實現TopSQL功能？
在GaussDB中，透過設定GUC引數，來開啟TopSQL功能，由於小功能不同，那麼相應的小功能的GUC引數也會有所區別。
大部分都是開關型別的，想要自定義場景就只需要關注傳參型別的引數，以及引數之間的依賴關係即可。下一章節將介紹常用系統表和引數的設定介紹。
開啟TopSQL功能必須要確保如下引數設定（下列引數為開啟TopSQL功能的必要條件）：
- user_workload_manager = on（預設為on）
- enable_resource_track = on（預設為on）
- resource_track_level = query / perf / operator（預設為query）
- 若是8.2.1.200版本，新增一個列舉引數為operator_realtime
二、TopSQL系統表和引數介紹
1. 公共GUC引數
以GaussDB(DWS) 8.1.3版本為例，推薦TopSQL相關的一些引數，如下表所示：
| 引數配置 | 解釋及用法 |
|---|---|
| enable_resource_track：on | 資源監控總開關，開啟後TopSQL才能發揮作用。 |
| enable_resource_record：on | 控制實時TopSQL是否做歷史TopSQL轉儲，813版本推薦開啟，方便對歷史問題做定位分析。 |
| resource_track_cost：0 | 執行代價超過預值，才會被記錄，建議保持預設值0。 |
| resource_track_duration：1 | 實時TopSQL是否轉儲到歷史TopSQL，預設值為60秒，建議設定為1。 |
| resource_track_level：query/perf | 語句級TopSQL資訊記錄到歷史TopSQL的系統表中。 |
| TopSQL_retention_time：30 | TopSQL老化時間為30秒，建議保持預設值。 |
| enable_track_record_subsql：按需開啟 | 控制子查詢是否記錄到TopSQL系統表中。 |
TopSQL系統表關鍵欄位：
| 欄位 | 欄位說明 |
|---|---|
| username | 使用者名稱 |
| block_time | 排隊時間，輔助定位CCN場景 |
| Start_time | 語句執行的開始時間 |
| duration | 語句執行時長，重點關注 |
| Estimate_memory | 估算記憶體，複製定位ccn排隊或記憶體問題 |
| Max_peak_memory | 實際最大使用記憶體，定位記憶體不足場景 |
| Max_spill_size | 下盤大小 |
| Unique_sql_id | 歸一化ID，標誌同一語句/同一語句不同入參 |
| Cpu_skew_percent | CPU傾斜情況 |
| warning | 自診斷資訊 |
2. 實時TopSQL
系統提供了query級別和運算元級別的資源監控實時檢視來用於查詢實時TopSQL。資源監控實時檢視記錄了查詢作業執行時的資源使用情況（包括記憶體、下盤、CPU時間、IO等）以及效能告警資訊。
實時檢視具體的對外介面如下表所示：
| 檢視級別 | 節點範圍 | 查詢檢視 |
|---|---|---|
| Query級別 | 當前CN | gs_wlm_session_statistics |
| 所有CN | pgxc_wlm_session_statistics | |
| 運算元級別 | 當前CN | gs_wlm_operator_statistics |
| 所有CN | pgxc_wlm_operator_statistics |
開啟條件
必須要確保如下GUC引數設定：
- use_workload_manager為on（預設為on）
- enable_resource_track為on（預設為on）
- resource_track_level為query或operator（預設為query）
監控作業型別為：
- 最佳化器估算的執行代價大於或等於resource_track_cost的取值的作業。
- 增刪改查，包括使用explain analyze和explain performance來執行的場景。query級別檢視還額外支援create table as語法。
- 重分佈過程中的作業不統計。
Cgroups功能正常載入，可透過gs_cgroup -P檢視控制組資訊。
use_workload_manager為系統級引數，用於是否開啟資源管理功能。
enable_resource_track為系統級引數，用於設定是否開啟資源監控功能。
resource_track_level為session級引數，可以對某個session的資源監控級別進行靈活設定。
這兩個引數的設定方法如下表：
| enable_resource_track | resource_track_level | query級別資訊 | 運算元級別資訊 |
|---|---|---|---|
| on(default) | none | 不統計 | 不統計 |
| on(default) | query(default) | 統計 | 不統計 |
| on(default) | operator | 統計 | 統計 |
| off | none/query/operator | 不統計 | 不統計 |
3. 歷史TopSQL
系統提供了query級別和運算元級別的資源監控歷史檢視用例查詢歷史TopSQL。資源監控歷史檢視記錄了查詢作業執行結束時的資源使用情況（包括記憶體、下盤、CPU時間、IO等）和執行狀態資訊（包括報錯、終止、異常等）以及效能告警資訊。但對於由於FATAL、PANIC錯誤導致查詢異常結束時，狀態資訊列只顯示aborted，無法記錄詳細異常資訊。對於查詢解析，最佳化階段的狀態資訊則無法監控。
歷史檢視具體的對外介面如下表所示：
| 檢視級別 | 節點範圍 | 查詢檢視 | |
|---|---|---|---|
| Query級別 | 當前CN | 歷史（Database Manager介面） | gs_wlm_session_history |
| 歷史（內部轉儲介面） | gs_wlm_session_info | ||
| 所有CN | 歷史（Database Manager介面） | pgxc_wlm_session_history | |
| 歷史（內部轉儲介面） | pgxc_wlm_session_info | ||
| 運算元級別 | 當前CN | 歷史（Database Manager介面） | gs_wlm_operator_history |
| 歷史（內部轉儲介面） | gs_wlm_operator_info | ||
| 所有CN | 歷史（Database Manager介面） | pgxc_wlm_operator_history | |
| 歷史（內部轉儲介面） | pgxc_wlm_operator_info |
開啟條件
必須要確保如下GUC引數設定：
- use_workload_manager為on （預設為on）
- enable_resource_track為on （預設為on）
- resource_track_level為query或operator（預設為query）
- enable_resource_record為on（預設為off）
- resource_track_duration小於作業執行時間（預設為60s）
監控作業型別為：
- 資源監控實時檢視中記錄的作業結束時的執行時間大於或等於resource_track_duration的作業。
- 最佳化器估算的執行代價大於或等於resource_track_cost的取值的作業
Cgroups功能正常載入，可透過gs_cgroup -P檢視控制組資訊。
該功能會引起儲存空間膨脹及輕微效能影響，不建議生成系統預設使用。
三、TopSQL典型應用場景
1. 實時TopSQL
問題場景：某叢集業務反饋某業務SQL偶發執行慢，該叢集resource_track_duration設定較大，歷史TopSQL中沒有記錄計劃詳情，需要定位原因。
處理過程：
① 系統管理員根據queryid檢視等待檢視，等待檢視部分結果如下：
② 管理員執行explain verbose，得到的執行計劃如下
③ 第8層非Stream運算元，說明該計劃不是正在執行語句的計劃，使用實時TopSQL檢視實際計劃：
2. 歷史TopSQL
問題場景：某客戶由於記憶體規格較小，經常出現記憶體不可用的報錯，雲上運維人員透過autopilot定期巡檢時發現該叢集記憶體週期性衝高，且存在單例項記憶體使用傾斜的問題，需定位原因。
處理過程：
① 透過歷史TopSQL找到記憶體佔用高的語句
Select \* from pgxc_wlm_session_info where start_time > '2023-10-30 10:05' and start_time < '2023-10-30 10:10' order by max_peak_memory desc limit 100;
② 根據unique_sql_id確認作業的歷史執行情況
3. 儲存過程子語句
問題場景：某客戶在業務中封裝了大量的儲存過程和匿名塊指令碼，用於業務系統的排程，隨著業務資料越來越多，儲存過程和匿名塊指令碼執行越來越慢，需要對其中的指令碼進行最佳化。示例指令碼如下：
處理過程：
① 檢視歷史TopSQL，儲存過程和匿名塊的query_plan欄位顯示NoPlan。
② 設定enable_track_record_subsql: on，該引數開啟後可以記錄儲存過程和匿名塊中的自語句和執行計劃。
③ 重新執行慢的儲存過程，根據query_id檢視歷史TopSQL中各個自語句的執行計劃。
四、如何透過TopSQL進行資訊統計
1. 常用TopSQL進行業務資訊統計
① 識別stream數量多的語句：
select *,(length(query_plan) - length(replace(query_plan, 'Streaming', ''))) / length('Streaming') as stream_count FROM pgxc_wlm_session_info ORDER BY stream_count DESC limit 100;
② 識別記憶體佔用高的語句：
select * from pgxc_wlm_session_info where start_time > 'xxxx-xx-xx' and start_time < 'xxxx-xx-xx' order by max_peak_memory desc limit 100;
③ 識別需要最佳化的語句：
select * from pgxc_wlm_session_info where start_time > 'xxxx-xx-xx' and start_time < 'xxxx-xx-xx' and warning is not null order by duration desc limit 100;
2. TopSQL使用注意事項
① 查詢時使用start_time做條件，避免全表查詢。
② 查詢時使用limit對結果集大小限制，防止結果集過大導致客戶端OOM。
總結
- GaussDB 具備將瞬息萬變的負載情況記錄下來，提供回看資料庫系統內部資源負載情況的能力，方便管理員對已經發生的問題做定位定界。
- TopSQL收集的資料來源於資料庫內部，為使用者提供了實時監控資料庫的能力。
- TopSQL功能被使用者廣泛使用，是效能定位、劣化分析、審計回溯等重要的基石，為使用者提供覆蓋記憶體、耗時、IO、網路、空間等多方面的監控能力。在此基礎上，核心也擴充出了異常規則等一些高階用法，在日常使用中，使用者可對TopSQL提出了更高的要求，比如記錄子語句、記錄語句型別、提升運算元級別語句監控準確性等諸多建議。
點選關注，第一時間瞭解華為雲新鮮技術~