カモランドではApacheのアクセスログをPostgreSQLのテーブルに入れて管理しているが,件数が多い(1300万)ため,アクセス統計のSQLが遅い.そこでパーティショニングを試してみることにした.

PostgreSQLでインターバルパーティションもどき

環境は,
  • PostgreSQL 8.3.4

カラムが1個だけという簡単な例で説明します.

1.基本的なパーティショニング

マスタテーブル作成

CREATE TABLE par (k1 timestamp);
  • 主キー(PK)などの制約類は定義しない

子テーブル作成

パーティションキー(k1)が,2008年1月の場合に適用するパーティションの場合.
CREATE TABLE par_01 (
	CHECK (k1 >= date '2008-01-01' and k1 < date '2008-02-01')
) INHERITS (par);
ALTER TABLE par_01 ADD CONSTRAINT par_01_p PRIMARY KEY (k1);
  • このテーブルに格納するパーティションキー(k1)の範囲を,CHECK制約で定義する.この例では2008年1月
  • PKを定義する
  • インデックスも,必要に応じて作成する

INSERTリダイレクト用のトリガー

CREATE OR REPLACE FUNCTION redirect_insert_par()
RETURNS TRIGGER AS $$
BEGIN
	IF (NEW.k1 >= date '2008-01-01' AND NEW.k1 < date '2008-02-01') THEN
		INSERT INTO par_01 VALUES (NEW.*);
		RETURN NULL;
	ELSE
		RAISE EXCEPTION 'Partition key mapping error.';
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_par BEFORE INSERT ON par
	FOR EACH ROW EXECUTE PROCEDURE redirect_insert_par();

INSERTは,マスタテーブルのparテーブルに対して行えばよい. するとこのトリガーによって,パーティションキー(k1)の値に応じて対応する子テーブルにリダイレクトされて, レコードは子テーブルに作られる.

例えば,

INSERT INTO par values (date '2008-01-15');
とすると,このレコードはpar_01テーブルに格納される.

2.パーティションを追加したい場合

2008年2月のパーティションを追加する場合は,2月用のテーブルを作成し, リダイレクト関数を書き換えればよい
CREATE TABLE par_02 (
	CHECK (k1 >= date '2008-02-01' and k1 < date '2008-03-01')
) INHERITS (par);
ALTER TABLE par_02 ADD CONSTRAINT par_02_p PRIMARY KEY (k1);
CREATE OR REPLACE FUNCTION redirect_insert_par()
RETURNS TRIGGER AS $$
BEGIN
	IF (NEW.k1 >= date '2008-01-01' AND NEW.k1 < date '2008-02-01') THEN
		INSERT INTO par_01 VALUES (NEW.*);
		RETURN NULL;
	ELSIF (NEW.k1 >= date '2008-02-01' AND NEW.k1 < date '2008-03-01') THEN
		INSERT INTO par_02 VALUES (NEW.*);
		RETURN NULL;
	ELSE
		RAISE EXCEPTION 'Partition key mapping error.';
	END IF;
END;
$$
LANGUAGE plpgsql;
こうすれば,
INSERT INTO par values (date '2008-02-04');
の結果は,par_02テーブルに格納される.

しかし,毎月これをやるのは面倒だ

3.インターバルパーティションもどきによる自動化

こういう面倒くささに対して,Oracleの場合は11gの新機能で,インターバル・パーティションというのが提供されている.

これは,パーティションキーが既存のパーティションの範囲を超えたときに自動的にパーティションを追加するものだが, なかなか便利そうなので,この動作をまねてみることにした.

考え方としては,リダイレクト関数内で,パーティションキーに対応するパーティションが見つからなかった場合に, 新しいパーティションを追加するようにすればよい.

パーティションの追加とは具体的には,

  1. 新しいパーティションテーブルの作成
  2. リダイレクト関数の再作成

を行う.

管理テーブルの作成

まず,現在のパーティションの状態を自前で管理するために,テーブルを作成する.
CREATE TABLE partition_manage (
     master_table  text         NOT NULL
    ,start_date    timestamp    NOT NULL
    ,time_interval interval     NOT NULL
    ,pk_cols       text         NOT NULL
    ,partition_key text         NOT NULL
    ,next_no       integer      NOT NULL
    ,next_date     timestamp    NOT NULL
    ,mod_date      timestamp    NOT NULL
    ,reg_date      timestamp    DEFAULT current_timestamp NOT NULL
);
alter table partition_manage
 add constraint partition_manage_p primary key(master_table);

partition_manage テーブルの説明

列No.列名(論理)列名(物理)データ型主キー
1マスタテーブル名master_tabletext NOT NULL
2開始日時start_datetimestamp NOT NULL 
3時間間隔time_intervalinterval NOT NULL 
4主キーカラム名リストpk_colstext NOT NULL 
5パーティションキーカラム名partition_keytext NOT NULL 
6次パーティション番号next_nointeger NOT NULL 
7次パーティション開始日時next_datetimestamp NOT NULL 
8更新日時mod_datetimestamp NOT NULL 
9登録日時reg_datetimestamp NOT NULL 

今回の,parテーブルのパーティショニングの場合は,以下の値を設定する.

カラム名master_tablestart_datetime_intervalpk_colspartition_keynext_nonext_date
par2008-01-011 monthsk1k112008-01-01

master_table
今回のパーティションマスタテーブル
start_date
第1パーティションのパーティションキーの開始日時
time_interval
1パーティションに入るパーティションキーの範囲期間
pk_cols
主キーカラム名をカンマ区切りで並べたリスト
partition_key
パーティションキーのカラム名.timestamp型であること
next_no
次のパーティションの番号.パーティションが追加されると増える
next_date
次のパーティションのパーティションキーの開始日時.パーティションが追加されると増える
mod_date,reg_date
パーティショニングの動作に関係しないので自由

この例だと,

  • k1を主キー,パーティションキーとして
  • 2008-01-01から開始し
  • 1ヶ月毎に新しいパーティションを作成する

という設定になる

関数の作成

以下のPL/pgSQL関数を作る.

No概要関数名備考
1パーティション追加関数fn_create_partition 
2リダイレクト関数(初期状態)fn_partition_redirect_parパーティション追加時に自動再生成
3トリガー関数redirect_insert_par 

1) パーティション追加関数
CREATE OR REPLACE FUNCTION fn_create_partition(
	IN i_m_table text,
	IN i_redirect_func_name text,
	IN i_partition_key timestamp)
RETURNS integer AS $$
DECLARE
	rec_manage partition_manage%rowtype;
	v_stat integer;
	v_ds text;
	v_de text;
	v_newtab text;
	v_buff text;
	v_date_s date;
	v_date_e date;
	v_func_cmd text;
BEGIN
	-- 管理テーブルをロック付きで取得する
	select *
	into rec_manage
	from partition_manage
	where master_table = i_m_table
	for update;

	if (i_partition_key < rec_manage.next_date) THEN
		-- 他のトランザクションが作成した場合は
		-- パーティション作成を中止する
		RETURN 1;
	END IF;

	-- パーティションテーブル作成
	v_ds := to_char(rec_manage.next_date, 'YYYY-MM-DD');
	v_de := to_char(rec_manage.next_date + rec_manage.time_interval, 'YYYY-MM-DD');
	v_newtab := i_m_table || '_' || rec_manage.next_no;

	-- 1. テーブル作成
	v_buff := 'create table '|| v_newtab || ' (' ||
		'check (' || rec_manage.partition_key || ' >= date ''' || v_ds ||
		''' and ' || rec_manage.partition_key || ' < date ''' || v_de ||
		''')' || ') inherits (' || i_m_table || ');';
	EXECUTE v_buff;

	-- 2. 主キー追加
	v_buff := 'alter table ' || v_newtab ||
		' add constraint ' || v_newtab || '_p primary key(' ||
		rec_manage.pk_cols || ');';
	EXECUTE v_buff;

	-- 3. テーブルコメント追加
	v_buff := 'comment on table ' || v_newtab || ' is ''(' || v_ds || ',' ||
		v_de || '). Partition of ' || i_m_table || ''';';
	EXECUTE v_buff;

	-- パーティションリダイレクトFunctionの再作成
	v_buff := 'BEGIN';
	v_date_s := rec_manage.start_date;
	v_date_e := rec_manage.start_date + rec_manage.time_interval;

	FOR i IN 1..rec_manage.next_no LOOP
		v_newtab := i_m_table || '_' || i;

		IF (i = 1) THEN
			v_buff := v_buff || ' IF';
		ELSE
			v_buff := v_buff || ' ELSIF';
		END IF;
		v_buff := v_buff || ' (i_new.' || rec_manage.partition_key ||
			' >= date ''' || to_char(v_date_s, 'YYYY-MM-DD') || '''' ||
			' AND i_new.' || rec_manage.partition_key || ' < date '''  ||
			to_char(v_date_e, 'YYYY-MM-DD') || ''') THEN' ||
			' INSERT INTO ' || v_newtab || ' VALUES (i_new.*);' ||
			' RETURN 0;';

		v_date_s := v_date_s + rec_manage.time_interval;
		v_date_e := v_date_e + rec_manage.time_interval;
	END LOOP;

	v_buff := v_buff || ' ELSE RETURN 1; END IF; END;';

	v_func_cmd :=
		'CREATE OR REPLACE FUNCTION ' || i_redirect_func_name ||
		'(IN i_new ' || i_m_table || ')
		RETURNS integer AS  '
		|| quote_literal(v_buff)
		|| ' LANGUAGE plpgsql;' ;
	EXECUTE v_func_cmd;

	-- 管理テーブルを更新する
	update partition_manage
	set next_no = next_no + 1,
		next_date = next_date + time_interval,
		mod_date = current_timestamp
	where master_table = i_m_table;

	RETURN 0;
END;
$$
LANGUAGE plpgsql;

2) リダイレクト関数(初期状態)
CREATE OR REPLACE FUNCTION fn_partition_redirect_par(IN i_new par)
RETURNS integer AS $$
BEGIN
	-- 常にパーティションが存在しないという扱いにする
	RETURN 1;
END;
$$
LANGUAGE plpgsql;

3) トリガー関数
CREATE OR REPLACE FUNCTION redirect_insert_par()
RETURNS TRIGGER AS $$
DECLARE
	v_stat integer;
BEGIN
	SELECT fn_partition_redirect_par(NEW) INTO v_stat;
	IF (v_stat = 0) THEN
		-- パーティションが存在した場合
		RETURN NULL;
	ELSE
		-- パーティションが存在しなかった場合は
		-- パーティションを追加する
		SELECT fn_create_partition('par', 'fn_partition_redirect_par',  NEW.k1) INTO v_stat;

		-- 再度INSERTする
		SELECT fn_partition_redirect_par(NEW) INTO v_stat;
		IF (v_stat = 0) THEN
			RETURN NULL;
		ELSE
			RAISE EXCEPTION 'Partition key mapping error. After Partition added.';
		END IF;
	END IF;
END;
$$
LANGUAGE plpgsql;

1)のパーティション追加関数はマスタテーブルに依存しないように汎用化されているが, その他の関数は,マスタテーブル(今回はparテーブル)に依存する.

パーティション追加関数では,動的SQLでテーブル作成とFunctionの書き換えをしているのが, なかなか強烈かも知れない.

4.実行例

その1

INSERT INTO par values (date '2008-01-15');
→以下のパーティションテーブルが作成されて,その中にレコードができています.

テーブル名テーブルコメント
par_1(2008-01-01,2008-02-01). Partition of par

※パーティションキーの範囲を,テーブルのコメントに設定するようにしています.

その2

INSERT INTO par values (date '2008-02-02');
→以下のパーティションテーブルが作成されて,その中にレコードができています.

テーブル名テーブルコメント
par_2(2008-02-01,2008-03-01). Partition of par

その3

INSERT INTO par values (date '2008-01-03');
→パーティションテーブルpar_1に,レコードができています.

...この方法で,カモランドのApacheのログ合計1300万行をパーティション化できたので, まぁ動いているようだ.(3ヶ月毎のパーティションで,計23パーティション)

また,このパーティショニングによって,当月レポート用のクエリーの実行時間が62秒から16秒に短縮されたので,まぁ良かった.

ただクエリー性能については, Constraint Exclusion(Oracleで言うところのパーティションプルーニング) が効くかどうかの動作でもう少し調べておきたいところがあるので,後日まとめる予定.


© 2024 KMIソフトウェア