この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは、菊池です。
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のストレージに相当)
- ○:EVEN分散された注文書とALL分散された名簿
- 現実はさらに難しい
- 様々なクエリーに対応する必要がある
- 分散キーは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列の両方を使用して結合されている
- 以下の条件を全て満たすとき、最も高速な結合操作であるMERGE JOINが行われる
分散スタイルの決定
- そのテーブルは結合に使用されているか
- 結合に使用されていなければ、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で十分
- COMPOUND:
ソート形式の決定
- ソートはMERGE JOINを有効にするか
- 以下の条件を全て満たすとき、最も高速な結合操作であるMERGE JOINが行われる
- 2つのテーブルで同じソートキーが指定され、同じ列で分散されている
- どちらのテーブルも80%以上ソートされている
- 2つのテーブルがJOIN条件でDISTKEY列とSORTKEY列の両方を使用して結合されている
- 以下の条件を全て満たすとき、最も高速な結合操作であるMERGE JOINが行われる
- ソートは実行時のソート処理を削減するか
- 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などで使われる列
- ゾーンマップを改善するためのソートキー:フィルターされる列
まとめ
- 分散スタイルの決定方法:結合に注目
- KEY分散に向く列が存在するか検討
- ALL分散に向くか検討
- ソートキーの決定方法:結合、ORDER BY、フィルターに注目
- なんのためにソートするのか検討
- ゾーンマップの改善のための場合にはINTERLEAVEに向くか検討
今後のオンラインセミナー
9月以降もいろいろなサービス/ソリューションの紹介があります。
最後に
Redshiftのテーブル設計の指針として、大変参考になりました。
ベストプラクティスは理解していても、実際にうまく設計していくのは難しいケースが多いですが、フローチャートで判定していけるのでとても助かりますね。