本文分享自華為雲社群《GaussDB(DWS)運維利刃：TopSQL工具解析》，作者：胡辣湯。
在生產環境中，難免會面臨查詢語句出現異常中斷、阻塞時間長等突發問題，如果沒能及時記錄資訊，事後就需要投入更多的人力及時間成本進行問題的定位和解決，有時還無法定位到錯誤出現的地方。在本期《GaussDB(DWS)運維利刃：TopSQL工具解析》的主題直播中，華為雲數倉GaussDB(DWS)調優專家劉坤鵬老師，深入講解GaussDB(DWS) TopSQL的基本原理、能力及典型應用場景。
一、 什麼是TopSQL？
TopSQL是GaussDB(DWS)資料庫中內建的一款功能十分強大的效能分析工具。在生產環境中，難免會出現一些突發情況，導致查詢語句出現異常中斷、阻塞時間長等情況，如果當時沒能記錄下來，那麼事後就要投入更多的人力以及時間成本去對錯誤進行定位和解決，有時還往往定位不到錯誤出現的地方。為了解決這樣的窘迫的情況，GaussDB(DWS)開發了TopSQL功能，對執行中的語句記錄（實時TopSQL），對執行完成的語句進行記錄（歷史TopSQL）。
TopSQL作為GaussDB(DWS)的效能診斷工具，記錄GaussDB(DWS)中各個作業、運算元級別的資源使用資料、耗時資料，包括下盤資訊、記憶體、網路、耗時、自診斷告警、基礎資訊等作業執行的資料。
TopSQL工作原理
二、TopSQL系統表和引數介紹
以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常用系統表：
• 實時TopSQL：pgxc_wlm_session_statistics
• 歷史TopSQL：pgxc_wlm_session_info
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 |
自診斷資訊 |
三、TopSQL的3個典型應用場景
1、 實時TopSQL
問題場景：某叢集業務反饋某業務SQL偶發執行慢，該叢集resource_track_duration設定較大，歷史TopSQL中沒有記錄計劃詳情，需要定位原因。
處理過程：
1) 系統管理員根據queryid檢視等待檢視，等待檢視部分結果如下：
2）管理員執行explain verbose，得到的執行計劃如下
3） 第8層非Stream運算元，說明該計劃不是正在執行語句的計劃，使用實時TopSQL檢視實際計劃：
2、 歷史TopSQL
問題場景：某客戶由於記憶體規格較小，經常出現記憶體不可用的報錯，雲上運維人員透過autopilot定期巡檢時發現該叢集記憶體週期性衝高，且存在單例項記憶體使用傾斜的問題，需定位原因。
處理過程：
1) 透過歷史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;
2) 根據unique_sql_id確認作業的歷史執行情況
3、 儲存過程子語句
問題場景：某客戶在業務中封裝了大量的儲存過程和匿名塊指令碼，用於業務系統的排程，隨著業務資料越來越多，儲存過程和匿名塊指令碼執行越來越慢，需要對其中的指令碼進行最佳化。示例指令碼如下：
處理過程：
1) 檢視歷史TopSQL，儲存過程和匿名塊的query_plan欄位顯示NoPlan；
2) 設定enable_track_record_subsql: on，該引數開啟後可以記錄儲存過程和匿名塊中的自語句和執行計劃；
3) 重新執行慢的儲存過程，根據query_id檢視歷史TopSQL中各個自語句的執行計劃。
四、如何透過TopSQL進行資訊統計
1、 常用TopSQL進行業務資訊統計
1) 識別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;
2) 識別記憶體佔用高的語句：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;
3) 識別需要最佳化的語句：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使用注意事項
1) 查詢時使用start_time做條件，避免全表查詢；
2) 查詢時使用limit對結果集大小限制，防止結果集過大導致客戶端OOM。
本期分享到此結束，更多關於GaussDB(DWS)產品技術解析、數倉產品新特性的介紹，請關注GaussDB(DWS)開發者平臺，GaussDB(DWS)開發者平臺為開發者們提供最新、最全的資訊諮詢，包括精品技術文章、最佳實踐、直播集錦、熱門活動、海量案例、智慧機器人。讓您學+練+玩一站式體驗GaussDB(DWS)。
GaussDB(DWS)開發者平臺連結：https://bbs.huaweicloud.com/contents/dws/learning.html
點選關注，第一時間瞭解華為雲新鮮技術~