Amazon Redshiftチューニングメモ(WIP)
仕事でRedshiftのチューニングをすることになりそうなのでメモ
※適宜更新
- 2018/04/04 更新
- 2018/04/11 更新
- 2018/10/04 更新。嘘いっぱい書いてたのを訂正。分散キーに関する項目を拡充
- 2018/11/07 更新。列圧縮に関する嘘八百を訂正。ソートキーに関して追記
- 2020/02/06 改めて見直すと肝心なことが書かれていなかったので更新。
この記事が想定する事例
- 検証環境では5秒~10秒程度で帰ってきているが、一定の負荷がある本番環境では何故かパフォーマンスが半分以下になる。
Redshiftのチューニングの前に
色々な概念の紹介
クラスタ
https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/working-with-clusters.html
Amazon Redshift データウェアハウスは、ノードと呼ばれるコンピューティングリソースのコレクションであり、これらはクラスターと呼ばれるグループを構成します。各クラスターは、1 つの Amazon Redshift エンジンを実行し、1 つ以上のデータベースを含みます。
1機の仮想DBサーバー。 クラスタ一つサーバーとして落としたり上げたりしたり、稼働時間=課金額になったりする。 クエリを投げるのもクラスタに対して投げる。
リーダーノード
https://doc.aws.amazon.com/ja_jp/redshift/latest/dg/c_high_level_system_architecture.html
クラスタの先頭に備えている、1機のコンピューティングリソース。 SQLの構文解析やSQL結果キャッシュ、WMLの機能(コンピューティングノード処理の同時処理数制限など)を担当。
ノード、コンピューティングノード
https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/working-with-clusters.html
Amazon Redshift クラスターは、ノードで構成されています。クラスターごとに、リーダーノードと 1 つまたは複数のコンピューティングノードがあります。リーダーノードは、クライアントアプリケーションからクエリを受け取ってクエリを解析し、クエリ実行プランを作成します。次に、これらのプランの並列実行をコンピューティングノードと調整し、コンピューティングノードからの中間結果を集計します。最終的にクライアントアプリケーションに結果を返します。
演算、データ保持担当のコンピューティングリソース。 料金表を眺めたことがある方は、以下のインスタンスタイプに見覚えがあるかもしれない。
現在の名前 | 以前の名前 |
---|---|
ds2.xlarge | ds1.xlarge、dw.hs1.xlarge、dw1.xlarge |
ds2.8xlarge | ds1.8xlarge、dw.hs1.8xlarge、dw1.8xlarge |
dc1.large | dw2.large |
dc1.8xlarge | dw2.8xlarge |
内部に「ノードスライス」という末端コンピューティングリソースを持つ。CPUコア1つにつき1スライス。
ノードスライス
コンピューティングノード内の一番最小のリソース割当単位。CPUや記憶領域(HDD・SSDやメモリ)のリソースセット。 コンピューティングノードのCPU 1 コアにつき 1 ノード存在する。テーブルの実データは、どれかのスライスに分散される。
料金表には出てこないが、パフォーマンスチューニングでは重要な概念。(詳しくは後述)
システムテーブル STV_TBL_PERM
を用いれば、テーブルごとの各スライスの分散状況が見れる。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_TBL_PERM.html
データブロック と ゾーンマップ
Redshiftは、列ごとに、1 MB 区切りのブロックにデータを格納する。このブロックをデータブロックと呼ぶ。 各データブロックごとにゾーンマップと呼ばれるメタ情報があり、値の最大値・最小値やブロック内の値の個数などのメタ情報を保持する。ゾーンマップはメモリに保持される。
データブロックは、システムテーブル STV_BLOCKLIST
から一覧が見れる。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_BLOCKLIST.html
チューニング:スライスと分散
- 並列分散システムの弱点。データを一か所に集める演算が必要になったときにデータをネットワーク経由で全ノードに転送(再分散)
- 1回あたりの再分散は意外なほど高速。だが、JOINやサブクエリを滅茶苦茶重ねたりすると、ネットワーク帯域や演算結果を保持するためのメモリを浪費する。重い再分散をスロークエリが数個あると、帯域が枯渇して急激にパフォーマンスが低下する。
- 分散スタイルのチューニングは、行数・列数が多く、ソートキーなどでスキャン範囲を限定できないテーブルを含むGROUP BY/JOINに有効。
スライスへの分散
レコードが挿入された時、テーブルごとに設定された分散スタイルに従っていずれかのノードに分散される。
分散スタイルについて
- EVEN分散 ・・・ ラウンドロビンで均等に分散される。何も指定しないときはこれとなる。
- KEY分散・・・ 列を1つ指定し、その値によってどのノードに配分するか決定する。
- ALL分散 ・・・ すべてのノードに分散する。(データは全ノードで重複して持つ)。INSERTやCOPYのときのトラフィック総量がスライス数分倍増。また、並列演算のリソースも食う。
スライスごとに並列で計算するので、均等に分散ができているほど、特定のスライスに負荷がかかりすぎるなどが発生せず高速に演算できる。
再分散
2つのテーブルを結合するとき、各結合するデータ同士が同じスライスに分散されている場合は、そのまま計算するが、違うスライスにあった場合は、全ノードにばらまきなおす「再分散」が起きる。 explainでSQLの実行計画を見ると、「DS_BCAST_INNER」など「BCAST」付きの結合をしていると出力される場合がある。これは再分散が発生している。 再分散を避ける設計は2つ。
- 分散キーを複数テーブル間で同一に設定する
- outer 側の結合テーブルをALL分散に設定する。
分散キー
分散スタイルで「キー分散」を選ぶときは、テーブルのうち1つの項目を指定する。 一般的に、レコード数の偏りがないものを選ぶとよい。(受注番号やidなど連番で重複のないもの) 上記の再分散を避けるため、「ヘッダー」-「明細」のようによく結合すうるテーブル間で共通のキーを設定するとよい。
分散キーを実行プランから測定する
分散キーは、テーブルのJOINで結合キーに指定していると再分散を防げる。 実際に効果があるかどうかは、explainで実行計画をみてテーブル同士の結合時の分散のしかたを見るとよい。 (コストに関してはあまりアテにならないので見ない)
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_data_redistribution.html
上記にある通り、 DS_BCAST_INNER
や DS_BCAST_BOTH
が出ると結合のパフォーマンスが著しく悪くなる。
いくつかSQLを作って実行計画を見た感じ、2のテーブルをJOINする程度の簡易なクエリであれば DS_BCAST_INNER
は発生せず、
少し複雑になって tableX <-> tableY <-> tableZ <-> tableV
で結合するSQLが分散キーで不一致になると割と出るようになる。
クエリの実行ログから分散の処理データ量を見る
分散の回数、転送バイト数、処理した行数が見れる
-- トランザクションを初めておく BEGIN; -- 測定したいクエリを実行 SELECT col, count(*) FROM some_queries GROUP BY col; -- 最後に想定したクエリのquery idを取得 SELECT PG_LAST_QUERY_ID(); -- BCASTな分散のログ SELECT * FROM pg_catalog.stl_bcast WHERE query = <↑で見たqueryid>; -- DISTな分散のログ SELECT * FROM pg_catalog.stl_dist WHERE query = <↑で見たqueryid>;
脳死的分散スタイルの決め方
- 条件:LEFT JOINするようなマスタデータ(1000万件の実績データに対して数件~10万件のマスタをあてるなど)
- →選択:ALL分散
条件: マートやFACT、実績データなど、参照のしかたでGROUP BY/WHERE/JOIN キーが不定
- →選択:EVEN分散
条件:EVENだとJOINが遅い、バッチなどでしか使わず結合、GROUP BYのキーなどが一定
- →選択:KEY分散だけど脳死できない
分散キーのBADパターン : クエリを実行してみるとスライスがめっちゃ偏る
例)日次アクセス実績データ
path | logday | logym | ua_category | pvcount |
---|---|---|---|---|
/index.html | 2018/01/01 | 201801 | bot | 3000 |
/index.html | 2018/01/01 | 201801 | pc | 300 |
/index.html | 2018/01/01 | 201801 | sp | 500 |
/index.html | 2018/01/02 | 201801 | bot | 3300 |
/index.html | 2018/01/02 | 201801 | pc | 300 |
/index.html | 2018/01/02 | 201801 | sp | 500 |
危ないパターン:集計はだいたい月ごとでやるから、logymに distkey 設定したら再分散避けれるんじゃないか?(単純に集計単位となるキーをあてる)
- 下記のSQLは確かに再分散を避けれるが、検索条件を含むスライスが1つしかなく、コンピューティングノードごとの並列処理がされない。
--日次のレポートつくるときのSQL SELECT logday, ua_category, SUM(pvcount) FROM taccesslogsummary GROUP BY logday, ua_category WHERE logym='201801'; -- ここ
- logymのこの値を持つノードは分散キーにより1つだけ = このSQLの裏で稼働するコンピューティングノードが 1 つしかない。
再分散を避けるより、EVEN分散にして全ノードで分散演算したほうが、データ量によっては速い
分散キーはワイルドカードではなく、実行計画において再分散を避けるだけの効果しかない。
- 実際に発行するクエリに対する効果とセットで見積もるべき。
- SQLを実行してから、システムテーブル
STL_PROJECT
を見ると各スライスから読み出した行数を見れるので、そちらで計測するとよし。
チューニング:ソートキー
チューニングの奥義。ゆえに時と場合、代償が求められる。
通常、INSERTすると一番最後のデータブロックに追記される形でデータが格納。
値が自然とソート順になれば問題ないが、1つのデータブロック内で値の最大/最小値が大きくなるとWhere句による読み込みスキップが発生しづらくなる。
- データブロックは1つごとにゾーンマップ(最大値、最小値、データ個数など)のメタ情報があり、スキャンする際にWHERE句などに設定される内容が最大値/最小値の範囲外であれば、データブロック(I/O)の読み込みをスキップする。
テーブルにソートキーを設定すると、最後にVACUUMした or テーブル0件から INSERT した時点までのデータが、ソートキー順に物理的に並ぶ。この物理的にソートされた列に対してWHERE句をかけると劇的に効く。
もちろんソートキーが設定されている項目をクエリで使ったときのみ有効。理屈では、ソートキー以外の列のゾーンマップは最大・最小値が相対的に拡大するため、そちらのみをフィルタするクエリの性能は数%劣化する。
- また、VACUUMの際にsort、mergeといった非常に負荷の高いステップが追加される。
【2019/06/23追記】詳しい記事をかいた。
チューニング:列圧縮タイプ
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Compressing_data_on_disk.html
データの物理的な保存形式を指定する。単純な圧縮ではなくデータ配列の仕方に近く、様々な記録方式がある。(詳しくは公式ドキュメント参照) 最適なものを選べば、スライス内でのディスク→メモリへのページインにおけるディスクI/Oが減る。(・・・が、ディスクReadあたりのメモリに展開される容量が相対的に増える)
- CREATE TABLE したとき、ソートキーを設定したものは一律 raw(圧縮なし)、それ以外のものはlzo(汎用圧縮)になる。
一般的には高圧縮であればよいが、例えば1MBのデータブロックを読み出すと10GBにメモリ展開される場合、1行欲しい場合でも1データブロック単位での展開が強要されるため過多なメモリコピーが発生するし、スキャンする際はデータブロックが単位となるので、1データブロックで10GB相当のレコードを全件参照することになる。
勘所としては下記のようなケース。
- 「RAWに比べてLZO選択でディスクサイズが 1/10になる」
- 「数バイト値に下手な圧縮をかけて容量が5%減程度とかであまり意味がない。RAWにしてINSERT・LOAD時のパフォーマンスを上げる」など。
列圧縮の効果のサマりかた
ディスクサイズのとりかた = STV_BLOCKLISTビューを参照
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_BLOCKLIST.html
-- 特定テーブルの列ごとのデータブロック(1MBのディスク書き込み領域)消費量をサマる SELECT name, col, slice, count(*) AS "using disk(MB)", avg(num_values) AS "num values/1MB" FROM pg_catalog_stv_blocklist WHERE name ='tablename' GROUP BY name, col, slice ORDER BY name, col, slice;
colがテーブルの列のindex。ただし、末尾3列は暗黙的に追加される非表示列。 ここから、1record 読むあたりのI/O処理バイト数が読める。
Read/Writeの際には、別途列圧縮のための演算が各コンピューティングノードにかかる。
実測値
(2019/08/25追記) ディスク使用率に関する検証結果をまとめた。
WLM(Work Load Manager)
SQL実行したときのリソース割り当て管理をしてくれる。
Amazon Redshiftのワークロード管理(WLM)を使ってミックスワークロードを実行する | Amazon Web Services ブログ
- 特定ユーザーグループが同時にSQLを実行できる数の上限
- 特定ユーザーグループが利用できるメモリの上限
CPUが使用率低いのにSQLが実行されない・・・というボトルネックがある場合こいつを見直すとよいかもしれない。 むしろ運用上、①アプリ用グループ ②バッチ用グループ ③保守用グループ ④特権ユーザー用グループ とわけないといざというときにSQLが実行できなくなるので、もし困っているのであれば早めに検討したほうがよい。
デフォルトは同時実行数 5 。理論上は500まで設定可能。しかし、無制限に開放すると様々なリソースを浪費して逆に遅くなる。
クエリがキュー待ちになっているか確認する
システムテーブル STL_WLM_QUERY
から、スーパーユーザーなら全ユーザーの実行履歴、通常ユーザーならばそのユーザーの分のSQL実行履歴が見れる。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_WLM_QUERY.htmldocs.aws.amazon.com
その中で total_queue_time
が 0 でなければ、そのクエリは同時実行数の制限によりキュー待ちとなっている。(単位はマイクロ秒)
VACUUM と ANALYZE
Redshift公式でも、定期実行が勧められている。
VACUUM
DELETEやUPDATEによりできた不使用空間のコレクト、ソートキーに基づいた再ソートを実施する。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_VACUUM_command.html https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Reclaiming_storage_space202.html https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/vacuum-managing-volume-of-unmerged-rows.html
- 削除フラグがついた無駄領域を削減することで、スキャンのミス率を低減する。
- 2018年11月ぐらいのアップデートで、不要領域のVACUUMはバックグラウンドで自動実行されるようになった。ソートキーのついていないテーブルなら実行不要に。
- ソートキーを設定しても更新をした時点では、新しいデータはソートキー順に並んでいない。ソートキー順に並べなおす。
- なお、デフォルトだと、ソート率95%以上のテーブルはVACUUMをスキップする
ANALYZE
テーブルの統計情報を更新する。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/vacuum-managing-volume-of-unmerged-rows.html
- 統計情報は、各列ごとに作られる。
- 統計情報はクエリの実行プランに反映。
- 統計情報がある程度古い場合、初回SELECT時などにオートアナライズがかかる場合がある。
- テーブルの全体行数に対する変更された行の割合が、パラメータanalyze_threshold_percent以下であればスキップする。
- analyze_threshold_percent を 0 にするとANALYZEはスキップしなくるが、普通のSELECTやUPDATE・DELETEなどでも参照する時に必ずオートアナライズがかかり、都度更新がかかる環境やバッチ処理はむしろ劇的にパフォーマンス劣化する。永続的な設定をせず、
SET
コマンドを利用してANALYZE実行セッションのみ 0 書き換えを推奨。
- analyze_threshold_percent を 0 にするとANALYZEはスキップしなくるが、普通のSELECTやUPDATE・DELETEなどでも参照する時に必ずオートアナライズがかかり、都度更新がかかる環境やバッチ処理はむしろ劇的にパフォーマンス劣化する。永続的な設定をせず、
チューニング:列サイズの縮小
大容量のVARCHAR型の利用を避ける
RedhisftはVARCHAR型の列を選択する時、メモリ上に型サイズそのままの領域を確保する・・・といわれおり、パフォーマンスに多大な影響を与えてきた。 過去何回か、対策したらしきアップデートがあったが、どうも日本社内のデータマートではあまり改善が見られなかった。
要件が絡む以上難しくなるが、VARCHAR型を持つ時、下記のようにチューニングできないか検討する。
- マスタや検索項目で日本語を使いたいなら、別途コード化する。数値型や、ある程度の表意が求められるのであれば、数十バイト程度のかなり短いものにする。
- もし数百バイトを超える字句を保存する場合は、SELECT時ソートキーによる絞り込み、VACUUM時のmergeによる負荷を検討する。
- Redshift Spectrum を検討する。(単純にJOINすると意味がなくなるが、1テーブルを単純にSELECTするだけであればコンピューティングノードのメモリを消費しない)
そのほかTips
別記事かく