slow queryに対する意思決定
概要
皆様、寒い中お疲れ様です！
年末年始長いですね。
本記事では、slow queryに関する記事でございます。
- 経緯
- slow queryとは
- slow queryどこまで対応する？
- これから
経緯
監視とか、sqlとかに関して記載したいなー、
その上で本日はslow queryに対する内容を書きたいと思い。
本筋ではないので当社システムおよび監視基盤の流れは超ざっくり。
- 当社システム
- BtoBマーケティングソフトウェアSaaS
- 大量データを取り込み、集計するバッチ集計が多数
- 一方で管理画面からのアプリケーションクエリももちろん多数発行される
- 監視基盤
- cloudwatchからロググループごとにslackへ通知するアラート型設計
- そのエラーログ等はnotionに対応status付きで保存され、対応漏れを防ぐ
- バッチ集計等は遅延が発生していないか閾値を設定した上で実行時間によってはアラート通知が流れる
- DB CPUアラートおよびslow queryにも検知基盤を設定している
slow queryとは
順番が前後してしまいましたが、slow queryに関する定義は下記でございます。
slow query：
クエリのうち、一定の基準に照らして遅い、時間がかかっているもの。
割と広義自体はそのままの意味です。
ただ「slow」の定義はまちまち
- PostgreSQL（auto_explain）
-
auto_explain.log_min_durationms を超えたクエリの実行計画を自動で EXPLAIN してログに出す拡張。 - デフォルト無効で、通常の
log_min_duration_statement（クエリテキストのみ）に「プランも出す」機能を足すイメージ
-
- MySQL（slow query log）
-
long_query_time秒を超え、かつmin_examined_row_limit行以上読んだクエリを slow とみなしてログに出す。 - デフォルトは10秒＆ログ機能はOFFで、SQLテキスト＋実行時間などが出る。
-
- TiDB（slow query）
- 実行時間が
tidb_slow_log_threshold（デフォルト300ms）を超えたクエリを slow としてtidb-slow.logに出す。 - デフォルトでONで、SQL＋時間内訳＋プランなどかなりリッチな情報が残る。
- 実行時間が
- etc...
多くがカスタム値を設定できるということから、結局slow queryどうすればいいんだ問題ですよね。
システムにおけるSlow query基準をビジネス要求を元に定義できている必要がある
ここでslow queryに対して重要だと考えているのは
- 該当システムにおける、ビジネス要求を元に定義されたSlow query基準が明確
だと考えてます。
DBのデフォルト10秒は満たしているとか言ったとしても、
そのシステムが2秒以内のレスポンスが顧客によって最善だったとしたら、
全然slow query対策できていない。
発行するクエリが裏側の集計処理が中心でN分は普通にかかるのに、
アプリケーションと同じような10秒とか設定してたとしてもノイズにしかならないし対応しきれるわけがありません。
なので、システムにおいて、ビジネス要求を元に適切なSlow query基準を制定する。
がSlow query対策の中心部分であり第一歩と考えております。
当社は後述しますが、裏側のバッチ処理が中心かつ、
顧客影響リスクが高いため対象としており、15分以降は原則対応としております。
別の記事で書ければと思いますがアプローチはこんな感じです。
- performance insightみて該当時間にどのようなメトリクスの元、そのクエリはslow queryとなったのか
- クエリの実行計画みてチューニングできそうなところあるか
- 不必要なデータまで持っていないか
- (複数DBの場合)該当dbでしか処理できない処理か、別DBの方が適切なクエリではないか
- (単一でなくそもそもの話だった時)インフラスペックを上げるべきか
- etc...
全部対応しきれない時、静観総コストと実施総コストを天秤にかけて
かといって全部対応しきれないなんて絶対発生すると思います。
その際は静観総コストと実施総コストのどちらが高いかを判断するようにしています。
- 静観総コスト：将来顧客への影響・品質低下で発生するコスト(リスクベース)および発生した後に発生する対応工数
- 実施総コスト：変更障害で発生するコスト(リスクベース)および対応工数
もちろん全部やりたいが、
slow query基準を満たせていないが、その数値で安定して実行できている、
少々遅れたとしても顧客影響は限りなく発生しないが、修正難易度はかなり高い。
とかをやってたら顧客に届けるべき課題への時間が取れなくなる。
なので「どうしたらやらない」意思決定もあらかじめ決めておく必要がある。
あくまで自分はその意思決定基準を上記の総コストを天秤にかけることで決めている。
静観したとしてもノイズのまま放置はしない
静観したとしても、
slack通知垂れ流しは厳禁です。
必ずそのクエリが垂れ流しにならないように設定変更してアラートが出る状態を異常と思える健全な状態を心がけましょう。
静観に至った予測値が外れていた時、検知できるように
ここで、もう一つslow queryで心がけていることとして、
静観に至った予測値が外れていた時、検知できるようにしています。
というのも、15分デフォルトで検知にしてますが、
静観判断のクエリはカスタム閾値を設定できるようにしています。
例えば30分までは許容・実行時間が増える見込みがない、60分までは許容というようなクエリが、
90分以上かかり始めてて顧客影響が出てしまう なんてケースを防ぐためでございます。
あくまでこれは予測値であります。
静観総コスト：将来顧客への影響・品質低下で発生するコスト(リスクベース)および発生した後に発生する対応工数
この予測が外れていた時は意思決定を変更する必要がある可能性が生じるため、
即時検知できるように、一律除外ではなくカスタム閾値による静観が役立っております。
これから
- slow queryに対して、ビジネス要求において健全とみなせる閾値を設定し、検知できる仕組みは必須。
- 原則、放置しない方が良い。影響発生および品質低下の因子であるため。
- その上で一から十まで対応し切れると言ったらそうではないと思う。
- 迷った際の意思決定は静観による総コストor 実施による総コストのどちらが高いのかの天秤をかける。
- 実施しないという判断を行ったということは、静観総コストが高くないと判断したということ。
- ここで大事なのは予測値が外れていた時は改めて検知できるような検知基盤構成にすること。
Discussion