カモランドではApacheのアクセスログをPostgreSQLのテーブルに入れて管理しているが,件数が多い(1300万)ため,アクセス統計のSQLが遅い.そこでパーティショニングを試してみることにした.
PostgreSQLでインターバルパーティションもどき環境は,
カラムが1個だけという簡単な例で説明します.
1.基本的なパーティショニング
マスタテーブル作成CREATE TABLE par (k1 timestamp);
子テーブル作成パーティションキー(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);
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');
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'); しかし,毎月これをやるのは面倒だ
3.インターバルパーティションもどきによる自動化こういう面倒くささに対して,Oracleの場合は11gの新機能で,インターバル・パーティションというのが提供されている.
これは,パーティションキーが既存のパーティションの範囲を超えたときに自動的にパーティションを追加するものだが, なかなか便利そうなので,この動作をまねてみることにした. 考え方としては,リダイレクト関数内で,パーティションキーに対応するパーティションが見つからなかった場合に, 新しいパーティションを追加するようにすればよい. パーティションの追加とは具体的には,
を行う.
管理テーブルの作成まず,現在のパーティションの状態を自前で管理するために,テーブルを作成する.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 テーブルの説明
今回の,parテーブルのパーティショニングの場合は,以下の値を設定する.
この例だと,
という設定になる
関数の作成以下のPL/pgSQL関数を作る.
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.実行例その1INSERT INTO par values (date '2008-01-15');
※パーティションキーの範囲を,テーブルのコメントに設定するようにしています.
その2INSERT INTO par values (date '2008-02-02');
その3INSERT INTO par values (date '2008-01-03'); ...この方法で,カモランドのApacheのログ合計1300万行をパーティション化できたので, まぁ動いているようだ.(3ヶ月毎のパーティションで,計23パーティション) また,このパーティショニングによって,当月レポート用のクエリーの実行時間が62秒から16秒に短縮されたので,まぁ良かった. ただクエリー性能については, Constraint Exclusion(Oracleで言うところのパーティションプルーニング) が効くかどうかの動作でもう少し調べておきたいところがあるので,後日まとめる予定. |