【Amazon Redshift】分散スタイルの効果実証ログ

Amazon Redshift の分散スタイルのチューニング結果を手元で試したログ。

結論

  • 分散スタイル設定の主目的は、ネットワークトラフィック抑制。
  • BIツールから投げられるような、取得する総データ量が軽微なクエリに対して、レスポンスの改善は期待できない。
  • バッチ処理によるGROUP BY や、JOINを多量にして、VARCHAR(50)とかデータ量の多いデータをどんどん取得・集計するクエリにはちょっと効果があるかも。

何故分散スタイルをチューニングするのか

再分散を抑制するため。

  • Redshiftは演算を実施するスライスたちにデータを分散させ、並列で実行させるアーキテクチャ。
  • 再分散とは、分散したデータを1か所に集めないとできない演算が発生したときに、ネットワーク経由ですべてのノードにデータが完全に複製している状態を仮想で作り出す実行ステップ。

再分散はどれほど怖いのか？

  • 再分散はテーブル結合やGROUP BY 句を伴うクエリで発生する。

  • 単純なクエリで、最終的に転送するデータ量が少ないテーブルに対しては脅威とならない。

  • 最終的に取得する・転送するデータ量が多いとき、多量のbyteでデータ転送が発生するとき、再分散だけで数分要するなどのケースが発生しえる。

    • GROUP BY 後に流れる総データ量が数百GBに達するINSERT SELECTや、数千万レコードと数千万レコードでのJOINが発生するなど、明確なボトルネックが発生するケースのみチューニングする価値がある。

  • 何より肝心なのが、単体のレスポンスには直接関与することは少ない。たいていのクエリでは、EVENからDISTにして5～10%性能向上する程度。

  • トラフィック抑制には一定有効。逆に言えば、利用者があまりおらずトラフィックやリソースが競合しない環境ではチューニングに対する定常的な効果は薄い。

脅威とならないケースのコスト検証

  • あまり脅威とならないケースから。
  • 下記は、SQLを実行後、SELECT pg_last_query_id(); などでquery idを取得して svl_query_summaryテーブルの明細を出したもの。
-- サンプルテーブルに対して行取得
-- sandbox.tdata_even→ 1000万レコード
-- sandbox.master_a_10000→ 1万レコード
-- 取得結果→ 1万レコード
SELECT  master_a_10000.codea, count(*)
FROM sandbox.tdata_even
  LEFT JOIN sandbox.master_a_10000 ON (tdata_even.codea_loop = master_a_10000.codea)
GROUP BY codea;

SELECT pg_last_query_id();
SELECT query,stm,seg,step,maxtime,rows,bytes,label,workmem FROM svl_query_summary WHERE query = 6023269 ORDER BY query, stm, seg, step;

実績取得結果

query stm seg step maxtime rows bytes label workmem
6023269 0 0 0 64765 10000 130000 scan tbl=5892173 name=master_a_10000 0
6023269 0 0 1 64765 10000 0 project 0
6023269 0 0 2 64765 10000 80000 bcast 0
6023269 0 1 0 65898 10000 80000 scan tbl=852075 name=Internal Worktable 0
6023269 0 1 1 65898 10000 0 project 0
6023269 0 1 2 65898 10000 160000 hash tbl=405 256376832
6023269 1 2 0 213503 10000000 130000000 scan tbl=5891185 name=tdata_even 0
6023269 1 2 1 213503 10000000 0 project 0
6023269 1 2 2 213503 10000000 0 project 0
6023269 1 2 3 213503 10000000 0 hjoin tbl=405 0
6023269 1 2 4 213503 10000000 0 project 0
6023269 1 2 5 213503 10000000 0 project 0
6023269 1 2 6 213503 1000 24000 aggr tbl=414 60555264
6023269 1 2 7 213503 1000 0 dist 0
6023269 1 3 0 215655 1000 16000 scan tbl=852077 name=Internal Worktable 0
6023269 1 3 1 215655 1000 24000 aggr tbl=417 242221056
6023269 1 3 2 215655 1000 0 project 0
6023269 1 3 3 215655 1000 0 project 0
6023269 1 3 4 215655 1000 0 return 0
6023269 1 3 5 215655 0 0 merge 0
6023269 1 3 6 215655 0 0 aggr tbl=425 0
6023269 1 3 7 215655 0 0 project 0
6023269 1 4 0 216345 1000 16000 scan tbl=852078 name=Internal Worktable 0
6023269 1 4 1 216345 1000 17000 return 0
  • 上記のうち、labelがbcast（ブロードキャスト）およびdist（必要分だけ分散）が再分散の実行ステップ明細になる。
  • ご覧の通り、scan（スキャン）やproject（データ選択操作）で処理したbytesの総量は130MB にも上っているが、肝心の再分散の転送バイト数は非常に小さいものとなっている。
    • Redshiftは列指向で処理するため、SQLで指定した列のみを処理している。再分散についても同様で、100列あろうとSQLに書いた列のみが対象となる。
    • GROUP BY 演算（project、aggr）に対しても発生するが、svl_query_summaryを見るに、どうやら1スライス内でできる限り演算量を減らした後、必要分だけ分散している。

改善ケース：表結合による大量レコードの分散

  • 通常、大量レコードのテーブル×数レコードのテーブルの結合では、負荷の少ないほうのテーブルが分散する。（ANALYZEにて統計情報を最新化しておく必要あり。また、BCAST_BOTHの場合はどちらのテーブルも分散される）
  • レコード数が少ないテーブルの分散は数十kbに満たないこと多々あり、これらがボトルネックとなることはまれ。

バッドケースのコスト検証

-- 1000万レコードx1000万レコードで、1レコードあたり 150byte程度のデータを1万レコードに集約
SELECT a.codea_loop, count(*)
FROM sandbox.tdata_even a join sandbox.tdata_even b using (sha512)
GROUP BY a.codea_loop;
SELECT pg_last_query_id();
SELECT query,stm,seg,step,maxtime,rows,bytes,label,workmem FROM svl_query_summary WHERE query = 6023418 ORDER BY query, stm, seg, step;
query stm seg step maxtime rows bytes label workmem
6023687 0 0 0 5821035 10000000 1400000000 scan tbl=5891185 name=tdata_even 0
6023687 0 0 1 5821035 10000000 0 project 0
6023687 0 0 2 5821035 10000000 1400000000 bcast 0
6023687 0 1 0 5835378 10000000 1400000000 scan tbl=853799 name=Internal Worktable 0
6023687 0 1 1 5835378 10000000 0 project 0
6023687 0 1 2 5835378 10000000 1520000000 hash tbl=405 256376832
6023687 0 1 2 5822111 0 0 hash tbl=405 0
6023687 1 2 0 15292438 10000000 1450000000 scan tbl=5891185 name=tdata_even 0
6023687 1 2 1 15292438 10000000 0 project 0
6023687 1 2 2 15292438 10000000 0 project 0
6023687 1 2 3 15292438 10000000 0 hjoin tbl=405 0
6023687 1 2 4 15292438 10000000 0 project 0
6023687 1 2 5 15292438 10000000 0 project 0
6023687 1 2 6 15292438 1000 24000 aggr tbl=414 18087936
6023687 1 2 7 15292438 1000 0 dist 0
6023687 1 3 0 15305103 1000 16000 scan tbl=853810 name=Internal Worktable 0
6023687 1 3 1 15305103 1000 24000 aggr tbl=417 72351744
6023687 1 3 2 15305103 1000 0 project 0
6023687 1 3 3 15305103 1000 0 project 0
6023687 1 3 4 15305103 1000 0 return 0
6023687 1 3 5 15305103 0 0 merge 0
6023687 1 3 6 15305103 0 0 aggr tbl=424 0
6023687 1 3 7 15305103 0 0 project 0
6023687 1 4 0 15305790 1000 16000 scan tbl=853811 name=Internal Worktable 0
6023687 1 4 1 15305790 1000 17000 return 0
  • bcast により、1,400,000,000 bytes の転送がある。全ノード含めて1.4GB弱。
    • 再分散のためのネットワーク帯域、メモリスペース、およびCPUパワーすべてを消費していると思われる。
    • このクエリ単体ではあまりボトルネックとならないが、複数並列で稼働した場合に、各リソースを奪い合いボトルネックとなることが起こりえる。
  • また、このクエリは全体で21秒かかったが、結合に使ったのが128バイトのVARCHARであり、HASH演算と結合にかかったコストが非常に高かったことが主要因。

改善する

先ほどEVEN分散だったテーブルを、sha512列を分散キーに選んだテーブルに変更しなおしたテーブルを作成。

SELECT a.codea_loop, count(*)
FROM sandbox.tdata_distsha512 a join sandbox.tdata_distsha512 b using (sha512)
GROUP BY a.codea_loop;
SELECT pg_last_query_id();
query stm seg step maxtime rows bytes label workmem
6023693 0 0 0 4495695 10000000 1400000000 scan tbl=5893210 name=tdata_distsha512 0
6023693 0 0 1 4495695 10000000 0 project 0
6023693 0 0 2 4495695 10000000 0 project 0
6023693 0 0 3 4495695 10000000 1520000000 hash tbl=403 256376832
6023693 1 1 0 13986572 10000000 1450000000 scan tbl=5893210 name=tdata_distsha512 0
6023693 1 1 1 13986572 10000000 0 project 0
6023693 1 1 2 13986572 10000000 0 project 0
6023693 1 1 3 13986572 10000000 0 hjoin tbl=403 0
6023693 1 1 4 13986572 10000000 0 project 0
6023693 1 1 5 13986572 10000000 0 project 0
6023693 1 1 6 13986572 2000 48000 aggr tbl=412 21626880
6023693 1 1 7 13986572 2000 0 dist 0
6023693 1 2 0 13994773 2000 32000 scan tbl=853838 name=Internal Worktable 0
6023693 1 2 1 13994773 1000 24000 aggr tbl=415 86507520
6023693 1 2 2 13994773 1000 0 project 0
6023693 1 2 3 13994773 1000 0 project 0
6023693 1 2 4 13994773 1000 0 return 0
6023693 1 2 5 13994773 0 0 merge 0
6023693 1 2 6 13994773 0 0 aggr tbl=422 0
6023693 1 2 7 13994773 0 0 project 0
6023693 1 3 0 13995445 1000 16000 scan tbl=853839 name=Internal Worktable 0
6023693 1 3 1 13995445 1000 17000 return 0
  • 先ほどと比べると、stm 0 の再のbcastとInternalWorktableに対するscan / projectがなくなり、seg1が消滅した。
  • ただし、改善しても所要秒数は 18.5 秒と、10%強しか改善していない。
  • ネットワークトラフィックは1.4GB弱から数十KBにまで低減している。（詳しくは、STL_DISTやSTL_BCASTテーブルから観測可能。）