読者です 読者をやめる 読者になる 読者になる

【AWS】Redshiftの分散キーとソートキー

AWS Redshift
スポンサーリンク

Redshiftのパフォーマンスで重要になる分散キーとソートキーについてまとめました。

分散キー(DISTKEY)

テーブルにデータをロードすると、そのテーブルの分散スタイルに従って、テーブルの行が各ノードスライスに分散されます。Redshift では1ノードの中で実際に処理を行うプロセスが複数動いており、このプロセスをノードスライスといいます。並列処理の単位はノードではなくノードスライス単位です。スライスの数は、次のようにノード上のプロセッサコアの数と同じになります。

f:id:tasukujp:20150829214616p:plain

均等分散

均等分散はデフォルトの分散スタイルです。リーダーノードは、特定の列の値に含まれている値にかかわらず、ラウンドロビン方式によって複数のスライス間で行を分散させます。例えば 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>;

ソートキーについての参考リンク

 最良のソートキーの選択 - Amazon Redshift

 ステップ 3: ソートキーを選択する - Amazon Redshift

 ソートキーの選択 - Amazon Redshift