Postgres Plus Advanced Server 9.3 パーティションテーブルの特徴と性能検証レポート ~ データロード編 ~ v1.1 テクノロジーコンサルティング事業統括オープンソース部高橋智雄 2014 年 7 月
変更履歴 版 日付 作成 修正者 説明 1.0 2014/5/19 日本 HP 高橋智雄 初版作成 1.1 2014/7/8 日本 HP 高橋智雄 表現を微修正 2
はじめに 本書は 2014 年 3 月に実施した PostgreSQL 9.3 および Postgres Plus Advanced Server( 以下 PPAS) 9.3 のパーティションテーブルのデータロード性能を測定した結果をまとめたものです コミュニティ版の PostgreSQL では パーティションテーブルの作成方法が複雑で しかもパーティション数が多くなるとパーティションテーブルへのデータロード処理に時間がかかります PPAS でパーティションテーブルの作成が簡素化され データロード処理の時間が大幅に改善されていることを検証で確認しました 3
目次 1. PostgreSQL のパーティションテーブル 2. PPAS のパーティションテーブル 3. 検証内容 4. 検証結果 5. まとめ 4
1. PostgreSQL のパーティションテーブル
1-1. PostgreSQL のパーティションテーブルの構成要素 親テーブル 子テーブル 継承 制約 トリガー PostgreSQLのパーティショニングは専用の組み込み機能ではなく 継承 CHECK 制約 トリガー を組み合わせて実現します 継承テーブルに親子関係を定義し 子は親の列構成を引き継ぎます 親テーブルへのクエリは子テーブルも含むように自動的に展開されます CHECK 制約パーティションに含まれるデータの範囲を定義するために使用します トリガー親テーブルに対してINSERTされたデータを子テーブルに振り分けるため 親テーブルにトリガーを設定します 6
1-2. パーティションテーブルの作成例 (1/3) 1. 親テーブルを作成 create table part_test ( customer_id char(12) not null, start_datetime char(14) not null, ); パーティションのキーとする列 2. 子テーブルを作成 create table part_test_201404( check( start_datetime >= '20140401000000' and start_datetime < '20140501000000' ) ) inherits(part_test); CHECK 制約 2014 年 4 月のデータを入れるパーティション 継承 7
1-2. パーティションテーブルの作成例 (2/3) 3. トリガー用関数を作成 create or replace function func_part_test_insert() returns trigger as $$ begin if ( new.start_datetime >= '20140401000000' and new.start_datetime < '20140501000000' ) then insert into part_test_201404 values (new.*); elsif ( new.start_datetime >= '20140501000000' and new.start_datetime < '20140601000000' ) then insert into part_test_201405 values (new.*); elsif ( else raise exception 'ERROR! Date out of range.'; end if; return null; end; $$ パーティション数分 elsifを続ける language plpgsql; 8
1-2. パーティションテーブルの作成例 (3/3) 4. 親テーブルにトリガーを作成 create trigger trg_part_test_insert before insert on part_test for each row execute procedure func_part_test_insert(); トリガーにより 親テーブルへの INSERT が子テーブルに振り分けられる 9
1-3. パーティションテーブルの問題点 パーティション数が多い場合 INSERT が遅い 例えば 1000 番目のパーティションにデータを振り分ける場合 if 文が1000 回実行されるため遅い パーティション数が多くても 1 番目のパーティションへのINSERTは遅くない そもそもPL/pgSQLはインタープリタであるため 実行速度はそれほど速くない パーティションの追加時にメンテナンスが必要 パーティションを追加するたびに トリガー用関数を修正する必要がある 10
1-4. 対処方法 (1/2) 1. トリガー用関数を動的関数にする create or replace function func_part_test_calllog_insert() returns trigger as $$ declare part_table text; -- パーティション テーブルの名前 begin part_table := 'part_test_calllog_' substring(new.start_datetime from 1 for 6); execute 'insert into ' part_table ' values(($1).*)' using new; return null; end; $$ language plpgsql; パーティション数に関わらず実行時間は一定 パーティション追加時も修正不要 検証で時間を測定 11
1-4. 対処方法 (2/2) 2. トリガー用関数を C 言語で作成する 本検証では未検証ですが 以下にサンプルコードがあります https://github.com/matheusoliveira/pg_partitioning_tests PostgreSQLエンタープライズ コンソーシアム (PGECons) の2013 年度 WG1 活動報告に C 言語を使用したパーティションの性能検証結果が公開されています https://www.pgecons.org/download/works_2013/ 上記からダウンロード可能 12
2. PPAS のパーティションテーブル
2-1. PPAS のパーティションテーブルの作成例 Postgres Plus Advanced Server 9.3 でのパーティションテーブル作成例 create table part_test( customer_id char(12) not null, start_datetime char(14) not null, ) partition by range (start_datetime) ( partition part_test_201403 values less than('20140401000000'), partition part_test_201404 values less than('20140501000000'), ); レンジパーティションは Oracle Database と同様の構文で作成可能 14
2-2. パーティションテーブルの実体 システムカタログで作成したパーティションを確認 partdb=# d リレーションの一覧スキーマ 名前 型 所有者 ----------+----------------------------+----------+-------------- public part_test テーブル enterprisedb public part_test_part_test_201403 テーブル enterprisedb public part_test_part_test_201404 テーブル enterprisedb public part_test_part_test_201405 テーブル enterprisedb public part_test_part_test_201406 テーブル enterprisedb public part_test_part_test_201407 テーブル enterprisedb (6 行 ) partdb=# d part_test_part_test_201404 テーブル "public.part_test_part_test_201404" 列 型 修飾語 ----------------+---------------+---------- customer_id character(12) not null start_datetime character(14) not null 検査制約 : パーティションの実体は PostgreSQL 同様継承と CHECK 制約を使用して作成された子テーブル "part_test_part_test_201404_partition" CHECK (ROW(start_datetime) IS NOT NULL AND start_datetime >= '20140401000000'::character(14) AND start_datetime < '20140501000000'::character(14)) 継承 : part_test 15
2-3. パーティションテーブルのトリガー 親テーブルへの INSERT を子テーブルに振り分けるトリガーの確認 partdb=# select tgname, tgfoid, tgisinternal from pg_catalog.pg_trigger; tgname tgfoid tgisinternal ----------------------------+--------+-------------- part_test_part_ins_1670323 5026 t part_test_part_upd_1670324 5027 t part_test_part_upd_1670330 5027 t part_test_part_upd_1670336 5027 t part_test_part_upd_1670342 5027 t part_test_part_upd_1670348 5027 t part_test_part_upd_1670354 5027 t (7 行 ) partdb=# select pg_get_functiondef(5026); pg_get_functiondef ------------------------------------------------------------------ CREATE OR REPLACE FUNCTION pg_catalog.partition_insert_trigger()+ RETURNS trigger + LANGUAGE internal + IMMUTABLE STRICT + AS $function$partition_insert_trigger$function$ + (1 行 ) LANGUAGE internal は C 言語による関数であることを示している 自動的に C 言語関数を使用したトリガーが作成される 16
2-4. PPAS パーティションテーブルの特徴まとめ Oracle Database と同様の構文でパーティションの作成が可能 レンジパーティション リストパーティションの作成が可能 サブパーティションの作成も可能 ALTER TABLE 文でパーティションのADD DROP SPLIT 等も可能 パーティションの実体はPostgreSQLと同様の構造 パーティションは親テーブルを継承する子テーブルとして作られる トリガー用関数として C 言語関数が内部的に自動設定される pg_catalog.partition_insert_trigger() pg_catalog.partition_update_trigger() 17
3. 検証内容
3-1. 検証目的 PostgreSQL では 数多くのパーティションを作成することは推奨されていない パーティション数が多いと 実際にどの程度性能影響があるのか基礎数値を測定する PostgreSQL と PPAS におけるパーティション性能の差を測定し パーティションを多用するシステムにおいてはどちらの DBMS が有利であるか確認する 19
3-2. 検証方法と内容 PostgreSQL PPAS の双方で約 2000 のパーティションを持つテーブルを作成し 1 番目 481 番目 961 番目 1441 番目 1921 番目のパーティションにデータをロードし 所要時間を測定する データのロードは以下の手順で行う あらかじめデータロード用 CSVファイルを作成 COPY 文を使用してテーブルへデータをロード 測定は以下のパーティション構成に対して実施 PostgreSQL( 静的トリガ関数による振り分け実装 ) PostgreSQL( 動的トリガ関数による振り分け実装 ) PPAS 20
3-3. 検証環境ハードウェア / ソフトウェア構成 ハードウェアサーバー HP ProLiant SL335s G7 Hypervisor OS PostgreSQL PPAS CPU メモリディスクコントローラ内蔵 HDD NIC AMD Opteron Processor 4176HE 2.4GHz 20MB Cache 2P/12Core 32GB HP Smart アレイ B110i SATA RAID コントローラー HP 1TB 3Gbps SATA 7.2k rpm 2.5inch MDL HDD 4 (RAID10) HP NC362i 内蔵デュアルポート Gigabit サーバーアダプター VMware ESXi 5.0.2/4vCPU/16GB メモリ Red Hat Enterprise Linux 6.2 (kernel 2.6.32-220) x86_64 PostgreSQL 9.3.0 64bit Postgres Plus Advanced Server 9.3.1.3 64bit 21
3-4. データベースパラメータ PostgreSQL PPASの双方とも以下のパラメータを設定 shared_buffers:4gb checkpoint_segments:64 listen_addresses:* その他のパラメータはデフォルト値を使用 22
4. 検証結果
4-1. PostgreSQL のパーティション性能 (1/2) 静的トリガーと動的トリガーの比較 ロード時間 ( 秒 ) 2,000 1,800 1,600 1,400 1,200 1,000 800 600 400 200 0 10 万行のロードにかかる時間 1,883 1,248 736 400 22 11 10 11 10 9 0 500 1000 1500 2000 ロード対象パーティションのパーティション順 静的トリガ 動的トリガ 静的トリガ関数では パーティション順にロード時間がほぼ比例 動的トリガ関数では パーティション順に関係なくロード時間は一定 24
( 参考 ) 静的トリガー関数を使用している際の CPU 使用率 データロード時の CPU 使用状況 100 90 80 70 60 50 40 30 20 10 0 CPU0 CPU1 CPU2 0 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1,000 1,050 1,100 1,150 1,200 1,250 1,300 1,350 1,400 1,450 1,500 1,550 1,600 1,650 1,700 1,750 1,800 CPU 使用率 (%) CPU3 経過時間 ( 秒 ) 常に 1 つの CPU の使用率が 100% になっている 25
4-1. PostgreSQL のパーティション性能 (2/2) 動的トリガーを使用した場合のロード行数と実行時間 ロード行数と実行時間 120 100 99 100 97 100 98 ロード時間 ( 秒 ) 80 60 40 20 52 49 49 49 50 5 倍 10 倍 11 10 11 10 9 10 万行 50 万行 100 万行 0 0 500 1000 1500 2000 ロード対象パーティションのパーティション順 ロード行数によらず パーティション順に関係なくロード時間は一定 ロード時間はロードする行数に比例 26
4-2. PPAS のパーティション性能 (1/2) ロード時間 ( 秒 ) 250 200 150 100 50 0 ロード行数と実行時間 194 190 200 174 10 倍 100 倍 19 18 20 18 21 2 2 3 2 2 0 200 400 600 800 1000 1200 1400 1600 1800 2000 ロード対象パーティションのパーティション順 191 10 万行 100 万行 1000 万行 ロード行数によらず パーティション順に関係なくロード時間は一定 ロード時間はロードする行数に比例 27
4-2. PPAS のパーティション性能 (2/2) ロード時間 ( 秒 ) 120 100 80 60 40 20 0 PPAS vs PostgreSQL( 動的トリガー ) 10 5 倍速 99 2 5 倍速 PostgreSQL-10 万行 PPAS-10 万行 PostgreSQL-100 万行 PPAS-100 万行 19 PPAS は PostgreSQL の 5 倍のスピードでロード可能! 28
5. まとめ
まとめ PostgreSQLのパーティションテーブルの問題点一般的に使用される静的トリガー関数では パーティション数が多くなるとデータロードの時間がかかる パーティション追加時にメンテナンスが必要 問題点への対処方法 動的トリガー関数を使用するとデータロード性能が安定し メンテナンスも不要 PPASのパーティションテーブルの特徴 Oracleと同様のSQL 構文で作成可能 動的トリガーと同様にデータロード性能は安定 動的トリガーよりも高速 ( 検証では5 倍 ) 30
Thank you