PostgreSQL 9.3パーティションの効果検証

Size: px

Start display at page:

Download "PostgreSQL 9.3パーティションの効果検証"

おきまさみやのじょう
6 years ago
Views:

1 PostgreSQL 9.3 パーティションの効果検証テクノロジーコンサルティング事業統括オープンソース部高橋智雄 2014 年 7 月

2 変更履歴版日付作成修正者説明 /12/16 日本 HP 高橋智雄初版作成 /7/8 日本 HP 高橋智雄テンプレート等を修正 2

3 はじめに本書は PostgreSQL9.3 のパーティション表の検索に関する性能を評価したレポートです性能評価は同一の SQL 文を非パーティション表とパーティション表で実行しその処理時間の差異を比較することで評価していますパーティション表の効果としてメンテナンス性の向上に加え検索処理の性能が向上する点を検証しました 3

4 目次 1. 検証環境構成と事前準備 (1) インフラ構成 (2) PostgreSQL 環境 (3) 非パーティション表作成 (4) パーティション表作成 2. 検索処理時間測定 (1) 実行する検索処理 (2) 非パーティション表検索 ( デフォルト : インデックス使用 ) (3) 非パーティション表検索 (Seq Scan) (4) パーティション表検索 ( デフォルト : Seq Scan ) (5) パーティション表検索 ( インデックス使用 ) 3. まとめ 4

5 1. 検証環境構成と事前準備

6 1-1. インフラ構成ハードウェアサーバー HP ProLiant SL335s G7 CPU AMD Opteron 4176HE 2.4GHz 2P/12Core メモリ 32GB 内蔵 HDD 900GB 4 (RAID10により論理容量は1.8TB) NIC Gigabit Ethernet 仮想化環境仮想化ソフトウェア VMware ESXi 5.0u2 仮想マシンCPU 4core 仮想マシンメモリ 16GB 仮想マシンHDD 500GB 1 OS Red Hat Enterprise Linux 6.2 (x86_64) PostgreSQL PostgreSQL bit 6

7 1-2. PostgreSQL 9.3 環境 (1/2) PostgreSQL 動作環境 PostgreSQL 起動ユーザーインストールディレクトリデータディレクトリ WALディレクトリ ARCHIVEファイル配置ディレクトリ postgresユーザー PATH 環境変数 PGDATA 作業用ディレクトリ postgres /usr/local/pgsql /disk1/data /disk2/pg_xlog /disk3/archive /usr/local/pgsql/bin を追加 /disk1/data /work (postgres ユーザーで読み書き可能となっている前提 ) データディレクトリ WAL ディレクトリは実際には同一 LUN 上に作成 7

8 1-2. PostgreSQL 9.3 環境 (2/2) PostgreSQL の主なパラメータ [/disk1/data/postgresql.conf] listen_addresses = '*' shared_buffers = 4GB checkpoint_segments = 64 logging_collector = on 8

9 1-3. 非パーティション表作成 (1/5) テーブル作成 # su - postgres $ createdb testdb $ psql testdb 携帯電話の通話情報と料金を管理するテーブルのイメージ以下の SQL を実行 create table test_calllog( customer_id char(12) not null, phone_number char(11) not null, call_start_date char(8) not null, call_start_time char(6) not null, call_duration numeric(8,0) not null, call_charge numeric(8,0) not null ); 9

10 1-3. 非パーティション表作成 (2/5) データ作成用 SQL 文 [ /work/make_calllog.sql ] insert into test_calllog values ( trim(to_char(generate_series(:start,:end), ' ')), --customer_id trim(to_char(generate_series(:start,:end), ' ')), -- phone_number trim(to_char( current_date - round((random() * 365)::integer, 0)::integer, 'YYYYMMDD')), trim(to_char( current_timestamp, 'HH24MISS')), round((random() * 3600)::integer, 0), 30 ); customer_id: 連番 phone_number: 連番 call_start_date: 実行日から1 年前までのランダムな日付 call_start_time:current_timestampの時刻部分 call_duration:0から3600までのランダムな値 call_charge:30に固定 10

11 1-3. 非パーティション表作成 (3/5) SQL 文実行用シェルを利用して 10 億レコードのデータ作成 [ /work/make_calllog.sh ] #!/bin/sh i=1 TIMES=1000 UNIT= SID=1 EID=`expr ${SID} + ${UNIT} - 1` while [ ${i} -le ${TIMES} ] do psql testdb -f /work/make_calllog.sql -v start=${sid} -v end=${eid} i=`expr ${i} + 1` SID=`expr ${SID} + ${UNIT}` EID=`expr ${EID} + ${UNIT}` 11 done

12 1-3. 非パーティション表作成 (4/5) シェルの実行とプライマリキー検索用索引の作成 $ nohup /work/make_calllog.sh & この方式では insert 文を繰り返すので長時間かかるあらかじめ CSV を作成し COPY 文でロードする方が効率は良い $ nohup psql -c "alter table test_calllog add constraint test_calllog_pk primary key (customer_id, phone_number, call_start_date, call_start_time)" testdb & 実際は 1 行 $ nohup psql -c "create index test_calllog_idx1 on test_calllog ( call_start_date )" testdb & 検証環境ではプライマリーキー検索用索引の作成にそれぞれ約半日合計 1 日かかった 12

13 1-3. 非パーティション表作成 (5/5) データサイズの確認 $ psql testdb testdb=# testdb=# select pg_size_pretty(pg_total_relation_size('test_calllog')); pg_size_pretty GB (1 row) 13

14 1-4. パーティション表作成 (1/6) パーティション表の親テーブルを作成 # su - postgres $ psql testdb 非パーティション表と同じ構造以下の SQL を実行 create table part_test_calllog( customer_id char(12) not null, phone_number char(11) not null, call_start_date char(8) not null, call_start_time char(6) not null, call_duration numeric(8,0) not null, call_charge numeric(8,0) not null ); 14

15 1-4. パーティション表作成 (2/6) 子テーブルおよび制約インデックスの作成 # su - postgres $ psql testdb _YYYYMM を末尾に付加して月毎のパーティションを作成 create table part_test_calllog_ ( CHECK( call_start_date >= ' ' and call_start_date < ' ') ) INHERITS (part_test_calllog); alter table part_test_calllog_ add constraint part_test_calllog_201210_pk primary key (customer_id, phone_number, call_start_date, call_start_time); create index part_test_calllog_201210_idx on part_test_calllog_ (call_start_date); 後略 ( まで同様に作成 ) 15

16 1-4. パーティション表作成 (3/6) INSERT トリガー用関数の作成 CREATE OR REPLACE FUNCTION func_part_test_calllog_insert() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.call_start_date >= ' ' AND NEW.call_start_date < ' ' ) THEN INSERT INTO part_test_calllog_ VALUES (NEW.*); ELSIF ( NEW.call_start_date >= ' ' AND NEW.call_start_date < ' ' ) THEN INSERT INTO part_test_calllog_ VALUES (NEW.*); 中略 ELSIF ( NEW.call_start_date >= ' ' AND NEW.call_start_date < ' ' ) THEN INSERT INTO part_test_calllog_ VALUES (NEW.*); ELSE RAISE EXCEPTION 'ERROR! Date out of range.'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; 中略 16

17 1-4. パーティション表作成 (4/6) INSERT トリガ作成 CREATE TRIGGER trig_part_test_calllog_insert BEFORE INSERT ON part_test_calllog FOR EACH ROW EXECUTE PROCEDURE func_part_test_calllog_insert(); 17

18 1-4. パーティション表作成 (5/6) 非パーティション表のデータを利用してパーティション表にデータをロード $ nohup psql -c "insert into part_test_calllog select * from test_calllog" testdb & 検証環境では約 25 時間を要した 18

19 1-4. パーティション表作成 (6/6) データサイズの確認 $ psql testdb testdb=# testdb=# select pg_size_pretty( pg_database_size('testdb') - pg_total_relation_size('test_calllog')); pg_size_pretty GB (1 row) testdb=# select pg_size_pretty(pg_total_relation_size('part_test_calllog_201310')); pg_size_pretty GB (1 row) パーティション表の合計は 208GB 検証で検索するパーティションは 14GB( 全体の約 7%) 19

20 2. 検索処理時間測定

21 2-1. 実行する検索処理 2013 年 10 月の通話回数合計通話時間合計通話料金をもとめる SQL を 3 回実行し平均時間を求めるそれぞれデフォルトの状態と実行計画を変更させた状態で測定を行う非パーティション表 select count(*), sum(call_duration), sum(call_charge) from test_calllog where call_start_date between ' ' and ' ' ; パーティション表 select count(*), sum(call_duration), sum(call_charge) from part_test_calllog where call_start_date between ' ' and ' ' ; 21

22 2-2. 非パーティション表検索 ( デフォルト : インデックス使用 ) 処理時間 ( 秒 ) 実行計画 22 1 回目 2 回目 3 回目平均 3,571 3,625 3,577 3,591 testdb=# explain analyze select count(*), sum(call_duration), sum(call_charge) from test_calllog where call_start_date between ' ' and ' ' ; QUERY PLAN Aggregate (cost= rows=1 width=9) (actual time= rows=1 loops=1) -> Bitmap Heap Scan on test_calllog (cost= rows= width=9) (actual time= rows= loops=1) Recheck Cond: ((call_start_date >= ' '::bpchar) AND (call_start_date <= ' '::bpchar)) Rows Removed by Index Recheck: > Bitmap Index Scan on test_calllog_idx1 (cost= rows= width=0) (actual time= rows= loops=1) Index Cond: ((call_start_date >= ' '::bpchar) AND (call_start_date <= ' '::bpchar)) Total runtime: ms (7 rows) インデックスを使用する実行計画となっている

23 2-3. 非パーティション表検索 (Seq Scan)(1/2) 実行計画が Seq Scan になるようにパラメータを変更 testdb=# set enable_bitmapscan=off; SET testdb=# testdb=# set enable_indexscan=off; SET 23 testdb=# select name, setting from pg_settings where name like 'enable%'; name setting enable_bitmapscan off enable_hashagg on enable_hashjoin on enable_indexonlyscan on enable_indexscan off enable_material on enable_mergejoin on enable_nestloop on enable_seqscan on enable_sort on enable_tidscan on (11 rows)

24 2-3. 非パーティション表検索 (Seq Scan)(2/2) 処理時間 ( 秒 ) 1 回目 2 回目 3 回目平均実行計画 testdb=# explain analyze select count(*), sum(call_duration), sum(call_charge) from test_calllog where call_start_date between ' ' and ' ' ; QUERY PLAN Aggregate (cost= rows=1 width=9) (actual time= rows=1 loops=1) -> Seq Scan on test_calllog (cost= rows= width=9) (actual time= rows= loops=1) Filter: ((call_start_date >= ' '::bpchar) AND (call_start_date <= ' '::bpchar)) Rows Removed by Filter: Total runtime: ms (5 rows) Seq Scan が選択されているデフォルト状態で選択された実行計画よりも 4 倍以上高速 24

25 2-4. パーティション表検索 ( デフォルト :Seq Scan) 処理時間 ( 秒 ) 1 回目 2 回目 3 回目平均非パーティション表のデフォルト状態よりも 30 倍高速実行計画 25 testdb=# explain analyze select count(*), sum(call_duration), sum(call_charge) from part_test_calllog where call_start_date between ' ' and ' ' ; QUERY PLAN Aggregate (cost= rows=1 width=9) (actual time= rows=1 loops=1) -> Append (cost= rows= width=9) (actual time= rows= loops=1) -> Seq Scan on part_test_calllog (cost= rows=1 width=28) (actual time= rows=0 loops=1) Filter: ((call_start_date >= ' '::bpchar) AND (call_start_date <= ' '::bpchar)) -> Seq Scan on part_test_calllog_ (cost= rows= width=9) (actual time= rows= loops=1) Filter: ((call_start_date >= ' '::bpchar) AND (call_start_date <= ' '::bpchar)) Total runtime: ms (7 rows) デフォルトで Seq Scan が選択されている

26 2-5. パーティション表検索 ( インデックス使用 )(1/2) インデックスを使用した実行計画になるようにパラメータを変更 testdb=# set enable_seqscan=off; SET testdb=# select name, setting from pg_settings where name like 'enable%'; name setting enable_bitmapscan on enable_hashagg on enable_hashjoin on enable_indexonlyscan on enable_indexscan on enable_material on enable_mergejoin on enable_nestloop on enable_seqscan off enable_sort on enable_tidscan on (11 rows) 26

27 2-5. パーティション表検索 ( インデックス使用 )(2/2) 処理時間 ( 秒 ) 1 回目 2 回目 3 回目平均 Seq Scan よりも処理時間が長くなる実行計画 27 testdb=# explain analyze select count(*), sum(call_duration), sum(call_charge) from part_test_calllog where call_start_date between ' ' and ' ' ; QUERY PLAN Aggregate (cost= rows=1 width=9) (actual time= rows=1 loops=1) -> Append (cost= rows= width=9) (actual time= rows= loops=1) -> Seq Scan on part_test_calllog (cost= rows=1 width=28) (actual time= rows=0 loops=1) Filter: ((call_start_date >= ' '::bpchar) AND (call_start_date <= ' '::bpchar)) -> Bitmap Heap Scan on part_test_calllog_ (cost= rows= width=9) (actual time= rows= loops=1) Recheck Cond: ((call_start_date >= ' '::bpchar) AND (call_start_date <= ' '::bpchar)) -> Bitmap Index Scan on part_test_calllog_201310_idx (cost= rows= width=0) (actual time= rows= loops=1) Index Cond: ((call_start_date >= ' '::bpchar) AND (call_start_date <= ' '::bpchar)) Total runtime: ms (9 rows) インデックスを使用した実行計画になっている

28 3. まとめ

29 3. まとめテーブル構造非パーティション表パーティション表アクセスパス ( 実行計画 ) インデックス使用 ( デフォルト ) 平均処理時間 ( 秒 ) 3,591 Seq Scan 813 Seq Scan 144 ( デフォルト ) インデックス使用 233 検索処理においてもパーティション表は有効データ量が非常に多いためにキャッシュに乗り切らないテーブルからある程度大量のレコードを検索するような処理の場合特に有効 29

30 付録

31 参考テーブル構造非パーティション表パーティション表別環境での測定結果も同様アクセスパス ( 実行計画 ) 別環境 1 における処理時間別環境 2 における処理時間インデックス使用 ( デフォルト ) Seq Scan Seq Scan ( デフォルト ) インデックス使用

32 参考 ( 続き ) 別環境 1 別環境 2 サーバー DL380p Gen8 DL980 G7 CPU タイプ Intel Xeon CPU E (2.90GHz Cache 20MB) CPU( コア ) 数 2P16C 8P80C Intel Xeon CPU E (2.40GHz Cache 30MB) メモリ 128GB (8GB PC x16) 2TB (16GB PC3L-10600x128) HDD 300GB 6Gbps SAS 15K rpm 2.5inch x 8 900GB 6Gbps SAS 10K rpm 2.5inch x 8 DB 領域同上 HP 3PAR StoreServ 7400 (900GB 6Gbps SAS 10K rpm 2.5inch HDD x20 で VRAID1) OS Red Hat Enterprise Linux 6.3 (x86-64) Red Hat Enterprise Linux 6.3 (x86-64) 32

33 Thank You

Postgres Plus Advanced Server 9.3パーティションテーブルの特徴と性能検証レポート

Postgres Plus Advanced Server 9.3 パーティションテーブルの特徴と性能検証レポート ~ データロード編 ~ v1.1 テクノロジーコンサルティング事業統括オープンソース部高橋智雄 2014 年 7 月変更履歴版日付作成修正者説明 1.0 2014/5/19 日本 HP 高橋智雄初版作成 1.1 2014/7/8 日本 HP 高橋智雄表現を微修正 2 はじめに