Redshiftのパフォーマンスで重要になる分散キーとソートキーについてまとめました。
分散キー（DISTKEY）
テーブルにデータをロードすると、そのテーブルの分散スタイルに従って、テーブルの行が各ノードスライスに分散されます。Redshift では1ノードの中で実際に処理を行うプロセスが複数動いており、このプロセスをノードスライスといいます。並列処理の単位はノードではなくノードスライス単位です。スライスの数は、次のようにノード上のプロセッサコアの数と同じになります。
均等分散
均等分散はデフォルトの分散スタイルです。リーダーノードは、特定の列の値に含まれている値にかかわらず、ラウンドロビン方式によって複数のスライス間で行を分散させます。例えば 8スライスのテーブルに 8行 insert した場合、各ノードスライスへ順番に 1行ずつ配布されます。均等分散は、テーブルが結合に関与していない場合や、キー分散と ALL分散のどちらを選択すべきかが明確でない場合に適切です。
キー分散
分散キーを使って各ノードスライスへの配分を決めます。Redshift ではテーブルのカラムのうち、1カラムだけを分散キーに指定できます。リーダーノードは、複数の一致する値を同じノードスライスに配置しようと試みるため、結合キーに基づいてテーブルのペアを分散する場合、結合列に含まれている値に従って行を各ノードスライスに配分します。これによって、共通の列からの一致する値が物理的にまとめて格納されるようになります。
分散キーを指定するカラムには、値が偏らずに、できるだけ多くの値のあるカラムが適しています。性別やフラグのように値が少ししかなかったり、数量カラムのように偏りがちなカラムは避けましょう。また、結合や GROUP BY のキーを選択すると、再分散を防げる場合が多いです。
ALL分散
テーブル全体のコピーがすべてのノードに分散されます。ALL分散では、クラスタ内のノードの数だけ必要なストレージが増えるため、データをロードまたは更新したり、複数のテーブルに挿入したりするのに時間がかかります。ALL分散は、更新頻度が低く、更新範囲が広くないテーブルに適しています。サイズの小さいディメンションテーブルの場合は、再分散コストが低いため、ALL 分散を使用しても大きなメリットが得られません。
CREATE TABLE時の指定方法
分散スタイルのテーブル作成時の指定方法は以下の通りです。
CREATE TABLE customer ( c_custkey INTEGER NOT NULL, c_name VARCHAR(25) NOT NULL, c_address VARCHAR(25) NOT NULL, c_city VARCHAR(10) NOT NULL ) DISTSTYLE KEY DISTKEY (c_custkey);
キー分散の場合はDISTKET <colum>を書くと自動的にDISTSTYLE KEYが指定されますので、DISTSTYLE の記述は不要です。また、カラムの横にDISTKEYとだけ記述しても問題ありません。
| 分散スタイル | 指定方法 |
|---|---|
| 均等分散 | DISTSTYLE EVEN |
| キー分散 | (DISTSTYLE KEY) DISTKEY <colum> |
| ALL分散 | DISTSTYLE ALL |
再分散
ALL分散の説明で少し出てきましたが、再分散という処理があります。均等分散やキー分散の場合、データはいずれかのノードスライスに配置されており、他のノードスライスのデータに直接触ることはできません。しかし、結合や集約などの処理において他のノードスライス上のデータが必要になることがあります。この時に行われるのがデータの再分散です。
再分散とは、分散キーを変更した一時テーブルを内部的に作成し、この一時テーブルへデータを入れ直す処理の事です。この処理によって、各ノードスライスで必要なデータが集められ、結合などの処理が実行できるようになります。再分散はテーブルに格納されている全てのデータをネットワーク経由で転送する処理となる為、非常に重たい処理です
このため、なるべく再分散処理は発生させない方がいいのですが、ここで必要になるのが適切な分散キーの設定です。実行するクエリに再分散処理が存在するかどうかはEXPLAINコマンドをクエリの前に付けて実行することによってクエリプランを確認できます。
explain select sum(lo_revenue), d_year from lineorder, dwdate where lo_orderdate = d_datekey group by d_year order by d_year; QUERY PLAN -------------------------------------------------------------------------------------------------------- XN Merge (cost=1000431461452.87..1000431461452.88 rows=7 width=8) Merge Key: dwdate.d_year ... -> XN Hash Join DS_BCAST_INNER (cost=31.95..428461263.31 rows=600037888 width=8) Hash Cond: ("outer".lo_orderdate = "inner".d_datekey) ...
確認するポイントはDS_BCAST_INNERと書かれている箇所です。これは内部結合テーブルがすべてのスライスに複製されることを示します。DS_DIST_BOTHラベルがある場合、外部結合テーブルと内部結合テーブルの両方がスライス全体に再分散されることを示します。複製と再分散は、クエリパフォーマンスの観点でコストの高い処理になることがあるため、無くすか減らすように分散キーの見直しをしましょう。
DS_BCAST_INNERの場合、内部結合テーブルが小さいのならばそこまで気にする必要はないと思います。ただし、その場合は内部結合テーブルにALL分散DISTSTYLE ALLを指定すればいいでしょう。
分散キーを合わせてテーブルを作成し直してEXPLAINを再度実行してみると、以下の通りDS_DIST_NONEに変わっているのがわかります。
QUERY PLAN -------------------------------------------------------------------------------------------- XN Merge (cost=1000000000025.71..1000000000026.21 rows=200 width=8) Merge Key: dwdate.d_year ... -> XN HashAggregate (cost=17.57..18.07 rows=200 width=8) -> XN Merge Join DS_DIST_NONE (cost=0.00..14.63 rows=588 width=8) Merge Cond: ("outer".lo_orderdate = "inner".d_datekey) ...
クエリプランの評価は以下の通りです。
| 再分散方式 | 内容 |
|---|---|
| DS_DIST_NONE | 再分散しない |
| DS_DIST_ALL_NONE | 内部結合テーブルで DISTSTYLE ALL が使用されているため再分散しない |
| DS_DIST_INNER | 内側のテーブルを再分散 |
| DS_BCAST_INNER | 内側のテーブルを全ノードスライスに複製 |
| DS_DIST_ALL_INNER | 外部テーブルで DISTSTYLE ALL が使用されるため内部テーブル全体を単一スライスに再分散 |
| DS_DIST_BOTH | 両方のテーブルを再分散 |
分散結果の確認
指定した分散スタイルによって、データがどのように分散されているか次のSQLで確認します。
select slice, col, num_values, minvalue, maxvalue from svv_diskusage where name='<tablename>' and col =0 order by slice, col;
キー分散
以下の例ではテーブルが非常に均等に分散されていることがわかります。ここでnum_valuesの値にバラつきがあるようなら分散キーの見直しをしましょう。
slice | col | num_values | minvalue | maxvalue -------+-----+------------+---------------------+---------------------- 0 | 0 | 125042 | 15 | 1000000 0 | 0 | 0 | 9223372036854775807 | -9223372036854775808 1 | 0 | 125077 | 1 | 999995 1 | 0 | 0 | 9223372036854775807 | -9223372036854775808 2 | 0 | 125369 | 4 | 999983 2 | 0 | 0 | 9223372036854775807 | -9223372036854775808 3 | 0 | 125096 | 3 | 999981 3 | 0 | 0 | 9223372036854775807 | -9223372036854775808 4 | 0 | 124671 | 5 | 999987 4 | 0 | 0 | 9223372036854775807 | -9223372036854775808 5 | 0 | 0 | 9223372036854775807 | -9223372036854775808 5 | 0 | 125306 | 11 | 999988 6 | 0 | 124376 | 2 | 999998 6 | 0 | 0 | 9223372036854775807 | -9223372036854775808 7 | 0 | 0 | 9223372036854775807 | -9223372036854775808 7 | 0 | 125063 | 13 | 999974
均等分散
1,000,000件のテーブルが 125,000件ずつ 8スライスに均等分散されています。
slice | col | num_values | minvalue | maxvalue -------+-----+------------+---------------------+---------------------- 0 | 0 | 0 | 9223372036854775807 | -9223372036854775808 0 | 0 | 125000 | 1 | 999993 1 | 0 | 125000 | 2 | 999994 1 | 0 | 0 | 9223372036854775807 | -9223372036854775808 2 | 0 | 0 | 9223372036854775807 | -9223372036854775808 2 | 0 | 125000 | 3 | 999995 3 | 0 | 125000 | 4 | 999996 3 | 0 | 0 | 9223372036854775807 | -9223372036854775808 4 | 0 | 125000 | 5 | 999997 4 | 0 | 0 | 9223372036854775807 | -9223372036854775808 5 | 0 | 0 | 9223372036854775807 | -9223372036854775808 5 | 0 | 125000 | 6 | 999998 6 | 0 | 125000 | 7 | 999999 6 | 0 | 0 | 9223372036854775807 | -9223372036854775808 7 | 0 | 0 | 9223372036854775807 | -9223372036854775808 7 | 0 | 125000 | 8 | 1000000 (16 rows)
ALL分散
キー分散と均等分散に比べて表示がわかりにくいのですが全てのノードにテーブル全体のコピーが分散されています。
slice | col | num_values | minvalue | maxvalue -------+-----+------------+---------------------+---------------------- 0 | 0 | 0 | 9223372036854775807 | -9223372036854775808 0 | 0 | 213745 | 786256 | 1000000 0 | 0 | 262085 | 524171 | 786255 0 | 0 | 262085 | 262086 | 524170 0 | 0 | 262085 | 1 | 262085 2 | 0 | 0 | 9223372036854775807 | -9223372036854775808 2 | 0 | 213745 | 786256 | 1000000 2 | 0 | 262085 | 524171 | 786255 2 | 0 | 262085 | 262086 | 524170 2 | 0 | 262085 | 1 | 262085 4 | 0 | 0 | 9223372036854775807 | -9223372036854775808 4 | 0 | 213745 | 786256 | 1000000 4 | 0 | 262085 | 524171 | 786255 4 | 0 | 262085 | 262086 | 524170 4 | 0 | 262085 | 1 | 262085 6 | 0 | 0 | 9223372036854775807 | -9223372036854775808 6 | 0 | 213745 | 786256 | 1000000 6 | 0 | 262085 | 524171 | 786255 6 | 0 | 262085 | 262086 | 524170 6 | 0 | 262085 | 1 | 262085 (20 rows)
分散キーについての参考リンク
最適な分散スタイルの選択 - Amazon Redshift
ステップ 4: 分散スタイルを選択する - Amazon Redshift
データ分散スタイルの選択 - Amazon Redshift
ソートキー（SORTKEY）
ソートキーはテーブルのデータを特定フィールドでソートするものです。分散キーは1個しか指定できませんが、ソートキーは400個まで指定可能です。以下のように指定します。
CREATE TABLE customer ( c_custkey INTEGER NOT NULL, c_name VARCHAR(25) NOT NULL, c_address VARCHAR(25) NOT NULL, c_city VARCHAR(10) NOT NULL ) SORTKEY (c_custkey, c_name);
ソートキーの選び方は WHERE 句の条件カラムを優先的に指定します。第一候補は TIMESTAMP です。AWSのドキュメントでソートキーのベストプラクティスとして次の3点が書かれています。
- 最新のデータが最も頻繁にクエリ処理される場合は、タイムスタンプ列をソートキーの主要な列として指定。
- 1 つの列に対して範囲フィルタリングまたは等価性フィルタリングを頻繁に実行する場合は、その列をソートキーとして指定。
- テーブルを頻繁に結合する場合は、結合列をソートキーと分散キーの両方として指定。
分散キーと比べると重要なポイントは少ないのですが、ソートキーの適切な指定もクエリの高速化に繋がってきます。例えば、日付でソートされているテーブルに対して WHERE 句で日付を指定すると、その範囲のデータのみを読み込むようになるため、読み込むデータ量を大幅に減らす事が可能です。ソートキーでテーブルを物理的に横に（行単位）で割り、カラムナーでテーブルを縦に（カラム単位）で割る事ができるため、必要な部分だけを実際に読み込み、ディスクIOを削ることで高速化に大きく貢献できるのです。
ソートキーの注意点
テーブルにソートキーを付けても自動でソートされる訳ではありません。自動でソートされるタイミングは、空のテーブルにCOPYコマンドで一括ロードした場合のみです。そのため、追加のデータをロードしたり INSERT した場合は、ソートキーとは関係なく順番に格納されてしまいます。
手動でソートするにはVACUUMコマンドを使用します。ただし、これは負荷が高いので、なるべく実行しなくてもいい設計にするか、夜間バッチなどで実行するようにしましょう。
VACUUM FULL <table>; VACUUM SORT ONLY <table>;
