色々統計を取るにはDBの方が便利なので,ApacheのアクセスログをDBに格納することにした.DBはPostgreSQL 8.3を使う.

DBの定義

以下のテーブルを作成した.
テーブルNoテーブル名(論理)テーブル名(物理)
1Apacheログテーブルapache_log
2除外ユーザーエージェントテーブルal_useragent_ex

1.Apacheログテーブル

アクセスログを格納するテーブルは,以下の定義で作成した. 基本的にはcombined形式ログに含まれる項目を列挙したものだが,最後の2カラムを追加している.あ,vhostも違うか.

Apacheログテーブル
物理名:apache_log

列No.列名(論理)列名(物理)データ型必須主キー
1VirtualHost名vhosttextYes1
2リモートホストhostnametextYes 
3identユーザidentusertext  
4basic認証ユーザbasicusertext  
5リクエスト日時reqtimetimestampYes2
6HTTPメソッドmethodtextYes 
7リクエストターゲットtargettextYes 
8プロトコルprototextYes 
9レスポンスコードstatuscodetextYes 
10レスポンスバイト数bytesinteger  
11リファラーreferertext  
12ユーザーエージェントuseragenttextYes 
13アクセスログ行番号loglinenointegerYes3
14登録日時reg_datetimestampYes 

アクセスログ行番号は,アクセスログファイル(access_logとか)内での行番号. これを主キーに組み込むことで,DB登録の時に同じアクセスログファイルを繰り返し使っても, リクエストがDBに重複登録されないようにしている.

データのロードは,Perlのプログラムで行った.access_logのファイルを読んで, DBI経由でApacheログテーブルにINSERTした.

2.除外ユーザーエージェントテーブル

検索エンジンなどのロボットからのアクセスは,統計を取るときに除外することが多い. ページに人気があるかどうかを調べる上で,ロボットからのアクセス数は意味がないからだ. (もっとも,これは統計を取る観点によりけりです)

そこで,その除外処理に必要な情報もテーブルに登録しておく.

除外ユーザーエージェントテーブル
物理名:al_useragent_ex

列No.列名(論理)列名(物理)データ型必須主キー
1除外パターンex_patterntextYes1
2登録日時reg_datetimestampYes 

除外するユーザーエージェントを,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


© 2024 KMIソフトウェア