概要
ある保険グループでは、夜間の変換パイプラインが処理時間枠を超過し、業務時間帯に食い込むようになっていました。直感的な対策はウェアハウスの拡大で、すでに一度試されていましたが、あまり効果はありませんでした。AceMQは、実際に何が時間を消費しているのかを突き止めるために起用されました。
課題
2つの別々の問題が、同じ症状を生んでいました。少数の変換クエリがリモートストレージにスピルしており、これはローカルに比べて桁違いに遅いうえ、その長時間実行されるクエリがウェアハウスを占有し、他のすべての処理がその後ろでキューイングされていました。ウェアハウスのサイズを上げたことで、スピルしているクエリはわずかに改善した一方、消費クレジットあたりで見るとキューイングはむしろ悪化していました。
環境
Azure上のSnowflake。Airflowでオーケストレーションされたdbtの変換処理を、保険契約データと保険金請求データに対して実行。
アプローチ
AceMQは、クエリ履歴を使ってクエリごとに実行時間、キューイング時間、スピル量を切り分け、2つの問題に個別に対処できるようにしました。ワーキングセットが収まらないウェアハウスのサイズでは、スピルするクエリはどのサイズでもスピルするため、まずクエリレベルの修正から着手しました。
ソリューション
- クエリ履歴から、クエリごとに実行時間、キュー待ち時間、リモートへのスピル量を切り分け
- スピルが最も深刻なクエリの結合とウィンドウ関数を書き直し、中間結果のサイズを削減
- パイプラインを別々のウェアハウスに分割し、長時間の変換処理が短いジョブをブロックしないように
- 同時実行される短いクエリを処理するウェアハウスに、マルチクラスターのスケーリングを設定
- 最大規模のソーステーブルのクラスタリングとフィルタリングを修正し、スキャンするマイクロパーティションを削減
- 処理時間枠の超過を早期に示す先行指標として、スピル量とキューの深さをパイプラインの監視に追加
結果
夜間のパイプラインは処理時間枠内に収まるようになり、業務時間帯に食い込むこともなくなりました。最も深刻なクエリではリモートへのスピルが解消され、以前試したウェアハウスの拡大と比べてクレジット消費も減少しました。