Amazon Redshift テーブル設計詳細ガイド:Part 2 分散スタイルと分散キー
著者 Zach Christopherson は Amazon Redshift チームのシニアデータベースエンジニアです。
Part 1: Preamble, Prerequisites, and Prioritization（序文、事前準備、優先順位付け）
Part 2: Distribution Styles and Distribution Keys（分散スタイルと分散キー）［本稿］
Part 3: Compound and Interleaved Sort Keys（CompoundとInterleavedソートキー）
Part 4: Compression Encodings（圧縮エンコーディング）
Part 5: Table Data Durability（テーブルデータの永続性）
このブログ連載で最初に議論されるテーブルと列のプロパティは、テーブルの分散スタイル（DISTSTYLE）と分散キー（DISTKEY）です。このブログ記事では、皆さんの固有のワークロードに対して最適なDISTSTYLEとDISTKEYを選ぶ方法論を紹介します。
データをテーブルにロードするとき、Amazon Redshift はテーブルのDISTSTYLEに従って各コンピューティングノードに行を分散します。各コンピューティングノード内で、行はクラスタースライスに割り当てられます。ノードタイプに応じて、各コンピューティングノードには2または16、32のスライスが含まれます。1つのスライスは1つの仮想コンピューティングノードと考えることができます。クエリーの実行中、すべてのスライスはそれぞれに割り当てられた行を並列に処理します。テーブルのDISTSTYLEを選択する際の主たるゴールは、並列処理のためにクラスター全体にデータを均等に分散することです。
クエリーを実行すると、クエリーオプティマイザは結合操作や集約操作を効率よく実行するために、クラスター全体に中間タプルを再分散またはブロードキャストします。テーブルのDISTSTYLEを選択する際の第2のゴールは、クエリー処理に必要なデータ移動のコストを最小限に抑えることです。最小化を実現するには、クエリーを実行する前にデータを必要な場所に配置することです。
テーブルにはEVENまたはKEY、ALLのDISTSTYLEが定義されているかもしれません。これらのテーブルプロパティに詳しくない場合は、2016 AWS Santa Clara Summit での私のプレゼンテーションをご覧ください。17分目から分散の基本について説明しています。要約すると以下のとおりです。
- EVENはデータをラウンドロビンで分散します。
- KEYでは単一の列をDISTKEYとして定義する必要があります。取り込み時に、Amazon Redshift は各DISTKEY列の値をハッシュし、同一ハッシュを同一スライスに配置します。
- ALL分散は各ノードの最初のスライスにテーブルの完全なコピーを格納します。
あるテーブルに最も適したスタイルは、いくつかの基準によって決まります。皆さんのシナリオに最適なDISTSTYLEとDISTKEYを決定するために、そのデータの特性について質問する2段階のフローチャートをこの記事では用意しています。
フェーズ1: 適切なDISTKEY列の特定
第1段階では、KEY分散が適切かどうか判断します。もしDISTKEYがすでに指定されている場合は、それがテーブルデータを適切に分散できるかどうかを判断します。DISTKEYに適切な列がない場合は、このテーブルのDISTSTYLEの選択肢としてKEYは除外できます。
その列のデータは均一に分散していますか？
もしハッシュされた列の値でクラスタースライスに均一にデータ分散できない場合、格納時点でのデータの偏りとクエリー実行中のデータの偏りの両面から不均一な並列ワークロードを引き起こし、パフォーマンスに悪影響があります。不均一なデータ特性は次のようなシナリオで発生します。
- NULL値の割合が大きい列での分散
- 少数の顧客がデータの大部分を占める列であるcustomer_id列での分散
いくつかのシンプルなSQLコードを使ってデータセットを確認することで、ヘビーユーザーやホットスポットを含む列を簡単に見つけることができます。次の例ではl_oderkeyがDSITKEYの候補から除外できることを示しています。
root@redshift/dev=# SELECT l_orderkey, COUNT(*)
FROM lineitem
GROUP BY 1
ORDER BY 2 DESC
LIMIT 100;
l_orderkey | count
------------+----------
[NULL] | 124993010
260642439 | 80
240404513 | 80
56095490 | 72
348088964 | 72
466727011 | 72
438870661 | 72
...
...
ある列で分散させたときに、各スライス上のブロック数や行数がほぼ一定になることが望ましいです。ある列によって均等分散されると仮定したものの、本当にそうなるか確認したいとします。この場合、不均一な分散になることを発見するためにテーブル全体を再分散するのではなく、1列だけの一次テーブルを作成したほうがより効率的です。
-- 分散確認用の1列のテーブルを作成 CREATE TEMP TABLE lineitem_dk_l_partkey DISTKEY (l_partkey) AS SELECT l_partkey FROM lineitem; -- テーブルのOIDを特定（訳注: 後述の他SQLで使用します） root@redshift/tpch=# SELECT 'lineitem_dk_l_partkey'::regclass::oid; oid -------- 240791 (1 row)
確認用のテーブルができたら分散について確認することは簡単です。次のクエリー結果からDISTKEYが定義されたテーブルの次の特性について評価できます。
- skew_rows: 最も多くの行を含むスライスの行数と、最も少ない行を含むスライスの行数の比率。もしクラスター内の各スライスにデータが含まれていない場合は、デフォルト値として100.00になります。1.00に近いほうが理想的です。
- storege_skew: 最も多くのブロックを含むスライスのブロック数と、最も少ないブロックを含むスライスのブロック数の比率。1.00に近いほうが理想的です。
- pct_populated: クラスター内で、少なくとも1つのテーブルを含むスライスのパーセンテージ。100に近いほうが理想的です。
SELECT "table" tablename, skew_rows,
ROUND(CAST(max_blocks_per_slice AS FLOAT) /
GREATEST(NVL(min_blocks_per_slice,0)::int,1)::FLOAT,5) storage_skew,
ROUND(CAST(100*dist_slice AS FLOAT) /
(SELECT COUNT(DISTINCT slice) FROM stv_slices),2) pct_populated
FROM svv_table_info ti
JOIN (SELECT tbl, MIN(c) min_blocks_per_slice,
MAX(c) max_blocks_per_slice,
COUNT(DISTINCT slice) dist_slice
FROM (SELECT b.tbl, b.slice, COUNT(*) AS c
FROM STV_BLOCKLIST b
GROUP BY b.tbl, b.slice)
WHERE tbl = 240791 GROUP BY tbl) iq ON iq.tbl = ti.table_id;
tablename | skew_rows | storage_skew | pct_populated
-----------------------+-----------+--------------+---------------
lineitem_dk_l_partkey | 1.00 | 1.00259 | 100
(1 row)
注: データの偏りが多少あるからといって、すぐに適切な分散キーでないと判断することは適切ではありません。多くの場合、大きなJOIN操作に対するメリットは、わずかに不均一なワークロードを処理するクラスタースライスのコストを相殺します。
その列のカーディナリティは高いですか？
カーディナリティとは、列内にいくつの個別値が存在するかの相対的な尺度です。データ分散の均一性に加えて、カーディナリティも考慮することが重要です。一部のシナリオでは、データの均一な分散によって相対的なカーディナリティが低くなる可能性があります。相対的なカーディナリティが低いと、並列化が不十分なことでコンピューティング能力を浪費してしまいます。たとえば576スライス（36台のds2.8xlarge）のクラスターと次のテーブルを考えてみます。
訳注: 個別値とは、列をDISTINCTした結果の値のことです。たとえば、顧客テーブルの都道府県住所列の個別値数は47になります。
CREATE TABLE orders ( o_orderkey int8 NOT NULL, o_custkey int8 NOT NULL, o_orderstatus char(1) NOT NULL, o_totalprice numeric(12,2) NOT NULL, o_orderdate date NOT NULL DISTKEY, o_orderpriority char(15) NOT NULL, o_clerk char(15) NOT NULL, o_shippriority int4 NOT NULL, o_comment varchar(79) NOT NULL );
このテーブルの中には12カ月の注文をテーブルす10億のレコードがあります。毎日の注文数は多少の差はあれど安定していると思われます。この安定性により、均一に分散したデータセットが作成されます。
root@redshift/tpch=# SELECT o_orderdate, count(*) FROM orders GROUP BY 1 ORDER BY 2 DESC; o_orderdate | count -------------+--------- 1993-01-18 | 2651712 1993-08-29 | 2646252 1993-12-05 | 2644488 1993-12-04 | 2642598 ... ... 1993-09-28 | 2593332 1993-12-12 | 2593164 1993-11-14 | 2593164 1993-12-07 | 2592324 (365 rows)
ただし、o_orderdate DISTKEY 列の365個の個別値というのは、576個のクラスタースライスと比べるとカーディナリティは相対的には低くなります。各日付の値がハッシュされて空のスライスに割り当てられる場合、このデータは良くてもクラスターの63%にしか配置されません。クラスターの37%以上が、このテーブルをスキャンするときに動作しません。空のスライスに少なくとも1つの値を配置するのではなく、空ではないスライスにさらに複数の値を割り当てることになります。
-- 各スライスにいくつの値が割り当てられているか
root@redshift/tpch=# SELECT rows/2592324 assigned_values,
COUNT(*) number_of_slices FROM stv_tbl_perm
WHERE name=‘orders’ AND slice<6400
GROUP BY 1 ORDER BY 1;
assigned_values | number_of_slices
-----------------+------------------
0 | 307
1 | 192
2 | 61
3 | 13
4 | 3
(5 rows)
訳注: ブログシステムの制限により、シングルクォートがスマートクォート ‘ ’ になっていますので、お手数ですが置換してご利用ください
このシナリオでは、1日分のデータも格納されていないスライスが576個の内307個あり、4日分のデータが格納されているスライスが3個あります。クエリーの実行は、この3つのスライスがデータ処理できる速度に制限されています。同時に、クラスターの半分以上がアイドル状態のままです。
注: table_inspector.sqlクエリーの結果内のpct_slices_populated列で、クラスター内のスライスすべてを使って配置されていないテーブルを識別できます。
一方で、o_orderdate DISTKEY 列がtimestampデータ型で定義されていて、実際に注文されたタイムスタンプデータ（timestamp型として格納された日付データではなく）がそのまま格納されているとします。この場合、時間ディメンションの粒度から、列のカーディナリティが数百の単位から数百万の単位に増加します。この手法の結果、576個のすべてのスライスに均等に配置されるようになります。
注: タイムスタンプ列はあまり結合や集約に使用されないため、通常はDISTKEY列に適していません。このケースでは、相対的なカーディナリティがデータの粒度によってどのような影響があるのかと、クラスター全体でテーブルデータを均一かつ完全に分散させる方法について説明しています。
クエリーは選択的フィルターを実行しますか？
DISTKEY列がクラスター全体で均一にデータ分散してしていることが保証できたとしても、その列を使ってテーブルのレコードを選択的にフィルタリングすると、最適でない並列度になる可能性があります。これについての説明のために、o_orderdate列にDISTKEYが設定された同じordersテーブルに、365日分の10億レコードが配置されているとします。
CREATE TABLE orders ( o_orderkey int8 NOT NULL, o_custkey int8 NOT NULL, o_orderstatus char(1) NOT NULL, o_totalprice numeric(12,2) NOT NULL, o_orderdate date NOT NULL DISTKEY, o_orderpriority char(15) NOT NULL, o_clerk char(15) NOT NULL, o_shippriority int4 NOT NULL, o_comment varchar(79) NOT NULL );
今回は576個のスライスではなく、80個のスライス（5台のds2.8xlarge）を持つ小さなクラスター上のテーブルを考えてみましょう。均一なデータ分散と、クラスタースライス数より4から5倍以上多い個別値を使用することで、テーブル全体をスキャンするクエリーの実行が均一に並列化されるでしょう。これは、各スライスに同数のレコードが割り当てられることで実現されます。
ただし、多くのケースでは、テーブル全体をスキャンすることはあまりありません。時系列データの場合、過去1日や7日、30日のデータをスキャンするワークロードのほうが、テーブル全体を繰り返しスキャンするよりも一般的です。過去7日間の注文に対して分析する時系列ワークロードの一つについて考えてみましょう。
SELECT ... FROM orders JOIN ... JOIN ... WHERE ... AND o_orderdate between current_date-7 and current_date-1 GROUP BY ...;
このような述語によって、関連する値をわずか7日間に制限しています。この7日間は、クラスター内の最大7つのスライスに存在するはずです。一貫性のあるハッシュによって、この7つの値の内の1つ以上を含むスライスには、その値を持つすべてのレコードが含まれています。
root@redshift/tpch=# SELECT SLICE_NUM(), COUNT(*) FROM orders
WHERE o_orderdate BETWEEN current_date-7 AND current_date-1
GROUP BY 1 ORDER BY 1;
slice_num | count
-----------+---------
3 | 2553840
33 | 2553892
40 | 2555232
41 | 2553092
54 | 2554296
74 | 2552168
76 | 2552224
(7 rows)
上記のデータセットでは7つのスライスがあり、それぞれが処理を実行するために250万行ずつフェッチします。EVENディストリビューションを使用する場合は、80スライスで24万行（109レコード / 365日 * 7日間 / 80スライス）ずつフェッチすることが期待されます。重要な比較点は、7スライスだけで250万行ずつフェッチして処理するか、全80スライスで24万行ずつフェッチして処理するかです。
作業の大部分を処理する一部のスライドが相当なオーバーヘッドになる場合、選択的フィルターと分散方法を別にしておきたくなります。そのためには別の分散キーを選ぶ必要があります。
次のクエリーを使用して、テーブルの様々な列に対してフィルターする述語を含むスキャンがどのくらいの頻度であるのかを特定します。
SELECT
ti."table", ti.diststyle, RTRIM(a.attname) column_name,
COUNT(DISTINCT s.query ||‘-’|| s.segment ||‘-’|| s.step) as num_scans,
COUNT(DISTINCT CASE WHEN TRANSLATE(TRANSLATE(info,‘)’,‘ ’),‘(’,‘ ’)
LIKE (‘%’|| a.attname ||‘%’)
THEN s.query ||‘-’|| s.segment ||‘-’|| s.step END) AS column_filters
FROM stl_explain p
JOIN stl_plan_info i
ON ( i.userid=p.userid AND i.query=p.query AND i.nodeid=p.nodeid )
JOIN stl_scan s
ON (s.userid=i.userid AND s.query=i.query AND
s.segment=i.segment AND s.step=i.step)
JOIN svv_table_info ti ON ti.table_id=s.tbl
JOIN pg_attribute a ON (a.attrelid=s.tbl AND a.attnum > 0)
WHERE s.tbl IN ([table_id])
GROUP BY 1,2,3,a.attnum
ORDER BY attnum;
訳注: ブログシステムの制限により、シングルクォートがスマートクォート ‘ ’ になっていますので、お手数ですが置換してご利用ください
このクエリーの結果から、DISTKEY列の候補が頻繁にスキャンされていることが分かった場合、より複雑なSQLを使用してそのフィルターがさらに選択的であるかの調査を行なうことができます。
SELECT
ti.schemaname||‘.’||ti.tablename AS "table",
ti.tbl_rows,
AVG(r.s_rows_pre_filter) avg_s_rows_pre_filter,
100*ROUND(1::float - AVG(r.s_rows_pre_filter)::float/ti.tbl_rows::float,6)
avg_prune_pct,
AVG(r.s_rows) avg_s_rows,
100*ROUND(1::float - AVG(r.s_rows)::float/AVG(r.s_rows_pre_filter)::float,6)
avg_filter_pct,
COUNT(DISTINCT i.query) AS num,
AVG(r.time) AS scan_time,
MAX(i.query) AS query, TRIM(info) as filter
FROM stl_explain p
JOIN stl_plan_info i
ON ( i.userid=p.userid AND i.query=p.query AND i.nodeid=p.nodeid )
JOIN stl_scan s
ON (s.userid=i.userid AND s.query=i.query AND s.segment=i.segment AND
s.step=i.step)
JOIN (SELECT table_id,"table" tablename,schema schemaname,tbl_rows,unsorted,
sortkey1,sortkey_num,diststyle FROM svv_table_info) ti
ON ti.table_id=s.tbl
JOIN (
SELECT query, segment, step, DATEDIFF(s,MIN(starttime),MAX(endtime)) AS time,
SUM(rows) s_rows, SUM(rows_pre_filter) s_rows_pre_filter,
ROUND(SUM(rows)::float/SUM(rows_pre_filter)::float,6) filter_pct
FROM stl_scan
WHERE userid>1 AND type=2
AND starttime < endtime
GROUP BY 1,2,3
HAVING sum(rows_pre_filter) > 0
) r ON (r.query = i.query and r.segment = i.segment and r.step = i.step)
LEFT JOIN (SELECT attrelid,t.typname FROM pg_attribute a JOIN pg_type t
ON t.oid=a.atttypid WHERE attsortkeyord IN (1,-1)) a ON a.attrelid=s.tbl
WHERE s.tbl IN ([table_id])
AND p.info LIKE ‘Filter:%’ AND p.nodeid > 0
GROUP BY 1,2,10 ORDER BY 1, 9 DESC;
訳注: ブログシステムの制限により、シングルクォートがスマートクォート ‘ ’ になっていますので、お手数ですが置換してご利用ください
上記のSQLでは以下が返されます。
- tbl_rows: 現時点でのテーブル内の行数
- avg_s_row_pre_filter: フェッチ時にゾーンマップによってプルーニングされた後に実際にスキャンされた行数
- avg_prune_pct: ゾーンマップによってテーブルからプルーニングされた行のパーセンテージ
- avg_s_rows: SQLで定義されたフィルター条件を適用した後に残った行数
- avg_filter_pct: ユーザー定義フィルタが適用された後の、avg_s_rows_filterに対する相対的な残りの行のパーセンテージ
- num: このフィルター条件を含むクエリーの数
- scan_time: スキャンを含むセグメント取得完了までに要する平均秒数。
- query: これらのフィルター条件を発行したクエリーのクエリーIDの例
- filter: ユーザーが指定したフィルター条件の詳細
次のクエリー結果では、指定したあるフィルター述語がどれだけ選択的なのかについて評価できます。データ特性やフィルター条件によって制限された範囲内にいくつの個別値が存在するかのユーザーの事前知識によって、フィルターが選択的であるとみなすべきかどうかを識別できます。データ特性が不明な場合は、クエリー結果からSQLコードを作成して、その範囲内の個別値の数を取得できます。
table | public.orders tbl_rows | 22751520 avg_s_rows_pre_filter | 12581124 avg_prune_pct | 44.7021 avg_s_rows | 5736106 avg_filter_pct | 54.407 num | 2 scan_time | 19 query | 1721037 filter | Filter: ((o_orderdate < ‘1993-08-01’::date) AND (o_orderdate >= ‘1993-05-01’::date)) SELECT COUNT(DISTINCT o_orderdate) FROM public.orders WHERE o_orderdate < ‘1993-08-01’ AND o_orderdate >= ‘1993-05-01’;
訳注: ブログシステムの制限により、シングルクォートがスマートクォート ‘ ’ になっていますので、お手数ですが置換してご利用ください
特に次のような特徴のクエリパターンを持つ列は避けてください。
- tbl_rowsに対して
- avg_s_rowsが小さい
- avg_s_row_pre_filterが大きい
- DISTKEY列の候補に対する選択的フィルター
- 戻される範囲に含まれる個別値が限られている
- scan_timeが大きい
このようなパターンが列に存在する場合、その列は良いDISTKEY候補ではない可能性が高いです。
その列は第1ソートキーですか？
注: ソートキーについては、このブログ連載の第3回で詳しく説明します。
フローチャートで示されているように、その列を使用してレコードを選択的にフィルタリングしても（スライスの一部のみにスキャン後の処理を制限する可能性があります）、場合によっては、その列を分散キーとして使用することができます。
その列で選択的にフィルタリングしている場合、その列をソートキーとしても使用している可能性があります。この手法では、不要なスライス上での列ゾーンマップをより効果的に使用して、関連するブロックを迅速的に特定できます。これにより、各スライスで全列をスキャンするよりも選択してスキャンしたほうが桁違いに安いコストで実行できます。このコストの安さは、スキャン後に大量データ処理するスライス数の削減によるコスト増の相殺に役立ちます。
次のクエリーを使用して、テーブルの第1ソートキーを参照できます。
SELECT attname FROM pg_attribute WHERE attrelid = [table_id] AND attsortkeyord = 1;
先ほどのステップのSQLを使用して（avg_s_rowsや戻された範囲内の個別値の数など）、以下のような有効なDISTKEY候補の特性が含まれているかが分かります。
- tbl_rowsに対してavg_s_row_pre_filterの値が小さい
- avg_s_row_pre_filterとavg_s_rowsの値が近しい
- DISTKEY列の候補に選択的フィルターがある
- 戻された範囲内に多数の個別値がある
- 小さいか重大ではないscan_time
このようなパターンが存在する場合、この列は良いDISTKEY候補である可能性があります。
クエリーは MERGE JOIN を使用していますか？
次の条件が満たされるとき、MERGE JOIN 操作が使用できます。これは3種類の結合操作の中で、最も高速です。
- 2つのテーブルが（ソートキーで）ソート済みで、同じ列で分散されている
- どちらのテーブルも80%以上ソートされている（svv_table_info.unsorted < 20%）
- 2つのテーブルがJOIN条件でDISTKEY列とSORTKEY列を使用して結合されている
このように条件が限られているため、たまたま MERGE JOIN 操作が発生することはあまりありません。一般的には、エンドユーザーがこの結合操作を強制するために明示的に設計上の決定を行ないます。通常は特定のあるクエリーのパフォーマンス向上のためです。このJOINパターンがワークロードに存在しない場合、この最適化されたJOIN操作の恩恵を受けることはありません。
以下のクエリーでは、テーブルをスキャンしたクエリーの数と、同じ列でソートと分散されていてスキャンされる別のテーブル、実行された何らかのJOIN操作を戻します。
SELECT COUNT(*) num_queries FROM stl_query
WHERE query IN (
SELECT DISTINCT query FROM stl_scan
WHERE tbl = [table_id] AND type = 2 AND userid > 1
INTERSECT
SELECT DISTINCT query FROM stl_scan
WHERE tbl <> [table_id] AND type = 2 AND userid > 1
AND tbl IN (
SELECT DISTINCT attrelid FROM pg_attribute
WHERE attisdistkey = true AND attsortkeyord > 0
MINUS
SELECT DISTINCT attrelid FROM pg_attribute
WHERE attsortkeyord = -1)
INTERSECT
(SELECT DISTINCT query FROM stl_hashjoin WHERE userid > 1
UNION
SELECT DISTINCT query FROM stl_nestloop WHERE userid > 1
UNION
SELECT DISTINCT query FROM stl_mergejoin WHERE userid > 1)
);
このクエリーによって戻される値は、ほかのいずれかのテーブルを変更することなく、既存のクエリーで MERGE JOIN される可能性があることを意味します。このクエリーで結果がなにも戻されない場合は、単一のクエリーのパフォーマンス向上のために複数のテーブルを事前に調査する必要があります。
注: 複数のテーブルの評価と変更が必要な MERGE JOIN 最適化を行ないたい場合、この簡潔な方法とは異なるやり方で問題に対応します。このより複雑なやり方はこの記事の範囲を越えています。このような最適化の実装に興味がある場合は、JOIN操作に関するドキュメントを確認し、このブログ記事の末尾にあるコメント欄で具体的に質問してください。
フェーズ1のまとめ
このフェーズでは、あるテーブル内のどの列が適切なDISTKEY候補であるかという疑問に答えました。これらの手順が完了すると、特定のテーブルとデータセットに対して候補列が何個も見つかるかもしれません。次のフェーズでは、これらの候補列が複数あること（または一つもないこと）を念頭に置いています。
フェーズ2: 分散スタイルの決定
フェーズ2では分散スタイル候補について深く掘り下げて、ワークロードに対してどれが最適化を判断します。一般的には、適切な場合はいつでもDISTSTYLEにKEYを選択するのが最善です。ALLを選ぶことが（そしてKEYを選ばないことが）理にかなっているのであれば、ALLを選びます。KEYもALLも適切でない場合だけEVENを選びます。
この決定を補助するために、以下のフローチャートを使って作業します。DISTSTYLEはテーブルのプロパティなので、フェーズ1が完了したテーブルごとに分析します。
そのテーブルはJOINに使用されていますか？
DISTSTYLE ALL はJOIN条件で指定された列に関わらず、JOIN操作のコロケーションを確保するためだけに使用されます。テーブルがJOIN操作に使用されていない場合、DISTSTYLE ALL はパフォーマンス上のメリットを提供しないため、検討から排除する必要があります。
コロケーションのメリットを受けるJOIN操作は、一連のデータベース操作すべてに関係します。WHERE句やJOIN句による結合操作（INNTERやOUTERなど）は明示的ですが、INや NOT IN、MINUS、EXCEPT、INTERSECT、EXISTSなどの明示的でない操作や構文もあります。テーブルがJOINに使用されるかどうかに答えるときは、これらの操作すべてを考慮してください。
次のクエリーでは、このテーブルをスキャンした個別のクエリーがいくつあるのか、そのクエリー内でJOIN操作が1回以上含まれているかどうかを確認できます。
SELECT COUNT(*) FROM ( SELECT DISTINCT query FROM stl_scan WHERE tbl = [table_id] AND type = 2 AND userid > 1 INTERSECT (SELECT DISTINCT query FROM stl_hashjoin UNION SELECT DISTINCT query FROM stl_nestloop UNION SELECT DISTINCT query FROM stl_mergejoin));
このクエリーが0を戻す場合、使用されている操作に関係なく、このテーブルはいかなるJOIN操作にも関与していません。
注: ある特殊なクエリーパターンでは、このクエリーで偽陽性のある結果を戻す場合があります（たとえばJOINを含むサブクエリーの結果セットを遅延追加するテーブルに対する単純なスキャンなど）。もし不可解な点がある場合は、以下のコードで具体的なクエリーを常に参照できます。
SELECT userid, query, starttime, endtime, rtrim(querytxt) qtxt FROM stl_query WHERE query IN ( SELECT DISTINCT query FROM stl_scan WHERE tbl = [table_id] AND type = 2 AND userid > 1 INTERSECT (SELECT DISTINCT query FROM stl_hashjoin UNION SELECT DISTINCT query FROM stl_nestloop UNION SELECT DISTINCT query FROM stl_mergejoin)) ORDER BY starttime;
テーブルに少なくとも1つのDISTKEY候補が含まれていますか？
フェーズ1で記述されているプロセスは、テーブルの適切なDISTKEY列を識別するのに役立ちました。適切なDISTKEY列が存在しない場合、KEY DISTSTYLE は検討から外れます。適切なDISTKEY列が存在する場合、EVEN分散は検討から外れます。
この単純なルールでは、KEY、EVEN、ALLのいずれか一つに決定されません。次のような複数の選択肢となります。
- 少なくとも一つの有効なDISTKEY列が存在する場合はKEYまたはALL
- 有効なDISTKEY列が存在しない場合はEVENまたはALL
追加のストレージオーバーヘッドを許容できますか？
追加のストレージオーバーヘッドを許容できるかどうかを判断するための質問は「テーブルのサイズはどれくらいであり、現在どのように分散されていますか？」です。この質問に対して、次のクエリーで答えることができます。
SELECT table_id, "table", diststyle, size, pct_used FROM svv_table_info WHERE table_id = [table_id];
次の例では、異なるDISTSTYLEを使用した同じordersテーブルの複数バージョンで、それぞれが消費している1MBブロック数とクラスターストレージの合計値に対するパーセンテージを表示しています。
root@redshift/tpch=# SELECT "table", diststyle, size, pct_used
FROM svv_table_info
WHERE "table" LIKE ‘orders_diststyle_%’;
table | diststyle | size | pct_used
-----------------------+-----------------+-------+----------
orders_diststyle_even | EVEN | 6740 | 1.1785
orders_diststyle_key | KEY(o_orderkey) | 6740 | 1.1785
orders_diststyle_all | ALL | 19983 | 3.4941
(3 rows)
訳注: ブログシステムの制限により、シングルクォートがスマートクォート ‘ ’ になっていますので、お手数ですが置換してご利用ください
DISTSTYLE EVEN またはKEYの場合、各ノードはテーブルデータ全体の内の一部のみを受け取ります。しかし、DISTSTYLE ALL では、各コンピューティングノード上にテーブル全体が格納されます。ALLの場合、ノードをクラスターに追加してもノードあたりのデータ量は変化しません。これが重要かどうかはテーブルサイズやクラスター構成、ストレージオーバーヘッドによって決まります。1ノードあたり16TBのストレージを備えたds2.8xlargeを使用する場合、この増加はノード単位のストレージ量にはほとんど影響しません。しかし、1ノードあたり16GBのストレージしか備えていないdc1.largeを使用する場合、クラスター全体のストレージの使用量が許容できないほど増加すると判断される可能性があります。
KEYまたはEVENで分散しているテーブルの現行サイズにノード数を掛けることで、DISTSTYLE ALL にしたときのテーブルのサイズを概算することができます。次の手法によって、ALLがテーブルストレージを許容できないほど増加させるかどうかを判断するための情報を提供できます。
SELECT "table", size, pct_used, CASE diststyle WHEN ‘ALL’ THEN size::TEXT ELSE ‘< ’ || size*(SELECT COUNT(DISTINCT node) FROM stv_slices) END est_distall_size, CASE diststyle WHEN ‘ALL’ THEN pct_used::TEXT ELSE ‘< ’ || pct_used*(SELECT COUNT(DISTINCT node) FROM stv_slices) END est_distall_pct_used FROM svv_table_info WHERE table_id = [table_id];
訳注: ブログシステムの制限により、シングルクォートがスマートクォート ‘ ’ になっていますので、お手数ですが置換してご利用ください
見積もりが受け入れられない場合、DISTSTYLE ALL は検討から除外されるべきです。
クエリーの並列度を下げることを許容できますか？
MPPデータベースシステムでは、複数の分散リソースでデータセット全体の内の一部ずつを処理することで、大規模なパフォーマンスを達成しています。DISTSTYLE ALL は、各ノードでのコロケーションを保証するために、読み取り操作と書き込み操作の両方の並列度を犠牲にします。
DISTSTYLE ALL テーブルの利点は並列度が下がると相殺されてしまう面があります。この観点からは、DISTSTYLE ALL は正しい選択肢ではないかもしれません。この判断基準は書き込み操作と読み込み操作で異なる場合があります。
書き込み操作
KEYまたは EVEN DISTSTYLE のテーブルの場合、データベースへの書き込み操作は各スライスで並列化されます。この並列性はそれぞれのスライスが書き込み操作全体の内の一部だけを処理することを意味します。ALL分散では、すべてのノードでそれぞれのノードが同期されたデータセット全体を持っているため、すべてのノードごとに完全な書き込みを実行する必要あるので、書き込み操作は並列化のメリットを得られません。この手法はKEYまたはEVEN分散テーブルで実行される同じ書き込み操作と比較して、パフォーマンスを大幅に低下させます。
テーブルが頻繁に書き込まれる対象で、パフォーマンス劣化が許容できないと判明した場合、DISTSTYLE ALL を検討から除外します。
このクエリーはテーブルを変更した書き込み操作の数を戻します。
SELECT ‘[table_id]’ AS "table_id", (SELECT count(*) FROM (SELECT DISTINCT query FROM stl_insert WHERE tbl = [table_id] INTERSECT SELECT DISTINCT query FROM stl_delete WHERE tbl = [table_id])) AS num_updates, (SELECT count(*) FROM (SELECT DISTINCT query FROM stl_delete WHERE tbl = [table_id] MINUS SELECT DISTINCT query FROM stl_insert WHERE tbl = [table_id])) AS num_deletes, (SELECT COUNT(*) FROM (SELECT DISTINCT query FROM stl_insert WHERE tbl = [table_id] MINUS SELECT distinct query FROM stl_s3client MINUS SELECT DISTINCT query FROM stl_delete WHERE tbl = [table_id])) AS num_inserts, (SELECT COUNT(*) FROM (SELECT DISTINCT query FROM stl_insert WHERE tbl = [table_id] INTERSECT SELECT distinct query FROM stl_s3client)) as num_copies, (SELECT COUNT(*) FROM (SELECT DISTINCT xid FROM stl_vacuum WHERE table_id = [table_id] AND status NOT LIKE ‘Skipped%’)) AS num_vacuum;
訳注: ブログシステムの制限により、シングルクォートがスマートクォート ‘ ’ になっていますので、お手数ですが置換してご利用ください
テーブルに書き込みがほとんど行なわれない、またはパフォーマンス劣化が許容できる範囲であれば、DISTSTYLE ALL は引きつづき有効な選択肢です。
読み取り操作
単一のクエリーでも、DISTSTYLE ALL テーブルにアクセスする読み取りには、同じデータをスキャンして処理する複数のスライスが必要となります。この手法は、結合や集約を容易にするために、データのブロードキャストや再分散によるネットワークI/Oオーバーヘッドを回避することによってクエリーのパフォーマンス向上を目指しています。同時に、同じデータに対して複数回実行される冗長な作業のために、必要な計算量とディスクI/Oが増加します。
いろいろな方法でテーブルにアクセスし、ときには結合したり、ときには結合しなかったりすると思います。この場合、DISTSTYLE ALL での結合によるメリットが重要かつそれを望んでいるのか、それとも並列度の低下によるコスト増がクエリーに大きな影響を与えるのかを判断する必要があります。
回避すべきパターンと傾向
DISTSTYLE ALL テーブルは、小さくてあまり変化のないディメンションテーブルに最適です。一般的なガイドラインとして、次のような傾向がある場合は DISTSTYLE ALL に不向きなテーブルです。
- 読み取り操作
- 大きなファクトテーブルに対するスキャン
- 結合しない単一のテーブルスキャン
- 複雑な集計のあるテーブルへのスキャン（たとえばパーティションやオーダー、フレーム句を使ったウインドウ集約関数）
- 書き込み操作
- DML文で頻繁に変更されるテーブル
- 膨大なデータをロードするテーブル
- VACUUMまたは VACUUM REINDEX 操作で頻繁にメンテナンスする必要のあるテーブル
これらの条件を満たす方法でテーブルにアクセスする場合、DISTSTYLE ALL は有効な選択肢ではありません。
JOIN条件でDISKEY列の候補を使用しますか？
そのテーブルがJOIN操作に使われ、適切なDISTKEY列を持っている場合、KEYまたはALLの分散スタイルを指定すべきです。テーブルがJOIN操作にどのように関与し、ほかの外部要因も想定されない場合のみ、次の基準が適用されます。
- これらのいずれかのが当てはまる場合はALL分散が最も適しています。
- 複数の列で構成されたJOIN条件に使われているテーブル
- 最も重要なJOIN条件が適切なDISTKEY列を使用していない
- 異なる条件列と同程度重要なJOINに使われているテーブル
- 重要なJOIN条件が一貫してDISTKEY列候補を含む場合、KEY分散が最も適切です
最適なDISTKEY列の決定
DISTSTYLE KEY がそのテーブルに最適であると判断した場合、次のステップは理想的なDISTKEY列として機能する列を決定することです。フェーズ1で適切なDISTKEY列の候補として挙げたものの内、特定のワークロードに最も大きな影響を及ぼす列を特定することが必要になります。
単一の候補しかないテーブルまたはJOINに候補列の1つのみしか使用していないワークロードでは、選択肢は明らかです。同じテーブルに対して複数のJOIN条件が混在しているワークロードの場合、最適な列はビジネス要件に基づいて決定されます。
たとえば、よくある一般的なシナリオで分散方法について自問自答する内容は次のとおりです。
- 私の変換SQLコードとレポーティングワークロードは、異なる列を使用しています。変換ジョブとレポーティングの性能のどちらを向上させたいですか？
- 私のダッシュボードクエリーと定型レポートは異なるJOIN条件を使用しています。ビジネスクリティカルなレポートのSLAよりも、インタラクティブなクエリーのエンドユーザー体験を重視していますか？
- 重要度の低い分析のために毎日何千回もJOIN条件に使われているcolumn_Aで分散するべきですか？ それとも、重要な分析のために毎日10回参照されるcolumn_Bで分散するべきですか？ むしろ、毎日1000回実行される5秒のクエリーを2秒に改善すべきでしょうか？ それとも、1日に2回しか実行されない60分のクエリーを24分に改善すべきでしょうか？
ビジネス要件とどこに価値を置くかがこの質問への回答になります。すべてのシナリオをカバーできるガイドを提供する簡単な方法はありません。JOIN条件が混在し、価値のあるものが明らかでないシナリオの場合は、常に複数の分散キーをテストし、最適なものを測定する必要があります。あるいは、異なる列で分散されたテーブルの複数コピーを作成し、クエリー要件に基づいて異なるテーブルを使い分けることもできます。後者の手法を取る場合は、pgbouncer-rrはエンドユーザーのクエリールーティングを単純化するのに最適なユーティリティです。
次のステップ
テーブルに最適なDISTSTYLEとDISTKEYオプションを選択すると、データが並列処理のために均等に分散され、クエリー実行中のデータの再分散が最小限に抑えられます。これによって、数ペタバイトのデータベースに対する複雑な分析ワークロードを実現できるようになります。
前述のプロセスを実行することで、それぞれのテーブルの理想的なDISTSTYLEとDISTKEYを決定できました。最後のステップは、これらの最適化を適用するためにテーブルを再構築することです。この再構築はいつでも実行できます。ただし、Amazon Redshift テーブル設計詳細ガイドのパート3、4、5を引きつづき読む場合は、読み終えるまでテーブル再構築の実行を待つこともできます。または、以降のパートで説明する最適化を実装するたびにテーブルを再構築してもよいでしょう。
パート3では、パフォーマンス向上を実現するもう一つの重要な方法であるソート手法やソートキーに関連するテーブルプロパティをどう使うのかについて説明します。
Part 1: Preamble, Prerequisites, and Prioritization（序文、事前準備、優先順位付け）
Part 2: Distribution Styles and Distribution Keys（分散スタイルと分散キー）［本稿］
Part 3: Compound and Interleaved Sort Keys（CompoundとInterleavedソートキー）
Part 4: Compression Encodings（圧縮エンコーディング）
Part 5: Table Data Durability（テーブルデータの永続性）
著者について
Zach Christopherson はパロアルトを拠点とするAWSのシニアデータベースエンジニアです
彼は、全ての業界において、Amazon Redshift のユーザーが最適なパフォーマンスを得るためのワークロードをチューニングすることを支援しています。 Amazon Redshift サービスチームの一員として、彼は新しいサービスや既存のサービスの開発にも影響を与えており、貢献しています。休暇の際は妻のメアリーと一緒に新しいレストランにトライし、生まれたばかりの娘ソフィアの世話を楽しんでいます。
※日本語訳はデータベースSA柴田<@rewse>が担当しました。原文は Amazon Redshift Engineering’s Advanced Table Design Playbook: Distribution Styles and Distribution Keys
コメント