SQLの負荷分析と改善

ども、@kimihom です。

f:id:cevid_cpp:20210212104015j:plain

今回 SQL の負荷を分析して改善することをしたので、その実施を残しておこう。

パフォーマンス解析基礎

何かしらサービスをローンチする時点で、パフォーマンス解析するようなサービスを導入することかと思う。

実際にその "SQL をどの頻度で使っているのか、平均どのくらい時間がかかっているのか" を定期的にチェックするのが、サービスの運用として必要なことになる。

最初のローンチ時点で SQL の負荷がとてもかかるものなんてのは、出てこない。データ数が全くもって少ないので、例え時間のかかる SQL 処理を実行しても、問題にはならないからだ。そして、最近のデータベースの進化によって、データが増えても、重たい SQL で大きな問題が起きづらいようになっている。私が実際に問題を経験したのは、特定のテーブルのデータ数が10万件以上になってから程度だった。

また、データベースへのリクエスト数がそこまでなければ、例え 1回の SQL 実行で数秒以上かかってしまったとしても、なんとか処理してしまうので問題に気づきづらいってのがある。

問題への気づき方

まず、時間制限を設けよう。

PostgreSQL であれば、statement_timeout を 10秒などに設定することになるだろう。デフォルトでは制限なし? のようで、SQL で大量のリクエストが来て、かつ重たい SQL が何秒も待たせてしまうと、それに伴って他の SQL も "待つ" ってことをしてしまうようになる。最終的にはメモリ消費が限界突破して、全ての SQL リクエストが返せなくなる大問題となってしまう。

statement_timeout を設定しておけば、その重たいリクエストだけがキャンセルされるので、他の SQL にできる限り影響を与えないようになる。まずタイムアウトを設定し、かつそのタイムアウトが発生した時には Slack などへ通知を出すようにしたいところだ。

問題への対応

もしタイムアウトが起こるようなら、その SQL を改善する必要がある。これには色々な対応方法が考えられる。

インデックスを貼る
機能の仕様を再検討する
データベースサーバーをより高性能なものを使う
大量データの一部を消す

インデックスを貼る際の注意点 (複合インデックス)

当然最初はアクセス効率を改善するってことで、インデックスでのアクセス改善となるだろう。ここで注意したいのは、「テスト環境ではちゃんと作ったインデックスが使われたけど、本番環境ではそのインデックスが使われない」といったことが起こるという点がある。

ユーザーに紐づく記事一覧を取得するケースを考えてみる。ユーザーAの記事が1万件以上ある状態だと、最新記事10件を取ってくるってだけで、ものすごく時間がかかる。だから、作成日時でのインデックスを貼ろう！となるだろう。テスト環境だと、その新しく作った作成日時のインデックスが正しく動作する。なぜなら、ユーザー数が10人くらいしかいないからだ。だが本番環境だと、ユーザー数が10万人いる状態で、そのユーザー1人が1万件の記事がある状態だ。となると、ユーザー取得でのインデックスの方が優先されてしまって、先ほど貼った作成日時でのインデックスが本番環境では使われないってことが起こりうる。

ここでの正解は、「ユーザーID と作成日時の2つを取った複合インデックスを作る」ってことになる。検証が難しく、「開発環境ではうまくいくけど本番環境ではうまくいかない」ってことで焦ることになりがちなので、理解しておいた方がいいだろう。