差分表示


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

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


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

&ltitle(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.除外ユーザーエージェントテーブル
検索エンジンなどのロボットからのアクセスは,統計を取るときに除外することが多い.
ページに人気があるかどうかを調べる上で,ロボットからのアクセス数は意味がないからだ.
(もっとも,これは統計を取る観点によりけりです)

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

&ltitle(除外ユーザーエージェントテーブル);~
物理名:al_useragent_ex
,列No.,列名(論理),列名(物理),データ型,必須,主キー
,1,除外パターン,ex_pattern,text,Yes,1
,2,登録日時,reg_date,timestamp,Yes, 

除外するユーザーエージェントを,SQLのLIKE書式でex_patternに登録する.

&ltitle(除外ユーザーエージェントテーブルの内容);
,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%

この内容は,これからもログを適宜調べて増やしていく必要があるので面倒...

**統計を取得する例
このテーブルを使って,今月の日別リクエスト数の統計を求める例.

&ltitle(今月の日別リクエスト);
--(
''・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は回避しているので良かろう.

&ltitle(実行計画);
--(
----- 実行計画 -----
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
--)


© 2020 KMIソフトウェア