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

(1)

PostgreSQL 9.3

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

テクノロジーコンサルティング事業統括

オープンソース部

高橋智雄

2014年7月

(2)

変更履歴

版

日付

作成・修正者

説明

1.0 2013/12/16

日本HP 高橋智雄

初版作成

(3)

はじめに

本書は

_{PostgreSQL9.3のパーティション表の検索に関する性能を評価したレポー}

トです。

性能評価は同一の

_{SQL文を非パーティション表とパーティション表で実行し、そ}

の処理時間の差異を比較することで評価しています。

パーティション表の効果として、メンテナンス性の向上に加え、検索処理の性能

が向上する点を検証しました。

(4)

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

(1)

インフラ構成

(2)

PostgreSQL環境

(3)

非パーティション表作成

(4)

パーティション表作成

2. 検索処理時間測定

(1)

実行する検索処理

(2)

非パーティション表検索（デフォルト：インデックス使用）

(3)

非パーティション表検索（

Seq Scan）

(4)

パーティション表検索（デフォルト：

Seq Scan ）

(5)

パーティション表検索（インデックス使用）

3. まとめ

(5)

(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）

(7)

1-2. PostgreSQL 9.3 環境（1/2）

※ データディレクトリ、WALディレクトリは実際には同一LUN上に作成。

PostgreSQL起動ユーザー

postgres

インストールディレクトリ

/usr/local/pgsql

データディレクトリ

/disk1/data

WALディレクトリ

/disk2/pg_xlog

ARCHIVEファイル配置ディレクトリ

/disk3/archive

postgresユーザー

環境変数

PATH

/usr/local/pgsql/bin を追加

PGDATA

/disk1/data

作業用ディレクトリ

/work

（

postgresユーザーで読み書き可能となっている前提）

PostgreSQL動作環境

(8)

1-2. PostgreSQL 9.3 環境（2/2）

[/disk1/data/postgresql.conf]

listen_addresses = '*'

shared_buffers = 4GB

checkpoint_segments = 64

logging_collector = on

PostgreSQLの主なパラメータ

(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

);

テーブル作成

携帯電話の通話情報と料金を

管理するテーブルのイメージ

(10)

1-3.非パーティション表作成（2/5）

[ /work/make_calllog.sql ]

insert into test_calllog values (

trim(to_char(generate_series(:start,:end), '000000000000')), --customer_id

trim(to_char(generate_series(:start,:end), '00000000000')),

-- 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に固定

データ作成用

SQL文

(11)

1-3.非パーティション表作成（3/5）

[ /work/make_calllog.sh ]

#!/bin/sh

i=1

TIMES=1000

UNIT=1000000

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}`

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日かかった。

(13)

1-3.非パーティション表作成（5/5）

$ psql testdb

testdb=#

testdb=# select pg_size_pretty(pg_total_relation_size('test_calllog'));

pg_size_pretty

---175 GB

(1 row)

(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

);

パーティション表の親テーブルを作成

(15)

1-4. パーティション表作成（2/6）

# su - postgres

$ psql testdb

create table part_test_calllog_201210 (

CHECK( call_start_date >= '20121001' and call_start_date < '20121101')

) INHERITS (part_test_calllog);

alter table part_test_calllog_201210 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_201210

(call_start_date);

・・・・・後略（201310まで同様に作成）

_YYYYMMを末尾に付加して

月毎のパーティションを作成

(16)

1-4. パーティション表作成（3/6）

CREATE OR REPLACE FUNCTION

func_part_test_calllog_insert()

RETURNS TRIGGER AS $$

BEGIN

IF (

NEW.call_start_date >= '20121001' AND

NEW.call_start_date < '20121101'

) THEN

INSERT INTO part_test_calllog_201210 VALUES

(NEW.*);

ELSIF (

NEW.call_start_date >= '20121101' AND

NEW.call_start_date < '20121201'

) THEN

INSERT INTO part_test_calllog_201211 VALUES

(NEW.*);

・・・・中略

INSERTトリガー用関数の作成

・・・・中略

ELSIF (

NEW.call_start_date >= '20131001' AND

NEW.call_start_date < '20131101'

) THEN

INSERT INTO part_test_calllog_201310 VALUES

(NEW.*);

ELSE

RAISE EXCEPTION 'ERROR! Date out of range.';

END IF;

RETURN NULL;

END;

$$

(17)

1-4. パーティション表作成（4/6）

CREATE TRIGGER trig_part_test_calllog_insert

BEFORE INSERT ON part_test_calllog

FOR EACH ROW EXECUTE PROCEDURE func_part_test_calllog_insert();

(18)

1-4. パーティション表作成（5/6）

$ nohup psql -c "insert into part_test_calllog select * from test_calllog" testdb &

非パーティション表のデータを利用してパーティション表にデータをロード

(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

---208 GB

(1 row)

testdb=# select

pg_size_pretty(pg_total_relation_size('part_test_calllog_201310'));

pg_size_pretty

---14 GB

(1 row)

パーティション表の合計は

208GB

検証で検索するパーティションは

14GB（全体の約7%）

データサイズの確認

(20)

(21)

2-1. 実行する検索処理

2013年10月の通話回数、合計通話時間、合計通話料金をもとめるSQLを3回実行し、

平均時間を求める。それぞれデフォルトの状態と、実行計画を変更させた状態で測定

を行う。



非パーティション表



パーティション表

select count(*), sum(call_duration), sum(call_charge) from test_calllog

where call_start_date between '20131001' and '20131031' ;

select count(*), sum(call_duration), sum(call_charge) from part_test_calllog

where call_start_date between '20131001' and '20131031' ;

(22)

2-2. 非パーティション表検索（デフォルト：インデックス使用）



処理時間（秒）



実行計画

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 '20131001' and '20131031' ;

QUERY PLAN

---Aggregate (cost=14098819.93..14098819.94 rows=1 width=9) (actual time=3556514.991..3556514.992 rows=1

loops=1)

-> Bitmap Heap Scan on test_calllog (cost=1750658.62..13586962.83 rows=68247614 width=9) (actual

time=106438.201..3432933.369 rows=69095685 loops=1)

Recheck Cond: ((call_start_date >= '20131001'::bpchar) AND (call_start_date <= '20131031'::bpchar))

Rows Removed by Index Recheck: 929558067

-> Bitmap Index Scan on test_calllog_idx1 (cost=0.00..1733596.72 rows=68247614 width=0) (actual

time=106409.711..106409.711 rows=69095685 loops=1)

Index Cond: ((call_start_date >= '20131001'::bpchar) AND (call_start_date <=

'20131031'::bpchar))

Total runtime: 3556533.867 ms

(7 rows)

インデックスを使用する実行

計画となっている

(23)

2-3. 非パーティション表検索（Seq Scan）（1/2）

testdb=# set enable_bitmapscan=off;

SET

testdb=#

testdb=# set enable_indexscan=off;

SET

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)

実行計画が

Seq Scanになるようにパラメータを変更

(24)



処理時間（秒）



実行計画

2-3. 非パーティション表検索（Seq Scan）（2/2）

1回目

2回目

3回目

平均

810

815

813

813 testdb=# explain analyze select count(*), sum(call_duration), sum(call_charge) from test_calllog where

call_start_date between '20131001' and '20131031' ;

QUERY PLAN

---Aggregate (cost=26324435.58..26324435.59 rows=1 width=9) (actual time=858397.025..858397.025 rows=1

loops=1)

-> Seq Scan on test_calllog (cost=0.00..25812578.48 rows=68247614 width=9) (actual

time=49.807..791015.290 rows=69095685 loops=1)

Filter: ((call_start_date >= '20131001'::bpchar) AND (call_start_date <= '20131031'::bpchar))

Rows Removed by Filter: 930904315

Total runtime: 858423.650 ms

(5 rows)

Seq Scanが選択されている。

デフォルト状態で選択された

実行計画よりも

4倍以上高速

(25)



処理時間（秒）



実行計画

2-4. パーティション表検索（デフォルト：Seq Scan）

1回目

2回目

3回目

平均

167

131

135

144 testdb=# explain analyze select count(*), sum(call_duration), sum(call_charge) from part_test_calllog where

call_start_date between '20131001' and '20131031' ;

QUERY PLAN

---Aggregate (cost=2266979.99..2266980.00 rows=1 width=9) (actual time=160513.650..160513.650 rows=1 loops=1)

-> Append (cost=0.00..1748762.32 rows=69095689 width=9) (actual time=0.101..107414.956 rows=69095685

loops=1)

-> Seq Scan on part_test_calllog (cost=0.00..0.00 rows=1 width=28) (actual time=0.002..0.002 rows=0

loops=1)

Filter: ((call_start_date >= '20131001'::bpchar) AND (call_start_date <= '20131031'::bpchar))

-> Seq Scan on part_test_calllog_201310 (cost=0.00..1748762.32 rows=69095688 width=9) (actual

time=0.096..92189.284 rows=69095685 loops=1)

Filter: ((call_start_date >= '20131001'::bpchar) AND (call_start_date <= '20131031'::bpchar))

Total runtime: 160513.784 ms

(7 rows)

デフォルトで

Seq Scanが選択されている。

非パーティション表のデフォル

ト状態よりも

30倍高速

(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)

インデックスを使用した実行計画になるようにパラメータを変更

(27)



処理時間（秒）



実行計画

2-5. パーティション表検索（インデックス使用）（2/2）

1回目

2回目

3回目

平均

230

234

233 testdb=# explain analyze select count(*), sum(call_duration), sum(call_charge) from part_test_calllog where call_start_date

between '20131001' and '20131031' ;

QUERY PLAN

--- Aggregate

(cost=10004283987.36..10004283987.37 rows=1 width=9) (actual time=262048.574..262048.574 rows=1 loops=1)

-> Append (cost=10000000000.00..10003765769.69 rows=69095689 width=9) (actual time=81719.992..203146.731 rows=69095685

loops=1)

-> Seq Scan on part_test_calllog (cost=10000000000.00..10000000000.00 rows=1 width=28) (actual time=0.003..0.003

rows=0 loops=1)

Filter: ((call_start_date >= '20131001'::bpchar) AND (call_start_date <= '20131031'::bpchar))

-> Bitmap Heap Scan on part_test_calllog_201310 (cost=2017007.37..3765769.69 rows=69095688 width=9) (actual

time=81719.988..186096.782 rows=69095685 loops=1)

Recheck Cond: ((call_start_date >= '20131001'::bpchar) AND (call_start_date <= '20131031'::bpchar))

-> Bitmap Index Scan on part_test_calllog_201310_idx (cost=0.00..1999733.45 rows=69095688 width=0) (actual

time=81717.462..81717.462 rows=69095685 loops=1)

Index Cond: ((call_start_date >= '20131001'::bpchar) AND (call_start_date <= '20131031'::bpchar))

Total runtime: 262048.733 ms

(9 rows)

インデックスを使用した

実行計画になっている。

Seq Scanよりも処理時間が

長くなる

(28)

(29)

3. まとめ

テーブル構造

アクセスパス

（実行計画）

平均処理時間

（秒）

非パーティション表

インデックス使用

（デフォルト）

3,591

Seq Scan

813 パーティション表

Seq Scan

（デフォルト）

144 インデックス使用

233 検索処理においてもパーティション表は有効

データ量が非常に多いためにキャッシュに乗り切らないテーブルから、

ある程度大量のレコードを検索するような処理の場合、特に有効。

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

PostgreSQL 9.3

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

テクノロジーコンサルティング事業統括

オープンソース部

高橋 智雄

2014年7月

変更履歴

版

日付

作成・修正者

説明

1.0

2013/12/16

日本HP 高橋智雄

初版作成

はじめに

本書は

PostgreSQL9.3のパーティション表の検索に関する性能を評価したレポー

トです。

性能評価は同一の

SQL文を非パーティション表とパーティション表で実行し、そ

の処理時間の差異を比較することで評価しています。

パーティション表の効果として、メンテナンス性の向上に加え、検索処理の性能

が向上する点を検証しました。

目次

1.

検証環境構成と事前準備

(1)

インフラ構成

(2)

PostgreSQL環境

(3)

非パーティション表作成

(4)

パーティション表作成

2.

検索処理時間測定

(1)

実行する検索処理

(2)

非パーティション表検索（デフォルト：インデックス使用）

(3)

非パーティション表検索（

Seq Scan）

(4)

パーティション表検索（デフォルト：

Seq Scan ）

(5)

パーティション表検索（インデックス使用）

3.

まとめ

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）

1-2. PostgreSQL 9.3 環境（1/2）

※ データディレクトリ、WALディレクトリは実際には同一LUN上に作成。

高橋智雄

_{PostgreSQL9.3のパーティション表の検索に関する性能を評価したレポー}

_{SQL文を非パーティション表とパーティション表で実行し、そ}