3月から Negitaku.org の負荷を改善するという試みをしているのでそれについて。
データベースサーバーをハイスペックなものに変更したけど再び負荷が高くなってきて4月くらいには自分でもアクセスする気が無くなるくらい重くなってしまった。
このままだと運営する気力も無くなるということで、いろいろと対策をしてみた次第。 でも、完璧に解決出来ているかというそうでも無くて順次対応している感じ。
Negitaku.org の負荷を改善する
とりあえず、DBサーバーを新しくしても結果としてあまり変わらないので、 プログラム関連を見直すしかないという事で少しずつ直していった。 主には以下の3点がポイント。
- 処理のキャッシュ化
- プログラム、MySQL クエリーの改善
- 設計ミス
Negitaku.org は、一般的な CMS を使っているのではなく､ CakePHP というフレームワーク上に 自分がプログラムして作った自前の CMS で動作している。
これは SK Gaming の代表を務めていた bds さんが運営していた GeekBoys.org というサイトみたいなものを作りたくて 独学で勉強して作った結果出来上がったものなのでいろいろと不具合がある。
キャッシュ化
まず、プロトタイプ作成時は特に負荷とか気にせず MySQL を叩きまくりな処理を作っていた。 その時はサーバーのスペックがそれなりにあったのと、 データが少なかったので問題なく動作していた。
しかし、次第に利用してくれる方々が増えて様々なデータが肥大化していった。 すると、毎回呼び出す必要も無いような所が キャッシュ化されておらずどんどん負荷が高まってきた。
例えば、現在のメンバー数やチーム数のデータなど。 個人的には正確な数字が出ていると良いよね、とか思っていた。
しかし、こんな数字は別に利用者的にはどうでも良い。 登録や退会ごとにちゃんとした数字が表示される必要はないので、 キャッシュしてそのうち正しいデータが出れば良いという事にした。 完全に無駄な処理だった。
この他にも、MySQL を叩きすぎな処理をみつけてキャッシュ化していった。
プログラム、MySQL クエリーの改善
MySQL の slow log やステータスを見ていると明らかに負荷が高い処理が幾つも見つかった。 これはなぜかというと、CakePHP にデータの取得を任せていた部分が とにかく余計な処理をしていて無駄な SQL クエリーが発生していた。
例えば、ニュースの記事を1件取得する場合に、 以下のような全く関係ないデータを取りまくっていた。
- ニュースコメント ユーザー1
- ユーザープロフィール
- ユーザーレビュー
- ユーザーダイアリー
- ユーザーデスク画像
- ユーザーゲーム
- ニュースコメント ユーザー2
- ユーザープロフィール
- ユーザーレビュー
- ユーザーダイアリー
- ユーザーデスク画像
- ユーザーゲーム
- ニュースコメント ユーザー3
- ユーザープロフィール
- ユーザーレビュー
- ユーザーダイアリー
- ユーザーデスク画像
- ユーザーゲーム
「ユーザープロフィール」には国籍やユーザー id 情報が含まれているので コメントを表示する際には必要になるのだけど、 他の要素というのは表示しないので取得する必要がない全くどうでもいい情報。
CakePHP は、データの関連性を事前に設定しておくことで、 $this->News->findById($id); みたいな感じで処理を実行すると この一行で必要なデータが簡単に取れるのが売りになっていたりする。
これは非常に便利だけど、本来必要なデータ以外も引っ張ってきてしまって 全く無駄な負荷が発生してしまう。
いままで負荷が発生していたのは、ほぼここに依存していたのが原因だった。 結局、改善策として処理ごとに最適化した MySQL クエリーを投げるように変更しなくてはらなかった。 CakePHP は、「一行書くだけでで簡単にデータを取得出来る」みたいな所を売りにしていることもあって、 noob な日曜プログラマーの自分はそんな処理を利用しまくっていたけど、 結果として負荷が増大し、そこを解決するために スマートな処理を後から考えなければならなくなった。 「誇大広告を信じないで下さい。レーザーセンサー must die!」みたいな感じです。
設計ミス
5/14に8時間ほどに渡る表示遅延が発生したのだけど、 MySQL の slow log を見たら ニュースの人気記事を表示する部分が原因だった。 これはサーバースペックに依存した実装にした自分のミス。
実装的には、ニュースの個別記事にアクセスがあった場合に、 その記事に対するユニークなアクセスだったのかを確認し、 新規アクセスだったならばログテーブルに保存するという処理をしていた。 毎アクセスごとにデータを保存するという ありえない処理なのは間違いないのだけど、 サーバースペック的に余裕だろうと思ってこんな実装にしてしまったのが失敗だった。
MySQL の処理が遅延しまくりなあり得ない状況。
このログテーブルは、定期的に削除しているものの、数十万～数百万くらいのデータ容量になった。 ここからソートを行ない人気記事TOP5を集計するという処理が24時間に1回行なわれいた。 この処理が実行されると結果がキャッシュされ、 以降はそのキャッシュデータを利用して表示する仕組みだったのだけど、 タイミング的に多数のアクセスが集中して同時アクセス数が多くなり、 結果としてクエリーを処理する事が出来無くて数時間に渡る表示遅延になってしまった。
そもそも、各アクセスに対する記録を MySQL に保存していくのが良くないので、 このアクセス情報の収集と集計処理を一切止めにしたいと思った時に、 Google Analitics からデータを取れたら良いのではないかと思って検索してみたら、 API が提供されていてそちらから取得するように変更してみた。
この処理も3秒くらいの時間がかかるのだけど、 全アクセスを記録して数十万～数百万のログデータに対してソートを行ない ランキングデータを取得するという処理に比べたら相当に軽い処理。
結果として、この処理に置き換えたおかげて毎アクセス毎の アクセスチェックと記録が無くなった他、データ集計処理がいらなくなったので、 MySQL の負荷改善としてはかなりの効果があったと思う。
あらためてログの記録を見直してみると、この集計がかなりの負荷を発生させていたので、 それなりにマシになるのでは無いだろうか。
3月からいろいろと対策してきた結果、 MySQL に投げているクエリー数はピーク時から 40% くらい削減出来た。
とりあえず、今のところの対策状況はこんな感じ。 他にも負荷が高い所が見つかったら順次対策していく次第。
