本番環境で動作している（はずの）selectを叩いてサービスを落とした話 @glassmonkey
この記事は本番環境でやらかしちゃった人アドベントカレンダー20205日目の記事です。
去年の投稿を見て自分も過去色々やらかしてしまったなあという反省と懺悔の元今回参加させていただきました。
TL;DR
当時新卒1~2年目だった自分にあるミッションが課せられました。
当時関わっていたサービスにいわゆる一覧画面 + ページングで表示機能を実装している箇所がありまして、表示速度改善に取り組みました。
特に特定のカテゴリページの2ページ目以降の表示速度がかなり遅く、タイムアウトが頻発していたという状況でした。
Qiitaでいうところのタグフィードのようなものと思っていただけるとありがたいです。
何をしたか
どの程度遅いかをまず調べようと、APIからDBに流れているはずのタイムアウトしているselect文をAPIと同様にRead Replicaにたたきました。
ちなみにデータベースはMySQLでした。
何が起こったか
すると、index的にきついクエリだったので、tmpファイルがあふれるという自体が発生した。どうきつかったかというと、規模的には数百万規模のレコードに対して、Using temporary; Using filesortが発生している状態だった。
その結果、サービスを数分の間停止させるという自体を招いた。
[2020年12月5日12時追記]
ちなみに、クエリに関してはGUIアプリケーションから実行して、2~3分程待って、データベースのレスポンスが悪化しはじめたのでやめた記憶です。
どう対応したのか
tmpファイルの削除などでディスクを開けることで解消した記憶です。
当時の環境はオンプレだったので、インフラチームの方々にはご迷惑をおかけしました。
[2020年12月5日12時追記]
本番環境のAPIがたたいているクエリそのものではあったのですが、APIクライアントがタイムアウトしてくれていたので、tmpファイルがあふれる前に処理が中断しているという状態でした。
今回の問題の原因
- カジュアルに本番環境にselect文を流すことが日常的にあった。
- 本番でないと再現しないDBのパフォーマンス的問題を当時はそれなりに抱えていた記憶。
- 当時のテーブルの構成的にクエリ最適化が限界にきていた点も大きかった。
- 実行計画に対する審美眼が自分になかった。
- 遅いクエリの認識はあったが、本番のユーザーアクセス時に動いていたSQLなのでまさかサービスを止めるほどやばいクエリだとは思わなかった。
- select = 読み込み操作という認識で、副作用がある認識をあまり持てていなかった。
再発防止策
全部が当時できたわけではないですが、以下が挙げられるかなと思います。
安全に壊せる環境を用意する
本番でなく開発環境で状況が再現すると理想ではあるのですが、データの規模などで本番準拠のDBでしか再現しない問題は実際あると思います。
今回の事象はread処理のパフォーマンスの問題だったので、外部からのLBから外したRead Replicaを用意することで改善をしました。最悪そいつが死んでもサービスへの影響が少ないものを用意して、安全に本番準拠のクエリを試すための環境としました。
一番の理想は、本番環境準拠の環境が別にあると良いのかなとは思います。予算の問題があるので難しい問題ではありますが。
チームの認識を揃える
本番環境に流すときのselect文は実行計画を含めてレビューするようにはなりました。
「このクエリはやばいから、whereで〇〇を追加して範囲を絞ろう」みたいな議論が生まれました。
また、select文を本番に流す際もレビューを通してチームの共通認識になってたりしたので、フォローに回ったり回ってもらったりも容易になった記憶もあります。
ペアオペを実施する
特に現代だと、リモートワークで自宅作業中心になりがちなので、本番作業は画面共有しながら先輩社員と一緒にやるのが理想かなと思います。
1人だとどうしても漏れが発生しがちなのと、ターミナル操作のようにコード上に現れにくいものをチームで共有できる良い機会なのかなと思います（良いツールとかこのとき教えてもらうことが多かった記憶）
余談（本来の目的のパフォーマンス改善に関しての対処に関して）
もともとの目的のパフォーマンス改善に関しては元のクエリがデータベースのテーブル的にほぼ絶望的だったので、ユーザーアクセス時に実行させない方針を取りました。
暫定対応としては、基本的にキャッシュを永続化し、定期実行するバッチで強制的にキャッシュを作り変えるようにしてユーザーアクセス時にスロークエリが流れないようにしました。
最終的にはRDBからデータを取得することをやめて、全文検索エンジン経由のデータ取得に切り替えることで他にも問題を抱えていた複雑なクエリ事情の解消としました。
おわりに
当時の関係者の方見てたら、ほんとすみませんでしたという謝罪の念でいっぱいです。
他にも色々やらかしてしまった思い出はありますが、その分チャンレンジさせていただいた感謝の念しかありません。
失敗した自分がいうのもあれですが、失敗しないとなかなか人間学べないと思います。
安全に失敗できる環境づくりを構築して、スピード感をもって良いプロダクト作りに貢献していきたいですね。
明日は@yutakatayさんの俺TUEEE系エンジニアが本番サーバーを0.1秒で落とした話とのです。
どんなRTAが待っているのか楽しみですね。
単語メモ
- TL;DR（Too Long, Didn’t Read）：文章が長すぎて、読んでいませんという意味の略語です。要約としても用いられます。
- ページング：仮想記憶（仮想メモリ）の方式の一つで、メモリ領域をページと呼ばれる一定の大きさの領域に分割し、物理的なアドレスとは別に仮想的なアドレスを割り当てて管理します。
- Read Replica（リードレプリカ）：データーベースの負荷分散のために作成される参照専用の複製されたものです。検索や読み込みのみを行うことができます。
- クエリ：データーベース管理システムに対する問い合わせのことで、データの抽出や更新などの処理要求を文字列で表します。
- tmpファイル（temporary file）：一時ファイルのことで、ソフトウェアを利用したタイミングで作られ、処理が終了すると削除されます。処理が中断したり、異常終了したりするとtmpファイルが残り、ストレージ容量が圧迫するなど影響があります。
- Using temporary：MySQLのクエリ実行計画で、結果を保持するために一時的にテーブルを作成する必要があることを示しています。出て来た時点でクエリの改善が必要です。
- Using filesort：ORDER BY句（結果レコードの順序を指定する時に使用します）にインデックスを使用できない状態です。
- GUIアプリケーション（Graphical User Interface application）：ユーザーが見やすいようにウィンドウ上で絵やボタンなどを操作できるインターフェースです。
- ターミナル：キーボードからコマンドと呼ばれる命令文を打ち込んでPCに命令を行うアプリケーションのことです。
- RDB（Relational Database）：関係データベースで、データを複数の表として管理し、表と表の関係を定義することで、複雑なデータの関連性を扱えるようにした管理方式です。