この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは。三上です。
河原にどんと焼き(さいとう焼き?)ができてました。昔より小さく感じるのは、時代の流れでしょうか?(しみじみ。。
やりたいこと
- Redshiftのテーブル設計を学びたい!(チューニングもできるようになりたい
- 分散スタイルと分散キーの違いで何が変わるか確認したい
分散スタイルって・・・?
Redshiftの構成
- Redshiftは、クラスタ > ノード > スライス の構成
- 各ノードは並列で動く
- データは各スライスに分割して持つ
- データが偏っちゃうと働かないノードがでてくるのでもったいない
- 別のノードが持っているデータが必要になると、データを再分散しないといけないので時間がかかる
分散スタイルの種類
- EVEN:均等分散
- KEY:指定キーはまとめて同じスライスに分散
- ALL:全ノード(先頭スライス)に同じテーブルをコピー
確認してみよう
確認環境
以下のRedshift(1ノード2スライス)で確認しました。
確認データ
以下から、使いたいカラムデータのみ抽出したCSVを作成しました。
テーブル構成
下記テーブル構成で、populationテーブルにすべての分散スタイルを指定 → 実行計画を比較しました。
データ準備
Redshiftに以下のテーブルを作り、データをロードします。
【住所マスタ】
create table m_addr(
pref_cd varchar(255),
city_cd varchar(255),
pref varchar(255),
city varchar(255),
primary key(city_cd)
)diststyle even;
【地域種別マスタ】
create table m_area_kind(
kind varchar(255),
name varchar(255),
primary key(kind)
)diststyle even;
【人口テーブル(×3)】
create table pop_even(
area_cd varchar(255),
area_kind varchar(255),
population int,
width double precision,
density double precision,
house int,
foreign key(area_cd) references m_addr(city_cd),
foreign key(area_kind) references m_area_kind(kind)
)diststyle even;
create table pop_dist(
area_cd varchar(255),
area_kind varchar(255),
population int,
width double precision,
density double precision,
house int,
foreign key(area_cd) references m_addr(city_cd),
foreign key(area_kind) references m_area_kind(kind)
)diststyle key distkey (area_cd);
create table pop_all(
area_cd varchar(255),
area_kind varchar(255),
population int,
width double precision,
density double precision,
house int,
foreign key(area_cd) references m_addr(city_cd),
foreign key(area_kind) references m_area_kind(kind)
)diststyle all;
分散スタイルを確認してみます。
select
relname,
reldiststyle
from
pg_class
where
relname like 'pop_%'
or relname = 'm_addr'
or relname = 'm_area_kind'
order by
relname;
relname | reldiststyle
-------------+--------------
m_addr | 0
m_area_kind | 0
pop_all | 8
pop_dist | 1
pop_even | 0
(5 rows)
※reldiststyleは以下の通りです。
- 0:EVEN
- 1:KEY
- 8:ALL
※pop_distの分散キーは、area_cd を指定しました。
実際にデータが分散されているか確認してみます。
select
name, slice, col, num_values as rows, minvalue, maxvalue
from
svv_diskusage
where
(name like 'pop_%' or name = 'm_addr' or name = 'm_area_kind') and col = 0 and rows > 0
order by
name, slice, col;
name | slice | col | rows | minvalue | maxvalue
--------------------------------------------------------------------------+-------+-----+------+--------------+---------------------
m_addr | 0 | 0 | 2776 | 0 | 2314885530818459444
m_addr | 1 | 0 | 2777 | 0 | 2314885530818459444
m_area_kind | 0 | 0 | 5 | 48 | 2314885530818453603
m_area_kind | 1 | 0 | 4 | 49 | 2314885530818453604
pop_all | 0 | 0 | 5553 | 206966894640 | 3472328296237087727
pop_dist | 0 | 0 | 2757 | 211261861936 | 2314885604236801844
pop_dist | 1 | 0 | 2796 | 206966894640 | 3472328296237087727
pop_even | 0 | 0 | 2777 | 215556829232 | 3472328296237087727
pop_even | 1 | 0 | 2776 | 206966894640 | 2314885604236801844
(9 rows)
rows の列を見ると、ALLの pop_all は先頭スライスに全データが格納され、それ以外は指定の分散スタイルで各スライスにデータが格納されました。
実行計画
実行計画のポイント
実行計画では
- DS_DIST_NONE
- DS_DIST_ALL_NONE
以外はダメ!(再分散されるから
※ひとまず、他は後回しで。。
実行計画をとる
各分散スタイルのテーブルに対する、以下のクエリの実行計画をとってみます。
※住所マスタ(m_addr)とJOINして、都道府県ごとの人口を取得するクエリです。
explain
select
a.pref,
sum(p.population) as population
from
[populationテーブル] p
inner join m_addr a on p.area_cd = a.city_cd
group by
a.pref
order by
population desc;
実行結果↓
【EVEN】
QUERY PLAN
------------------------------------------------------------------------------------------------------
XN Merge (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Merge Key: sum(p.population)
-> XN Network (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Send to leader
-> XN Sort (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=1332721189.06..1332721189.16 rows=39 width=14)
-> XN Hash Join DS_BCAST_INNER (cost=69.41..1332721153.65 rows=7082 width=14)
Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
-> XN Seq Scan on m_addr a (cost=0.00..55.53 rows=5553 width=19)
-> XN Hash (cost=55.53..55.53 rows=5553 width=13)
-> XN Seq Scan on pop_even p (cost=0.00..55.53 rows=5553 width=13)
(12 rows)
【KEY】
QUERY PLAN
------------------------------------------------------------------------------------------------------
XN Merge (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Merge Key: sum(p.population)
-> XN Network (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Send to leader
-> XN Sort (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=1332721189.06..1332721189.16 rows=39 width=14)
-> XN Hash Join DS_BCAST_INNER (cost=69.41..1332721153.65 rows=7082 width=14)
Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
-> XN Seq Scan on m_addr a (cost=0.00..55.53 rows=5553 width=19)
-> XN Hash (cost=55.53..55.53 rows=5553 width=13)
-> XN Seq Scan on pop_dist p (cost=0.00..55.53 rows=5553 width=13)
(12 rows)
【ALL】
QUERY PLAN
-----------------------------------------------------------------------------------------------------
XN Merge (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
Merge Key: sum(p.population)
-> XN Network (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
Send to leader
-> XN Sort (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=1189.06..1189.16 rows=39 width=14)
-> XN Hash Join DS_DIST_ALL_NONE (cost=69.41..1153.65 rows=7082 width=14)
Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
-> XN Seq Scan on m_addr a (cost=0.00..55.53 rows=5553 width=19)
-> XN Hash (cost=55.53..55.53 rows=5553 width=13)
-> XN Seq Scan on pop_all p (cost=0.00..55.53 rows=5553 width=13)
(12 rows)
→ALLのテーブルは DS_DIST_ALL_NONE なのでOK(その分ストレージ食ってますが。。)ですが、
EVENとKEYのテーブルに DS_BCAST_INNER が出てます。。
DS_BCAST_INNER 内部テーブル全体のコピーがすべてのコンピューティングノードにブロードキャストされます。
だそうで、よろしくありません。。
分散スタイルを見直す(1)
JOINのキー = 分散キー ならば、再分散なしで済むはずなので、
JOINする住所マスタ(m_addr)の分散スタイルを変えてみることにします。
テーブルを作り直し、データを入れなおしました。
※分散キー(city_cd)= JOIN句の結合キー です。
drop table m_addr;
create table m_addr(
pref_cd varchar(255),
city_cd varchar(255),
pref varchar(255),
city varchar(255),
primary key(city_cd)
)diststyle key distkey (city_cd);
defaultdb=# select relname, reldiststyle from pg_class where relname = 'm_addr';
relname | reldiststyle
---------+--------------
m_addr | 1
(1 row)
defaultdb=# select name, slice, col, num_values as rows, minvalue, maxvalue from svv_diskusage where name = 'm_addr' and col = 0 and rows > 0 order by name, slice, col;
name | slice | col | rows | minvalue | maxvalue
--------------------------------------------------------------------------+-------+-----+------+----------+---------------------
m_addr | 0 | 0 | 2757 | 0 | 2314885530818459444
m_addr | 1 | 0 | 2796 | 0 | 2314885530818459444
(2 rows)
再度、EVENとKEYのテーブルの実行計画を取ってみます。
【EVEN】
QUERY PLAN
------------------------------------------------------------------------------------------------------
XN Merge (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Merge Key: sum(p.population)
-> XN Network (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Send to leader
-> XN Sort (cost=1001332721190.19..1001332721190.29 rows=39 width=14)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=1332721189.06..1332721189.16 rows=39 width=14)
-> XN Hash Join DS_BCAST_INNER (cost=69.41..1332721153.65 rows=7082 width=14)
Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
-> XN Seq Scan on m_addr a (cost=0.00..55.53 rows=5553 width=19)
-> XN Hash (cost=55.53..55.53 rows=5553 width=13)
-> XN Seq Scan on pop_even p (cost=0.00..55.53 rows=5553 width=13)
(12 rows)
【KEY】
QUERY PLAN
------------------------------------------------------------------------------------------------------
XN Merge (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
Merge Key: sum(p.population)
-> XN Network (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
Send to leader
-> XN Sort (cost=1000000001190.19..1000000001190.29 rows=39 width=14)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=1189.06..1189.16 rows=39 width=14)
-> XN Hash Join DS_DIST_NONE (cost=69.41..1153.65 rows=7082 width=14)
Hash Cond: (("outer".city_cd)::text = ("inner".area_cd)::text)
-> XN Seq Scan on m_addr a (cost=0.00..55.53 rows=5553 width=19)
-> XN Hash (cost=55.53..55.53 rows=5553 width=13)
-> XN Seq Scan on pop_dist p (cost=0.00..55.53 rows=5553 width=13)
(12 rows)
→KEYのテーブルが DS_DIST_NONE に変わりました!
分散スタイルを見直す(2)
もうひとつ、別のクエリの実行計画を取ってみます。
※地域種別マスタ(m_area_kind)とJOINして、地域種別ごとの人口を取得するクエリです。
explain
select
k.name,
sum(p.population) as population
from
[populationテーブル] p
inner join m_area_kind k on p.area_kind = k.kind
group by
k.name
order by
population desc;
実行結果↓
【EVEN】
QUERY PLAN
-----------------------------------------------------------------------------------------------------
XN Merge (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
Merge Key: sum(p.population)
-> XN Network (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
Send to leader
-> XN Sort (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=3960208.35..3960208.37 rows=9 width=32)
-> XN Hash Join DS_BCAST_INNER (cost=0.11..3960180.58 rows=5553 width=32)
Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
-> XN Seq Scan on pop_even p (cost=0.00..55.53 rows=5553 width=9)
-> XN Hash (cost=0.09..0.09 rows=9 width=33)
-> XN Seq Scan on m_area_kind k (cost=0.00..0.09 rows=9 width=33)
(12 rows)
【KEY】
QUERY PLAN
-----------------------------------------------------------------------------------------------------
XN Merge (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
Merge Key: sum(p.population)
-> XN Network (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
Send to leader
-> XN Sort (cost=1000003960208.52..1000003960208.54 rows=9 width=32)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=3960208.35..3960208.37 rows=9 width=32)
-> XN Hash Join DS_BCAST_INNER (cost=0.11..3960180.58 rows=5553 width=32)
Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
-> XN Seq Scan on pop_dist p (cost=0.00..55.53 rows=5553 width=9)
-> XN Hash (cost=0.09..0.09 rows=9 width=33)
-> XN Seq Scan on m_area_kind k (cost=0.00..0.09 rows=9 width=33)
(12 rows)
先ほどと同じく、DS_BCAST_INNER 出てます。。
※同じ結果なので、ALLは省略しました。
分散キーは1つしか指定できないため、別のカラムをキーにしたJOINが必要になると、困ります。。
今度は、地域種別マスターの分散スタイルをALLに変更してみます。
drop table m_area_kind;
create table m_area_kind(
kind varchar(255),
name varchar(255),
primary key(kind)
)diststyle all;
defaultdb=# select relname, reldiststyle from pg_class where relname = 'm_area_kind';
relname | reldiststyle
-------------+--------------
m_area_kind | 8
(1 row)
defaultdb=# select name, slice, col, num_values as rows, minvalue, maxvalue from svv_diskusage where name = 'm_area_kind' and col = 0 and rows > 0 order by name, slice, col;
name | slice | col | rows | minvalue | maxvalue
--------------------------------------------------------------------------+-------+-----+------+----------+---------------------
m_area_kind | 0 | 0 | 9 | 48 | 2314885530818453604
(1 row)
実行計画を取ってみると・・・
【EVEN】
QUERY PLAN
-----------------------------------------------------------------------------------------------------
XN Merge (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
Merge Key: sum(p.population)
-> XN Network (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
Send to leader
-> XN Sort (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=208.35..208.37 rows=9 width=32)
-> XN Hash Join DS_DIST_ALL_NONE (cost=0.11..180.59 rows=5553 width=32)
Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
-> XN Seq Scan on pop_even p (cost=0.00..55.53 rows=5553 width=9)
-> XN Hash (cost=0.09..0.09 rows=9 width=33)
-> XN Seq Scan on m_area_kind k (cost=0.00..0.09 rows=9 width=33)
(12 rows)
【KEY】
QUERY PLAN
-----------------------------------------------------------------------------------------------------
XN Merge (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
Merge Key: sum(p.population)
-> XN Network (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
Send to leader
-> XN Sort (cost=1000000000208.52..1000000000208.54 rows=9 width=32)
Sort Key: sum(p.population)
-> XN HashAggregate (cost=208.35..208.37 rows=9 width=32)
-> XN Hash Join DS_DIST_ALL_NONE (cost=0.11..180.59 rows=5553 width=32)
Hash Cond: (("outer".area_kind)::text = ("inner".kind)::text)
-> XN Seq Scan on pop_dist p (cost=0.00..55.53 rows=5553 width=9)
-> XN Hash (cost=0.09..0.09 rows=9 width=33)
-> XN Seq Scan on m_area_kind k (cost=0.00..0.09 rows=9 width=33)
(12 rows)
→両方とも、DS_DIST_ALL_NONE になってくれました!(めでたしめでたしv
まとめ(わかったこと)
- テーブルの分散スタイルは要検討(JOINが必要な場合は特に
- スタイル指定しない場合のデフォルトはEVEN
スタイルはKEY(DISTKEY)指定がベストKEY(DISTKEY)指定でパフォーマンスチューニングできる(2017/09/01 訂正)- JOINのキーを分散キーに指定するといい
- カーディナリティの低い(バリエーションの少ない)カラムを分散キーに指定しちゃダメ(データが偏るから
- テーブルサイズが小さい場合はALL指定でもOK
- 分散スタイルでJOINに対応できない場合は、テーブルの非正規化も検討する