Xserverから高負荷アラートが来た。その3

二度あることは三度ある。

 

この言葉通り、Xserverからまたまたデーターベースが高負荷状態になった旨の指摘がありました。

 

以前、Xserverから高負荷アラートが来た。その2で紹介したとおり、いったんは解決したのですが、再発してしまいました。

想像以上にかなり手ごわい問題のようです。

 

今回連絡が来たタイミングは、前回の制限解除の連絡を受けてから、ちょうど１ヶ月経過した頃になります。

 

こうも短期間で発生すると、他のことが出来なくなってしまうので、今回は徹底的に調査・対応することにしました。

 

原因はなんなのか？

これが分かれば苦労しないのです。ハイ

 

今回Xserverから来た疑わしきSQLは、

 

１、SELECT object_id, term_taxonomy_id FROM XXXXX_term_relationships INNER JOIN XXXXX_posts ON object_id =

２、SELECT SQL_CALC_FOUND_ROWS XXXXX_posts.ID FROM XXXXX_posts WHERE 1=1 AND (((XXXXX_posts.post_title L

３、SELECT YEAR(post_date) AS year, MONTH(post_date) AS month, count(ID) as posts FROM wp_posts WHE

 

とのことなので、それぞれ調べてみました。

 

１、は前回のXserverから高負荷アラートが来た。その2で指摘を受けた「XXXXX_term_relationships」テーブルが絡んだSQLでした。カテゴリ名などを表示するSQLです。

 

２、は各カテゴリの投稿の合計レコード数を表示するSQLのようです。

 

３、は各投稿の年月日の情報を表示するSQLのようです。

 

つまり、このSQLを実行しているアプリをすべて洗い出し、原因を特定する必要があったわけです。

 

横着するなということですね。

急がば回れとは、まさにこのことです。

 

原因特定の方法

Xserverから指摘を受けているSQL文から、各記事のカテゴリ、タグを表示する際に実行するクエリであることはわかっているため、具体的にページのどこで表示しているカテゴリ名・タグなのかを特定する必要があります。

 

Google先生に尋ねたところ、「Debug Bar」というプラグインを使うことで、ページ内の各要素を表示するために実行しているクエリとその実行時間を参照できるようになるらしいので、これを使って特定することにしました。

 

原因調査

Debug Barを利用した調査で、WordPressのsidebar.phpの中の処理で疑わしいものがあることがわかりました。

（Debug Barの使い方は、サイト表示が遅い原因の調査にて説明しているのでそちらを参考にしてください）

 

サイトバーには、「カテゴリ一覧」「タグクラウド」「人気記事の表示」の表示機能があり、それぞれの処理速度を調べたところ、カテゴリ一覧であることが判明。

 

事象を、もう少々具体的に説明すると、投稿時にカテゴリ（＝都道府県）を選択させているので、そのカテゴリごとに投稿されている数を表示させていました。

 

ですが、この「各カテゴリ内に何件あるか？」という処理を、各カテゴリごと（都道府県ごとなので47回）にSQLクエリを実行していたことが原因でした。

 

カテゴリ一覧で利用しているSQLを調べたところ、Xserverから指摘を受けたクエリに関連しますので間違いないないですね。

 

対応

対応案としては、以下の3点を検討しました。

• カテゴリ内の投稿件数の表示をキャッシュ化する
• カテゴリ一覧の表示をやめる。
• カテゴリ内の投稿件数の表示をやめる

 

キャッシュについては、W3 Total Cacheのトラウマ（導入したらサイトが真っ白になり、アンインストールしたくてもできなかった）があるっていう苦い経験があるので、避ける。

 

次にカテゴリ一覧の表示をやめるかどうかですが、サイトを利用する上でカテゴリ（＝都道府県）での絞込みがないと不便なので、いったんカテゴリ内の投稿件数表示だけをやめてみて、それでもだめならカテゴリ一覧表示を諦めることにしました。

 

この決定事項に従い、以下のキャプチャのとおり、カテゴリ内の件数表示をやめたところ、

 

SQLクエリの実行回数が半分以下になり、SQL実行時間も2倍以上の速度改善となりました。

 

もう1点、今回Xserverに指摘を受けたSQLは、投稿記事があればあるほど処理時間がかかるということもわかりましたので、念のため投稿記事を半分削除することにしました。

（もちろんバックアップとった上で）

 

ここまで対応したら、あとはXserverへの報告です。

 

今回は、

• 1アクセスに必要なMySQLのクエリの見直し
• 過去記事の削除

 

の2点を対応した旨を報告しました。

すると、前回同様、1週間様子見の後、

 

改めて負荷状況を確認いたしましたところ問題のないレベルまで改善されておりましたので今回実施いたしました制限の強化を『解除』いたしました。

現在は通常のご利用が可能な状態でございます。

 

という連絡を受け、無事アクセス制限が解除されました。

 

ふ～、今回はかなりヘビーな戦いでした。。。

 

新しいサービスを考えたり、作ったり、また頭の中の情報をアウトプットするのも大変ですが、今あるサービスの保守・改善のほうが大変だと痛感しました。。。

 

もともと運用案件が嫌いだったので、避けて通れないものですね。

 

その後の経過

対応してから２ヶ月が経過しましたが、その後Xserverからは何の連絡もありません。

現状、安定稼動しているとしてみています。

 

あの後、投稿件数が一定以上増えないように、３ヶ月以上前の投稿は自動で削除されるような処理を入れていることも寄与していると思います。

 

もう２、３ヶ月安定稼動していることを確認できたら、カテゴリ内投稿件数表示を復活させてみたいと考えています。

 

---

 シリーズ記事

• Xserverから高負荷アラートが来た。その１
• Xserverから高負荷アラートが来た。その２
• Xserverから高負荷アラートが来た。その３