アクセス数やユーザ数の多いサイトを長年運用していると、ある日を境にデータベースのレスポンスが悪くなり、サーバにログインすることも困難になるような障害が発生することがあります。
原因として真っ先に思い浮かぶのは、データ量やアクセス数が多くなってサーバ性能が追いつかなくなったという事です。しかし実際には以前の記事で説明したように、データベースにインデックスが適切に設定されていないことが原因になっていることが多いのです。
1. サーバ状態の確認
まず不具合の検証として、データベースサーバが高負荷になっている時の状況を確認する必要があります。ログインすら出来ない状態であれば、最悪データセンターのオペレータにリセットを依頼する必要があります(立ちあがってくることを祈りましょう)。サーバにログインできたらプロセスやコネクションの状態を確認します。
プロセス数やアクティブなコネクション(ESTABLISHED)がデータベース設定値の上限に達していたら、これ以上リクエストを受け付けることが出来ないパンク状態にあります。
コマンド例
$ ps auxw
$ netstat -na |grep ESTABLISHED
次に原因となっているSQLを調査します。
2. 原因となるSQLの特定
psコマンドの結果で、ある程度負荷の原因となっているSQLが判明する場合もありますが、PostgreSQLやMySQLではスロークエリ、つまりレスポンスに時間がかかったSQL文をログに記録する機能があるので、それをあらかじめ設定して確認する方法もあります。
スロークエリのログを記録していなかったり、閾値が適切でなかった場合はデータベースサーバを確認しても原因となるSQL文が分からない場合があります。その場合は負荷が高かった時間帯のApacheログを確認し、データベースアクセスを伴うスクリプトに当たりをつけます。そしてそのプログラム内で発行されているSQL文を探しあてます。
3. EXPLAINによるSQLの検証
原因と思われるSQL文が確認できたら、実際にデータベースにコマンドモードでログインし、インデックスが有効に効いているか確認してみましょう。SQLのコマンドモードでEXPLAINという命令のあとに続けて、原因と思われるSQL文を入力して実行すると、オプティマイザがどの様なインデックスを使用したか、あるいは使用しなかったか、そしてスキャンされたレコード数やレスポンスタイムなどが確認できます。インデックスが効いていない検索の場合はシーケンシャルスキャンといって、対象となるテーブルを全件頭から最後まで照合しているので大変効率が悪く時間がかかります。
4. インデックスの作成
シーケンシャルスキャンとなってしまっているSQL文が見つかったら性能改善の余地があります。大抵の場合、条件に指定されているカラムにインデックスが設定されていなかったり、マッチング方法があいまいでインデックスが有効に機能していない状態になっています。そして最も簡単で安全な性能改善方法は、検索条件に指定されているカラムに対してCREATE INDEXを行うだけです。この操作はサイトの運用を続けたまま実行できます。
インデックスを作成したら再びEXPLAINでSQLを確認してみましょう。この結果、作成したインデックスが使用されるようになっていれば、レスポンスタイムとも劇的に性能が改善しているはずです。
インデックス作成例
mysql> CREATE INDEX インデックス名 ON テーブル名 (カラム名);
5. 全文検索用インデックスの利用
また良くあるケースでは、全ての投稿記事をキーワード検索(あいまい検索)するような機能を実装している場合にインデックスが効いていない事があります。記事の中から特定のキーワードだけ見つけるような場合に、通常のLike句での検索ではテーブル内のレコードがすべて対象となってしまい、検索効率が非常に悪くなります。このようなケースでは全文検索用のインデックスを使用する必要があります。
以前のフリーSQLサーバなどは、全文検索用のインデックスが標準装備されていないことが多かったので追加モジュールの組込などが必要でしたが、最近のバージョンではおおむね標準実装されている様です。全文検索用インデックスを利用するにはテーブルの属性変更やプログラムの改修、そしてバッチによるインデックス生成などの対応が必要ですが、高い効果が期待できます。
6. まとめ
このように、データベースサーバの性能が悪化してもインデックスの改善でレスポンスが劇的に改善することが非常に多くありますので、ハードウェア増強やクラウド移管を考える前に、まずはインデックスが適切に用いられているか確認することをお勧めします。
原因として真っ先に思い浮かぶのは、データ量やアクセス数が多くなってサーバ性能が追いつかなくなったという事です。しかし実際には以前の記事で説明したように、データベースにインデックスが適切に設定されていないことが原因になっていることが多いのです。
1. サーバ状態の確認
まず不具合の検証として、データベースサーバが高負荷になっている時の状況を確認する必要があります。ログインすら出来ない状態であれば、最悪データセンターのオペレータにリセットを依頼する必要があります(立ちあがってくることを祈りましょう)。サーバにログインできたらプロセスやコネクションの状態を確認します。
プロセス数やアクティブなコネクション(ESTABLISHED)がデータベース設定値の上限に達していたら、これ以上リクエストを受け付けることが出来ないパンク状態にあります。
コマンド例
$ ps auxw
$ netstat -na |grep ESTABLISHED
次に原因となっているSQLを調査します。
2. 原因となるSQLの特定
psコマンドの結果で、ある程度負荷の原因となっているSQLが判明する場合もありますが、PostgreSQLやMySQLではスロークエリ、つまりレスポンスに時間がかかったSQL文をログに記録する機能があるので、それをあらかじめ設定して確認する方法もあります。
スロークエリのログを記録していなかったり、閾値が適切でなかった場合はデータベースサーバを確認しても原因となるSQL文が分からない場合があります。その場合は負荷が高かった時間帯のApacheログを確認し、データベースアクセスを伴うスクリプトに当たりをつけます。そしてそのプログラム内で発行されているSQL文を探しあてます。
3. EXPLAINによるSQLの検証
原因と思われるSQL文が確認できたら、実際にデータベースにコマンドモードでログインし、インデックスが有効に効いているか確認してみましょう。SQLのコマンドモードでEXPLAINという命令のあとに続けて、原因と思われるSQL文を入力して実行すると、オプティマイザがどの様なインデックスを使用したか、あるいは使用しなかったか、そしてスキャンされたレコード数やレスポンスタイムなどが確認できます。インデックスが効いていない検索の場合はシーケンシャルスキャンといって、対象となるテーブルを全件頭から最後まで照合しているので大変効率が悪く時間がかかります。
4. インデックスの作成
シーケンシャルスキャンとなってしまっているSQL文が見つかったら性能改善の余地があります。大抵の場合、条件に指定されているカラムにインデックスが設定されていなかったり、マッチング方法があいまいでインデックスが有効に機能していない状態になっています。そして最も簡単で安全な性能改善方法は、検索条件に指定されているカラムに対してCREATE INDEXを行うだけです。この操作はサイトの運用を続けたまま実行できます。
インデックスを作成したら再びEXPLAINでSQLを確認してみましょう。この結果、作成したインデックスが使用されるようになっていれば、レスポンスタイムとも劇的に性能が改善しているはずです。
インデックス作成例
mysql> CREATE INDEX インデックス名 ON テーブル名 (カラム名);
5. 全文検索用インデックスの利用
また良くあるケースでは、全ての投稿記事をキーワード検索(あいまい検索)するような機能を実装している場合にインデックスが効いていない事があります。記事の中から特定のキーワードだけ見つけるような場合に、通常のLike句での検索ではテーブル内のレコードがすべて対象となってしまい、検索効率が非常に悪くなります。このようなケースでは全文検索用のインデックスを使用する必要があります。
以前のフリーSQLサーバなどは、全文検索用のインデックスが標準装備されていないことが多かったので追加モジュールの組込などが必要でしたが、最近のバージョンではおおむね標準実装されている様です。全文検索用インデックスを利用するにはテーブルの属性変更やプログラムの改修、そしてバッチによるインデックス生成などの対応が必要ですが、高い効果が期待できます。
6. まとめ
このように、データベースサーバの性能が悪化してもインデックスの改善でレスポンスが劇的に改善することが非常に多くありますので、ハードウェア増強やクラウド移管を考える前に、まずはインデックスが適切に用いられているか確認することをお勧めします。
コメント