パフォーマンス改善の役に立つ 知っていてほしい Snowflake の仕様 2 選 Yoshi Matsuzaki - Principal Cloud Support Engineer @ Snowflake Nov 16, 2022 - BUILD.local Tokyo @ IDOL Omotesando

Yoshi Matsuzaki Principal Cloud Support Engineer @ Snowflake • 日本のサポートエンジニア第 1 号として 日本語サポートを立ち上げた者 • 前職は Amazon Aurora/RDS MySQL チームの データベースエンジニア • 静岡市在住 (フルリモート) • 好きな関数: GENERATOR 誰

DISCLAIMER: 発表者は Snowflake の社員ですが 発表内容はすべて個人の見解です

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

その 1

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

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

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

• 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));

問題 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

• 値の数や論理データ量は同じなのに… • コンパイル時間が 426 倍 • しかも 1/100 の行数なのにパーティション数は 3 倍 • → 取得するメタデータが更に増える 問題 1: カラム数が多いテーブルのメタデータ

• Snowflake はカラムナーストレージ • カラムごとに連続するブロックで行を持っている • カラムまたぎでは圧縮が効かない 問題 2: カラム数が多いテーブルの圧縮率

• 各行がすべて同じ値を持つ 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));

• 行方向が同じ値 ... 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

• 圧縮率が下がる = 物理サイズが増える • ストレージコストが増える • 1 パーティションに格納できる行数が減る • = パーティション数が増える • = メタデータの数も増える 問題 2: カラム数が多いテーブルの圧縮率

• できるだけ縦方向に長いテーブルにしよう • (sales_2022, sales_2021, ...) → (year, sales) • (sales_us, sales_jp, ...) → (country, sales) カラム数が多いテーブルは圧倒的に不利

その 2

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

• 1 つのパーティションのスキャンは分担できない • = パーティション数で使えるサーバ数が決まる • 何が問題なの？ • 本来並列実行できる処理が並列実行できない • クエリ全体で使えるメモリ量が減る 1 つのパーティションをスキャンできるのは 1 つのサーバのみ

• この制約が問題になる例 • LATERAL FLATTEN による増幅 • データスキュー (データの偏り) 1 つのパーティションをスキャンできるのは 1 つのサーバのみ

• 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

• 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

• 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

• 展開済みテーブルを配列要素でソート • 290 秒 → 43 + 4.5 = 47.5 秒に短縮 • SELECT ... ORDER BY だけで見ると 64 倍高速化 例 1: LATERAL FLATTEN による増幅 select elem from t_flattened order by elem; -- 4.5 sec

• 英数字 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

• 最後のパーティションだけ '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

• 同じデータを空のテーブルに入れ直してみる • 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

• サイズ依存で重くなる処理を実行してみる (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

• スキューを直すだけで 5.6 倍高速化 • データスキューは並列度を下げてしまう • とりあえず直したい ... CTAS + ORDER BY • 継続的に回避したい ... 自動クラスタリング • すぐにデータは触れないけど改善したい場合は…？ 例 2: データスキュー

• 秘技: ORDER BY RANDOM() • ソート結果は各サーバに再分散される • スキューの影響 > 追加されるソートの負荷 例 2: データスキュー select hash_agg(repeat(s, 1000)) from (select * from t_skew order by random()); -- 1 min 12 sec

• データが適切に分散されるようにしよう • 自動クラスタリング便利 1 つのパーティションをスキャンできるのは 1 つのサーバのみ

• ファイルサイズのベストプラクティスと制限事項 • "並行して実行されるロード操作の数は、ロードされるデータファイルの数を超えること はできません。ロードの並列操作の数を最適化するには、 圧縮された データファイルの サイズをおよそ100から250 MB（またはそれ以上）にすることをお勧めします。" • 1 つの巨大ファイルだと 1 つのサーバしか使えない • ファイル分割して並列ロードさせたほうが速い 補足: 実は同じような話がドキュメントに書いてある

• 知っておくと便利な Snowflake の仕様 動作原理 • 動作原理を踏まえたチューニングができる • 動作原理に合ったデータの持ち方がわかる • 動作原理を理解すると実務の役にも立つ まとめ