Xserverから高負荷アラートが来た。Web業界で働いている人が仕事の失敗談をメインにいろいろつぶやいています。検索するこのサイトについてお問い合わせPHP(23)BackUp(1)UX(5)SSL(4)SIMフリー(4)SNS(8)mixi(1)if(1)レイアウト(1)犬(1)七夕(3)100均(1)十五夜(1)恵方巻(1)ストレス(3)ダース・ベイダー(1)粉ミルク(1)MMS(1)テザリング(1)利益(1)ばい菌(1)返金(1)記帳(1)トップページ(2)アンインストール(3)クエリー(5)CMS(1)elseif(1)顛末書(1)ボトルネック(1)このサイトについてお問い合わせXserverから高負荷アラートが来た。Xserver更新:2017-02-11二度あることは三度ある。この言葉通り、Xserverからまたまたデーターベースが高負荷状態になった旨の指摘がありました。以前、Xserverから高負荷アラートが来た。その2で紹介したとおり、いったんは解決したのですが、再発してしまいました。想像以上にかなり手ごわい問題のようです。今回連絡が来たタイミングは、前回の制限解除の連絡を受けてから、ちょうど1ヶ月経過した頃になります。短期間で発生すると、他のことが出来なくなってしまうので、今回は徹底的に調査・対応することにしました。原因はなんなのか?これが分かれば苦労しないのです。WHEとのことなので、それぞれ調べてみました。1、は前回のXserverから高負荷アラートが来た。その2で指摘を受けた「XXXXX_term_relationships」テーブルが絡んだSQLでした。カテゴリ名などを表示するSQLです。2、は各カテゴリの投稿の合計レコード数を表示するSQLのようです。3、は各投稿の年月日の情報を表示するSQLのようです。このSQLを実行しているアプリをすべて洗い出し、原因を特定する必要があったわけです。横着するなということですね。急がば回れとは、まさにこのことです。原因特定の方法Xserverから指摘を受けているSQL文から、各記事のカテゴリ、タグを表示する際に実行するクエリであることはわかっているため、具体的にページのどこで表示しているカテゴリ名・タグなのかを特定する必要があります。Bar」というプラグインを使うことで、ページ内の各要素を表示するために実行しているクエリとその実行時間を参照できるようになるらしいので、これを使って特定することにしました。Barを利用した調査で、WordPressのsidebar.phpの中の処理で疑わしいものがあることがわかりました。Barの使い方は、サイト表示が遅い原因の調査にて説明しているのでそちらを参考にしてください)サイトバーには、「カテゴリ一覧」「タグクラウド」「人気記事の表示」の表示機能があり、それぞれの処理速度を調べたところ、カテゴリ一覧であることが判明。事象を、もう少々具体的に説明すると、投稿時にカテゴリ(=都道府県)を選択させているので、そのカテゴリごとに投稿されている数を表示させていました。この「各カテゴリ内に何件あるか?」という処理を、各カテゴリごと(都道府県ごとなので47回)にSQLクエリを実行していたことが原因でした。カテゴリ一覧で利用しているSQLを調べたところ、Xserverから指摘を受けたクエリに関連しますので間違いないないですね。対応案としては、以下の3点を検討しました。カテゴリ内の投稿件数の表示をキャッシュ化するカテゴリ一覧の表示をやめる。Cacheのトラウマ(導入したらサイトが真っ白になり、アンインストールしたくてもできなかった)があるっていう苦い経験があるので、避ける。次にカテゴリ一覧の表示をやめるかどうかですが、サイトを利用する上でカテゴリ(=都道府県)での絞込みがないと不便なので、いったんカテゴリ内の投稿件数表示だけをやめてみて、それでもだめならカテゴリ一覧表示を諦めることにしました。この決定事項に従い、以下のキャプチャのとおり、カテゴリ内の件数表示をやめたところ、SQLクエリの実行回数が半分以下になり、SQL実行時間も2倍以上の速度改善となりました。1点、今回Xserverに指摘を受けたSQLは、投稿記事があればあるほど処理時間がかかるということもわかりましたので、念のため投稿記事を半分削除することにしました。(もちろんバックアップとった上で)ここまで対応したら、あとはXserverへの報告です。今回は、1アクセスに必要なMySQLのクエリの見直し過去記事の削除の2点を対応した旨を報告しました。負荷状況を確認いたしましたところ問題のないレベルまで改善されておりましたので今回実施いたしました制限の強化を『解除』いたしました。現在は通常のご利用が可能な状態でございます。いう連絡を受け、無事アクセス制限が解除されました。ふ~、今回はかなりヘビーな戦いでした。新しいサービスを考えたり、作ったり、また頭の中の情報をアウトプットするのも大変ですが、今あるサービスの保守・改善のほうが大変だと痛感しました。運用案件が嫌いだったので、避けて通れないものですね。対応してから2ヶ月が経過しましたが、その後Xserverからは何の連絡もありません。現状、安定稼動しているとしてみています。あの後、投稿件数が一定以上増えないように、3ヶ月以上前の投稿は自動で削除されるような処理を入れていることも寄与していると思います。2、3ヶ月安定稼動していることを確認できたら、カテゴリ内投稿件数表示を復活させてみたいと考えています。シリーズ記事Xserverから高負荷アラートが来た。その1Xserverから高負荷アラートが来た。その2Xserverから高負荷アラートが来た。iizukaフリーランスとしてUX視点で業務支援しています。Hi-Standard好きです。http://yukioiizuka.comMISLEADの記事に共感いただけましたらいいねをお願いします。関連記事Xserverから高負荷アラートが来た。その2Xserverから高負荷アラートが来た。21:28都道府県の記事数自体は必要ではあるので、一日一回クエリ飛ばして表示数カウントを更新するようにしては?もしくは、記事投稿時に更新して、静的なHTMLを参照するようにするとか。私自身、大規模を想定したサイト作っていますが、DBへの負荷を考慮して、編集時にHTMLに変換して基本はHTMLを参照。必要に応じてDBアクセスというような作り方をしています。私の場合は、WordPlessのようなCMSそのものを作ってる感じではありますが。他人が作ったもの(扱うための作法)を改良するのはいろんな意味で困難を伴います。コメントデータの処理方法の詳細はこちらをご覧ください。Reserved...。