パフォーマンス改善検討の備忘
2024/12/25仕事で小さい既存システムのパフォーマンス改善を担当する機会があり、今まであまり深掘りして対応する機会がなかったので、どう対応したかのをまとめて記録しておくことにしました。 早い→爆速にしたとかそういう話ではなく、めっちゃ遅い→許容範囲内にした、という感じの話です。
前提
- Webシステム
- DBはMySQL8系
- 全部が遅いわけではなく、特定操作で遅くなる
Phase1 調査
とりあえず、特定操作で遅くなることが判明していたため、以下の調査を実施しました。
- 本番相当のデータでの再現確認
- ボトルネック確認
本番相当のデータでの再現確認
まずは以下の目的を達成するため、開発環境で再現可能かの確認を実施しました。
- 時間帯・環境起因の可能性をある程度否定できそうか
- 今後の調査が開発環境でも可能そうか
ここで再現が取れない場合、再現条件をさらに探しに行かなければならなかったですが、幸い？開発環境でも再現したため、以降の調査を開発環境でも実施することとしました。
ボトルネック確認
開発環境で再現したため、以下の可能性を考慮しつつ調査を実施しました。
- DBとのやりとりの中で何かが発生している
- N+1や遅いクエリが紛れているなどが考えられる
- アプリケーション側で異様に時間がかかるロジックが紛れている
- 他の問題（CPU/メモリなどのリソースに異常が起きるなど）
調査といっても、大方DB/SQL周りだろうとは思っていたので、ソースコードの確認・投げられているSQLの確認などが主でした。 結果としては、遅いクエリが複数回投げられているという状況で、これさえ改善できればOKということが確認できました。
※ここで見つからなければ、DockerのStats見たりに移っていたと思います。
Phase2 解決策の検討
遅いクエリが複数回投げられているならどうしましょうという話なんですが、一旦以下のどれかがメインになるんじゃないかなと思います。
- サーバーサイドキャッシュを使った解決方法
- インデックス貼る、FORCE INDEXなどで実行計画を調整するなど、DBベースでの解決方法
- アプリ側でJOINなどをする、ロジックを見直すなどの、アプリベースでの解決方法
上記のうち、アプリケーションの特性や発行されるクエリの性質から、1のキャッシュは効果が薄そうなので一旦保留し、2と3を検討することとしました。
DBベースでの解決方法検討
定番ですがEXPLAINとEXPLAIN ANALYZEを見ることから調査を始めました。 その中で現状使われているインデックス等のあたりをつけつつ状況を確認しました。 分かったこととしては
- 行数多いところのクエリのtypeはref、それ以外もeq_refかrefなのでめっちゃ問題あるわけではなさそう
- ただ、1発のデータ量多い部分はある
- ↑に対するインデックス貼りは試す価値ありそう といったところでした。
ただ、重い部分に関係するWHERE句に使われているカラムが軒並みカーディナリティが低く、あんまりインデックスには期待できないと思いつつ、一時テーブルを作り、インデックスも複製した上で色々検証してみたのですがうまく行かず。
一旦DBベースでの解決方法は探るのを止めることとしました。
アプリベースでの解決方法検討
重いクエリの特徴としては、WHERE句が近しいものが複数回流れており、全て同じテーブルに向けてのクエリであったため、以下の改修方法で対応することとしました。
- 複数のクエリのWHERE句と取得カラムから、ベースとなるSQLを作り投げる
- 結果をメモリに展開(変数に格納)し、そのデータからさらにフィルタをかけるなどして、クエリ投げるデータ取得の代替とする
上記で試しに書き換えてみたところ、許容範囲内かつ、実行速度は（当然ですが）大体1/n(nは思いクエリの回数)くらいになりました。 メモリ使用量の増加はあると思いますが、元々最終的にはクエリの結果を変数に格納していたこともあり、あまり大きな影響はなさそうと踏んでいます。 ロジックの書き換えで今回は解決を図るのが一旦良さそうだという形となりました。
今回の結論
- アプリのロジック書き換えでなんとかなりそう
- SQL自体は遅いが、あまり改善の余地が少なそうで、データ量の増加に起因する
- これ以上の速さを求めるのであれgばDynamoにデータ入れて〜とか、設計を変えてしまうことも視野にいれた方が良さそう
他にやっておいた方がよかったこと
今回、いきなり再現から行ったんですが、というのもログが見やすい形で残っておらずという状況でした。 DataDogなど入れておいてSlow Query拾えたりするとよりよかったのかな、とか、単純にログでより細かく原因の確認ができればよかったな、とか考えてしまうので、隙間を見て備えを増やしたいです。
所感
色々気になる点はありつつも、解決策が見つかってよかったという感じでした。 インデックスはればええんやろと思っていたのですが全然そんな感じではなかったです。 あと、EXPLAIN ANALYZEは今回初めて使いましたが、どんなふうに動いているのか可視化されて興味深いです。もうちょっとDBの知識があれば読み取れる事項が多かったかもしれません。