概覽
一家保險集團每晚的轉換管線開始超出時段,與白天的營業時間撞在一起。直覺的解法是換用更大的倉儲,但這早已試過一次,效果有限。AceMQ 受託找出真正耗費時間的原因。
挑戰
兩個截然不同的問題,造成了相同的症狀。少數轉換查詢溢寫到遠端儲存,速度比本機慢上好幾個數量級;這些長時間執行的查詢佔用了倉儲,其他所有工作只能在後面排隊。加大倉儲規格雖然讓溢寫的查詢稍有改善,但以每單位 credit 來看,排隊問題反而更嚴重。
環境
Azure 上的 Snowflake,執行由 Airflow 協調的 dbt 轉換,處理保單與理賠資料。
做法
AceMQ 利用查詢歷程,逐一區分每個查詢的執行時間、排隊時間與溢寫量,讓兩個問題可以分開處理。我們先從查詢層級著手修正,因為只要查詢的工作資料集超出倉儲容量,不論換成哪種規格的倉儲都一樣會溢寫。
解決方案
- 從查詢歷程中區分每個查詢的執行時間、排隊時間與遠端溢寫量
- 改寫溢寫最嚴重之查詢中的 join 與視窗函式,縮小中間結果的大小
- 將管線拆分到不同的倉儲,讓長時間的轉換不再阻擋短時間的工作
- 為處理大量並行短查詢的倉儲設定多叢集擴展
- 修正最大來源資料表的叢集化與篩選條件,減少掃描的微分割區(micro-partition)
- 將溢寫量與佇列深度納入管線監控,作為超出時段的領先指標
成效
每晚的管線回到時段內完成,不再與營業時間衝突。最嚴重的查詢已完全消除遠端溢寫,與先前嘗試的加大倉儲做法相比,credit 用量也隨之下降。