Upgrade to Pro — share decks privately, control downloads, hide ads and more …

パフォーマンス改善の役に立つ 知っていてほしい Snowflake の仕様 2 選 / BUILD.local Tokyo: LEGEND OF DATA HEROES

パフォーマンス改善の役に立つ 知っていてほしい Snowflake の仕様 2 選 / BUILD.local Tokyo: LEGEND OF DATA HEROES

BUILD.local Tokyo: LEGEND OF DATA HEROES - LT 資料: https://snowflakejapan.connpass.com/event/265331/

Yoshi Matsuzaki

November 22, 2022
Tweet

More Decks by Yoshi Matsuzaki

Other Decks in Technology

Transcript

  1. パフォーマンス改善の役に立つ 知っていてほしい Snowflake の仕様 2 選 Yoshi Matsuzaki - Principal

    Cloud Support Engineer @ Snowflake Nov 16, 2022 - BUILD.local Tokyo @ IDOL Omotesando
  2. Yoshi Matsuzaki Principal Cloud Support Engineer @ Snowflake • 日本のサポートエンジニア第

    1 号として 日本語サポートを立ち上げた者 • 前職は Amazon Aurora/RDS MySQL チームの データベースエンジニア • 静岡市在住 (フルリモート) • 好きな関数: GENERATOR 誰
  3. DISCLAIMER: 発表者は Snowflake の社員ですが 発表内容はすべて個人の見解です

  4. パフォーマンス改善の役に立つ 知っていてほしい Snowflake の仕様 2 選

  5. その 1

  6. カラム数が多いテーブルは圧倒的に不利

  7. • 何が問題なの? • コンパイルに必要なメタデータが増える • 圧縮率が下がる カラム数が多いテーブルは圧倒的に不利

  8. • パーティションプルーニングに使用されるメタデータ • 最大値、最小値、値の種類の数… • カラムごと・パーティションごとに管理 • カラム数が多い = 取得するメタデータも多い

    問題 1: カラム数が多いテーブルのメタデータ
  9. • 10,000 列 * 10,000 行 = 1 億値のテーブル •

    100 列 * 1,000,000 行 = 1 億値のテーブル 問題 1: カラム数が多いテーブルのメタデータ create or replace transient table t_wide (c1 int, c2 int, ... , c9999 int, c10000 int) as select seq4(), seq4(), ... , seq4(), seq4() from table(generator(rowcount => 10000)); create or replace transient table t_deep (c1 int, c2 int, ... , c99 int, c100 int) as select seq4(), seq4(), ... , seq4(), seq4() from table(generator(rowcount => 1000000));
  10. 問題 1: カラム数が多いテーブルのメタデータ use warehouse xsmall; select * from t_wide;

    -- コンパイル: 2 min 21 sec (141 sec) -- クエリ実行: 4.2 sec -- パーティション数: 6 select * from t_deep; -- コンパイル: 331 msec (0.3 sec) -- クエリ実行: 4.8 sec -- パーティション数: 2
  11. • 値の数や論理データ量は同じなのに… • コンパイル時間が 426 倍 • しかも 1/100 の行数なのにパーティション数は

    3 倍 • → 取得するメタデータが更に増える 問題 1: カラム数が多いテーブルのメタデータ
  12. • Snowflake はカラムナーストレージ • カラムごとに連続するブロックで行を持っている • カラムまたぎでは圧縮が効かない 問題 2: カラム数が多いテーブルの圧縮率

  13. • 各行がすべて同じ値を持つ 100 列 * 1 億行 のテーブル • 各列がすべて同じ値を持つ

    100 列 * 1 億行 のテーブル 問題 2: カラム数が多いテーブルの圧縮率 create or replace transient table t_row (c1 int, c2 int, ... , c99 int, c100 int) as select row_number() over (order by true) * 10000, row_number() over (order by true) * 10000, ... from table(generator(rowcount => 100000000)); create or replace transient table t_col (c1 int, c2 int, ... , c99 int, c100 int) as select 10000 * 1, 10000 * 2, ... , 10000 * 99, 10000 * 100 from table(generator(rowcount => 100000000));
  14. • 行方向が同じ値 ... 1,060.18 MB (約 1 GB) • 列方向が同じ値

    ... 5.63 MB • 約 188 倍の差 問題 2: カラム数が多いテーブルの圧縮率 select table_name, round(bytes/1024/1024, 2) bytes_mb from information_schema.tables where table_name in ('T_ROW', 'T_COL'); -- T_ROW 1060.18 -- T_COL 5.63
  15. • 圧縮率が下がる = 物理サイズが増える • ストレージコストが増える • 1 パーティションに格納できる行数が減る •

    = パーティション数が増える • = メタデータの数も増える 問題 2: カラム数が多いテーブルの圧縮率
  16. • できるだけ縦方向に長いテーブルにしよう • (sales_2022, sales_2021, ...) → (year, sales) •

    (sales_us, sales_jp, ...) → (country, sales) カラム数が多いテーブルは圧倒的に不利
  17. その 2

  18. 1 つのパーティションをスキャンできるのは 1 つのサーバのみ

  19. • 1 つのパーティションのスキャンは分担できない • = パーティション数で使えるサーバ数が決まる • 何が問題なの? • 本来並列実行できる処理が並列実行できない

    • クエリ全体で使えるメモリ量が減る 1 つのパーティションをスキャンできるのは 1 つのサーバのみ
  20. • この制約が問題になる例 • LATERAL FLATTEN による増幅 • データスキュー (データの偏り) 1

    つのパーティションをスキャンできるのは 1 つのサーバのみ
  21. • 100 要素の配列を持つ 1,000,000 行のテーブル • パーティション数は 1 例 1:

    LATERAL FLATTEN による増幅 create or replace transient table t (idx int, arr array) as select seq4(), (select array_agg(seq4()) from table(generator(rowcount => 100))) from table(generator(rowcount => 1000000)); explain select * from t1; -- partitionsTotal: 1
  22. • LATERAL FLATTEN で配列を展開して配列要素でソート • ランタイム上で行数が 100 * 1,000,000 =

    1 億行に • 290 秒 (4 分 50 秒) かかる 例 1: LATERAL FLATTEN による増幅 use warehouse large; select f.value from t, lateral flatten(arr) f order by f.value; -- 290 sec
  23. • LATERAL FLATTEN した結果をテーブルに格納 • パーティション数が 9 に増加 例 1:

    LATERAL FLATTEN による増幅 create or replace temporary table t_flattened (idx int, elem string) as select t.idx, f.value from t, lateral flatten(t.arr) f; -- 43 sec explain select * from t_flattened; -- partitionsTotal: 9
  24. • 展開済みテーブルを配列要素でソート • 290 秒 → 43 + 4.5 =

    47.5 秒に短縮 • SELECT ... ORDER BY だけで見ると 64 倍高速化 例 1: LATERAL FLATTEN による増幅 select elem from t_flattened order by elem; -- 4.5 sec
  25. • 英数字 1 文字が格納された 1 億行のテーブル 例 2: データスキュー create

    or replace transient table t_skew (s varchar) as select randstr(1, random()) s from table(generator(rowcount => 100000000)) order by s; explain select * from t_skew; -- partitionsTotal: 8 explain select * from t_skew where s >= 'r'; -- partitionsAssigned: 2 explain select * from t_skew where s >= 's'; -- partitionsAssigned: 1
  26. • 最後のパーティションだけ 'zz...zz' (100 文字) に上書き • 8 つのパーティション中 1

    つだけ 100 倍大きい状態 例 2: データスキュー update t_skew set s = repeat('z', 100) where s >= 'z'; explain select * from t_skew where s >= 's'; -- partitionsTotal: 8 -- partitionsAssigned: 1
  27. • 同じデータを空のテーブルに入れ直してみる • 64 - 8 = 56 パーティション分のデータが偏っている 例

    2: データスキュー create or replace transient table t_dist (s varchar) as select * from t_skew order by s; explain select * from t_dist; -- partitionsTotal: 64
  28. • サイズ依存で重くなる処理を実行してみる (REPEAT) • 結果書き出しの負荷を無視するために HASH_AGG 例 2: データスキュー use

    warehouse large; select hash_agg(repeat(s, 1000)) from t_skew; -- 5 min 32 sec select hash_agg(repeat(s, 1000)) from t_dist; -- 58.71 sec
  29. • スキューを直すだけで 5.6 倍高速化 • データスキューは並列度を下げてしまう • とりあえず直したい ... CTAS

    + ORDER BY • 継続的に回避したい ... 自動クラスタリング • すぐにデータは触れないけど改善したい場合は…? 例 2: データスキュー
  30. • 秘技: ORDER BY RANDOM() • ソート結果は各サーバに再分散される • スキューの影響 >

    追加されるソートの負荷 例 2: データスキュー select hash_agg(repeat(s, 1000)) from (select * from t_skew order by random()); -- 1 min 12 sec
  31. • データが適切に分散されるようにしよう • 自動クラスタリング便利 1 つのパーティションをスキャンできるのは 1 つのサーバのみ

  32. • ファイルサイズのベストプラクティスと制限事項 • "並行して実行されるロード操作の数は、ロードされるデータファイルの数を超えること はできません。ロードの並列操作の数を最適化するには、 圧縮された データファイルの サイズをおよそ100から250 MB(またはそれ以上)にすることをお勧めします。" •

    1 つの巨大ファイルだと 1 つのサーバしか使えない • ファイル分割して並列ロードさせたほうが速い 補足: 実は同じような話がドキュメントに書いてある
  33. • 知っておくと便利な Snowflake の仕様 動作原理 • 動作原理を踏まえたチューニングができる • 動作原理に合ったデータの持ち方がわかる •

    動作原理を理解すると実務の役にも立つ まとめ