こんにちは 村上です。
この記事はTECHSCORE Advent Calendar 2014、9日目の記事です。
本日はパーティショニングについて書きたいと思います。
データの分割
システム開発をする上でデータを分割したい時がよくあると思います。
例えば
- マルチテナントな時
- スキーマ、テーブルスペースでデータを分けたい
- データ量増加により、何かのキーでテーブルを分けたい
などです。
今回は上記の3つめについて書きます。
テーブルのパーティショニング
データを分割(パーティショニング)したいものにはどんなものがあるか？
例えば、履歴データ
ユーザーの履歴データを格納するデータは日を追うごとに増えていきます。
他には、お気に入り商品
この場合、最大で「ユーザー数 ✕ 商品数」のデータが格納されます。
こういったテーブルに対してはパーティショニングをする必要がでてきます。
データを分割する場合は、単純にテーブルを分ければいいのですが、アプリケーションからはめんどくさい実装が必要になります。
たとえば、年月で判断して検索するテーブルを判断したり、年月をまたぐ場合はデータを統合したり。。。
そのため、パーティショニングはDBレイヤーで行うのがベストです。
商用のDBではこのパーティショニングが簡単にできるものもありますが、PostgreSQLの場合は単純にはいきません。
PostgreSQLによるパーティショニング
PostgreSQLのパーティショニングは下記の要領で行います。
- 分割元となるテーブルの作成
- 分割したデータが入るテーブルの作成
- データを分割する関数の作成
- データを分割するトリガを作成
といった感じになります。
試しに商品テーブルを「type」によってパーティショニングしてみましょう。
テーブルレイアウトはこんな感じです。
|
1 2 3 4 5 6 7 8 9 10 11 |
techscore=# \d items1 テーブル "public.items1" 列 | 型 | 修飾語 ------+------------------------+----------------------------------------------------- id | integer | not null default nextval('items1_id_seq'::regclass) code | character varying(255) | not null name | character varying(255) | not null type | integer | not null インデックス: "items1_pkey" PRIMARY KEY, btree (id) "items1_type_idx" btree (type) |
次に下記のSQLを実行して、分割したデータが入るテーブルを作ります。
|
1 2 3 4 5 |
-- 分割 CREATE TABLE items1_type1( LIKE items1 INCLUDING DEFAULTS INCLUDING INDEXES, CHECK (type = 1)) INHERITS (items1); CREATE TABLE items1_type2( LIKE items1 INCLUDING DEFAULTS INCLUDING INDEXES, CHECK (type = 2)) INHERITS (items1); CREATE TABLE items1_type3( LIKE items1 INCLUDING DEFAULTS INCLUDING INDEXES, CHECK (type = 3)) INHERITS (items1); CREATE TABLE items1_type4( LIKE items1 INCLUDING DEFAULTS INCLUDING INDEXES, CHECK (type = 4)) INHERITS (items1); |
items1を継承したテーブルです。
これらのテーブルにはCHECK制約でどのタイプが格納されるかを定義しています。
次に関数を作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR REPLACE FUNCTION items1_insert_trigger_func() RETURNS trigger AS $BODY$ DECLARE child text; BEGIN child := 'items1_type' || new.type; EXECUTE 'INSERT INTO ' || child || ' VALUES(($1).*)' USING new; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; |
ポイントは7-8行目です。
「items1_type」とtypeの項目を文字列結合して、挿入先をtypeによって分けています。
これで、挿入時にデータが分割されて挿入されていきます。
関数が作成できたらこの関数を実行するトリガを作成します。
|
1 2 3 4 5 |
CREATE TRIGGER items1_insert_trigger BEFORE INSERT ON items1 FOR EACH ROW EXECUTE PROCEDURE items1_insert_trigger_func(); |
itemsの行ごとの挿入前にテーブル分割用の関数を実行するように設定します。
これで準備が整いました。
データを挿入してみましょう。
挿入先はデータの分割元となるテーブルに対して行います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
insert into items1(code, name, type) values ('item_11', '商品11', 1); insert into items1(code, name, type) values ('item_12', '商品12', 1); insert into items1(code, name, type) values ('item_13', '商品13', 1); insert into items1(code, name, type) values ('item_14', '商品14', 1); insert into items1(code, name, type) values ('item_15', '商品15', 1); insert into items1(code, name, type) values ('item_21', '商品21', 2); insert into items1(code, name, type) values ('item_22', '商品22', 2); insert into items1(code, name, type) values ('item_23', '商品23', 2); insert into items1(code, name, type) values ('item_24', '商品24', 2); insert into items1(code, name, type) values ('item_25', '商品25', 2); insert into items1(code, name, type) values ('item_31', '商品31', 3); insert into items1(code, name, type) values ('item_32', '商品32', 3); insert into items1(code, name, type) values ('item_33', '商品33', 3); insert into items1(code, name, type) values ('item_34', '商品34', 3); insert into items1(code, name, type) values ('item_35', '商品35', 3); insert into items1(code, name, type) values ('item_41', '商品41', 4); insert into items1(code, name, type) values ('item_42', '商品42', 4); insert into items1(code, name, type) values ('item_43', '商品43', 4); insert into items1(code, name, type) values ('item_44', '商品44', 4); insert into items1(code, name, type) values ('item_45', '商品45', 4); |
では分割できているかどうか確認してみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
techscore=# select * from items1_type3; id | code | name | type ----+---------+--------+------ 11 | item_31 | 商品31 | 3 12 | item_32 | 商品32 | 3 13 | item_33 | 商品33 | 3 14 | item_34 | 商品34 | 3 15 | item_35 | 商品35 | 3 (5 行) techscore=# select * from items1 where type=3; id | code | name | type ----+---------+--------+------ 15 | item_35 | 商品35 | 3 14 | item_34 | 商品34 | 3 13 | item_33 | 商品33 | 3 12 | item_32 | 商品32 | 3 11 | item_31 | 商品31 | 3 (5 行) |
とりあえず、データは挿入されて分割もできています。
実行計画も見てみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
techscore=# explain select * from items1 where type=3; QUERY PLAN --------------------------------------------------------------------------------------------------- Append (cost=0.00..8.16 rows=2 width=1040) -> Seq Scan on items1 (cost=0.00..0.00 rows=1 width=1040) Filter: (type = 3) -> Index Scan using items1_type3_type_idx on items1_type3 (cost=0.14..8.16 rows=1 width=1040) Index Cond: (type = 3) (5 行) techscore=# explain select * from items1 where type=3 or type=4; QUERY PLAN ---------------------------------------------------------------------- Append (cost=0.00..22.10 rows=5 width=1040) -> Seq Scan on items1 (cost=0.00..0.00 rows=1 width=1040) Filter: ((type = 3) OR (type = 4)) -> Seq Scan on items1_type3 (cost=0.00..11.05 rows=2 width=1040) Filter: ((type = 3) OR (type = 4)) -> Seq Scan on items1_type4 (cost=0.00..11.05 rows=2 width=1040) Filter: ((type = 3) OR (type = 4)) (7 行) |
実行計画を見るとCHECK制約が効いており余計なテーブルスキャンが行われていないことも確認できます。
ではデータの削除をしてみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
techscore=# explain delete from items1 where type=3; QUERY PLAN ------------------------------------------------------------------------------------------------ Delete on items1 (cost=0.00..8.16 rows=2 width=6) -> Seq Scan on items1 (cost=0.00..0.00 rows=1 width=6) Filter: (type = 3) -> Index Scan using items1_type3_type_idx on items1_type3 (cost=0.14..8.16 rows=1 width=6) Index Cond: (type = 3) (5 行) techscore=# delete from items1 where type=3; DELETE 5 techscore=# explain delete from items1; QUERY PLAN -------------------------------------------------------------------- Delete on items1 (cost=0.00..42.80 rows=281 width=6) -> Seq Scan on items1 (cost=0.00..0.00 rows=1 width=6) -> Seq Scan on items1_type1 (cost=0.00..10.70 rows=70 width=6) -> Seq Scan on items1_type2 (cost=0.00..10.70 rows=70 width=6) -> Seq Scan on items1_type3 (cost=0.00..10.70 rows=70 width=6) -> Seq Scan on items1_type4 (cost=0.00..10.70 rows=70 width=6) (6 行) techscore=# delete from items1; DELETE 15 techscore=# select * from items1; id | code | name | type ----+------+------+------ (0 行) |
データも綺麗に消えてくれますね。
その他のコマンド
SQLではテーブル分割がうまくいくことがわかりました。
ではCOPYやtruncateといったコマンドではどうでしょうか？
COPY IMPORT
|
1 2 3 4 5 6 7 8 |
techscore=# COPY items1 (code, name, type) FROM '/tmp/techscore_items.csv' WITH CSV; COPY 0 techscore=# select count(*) from items1; count ------- 20 (1 行) |
COPYコマンドで返される件数が0件になってしまいました。
ですが、取込はできているようです。
COPY EXPORT
|
1 2 |
techscore=# COPY items1 TO '/tmp/techscore_items_export'; COPY 0 |
EXPORTも0件で返されます。
しかも！！
|
1 2 |
wc -l /tmp/techscore_items_export 0 /tmp/techscore_items_export |
ゼロ件。。。
EXPORTは対応できないみたいです。。。
ですが、SQLではEXPORTできるようです。
|
1 2 |
techscore=# COPY (select * from items1) TO '/tmp/techscore_items_export'; COPY 20 |
|
1 2 |
wc -l /tmp/techscore_items_export 20 /tmp/techscore_items_export |
TRUNCATE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
techscore=# select count(*) from items1; count ------- 20 (1 行) techscore=# truncate items1; TRUNCATE TABLE techscore=# select count(*) from items1; count ------- 0 (1 行) techscore=# |
truncateはできてそうですね。
運用では
PostgreSQLのパーティショニングですが、実際の運用でも使用しています。
1000万件以上のテーブル(中には億)に対してパーティショニングを実施しています。
その中で得たノウハウも紹介します。
- CHECK制約が複雑なものは効かない
テーブルの項目の値のみでCHECK制約を設ける場合は問題ありませんが、少し複雑になった場合は機能しません。
例えば、「お気に入り商品」などをユーザーごとに管理したい場合では、ユーザーIDを100で割ったあまりをsuffixとしてテーブルを分割する場合は、検索時にCHECK制約がうまく動作せず、パーティショニングされたテーブルを全件SCANしてしまいます。こういった場合はCHECK制約用の項目を設けて検索の条件に含めるようにした方が良いです。
- 分割数
PostgreSQLのドキュメントではパーティショニングは100個までを推奨しており、何千ものパーティションを使用することは避けるように書かれています。
PostgreSQLドキュメント
100個以上のパーティショニングは運用でもやっておりません。
- レスポンス
レスポンスが気になったことはありません。(CHECK制約がうまく動作している時！！)
あと、COPYコマンドによる大量のデータIMPORT(1000万件ほど)はパーティショニングがなければ、1分半ほどですみますが、パーティショニングしていると8分ぐらいかかります。
大量のデータ投入の際はパーティショニング先のテーブルに対してCOPYコマンドを実行する方が速いです。
