[AWS Black Belt Online Seminar] Amazon Redshift テーブル設計詳細ガイド:レポート

こんにちは、菊池です。

2017年8月29日(火)のAWS Black Belt Online Seminarを受講しましたので、レポートします。

今回は Amazon Redshift テーブル設計詳細ガイド ということで、。講師はシバタツの愛称でおなじみ、アマゾンウェブサービスジャパン、ソリューションアーキテクトの柴田竜典さんでした。

レポート

  • Redshiftをお使いの方の悩み
    • クエリー性能を向上させたい <- 今回のテーマ
    • 同時実行をうまくさばきたい
    • 料金を抑えたい
  • クエリ性能向上に大切なこと
  • 分散スタイルとソートキーの悩み
    • それぞれの方式のメリット/デメリット
    • その場その場で決めるので統一感がない
    • DDL設計者によって異なるポリシー

Agenda

  • 分散スタイル
    • 分散スタイルはなぜ重要なのか
    • 分散キーの候補となる列の抽出
    • 分散スタイルの決定
    • 最適な分散キーの決定
  • ソートキー
    • ソートキーについて
    • ソート形式の決定
    • 最良のソートキー列の決定

分散スタイルはなぜ重要なのか

  • 分散スタイルとは何か
    • 1万枚の注文書と5人の名前が書かれたお得意様リストがある。
    • お得意様からの注文書を10人で抽出したい。
      • EVEN分散:注文書を上から1000枚ずつ10人に配る
      • KEY分散:注文書を、注文者名が「ア行で始まる人」「カ行で始まる人」…の10グループに分ける
      • ALL分散:名簿を10部コピーして10人に配る
    • 最適な分散スタイルの例:結合の観点から
      • ○:EVEN分散された注文書とALL分散された名簿
        • 10人が等しい分量の作業を行う
      • △:KEY分散された注文書とALL分散された名簿
        • ア行やカ行に比べ、ワ行の件数が少ない
      • ×:ALL分散された注文書とALL分散された名簿
        • 10人が全く同じ作業を行う
        • 注文書のコピーが無駄(Redshiftのストレージに相当)
    • 現実はさらに難しい
      • 様々なクエリーに対応する必要がある
      • 分散キーは1テーブルに1個しか選べない
      • フローチャートで機械的に判断しよう

分散キーの候補となる列の抽出

  • その列のデータは均一に分散しているか
    • ア行vsワ行のように偏りがないか
    • NULLの割合が大きくないか
SELECT
col1,
COUNT(*)
FROM lineitems
GROUP BY col1
ORDER BY 2 DESC;
  • その列のカーディナリティは高いか
    • カーディナリティはスライス数に対して相対的に大きい必要がある(スライスの4〜5倍が目安)
SELECT APPROXIMATE COUNT (DISTINCT sku)
FROM lineitems;
  • その列でフィルターされるか
    • その列でフィルターされた結果、1つのスライスしか使われない場合も
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_name>'::regclass::oid)
GROUP BY 1, 2, 3, a.attnum
ORDER BY attnum;
  • その列は第1ソートキーか
    • フィルターされる列はソートキーにも使用されていることが少なくない
    • ソートキーにも使用されている場合、ゾーンマップが効く可能性
    • ゾーンマップが効くとスキャン速度の大幅向上が、スライス数の少なさを補うことも
      • IO量が劇的に減る
SELECT attname
FROM pg_attribute
WHERE attrelid = '<table_name>'::regclass::oid
AND attsortkeyord = 1;
  • その列でMERGE JOINを期待するか
    • 以下の条件を全て満たすとき、最も高速な結合操作であるMERGE JOINが行われる
      • 2つのテーブルで同じソートキーが指定され、同じ列で分散されている
      • どちらのテーブルも80%以上ソートされている
      • 2つのテーブルがJOIN条件でDISTKEY列とSORTKEY列の両方を使用して結合されている

分散スタイルの決定

  • そのテーブルは結合に使用されているか
    • 結合に使用されていなければ、ALL分散の重複コストに対するメリットがないため、ALL分散が選択肢になくなる
    • 結合はJOINだけでなく、IN、NOT IN、MINUS、EXCEPT、INTERSECT、EXISTSなどでも使われるので注意
SELECT COUNT(*) FROM (
SELECT DISTINCT query
FROM stl_scan
WHERE tbl = '<table_name>'::regclass::oid
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
));
  • 少なくともの1つの分散キー候補があるか
  • 追加ストレージを許容できるか
    • 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_name>'::regclass::oid;
  • 並列性能を犠牲にすることを許容できるか
    • ALL分散は並列性能を犠牲にする
    • ALL分散では1行の更新を全てのノードで更新
    • 結合に使用する場合
      • ネットワークIOが減る
      • 計算量とディスクIOは増える
    • ALL分散が不向きな一般的なガイドライン
      • 読み取り
        • 大きなファクトテーブルへのスキャン
        • 結合しない単一テーブルスキャン
        • ウインドウ集約関数など、複雑な集計のあるテーブルへのスキャン
      • 書き込み
        • DMLで頻繁に変更されるテーブル
        • 膨大なデータをロードするテーブル
        • VACUUM操作で頻繁にメンテナンスする必要があるテーブル
SELECT '<table_name>'::regclass::oid AS table_id,
(SELECT count(*) FROM (
SELECT DISTINCT query FROM stl_insert
WHERE tbl = '<table_name>'::regclass::oid
INTERSECT
SELECT DISTINCT query FROM stl_delete
WHERE tbl = '<table_name>'::regclass::oid
)) AS num_updates,
(SELECT count(*) FROM (
SELECT DISTINCT query FROM stl_delete
WHERE tbl = '<table_name>'::regclass::oid
MINUS
SELECT DISTINCT query FROM stl_insert
WHERE tbl = '<table_name>'::regclass::oid
)) AS num_deletes,
(SELECT COUNT(*) FROM (
SELECT DISTINCT query FROM stl_insert
WHERE tbl = '<table_name>'::regclass::oid
MINUS
SELECT distinct query FROM stl_s3client
MINUS
SELECT DISTINCT query FROM stl_delete
WHERE tbl = '<table_name>'::regclass::oid
)) AS num_inserts,
(SELECT COUNT(*) FROM (
SELECT DISTINCT query FROM stl_insert
WHERE tbl = '<table_name>'::regclass::oid
INTERSECT
SELECT distinct query FROM stl_s3client
)) as num_copies,
(SELECT COUNT(*) FROM (
SELECT DISTINCT xid FROM stl_vacuum
WHERE table_id =
'<table_name>'::regclass::oid
AND status NOT LIKE 'Skipped%'
)) AS num_vacuum;
  • 少なくとも1つの分散キー候補があるか
  • 結合条件で分散キー候補を使用するか

最適な分散キーの決定

  • どのクエリーを優先すべきか
    • クエリーごとに結合条件に使用する列が異なる場合、どのクエリーを優先すべきか、から分散キーを選ぶ
    • どのクエリーを優先するかは業務用件次第

ソートキーについて

  • ソートするメリット
    • ゾーンマップによりディスクIOを削減
    • クエリー実行時のソートをなくす
    • MERGE JOINによって結合のパフォーマンスを向上
  • ソート形式の種類
    • COMPOUND:
      • 定義した順序が重要
      • 第1ソートキーが使用されない場合は、第2ソートキー以降も使われない
    • INTERLEAVED:
      • 定義した全ての列が同じ重要度
      • 第1ソートキーを使う場合はCOMPOUNDより少し遅いが、使わない場合は圧倒的に速い
      • メンテナンスコストが非常に高い
    • 一般的に、90%のケースでCOMPOUNDで十分

ソート形式の決定

  • ソートはMERGE JOINを有効にするか
    • 以下の条件を全て満たすとき、最も高速な結合操作であるMERGE JOINが行われる
      • 2つのテーブルで同じソートキーが指定され、同じ列で分散されている
      • どちらのテーブルも80%以上ソートされている
      • 2つのテーブルがJOIN条件でDISTKEY列とSORTKEY列の両方を使用して結合されている
  • ソートは実行時のソート処理を削減するか
    • ORDER BY、GROUP BYおよび、WINDOW関数内のPARTITION BY、ORDER BYはソート処理が行われる
    • 指定の列を事前にソートしておけばクエリー時のソート処理を減らせる
  • ソートはゾーンマップを改善できるか
    • 改善できないケース
      • 各スライスに1MBブロックが1つしかない
      • 列に1つの値のみが含まれている
  • クエリーは様々な列でフィルターするか
    • ソートキーはテーブルに1つ
    • クエリーでフィルタの列が異なる場合、どのクエリーを優先すべきかからソートキーを選ぶべき
  • 必要に応じてVACUUM REINDEXできるか
    • COMPOUND SORTKEYはソート済みデータをロードする場合はVACUUM不要
    • INTERLEAVED SORTKEYはロード後にVACUUM REINDEXしないと効果が弱まる
    • VACUUM REINDEXはIOコストが非常に高い
  • データに9バイト以上のPrefixがあるか
    • COMPOUND SORTKEYは先頭8バイトまでしかソート順に考慮しない(https://で始まるURLなどではCOMPOUND SORTKEYは使用できないことに注意)
    • INTERLEAVED SORTKEYはデータ全体をソート順に考慮

最良のソートキー列の決定

  • MERGE JOINのためのソートキー:DISTKEYと同じ列
  • ソート処理を削減するためのソートキー:ORDER BYなどで使われる列
  • ゾーンマップを改善するためのソートキー:フィルターされる列

まとめ

  • 分散スタイルの決定方法:結合に注目
    1. KEY分散に向く列が存在するか検討
    2. ALL分散に向くか検討
  • ソートキーの決定方法:結合、ORDER BY、フィルターに注目
    • なんのためにソートするのか検討
    • ゾーンマップの改善のための場合にはINTERLEAVEに向くか検討

今後のオンラインセミナー

9月以降もいろいろなサービス/ソリューションの紹介があります。

最後に

Redshiftのテーブル設計の指針として、大変参考になりました。

ベストプラクティスは理解していても、実際にうまく設計していくのは難しいケースが多いですが、フローチャートで判定していけるのでとても助かりますね。