KUNISAN.JPブログコメント書き込み

先週の金曜日にアクセス解析用のMySQLデータ追加プログラムを公開しましたが、その後蓄積されたデータを分析・表示するプログラムを作り始めました。土曜日の朝から食事、風呂、トイレ、睡眠以外はずっとプログラムを組み続け、ようやく昨晩遅くにほぼ完成しました。トータルで50時間位はかかったと思います。

管理用のページなので残念ながらアドレスの公開はできませんが、ざっと内容を紹介するとこんな感じです。

■トップページ
・各ページの最近一週間のアクセス数(UV、PV)表示
・各ページの開設時からのアクセス数(UV、PV)表示
・ドメイン全体のアクセス数合計(UV、PV)表示
・データ詳細画面へのリンク
・掲示板、ブログの「コメントあり」アラーム
・掲示板、ブログのブロック解除、編集画面へのリンク

■詳細画面の機能
・アクセス履歴一覧表示(UV、PV、アクセス日時、アクセス元サイトなど)
・各項目(フィールド)の昇順、降順切替
・日付でのフィルタリング
・キーワードでのフィルタリング
・グラフ表示(日別、時間別、アクセス元IPランキング、アクセス元ドメインランキング、閲覧ページランキングなど)
・IPアドレスのWhois検索


今までのアクセス解析プログラムは、サーバーのCGI処理での負荷限界があったため、1サイトあたりのデータが最大2500件を超えると、日付に関わらず古いデータから自動的に削除するようにしていました。しかし、今回製作したものは200GB超のスペースにMySQL+PHPという組み合わせで、基本的に古いデータもそのまま保持し続けます。現在のアクセス数でデータが蓄積されていくとして、軽く数十年の単位は持つ計算になるので、恐らくサーバーとして使用しているネットブックの方が先に寿命が来てしまうのではないかと思います(万が一に備えて、1日1回自動でデータのバックアップを取っています)。

当初、データベースにはテーブルを1つだけ置いて、各サイトにID(サイト名)を振り分けてデータを蓄積していきました。サイト別になっているPVやUVのカウントには、一度データベースから該当サイトのPVとUVの最大値を読み込んで、PHP上で1つ数字を加えて、アクセス履歴と共にレコードに追加するという形を取っていました。しかし、「数十万件のデータをサイト名でフィルタリング＆グループ化して1つ取り出す」というのを、平均2秒に1回のペースでこなさなければならず、アクセスが少しでも集中すると、フリーズしたような状態になってしまい、正常にカウントされなくなる現象が出ることが分かりました。MySQLのバッファ設定をいじったりもしましたが、全く改善しません。ネットブックを調べてみると、CPU使用率が時折100%の状態になってしまうようです。Atom N280(1.66GHz)搭載程度のスペックでは、少々無理のある処理でした。

そのため、テーブルをサイト毎に分割して管理するよう変更し、さらにPVについてはフィールドの自動カウント機能(Auto Increment)を使うようにして、クエリー処理の負荷を減らすようにしてみました。するとCPU使用率が平均10%前後と大幅に下がり、比較的負荷の高い状態でも50%程度で収まるようになりました。

分析ツールはひとまず完成しましたが、後はこのデータを活用した「ニーズのあるサイト」を作れるようになれば、と思うところです。

Web管理関連記事(リンク一覧): SPF、DKIM、DMARCの設定とネームサーバー(DNS)設定のトラブル / さくらレンタルサーバーのアクセス履歴をPHPで表示 / PHPで画像のアップロード(さくらレンタルサーバーのPHPでImageMagick) / KUNISAN.JPサイトのメンテナンス(2018) / Webサーバー引っ越し(さくらインターネット スタンダード)とHTTPS(常時SSL)化 / ...(記事連続表示)

MySQL全機能バイブル ~現場で役立つAtoZ~ をAmazon.co.jpでチェック