ログ解析にパーティショニングを利用してみよう
Hitoshi Harada
ここまでで一通り分析を終えました。ある時点での分析を行うことももちろんのこと、継続的にログを分析していき結果を比較していきたいものです。今回のデータは6日ばかりを対象にしているためデータサイズは大きくありませんでしたが、1ヶ月、1年と経過するにつれてサイズが増大します。ここでは、分析を継続していくために必要なテーブルパーティショニング（分割）の技術についてご紹介します。パーティショニングについてはこちらでも紹介しています。併せてお読み下さい。
PostgreSQLにおけるパーティショニング
9.0 現在、PostgreSQLにおけるパーティショニング機能とは、単一ノードにおける水平分割です。つまり、1マシン上において、行を選り分けることでテーブルを分割します。世の中のRDBMSには垂直分割（列による選り分け）をサポートしたり複数ノード（複数のマシン上にテーブルを分散配置する）の機能を持つものがありますが、PostgreSQLではそういった機能をコアとして実装していません。 開発コミュニティでは複数マシン上での分散配置やノードを跨る検索／集計処理を行う提案も出てきているため、将来そういった機能が実装されるかもしれません。
パーティショニングの必要性
データを分割すると、範囲を指定した参照が高速化されます。特に日付などのキーを用いて範囲を限定した参照を行う場合に、不要なテーブルをキャッシュしなくてよくなったり、予め読むべきテーブルを限定したりすることができるためです。また、削除やデータの移行／バックアップについても、範囲を限定した操作が容易かつ高速になります。
大まかな流れ
今回は何もないところからパーティション構成を作成し、データを流し込みます。分割のキーをアクセスの日付を使って分割したテーブルを作成します。こうすることで、1テーブルあたりのボリュームを見積もることが容易になるためです。作業の流れは以下の通りです。
- 親テーブルを作る
- 子テーブルを親テーブルと同じ定義で作り、継承関係を定義する
- INSERT用のトリガを実装する
- 親テーブルにデータを投入する
- 検索してみる
一つずつ見ていきましょう。
親テーブルを作る
パーティショニングを行ったとしても、クライアント側からはこれまでと同様に一つのテーブルに見せることができます。そのための親テーブルを最初に作成します。構造は分割前と同様です。
CREATE TABLE log_table_p( sessionid char(32), atime timestamp, seq int, elapse int, action text, params text );
親テーブルなので "_p" をテーブル名に付けました。親テーブルは空のまま特に行うことはありません。データ投入後、クライアントはこの親テーブルに対して SELECT を発行しますが、実際には PostgreSQL が子テーブルにデータを探しに行きます。
子テーブルを親テーブルと同じ定義で作り、継承関係を定義する
少しややこしいように聞こえますが、実際はそれほど難しくありません。子テーブルは親テーブルと同じ定義ですので、CREATE TABLE の LIKE オプションを使うことができます。
CREATE TABLE log_table_20100513(
LIKE log_table_p
INCLUDING DEFAULTS INCLUDING INDEXES,
CHECK ('2010-05-13' <= atime AND atime < '2010-05-14')
) INHERITS (log_table_p);
これで親と同じ定義のテーブルが作成されました。
log_db=# \d log_table_20100513
Table "public.log_table_20100513"
Column | Type | Modifiers
-----------+-----------------------------+-----------
sessionid | character(32) |
atime | timestamp without time zone |
seq | integer |
elapse | integer |
action | text |
params | text |
INCLUDING オプションで、各列のデフォルト値とインデックスを同様に定義しています。また CHECK オプションによってこのテーブルに5月13日のアクセスログのみが保持されることを定義しています。さらに、INHERITS で親テーブルを指定します。この INHERITS により、両テーブルが親子であることが PostgreSQL にもわかるようになります。
NOTICE: merging column "sessionid" with inherited definition NOTICE: merging column "atime" with inherited definition NOTICE: merging column "seq" with inherited definition NOTICE: merging column "elapse" with inherited definition NOTICE: merging column "action" with inherited definition NOTICE: merging column "params" with inherited definition
実行すると上記がログに出力されますが、これは「LIKEとINHERITSの両方に由来する同一のカラムを一つにしました」という意味で特に気にする必要はありません。
INSERT用のトリガを実装する
PostgreSQLは親テーブルの参照に対して子テーブルへのアクセスを自動で変換しますが、データの追加についてはユーザがトリガで実装する必要があります。
CREATE FUNCTION log_insert_trigger_func() RETURNS TRIGGER AS $$
DECLARE
child text; -- パーティション・テーブルの名前
BEGIN
child := 'log_table_' || to_char(new.atime, 'YYYYMMDD');
-- new を渡す
EXECUTE 'INSERT INTO ' || child || ' VALUES(($1).*)' USING new;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
トリガ関数の暗黙変数newの値を使ってテーブル名を決定し、新たなINSERT文を発行しています。この関数をトリガとして登録します。
CREATE TRIGGER log_insert_trigger BEFORE INSERT ON log_table_p FOR EACH ROW EXECUTE PROCEDURE log_insert_trigger_func();
親テーブルにデータを投入する
ここまでできたら、後はデータを投入するだけです。INSERTに対するトリガはCOPYにも有効なので、これまで通りCOPYを使います。
=# SET client_encoding TO sjis; =# COPY log_table_p FROM '/var/log/access_log.csv' CSV NULL '';
たったこれだけです。
検索してみる
=# EXPLAIN SELECT * FROM log_table_p;
QUERY PLAN
----------------------------------------------------------------------
Result (cost=0.00..4785.64 rows=177164 width=111)
-> Append (cost=0.00..4785.64 rows=177164 width=111)
-> Seq Scan on log_table_p
(cost=0.00..13.30 rows=330 width=212)
-> Seq Scan on log_table_20100513 log_table_p
(cost=0.00..784.12 rows=29212 width=109)
-> Seq Scan on log_table_20100514 log_table_p
(cost=0.00..716.60 rows=26460 width=111)
-> Seq Scan on log_table_20100515 log_table_p
(cost=0.00..715.96 rows=26596 width=110)
-> Seq Scan on log_table_20100516 log_table_p
(cost=0.00..877.58 rows=32458 width=111)
-> Seq Scan on log_table_20100517 log_table_p
(cost=0.00..833.50 rows=30950 width=111)
-> Seq Scan on log_table_20100518 log_table_p
(cost=0.00..844.58 rows=31158 width=112)
(9 rows)
正しく参照できているようです。アクセス時間で絞り込むと、スキャンするテーブルの数が減ります。設定パラメータの constraints_exclusion が "partition" または "on" になっていることを確認しましょう。この値が "off" だとアクセスする子テーブルが減りません。
=# EXPLAIN SELECT count(*) FROM log_table_p
WHERE atime BETWEEN '2010-05-13 12:00' AND '2010-05-14 12:00';
QUERY PLAN
-------------------------------------------------------------------
Aggregate (cost=1866.57..1866.58 rows=1 width=0)
-> Append (cost=0.00..1794.03 rows=29013 width=0)
-> Seq Scan on log_table_p (cost=0.00..14.95 rows=2 width=0)
Filter: ((atime >= '2010-05-13 12:00:00'::timestamp)
AND (atime <= '2010-05-14 12:00:00'::timestamp))
-> Seq Scan on log_table_20100513 log_table_p
(cost=0.00..930.18 rows=20908 width=0)
Filter: ((atime >= '2010-05-13 12:00:00'::timestamp)
AND (atime <= '2010-05-14 12:00:00'::timestamp))
-> Seq Scan on log_table_20100514 log_table_p
(cost=0.00..848.90 rows=8103 width=0)
Filter: ((atime >= '2010-05-13 12:00:00'::timestamp)
AND (atime <= '2010-05-14 12:00:00'::timestamp))
(8 rows)
アクセスするテーブル数が少なくなるということはI/O負荷が小さくなる事に加え、メモリ上のキャッシュ効率もよくなるため、性能の向上が期待できます。
おまけ
子テーブルを作成したり管理するのにはまだ一苦労あり、PostgreSQLのデフォルトの機能だけでは手間がかかることがあります。ここでは、それらの手間をさらに省略する方法を紹介します。
DO文を使って初期テーブル作成を簡略化する
最初に子テーブルを纏めて定義するのが面倒です。9.0から導入されたDO文を使って簡略化してみます。
DO $$
DECLARE
dayoff int;
logday date;
BEGIN
-- 5/13～5/18のテーブルを作る
FOR dayoff IN 0 .. ('2010-05-18'::date - '2010-05-13'::date) LOOP
logday = '2010-05-13'::date + dayoff;
EXECUTE 'CREATE TABLE log_table_' ||
to_char(logday, 'YYYYMMDD') || '(' ||
'LIKE log_table_p INCLUDING DEFAULTS INCLUDING INDEXES, ' ||
'CHECK(''' || to_char(logday, 'YYYY-MM-DD') || ''' <= atime ' ||
'AND atime < ''' || to_char(logday + 1, 'YYYY-MM-DD') || ''')'
')INHERITS (log_table_p)';
END LOOP;
END;
$$;
DO文は、PL/pgSQL等のプロシージャ言語を関数の定義なしにそのまま実行することができる機能です。今回のように一度だけの実行でよいが動的なSQLをたくさん発行したいときなどに特に役立ちます。作ったテーブルを一括して削除する場合も、
DO $$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT tablename FROM pg_tables
WHERE tablename ~ E'^log_table_\\d+' LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || rec.tablename;
END LOOP;
END;
$$;
のように実行すれば子テーブルが全て削除できます。
ログの日付が新しくなったときに子テーブルを自動で追加する
日が進むにつれて子テーブルは次々に追加しなければなりませんが、それを全て手作業でやるのは骨が折れます。ここまでで紹介したトリガに一工夫加えて、新規日付のログが来たら自動的にテーブルを作成するようにトリガを修正してみます。
CREATE OR REPLACE FUNCTION log_insert_trigger_func()
RETURNS TRIGGER AS $$
DECLARE
child text; -- パーティション・テーブルの名前
result record;
BEGIN
child := 'log_table_' || to_char(new.atime, 'YYYYMMDD');
SELECT INTO result * FROM pg_tables WHERE tablename = child;
IF NOT FOUND THEN
EXECUTE 'CREATE TABLE ' || child || '(' ||
'LIKE log_table_p INCLUDING DEFAULTS INCLUDING INDEXES, ' ||
'CHECK(''' || to_char(new.atime, 'YYYY-MM-DD') ||
''' <= atime ' || 'AND atime < ''' ||
to_char(new.atime::date + 1, 'YYYY-MM-DD') || '''))' ||
'INHERITS (log_table_p)';
END IF;
-- new を渡す
EXECUTE 'INSERT INTO ' || child || ' VALUES(($1).*)' USING new;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
関数を定義しなおす (OR REPLACEを付けました) だけでトリガの再登録は不要です。
パーティショニングまとめ
ログ格納テーブルの分割について手順を簡単に纏めました。PostgreSQLの以前のバージョンに比べ、constraint_exclusionパラメータ、PL/pgSQLにおけるUSING句、LIKEとINHERITS、DO文などを使うととても簡単にテーブル分割を実現することができます。逆に言えばバージョンによる若干の違いが手順の中で出てきます。今回は最新の9.0を使っての手法をご紹介しましたが、バージョン毎の違いや細かな機能のメリット・デメリットなどはこちらも参考にするとよいでしょう。 冒頭の繰り返しになりますが、ログの分析は継続して行ってこそ意味があります。継続して実施できるような仕組みを作り、余計な手間は極力省略しておきましょう。