SREのクラシマです。
最近、達人が教えるWebパフォーマンスチューニング 〜ISUCONから学ぶ高速化の実践：書籍案内｜技術評論社の輪読会を行っているのですが、その中で過去にあったDBのパフォーマンスが急に悪化した障害のことを話したので、ついでにblogにしておきます。
deleted_atは突然に
論理削除、使っていますか？
トラーナでは使ってます。Laravel wayに素直に乗ってEloquentを使うと、SoftDeletesトレイトで簡単に論理削除を実装できます。
deleted_atカラムが自動で付与され、いちいちwhereNull('deleted_at')と書かなくてもEloquentが面倒を見てくれて便利です。
ある日から急に、とあるuse SoftDeletesしているテーブルへのクエリがSlowQuery Logにでてくるようになりました。
EXPLAINしてみると、deleted_atに貼ってあるindexを使用しています。
ステージング環境でEXPLAINを試してみると、別のカラムに貼ったindexが使用され、十分に早くレスポンスが返ってくるのです。
もうお分かりになる方もいるかも知れません。
使用してほしいindexを持つカラムより、deleted_atカラムの方がカーディナリティがある時点で高くなってしまい、indexの優先順位が上がってしまったようなのです。
論理削除の度に日付が入っていくわけですから、値の種類が増えるのは当然ですよね 。
アプリケーションからはWHERE deleted_at IS NULLとしかクエリしないのでbooleanで十分なのですが、datetimeにしており、かつindexを貼っていたことが原因でした。
QueryBuilderからuse indexして事なきを得ましたが、そもそもindexを貼るべきではなかったのかも知れません。
(論理削除自体がアンチパターンという話もありますが...)
$records = \DB::query()
->fromRaw('shipment_toys USE INDEX (toy_index)')
->...
SELECT FOR UPDATEがテーブルロック
プロダクトから送信するメールはSendGrid | クラウドメール配信サービス・メルマガ配信システムを使用しています。
ごく最近までSMTP経由だったのですが、メールのテンプレート管理の簡素化のためにAPI経由での送信に切り替えました。
その本番切り替えの際に発生した障害の話です。
従来は、Twigを使ってメール本文を生成していたのですが、メールテンプレートの更新が煩雑でした。
そのため、SendGrid側にメールテンプレートを持ち、API呼び出し時にパラメータを渡して、本文を生成する形に切り替えました。
これだと、API呼び出し時点ではどのような文面でメールが送信されたかわからないため、bccでSendGridのmx宛にメールを送信し、Inbound Email Parse Webhook - ドキュメント | SendGridを使って本文をwebhookで受け取る形にしました。
API呼び出しのレスポンスにトークンが含まれているため、送信時点で「いつ・どのテンプレートのメールを・誰に」送信したかをDBに保存しておきます。
SendGridからのwebhookが帰ってくるので、こちらに含まれるトークンと付き合わせて本文を後からUPDATEします。
さて、本番稼働時に何が起きたかというと、webhookの大量のタイムアウトです。
SMTP経由からの切り替えのため、メール送信結果を保存するテーブルにトークンカラムを追加していました。
しかし、このカラムにindexを貼り忘れていたため、全件scanが発生します。
MySQLのSELECT FOR UPDATEは、行レベルロックですが、scanされた行すべてをロックします。
そのため、indexを貼っていないカラムに対してWHEREでクエリを発行すると、全件scanが走り、結果的にテーブルロックと同じ状態になってしまいます。
これにより、ロック解放待ちが多発し、webhookが大量にタイムアウトした、という事例でした。
まとめ
以上、SQLのパフォーマンスが急に悪化した2件の障害のお話でした。
いずれも、コードレビューで捕まえるのはなかなか難しい内容ですし、ステージング環境で検証するにも本番と同じ中身のDBを用意しなければならず、これまた難しいものです。
SlowQueryやAPIレスポンスタイムの監視により、本番環境で素早く検知することはできますが、利用者への影響が懸念されます。
フィーチャーフラグ等によるプログレッシブデリバリーにより、本番環境で部分的に機能を有効化することで対策とできないか、検討しています。
