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

(1)

Postgres Plus Advanced Server 9.3

パーティションテーブルの特徴と

性能検証レポート

~データロード編 ~ v1.1

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

オープンソース部

(2)

変更履歴

版

日付

作成・修正者

説明

1.0 2014/5/19

日本HP 高橋智雄

初版作成

1.1 2014/7/8

日本HP 高橋智雄

表現を微修正

(3)

はじめに

本書は

2014年3月に実施した、PostgreSQL 9.3および、 Postgres Plus Advanced

Server（以下PPAS） 9.3のパーティションテーブルのデータロード性能を測定した

結果をまとめたものです。

コミュニティ版の

PostgreSQLでは、パーティションテーブルの作成方法が複雑

で、しかもパーティション数が多くなるとパーティションテーブルへのデータロー

ド処理に時間がかかります。

PPASでパーティションテーブルの作成が簡素化され、データロード処理の時間

(4)

1. PostgreSQLのパーティションテーブル

2. PPASのパーティションテーブル

3. 検証内容

4. 検証結果

5. まとめ

(5)

(6)

1-1. PostgreSQLのパーティションテーブルの構成要素

親テーブル、子テーブル、継承、制約、トリガー



PostgreSQLのパーティショニングは専用の組み込み機能ではなく、「継承」「CHECK制約」「トリ

ガー」を組み合わせて実現します。



継承

テーブルに親子関係を定義し、子は親の列構成を引き継ぎます。親テーブルへのクエリは

子テーブルも含むように自動的に展開されます。



CHECK制約

パーティションに含まれるデータの範囲を定義するために使用します。



トリガー

親テーブルに対して

INSERTされたデータを子テーブルに振り分けるため、親テーブルにトリ

ガーを設定します。

(7)

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

パーティションのキーとする列

(8)

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;

$$

language plpgsql;

パーティション数分elsifを続ける

(9)

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

トリガーにより、親テーブルへの

(10)

1-3. パーティションテーブルの問題点



パーティション数が多い場合、

INSERTが遅い



例えば、

1000番目のパーティションにデータを振り分ける場合、if文が1000回実行されるた

め遅い。



パーティション数が多くても、

1番目のパーティションへのINSERTは遅くない。



そもそも

PL/pgSQLはインタープリタであるため、実行速度はそれほど速くない。



パーティションの追加時にメンテナンスが必要



パーティションを追加するたびに、トリガー用関数を修正する必要がある。

(11)

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;

$$

(12)

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/

上記からダウンロード可能

(13)

(14)

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'),

・・・

);

レンジパーティションは

(15)

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制約を使用して作成された

子テーブル

(16)

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言語関数を使用したトリガーが

作成される。

(17)

2-4. PPASパーティションテーブルの特徴まとめ



Oracle Databaseと同様の構文でパーティションの作成が可能



レンジパーティション、リストパーティションの作成が可能



サブパーティションの作成も可能



ALTER TABLE文でパーティションのADD、DROP、SPLIT等も可能



パーティションの実体は

PostgreSQLと同様の構造



パーティションは親テーブルを継承する子テーブルとして作られる



トリガー用関数として

C言語関数が内部的に自動設定される



pg_catalog.partition_insert_trigger()



pg_catalog.partition_update_trigger()

(18)

(19)

3-1. 検証目的



PostgreSQLでは、数多くのパーティションを作成することは推奨されていない。

パーティション数が多いと、実際にどの程度性能影響があるのか基礎数値

を測定する。



PostgreSQLとPPASにおけるパーティション性能の差を測定し、パーティション

を多用するシステムにおいてはどちらの

DBMSが有利であるか確認する。

(20)

3-2. 検証方法と内容



PostgreSQL、PPASの双方で約2000のパーティションを持つテーブルを作成し、 1番目、

481番目、961番目、1441番目、1921番目のパーティションにデータをロードし、所要時

間を測定する。



データのロードは以下の手順で行う



あらかじめデータロード用

CSVファイルを作成



COPY文を使用してテーブルへデータをロード



測定は以下のパーティション構成に対して実施



PostgreSQL（静的トリガ関数による振り分け実装）



PostgreSQL（動的トリガ関数による振り分け実装）



PPAS

(21)

3-3.検証環境ハードウェア／ソフトウェア構成

ハードウェアサーバー

HP ProLiant SL335s G7

CPU

AMD Opteron Processor 4176HE 2.4GHz 20MB Cache 2P/12Core

メモリ

32GB

ディスク

コントローラ

HP SmartアレイB110i SATA RAIDコントローラー

内蔵

HDD

HP 1TB 3Gbps SATA 7.2k rpm 2.5inch MDL HDD× 4 （RAID10）

NIC

HP NC362i内蔵デュアルポートGigabitサーバーアダプター

Hypervisor

VMware ESXi 5.0.2／4vCPU／16GBメモリ

OS

Red Hat Enterprise Linux 6.2 （kernel 2.6.32-220） x86_64

(22)

3-4. データベースパラメータ



PostgreSQL、PPASの双方とも以下のパラメータを設定



shared_buffers：4GB



checkpoint_segments：64



listen_addresses：*



その他のパラメータはデフォルト値を使用

(23)

(24)

4-1. PostgreSQLのパーティション性能(1/2)

静的トリガーと動的トリガーの比較

 静的トリガ関数では、パーティション順にロード時間がほぼ比例

 動的トリガ関数では、パーティション順に関係なくロード時間は一定

22

400

736 1,248

1,883

11

10

11

10

9

0

200

400

600

800 1,000

1,200

1,400

1,600

1,800

2,000

0

500 1000

1500

2000

ロード時間（秒）

ロード対象パーティションのパーティション順

10万行のロードにかかる時間

静的トリガ

動的トリガ

(25)

（参考）静的トリガー関数を使用している際の

CPU使用率

0

10

20

30

40

50

60

70

80

90

100 0 50

₁₀

0

15

0

20

0

25

0

30

0

35

0

40

0

45

0

50

0

55

0

60

0

65

0

70

0

75

0

80

0

85

0

90

0

95

0 1,00

0 1,05

0 1,10

0 1,15

0 1,20

0 1,25

0 1,30

0 1,35

0 1,40

0 1,45

0 1,50

0 1,55

0 1,60

0 1,65

0 1,70

0 1,75

0 1,80

0 CPU

使用率（％）

経過時間（秒）

データロード時のCPU使用状況

CPU0

CPU1

CPU2

CPU3

(26)

4-1. PostgreSQLのパーティション性能(2/2)

動的トリガーを使用した場合のロード行数と実行時間

 ロード行数によらず、パーティション順に関係なくロード時間は一定

 ロード時間はロードする行数に比例

11

10

11

10 ₉

52

49

50

99

100

97

100

98

0

20

40

60

80

100

120

0

500 1000

1500

2000

ロード時間（秒）

ロード対象パーティションのパーティション順

ロード行数と実行時間

10万行

50万行

100万行

5倍

10倍

(27)

2

19

2

18

3

20

2

18

2

21

174

194

190

200

191

0

50

100

150

200

250

0

200

400

600

800 1000

1200

1400

1600

1800

2000

ロード時間（秒）

ロード対象パーティションのパーティション順

ロード行数と実行時間

10万行

100万行

1000万行

4-2. PPASのパーティション性能（1/2）

10倍

100倍

(28)

4-2. PPASのパーティション性能（2/2）

10

2

99

19

0

20

40

60

80

100

120 ロード時間（秒）

PPAS vs PostgreSQL（動的トリガー）

PostgreSQL-10万行

PPAS-10万行

PostgreSQL-100万行

PPAS-100万行

5倍速

(29)

(30)

まとめ



PostgreSQLのパーティションテーブルの問題点

一般的に使用される静的トリガー関数では



パーティション数が多くなるとデータロードの時間がかかる



パーティション追加時にメンテナンスが必要



問題点への対処方法



動的トリガー関数を使用するとデータロード性能が安定し、メンテナンスも不要

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

Postgres Plus Advanced Server 9.3

パーティションテーブルの特徴と

性能検証レポート

~データロード編 ~ v1.1

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

オープンソース部

変更履歴

版

日付

作成・修正者

説明

1.0

2014/5/19

日本HP 高橋智雄

初版作成

1.1

2014/7/8

日本HP 高橋智雄

表現を微修正

はじめに

本書は

2014年3月に実施した、PostgreSQL 9.3および、 Postgres Plus Advanced

Server（以下PPAS） 9.3のパーティションテーブルのデータロード性能を測定した

結果をまとめたものです。

コミュニティ版の

PostgreSQLでは、パーティションテーブルの作成方法が複雑

で、しかもパーティション数が多くなるとパーティションテーブルへのデータロー

ド処理に時間がかかります。

PPASでパーティションテーブルの作成が簡素化され、データロード処理の時間

目次

1. PostgreSQLのパーティションテーブル

2. PPASのパーティションテーブル

3. 検証内容

4. 検証結果

5. まとめ

1-1. PostgreSQLのパーティションテーブルの構成要素

親テーブル、子テーブル、継承、制約、トリガー



PostgreSQLのパーティショニングは専用の組み込み機能ではなく、「継承」「CHECK制約」「トリ

ガー」を組み合わせて実現します。



継承

テーブルに親子関係を定義し、子は親の列構成を引き継ぎます。親テーブルへのクエリは

子テーブルも含むように自動的に展開されます。



CHECK制約

パーティションに含まれるデータの範囲を定義するために使用します。



トリガー

親テーブルに対して

INSERTされたデータを子テーブルに振り分けるため、親テーブルにトリ

ガーを設定します。

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

パーティションのキーとする列

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;