背景
私は普段Railsアプリケーションを運用保守しています。
ある日、アプケーションで使用しているAWS RDSのCPU使用率が100%張り付きになってしまう問題が発生しました。
今回はその問題を解決するまでの道筋を記載していけたらと思います。
解決までの流れ
解決までの流れはざっくり書くと以下です。
- AWS RDSのCPU使用率メトリクスを確認し、問題発生時刻を把握
- AWS CloudWatchで該当時刻のスロークエリログを確認し、問題となるクエリを特定
- クエリをEXPLAIN
- 該当のクエリが作られるActiveRecordを特定、EXPLAINした結果を元にコードやDB設定を修正
- テスト、リリース
1. AWS RDSのCPU使用率メトリクスを確認し、問題発生時刻を把握
RDS > データベース > 該当のデータベース > モニタリング > CPU使用率
で確認できます。
2. AWS CloudWatchで該当時刻のアプリケーションログやスロークエリログを確認し、問題となるクエリを特定
※スロークエリログをCloudWatchに連携する方法は他の記事をご覧ください。今回はCloudWatchで確認できるものとして書いていきます。
スロークエリログで表示される内容のうち、特にQuery_timeが大きい数値のものを探します。
Query_timeが大きい数値 → クエリが長時間 → DBへの負荷が高い ためです。
原因と思われるQuery_timeが異常値（3秒以上など）のクエリを特定します。
３. クエリをEXPLAIN
EXPLAIN
-- 以下、該当のクエリを貼り付け
SELECT ~~~
FROM ~~~
WHERE ~~~
INNER JOIN ~~~
このようなEXPLAINを実行すると、
id -- SELECT識別子、実行順序を示してる
select_type -- シンプルなSELECT文なのかサブクエリなのかなどを識別
table -- 出力の行で参照しているテーブルの名前
partitions -- クエリが参照したパーティションテーブル。パーティションされていない場合はNULL
type -- テーブルの結合方法
possible_keys -- テーブルの行検索に使用できるindex。NULLの場合は参照するインデックスがない
key -- MySQLが実際に使用しているindex
key_ren -- MySQLが実際に使用したindexの長さ
ref -- 行検索の際にindexと比較されるカラムや値
rows -- クエリ実行のためにMySQLが調査する行数
filtered -- テーブル条件によってフィルタ処理される行数の割合
Extra -- MySQL がクエリーを解決する方法に関する追加情報
といった情報が表示されます。
特に、注目すべき点は
- typeがALLではないか？
- テーブルフルスキャンのためクエリが高負荷になりやすい
- key, possible_keysがNULLではないか？
- 参照するINDEXがないためtypeがALLになる原因になり得る
- rowsが極端に多くないか？
- ExtraにUsing filesortやUsing temporaryが出ていないか？
- Using filesortやUsing temporaryが出ている場合、INDEXが貼られていない、メモリのバッファーサイズに収まらないなどの問題を抱えている
参考
https://qiita.com/Stuffy86/items/809540b73cacde951997
４. 該当のクエリが作られるActiveRecordを特定、EXPLAINした結果を元にコードやDB設定を修正
怪しい箇所をexitで止めるなどして、該当のクエリが作られるActiveRecordを特定します。
特定したActiveRecordのコードやDB設定を修正します。
EXPLAINした結果を元に私が取った対策を参考までに共有します。（まだまだ改善の余地ありですが。。）
EXPLAINした結果
- typeがALLだった
- rowsが9万件もあった
- ExtraにUsing filesort, Using temporaryが出ていた
対策
- 適切なテーブルに適切なINDEXを貼った
- ActiveRecordで拾ってくるidの数を分割し１個１個のクエリを軽くした
結果
- typeがindexになった
- ExtraにUsing filesortが出なくなった
- 処理実行時、RDS CPU使用率が100%に張り付かなくなった
まとめ
Railsアプケーションでスロークエリが原因でAWS RDSのCPU使用率が100%張り付きになってしまう問題の解決方法の一例を示しました。
もっと効果的な方法が見つかったら追記していきます。