SQLのパフォーマンスが急に悪化した話

2023-11-27

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を貼るべきではなかったのかも知れません。(論理削除自体がアンチパターンという話もありますが...)

code: (php)

$records = \DB::query()

->fromRaw('shipment_toys USE INDEX (toy_index)')

->...

SELECT FOR UPDATEがテーブルロック

プロダクトから送信するメールはSendGrid | クラウドメール配信サービス・メルマガ配信システムを使用しています。ごく最近までSMTP経由だったのですが、メールのテンプレート管理の簡素化のためにAPI経由での送信に切り替えました。その本番切り替えの際に発生した障害の話です。

https://gyazo.com/56156736bddec84db40cd6c926f7d649

従来は、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レスポンスタイムの監視により、本番環境で素早く検知することはできますが、利用者への影響が懸念されます。フィーチャーフラグ等によるプログレッシブデリバリーにより、本番環境で部分的に機能を有効化することで対策とできないか、検討しています。

#トラーナテックブログ