色々統計を取るにはDBの方が便利なので,ApacheのアクセスログをDBに格納することにした.DBはPostgreSQL 8.3を使う.
DBの定義
以下のテーブルを作成した.
テーブルNo | テーブル名(論理) | テーブル名(物理) |
1 | Apacheログテーブル | apache_log |
2 | 除外ユーザーエージェントテーブル | al_useragent_ex |
1.Apacheログテーブル
アクセスログを格納するテーブルは,以下の定義で作成した.
基本的にはcombined形式ログに含まれる項目を列挙したものだが,最後の2カラムを追加している.あ,vhostも違うか.
Apacheログテーブル
物理名:apache_log
列No. | 列名(論理) | 列名(物理) | データ型 | 必須 | 主キー |
1 | VirtualHost名 | vhost | text | Yes | 1 |
2 | リモートホスト | hostname | text | Yes | |
3 | identユーザ | identuser | text | | |
4 | basic認証ユーザ | basicuser | text | | |
5 | リクエスト日時 | reqtime | timestamp | Yes | 2 |
6 | HTTPメソッド | method | text | Yes | |
7 | リクエストターゲット | target | text | Yes | |
8 | プロトコル | proto | text | Yes | |
9 | レスポンスコード | statuscode | text | Yes | |
10 | レスポンスバイト数 | bytes | integer | | |
11 | リファラー | referer | text | | |
12 | ユーザーエージェント | useragent | text | Yes | |
13 | アクセスログ行番号 | loglineno | integer | Yes | 3 |
14 | 登録日時 | reg_date | timestamp | Yes | |
アクセスログ行番号は,アクセスログファイル(access_logとか)内での行番号.
これを主キーに組み込むことで,DB登録の時に同じアクセスログファイルを繰り返し使っても,
リクエストがDBに重複登録されないようにしている.
データのロードは,Perlのプログラムで行った.access_logのファイルを読んで,
DBI経由でApacheログテーブルにINSERTした.
2.除外ユーザーエージェントテーブル
検索エンジンなどのロボットからのアクセスは,統計を取るときに除外することが多い.
ページに人気があるかどうかを調べる上で,ロボットからのアクセス数は意味がないからだ.
(もっとも,これは統計を取る観点によりけりです)
そこで,その除外処理に必要な情報もテーブルに登録しておく.
除外ユーザーエージェントテーブル
物理名:al_useragent_ex
列No. | 列名(論理) | 列名(物理) | データ型 | 必須 | 主キー |
1 | 除外パターン | ex_pattern | text | Yes | 1 |
2 | 登録日時 | reg_date | timestamp | Yes | |
除外するユーザーエージェントを,SQLのLIKE書式でex_patternに登録する.
除外ユーザーエージェントテーブルの内容
ex_pattern |
%Google% |
Infoseek% |
Scooter% |
Ultraseek% |
ia_archiver% |
moget% |
gazz% |
Bookmark% |
Aruyo% |
Wget% |
libwww-perl% |
WebAuto% |
dloader% |
msnbot% |
Feedfetcher% |
Feedpath% |
Bloglines% |
RssReader% |
RSS% |
YahooFeedSeeker% |
Agent% |
PEAR% |
Baiduspider% |
Yeti% |
%Slurp% |
OOZBOT% |
CyberPatrol SiteCat Webbot% |
%Crawler% |
%crawler% |
%Bot% |
psbot% |
MaSagool% |
この内容は,これからもログを適宜調べて増やしていく必要があるので面倒...
統計を取得する例
このテーブルを使って,今月の日別リクエスト数の統計を求める例.
今月の日別リクエスト
・SQL
SELECT
date_trunc('day', reqtime) AS d,
count(*) AS nreq
FROM
apache_log
WHERE
vhost = 'kamoland.com' AND
reqtime >= date_trunc('month', current_timestamp) AND
reqtime < date_trunc('month', current_timestamp) + interval '1 month'
AND
statuscode IN ('200', '206', '304')
AND
NOT EXISTS (
SELECT 1 FROM al_useragent_ex WHERE useragent LIKE ex_pattern
)
GROUP BY d
ORDER BY d
・実行結果
d | nreq
---------------------+-------
2008-08-01 00:00:00 | 6374
2008-08-02 00:00:00 | 3039
2008-08-03 00:00:00 | 2761
2008-08-04 00:00:00 | 6069
2008-08-05 00:00:00 | 6401
2008-08-06 00:00:00 | 7130
2008-08-07 00:00:00 | 6732
2008-08-08 00:00:00 | 6366
2008-08-09 00:00:00 | 2432
2008-08-10 00:00:00 | 3633
2008-08-11 00:00:00 | 9177
2008-08-12 00:00:00 | 14913
2008-08-13 00:00:00 | 11264
2008-08-14 00:00:00 | 6897
2008-08-15 00:00:00 | 6998
2008-08-16 00:00:00 | 4211
2008-08-17 00:00:00 | 4082
2008-08-18 00:00:00 | 9344
2008-08-19 00:00:00 | 10803
2008-08-20 00:00:00 | 8860
2008-08-21 00:00:00 | 8383
2008-08-22 00:00:00 | 8677
2008-08-23 00:00:00 | 602
(23 rows)
statuscodeの条件は,考える余地がある.俺の場合は,
- リダイレクト(301,302)は,二重カウントになるので除外する
- エラー(404や500など)は,カウントしない
という基本方針で,
statuscode IN ('200', '206', '304')
にしている.
あと実行計画は以下の通りで,まぁapache_logテーブルのSeqScanは回避しているので良かろう.
実行計画
----- 実行計画 -----
Sort (cost=1031579.43..1031579.94 rows=201 width=8) (actual time=11926.030..11926.088 rows=23 loops=1)
Sort Key: (date_trunc('day'::text, apache_log.reqtime))
Sort Method: quicksort Memory: 17kB
-> HashAggregate (cost=1031568.73..1031571.74 rows=201 width=8)
(actual time=11925.776..11925.860 rows=23 loops=1)
-> Bitmap Heap Scan on apache_log (cost=8472.23..1031201.20 rows=73506 width=8)
(actual time=815.828..11236.473 rows=155148 loops=1)
Recheck Cond: ((vhost = 'kamoland.com'::text) AND (reqtime >= date_trunc('month'::text, now()))
AND (reqtime < (date_trunc('month'::text, now()) + '1 mon'::interval)))
Filter: ((statuscode = ANY ('{200,206,304}'::text[])) AND (NOT (subplan)))
-> Bitmap Index Scan on apache_log_p (cost=0.00..8453.85 rows=169990 width=0)
(actual time=799.165..799.165 rows=242821 loops=1)
Index Cond: ((vhost = 'kamoland.com'::text) AND (reqtime >= date_trunc('month'::text, now()))
AND (reqtime < (date_trunc('month'::text, now()) + '1 mon'::interval)))
SubPlan
-> Seq Scan on al_useragent_ex (cost=0.00..24.50 rows=6 width=0)
(actual time=0.029..0.029 rows=0 loops=231411)
Filter: ($0 ~~ ex_pattern)
Total runtime: 11926.463 ms