Redshiftチューニングメモ(WIP)

仕事でRedshiftのチューニングをすることになりそうなのでメモ

※適宜更新

  • 2018/04/04 更新
  • 2018/04/11 更新

Redshiftのチューニングの前に

色々な概念の紹介

クラスタ

Amazon Redshift クラスター - Amazon Redshift

Amazon Redshift データウェアハウスは、ノードと呼ばれるコンピューティングリソースのコレクションであり、これらはクラスターと呼ばれるグループを構成します。各クラスターは、1 つの Amazon Redshift エンジンを実行し、1 つ以上のデータベースを含みます。

つまり、1機の仮想DBサーバーとみてよい。 クラスタ一つサーバーとして落としたり上げたりしたり、稼働時間=課金額になったりする。 クエリを投げるのもクラスタに対して投げるような感覚。

リーダーノード

https://doc.aws.amazon.com/ja_jp/redshift/latest/dg/c_high_level_system_architecture.html

クラスタの先頭に備えている、おもてっつらのコンピューティングリソース。 SQL構文解析SQL結果キャッシュ、WMLの機能(コンピューティングノード処理の同時処理数制限など)を担当。

ノード、コンピューティングノード

Amazon Redshift クラスター - Amazon Redshift

Amazon Redshift クラスターは、ノードで構成されています。クラスターごとに、リーダーノードと 1 つまたは複数のコンピューティングノードがあります。リーダーノードは、クライアントアプリケーションからクエリを受け取ってクエリを解析し、クエリ実行プランを作成します。次に、これらのプランの並列実行をコンピューティングノードと調整し、コンピューティングノードからの中間結果を集計します。最終的にクライアントアプリケーションに結果を返します。

SQLコンパイルして意味解析して、さあデータとってくんぞ演算するぞのコンピューティングリソース消費担当。 料金表を眺めたことがある方は、以下のインスタンスタイプに見覚えがあるかもしれない。

現在の名前 以前の名前
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や記憶領域(HDD・SSDやメモリ)のリソースセット。 1 ノードにつき2スライスある。テーブルの実データは、どれかのスライスに分散される。

料金表には出てこないが、パフォーマンスチューニングでは重要な概念。(詳しくは後述)

システムテーブル STV_TBL_PERM を用いれば、テーブルごとの各スライスの分散状況が見れる。

STV_TBL_PERM - Amazon Redshift

データブロック と ゾーンマップ

Redshiftは、列ごとに、1 MB 区切りのブロックにデータを格納する。(データブロック) ゾーンマップは、各データブロックごとの値の最大値・最小値を保持する。

データブロックは、システムテーブル STV_BLOCKLIST から一覧が見れるらしい。(みたくない)

STV_BLOCKLIST - Amazon Redshift


チューニング:スライスと分散

  • スライスは、コンピューティング資源の最小単位。

スライスへの分散

レコードが挿入された時、テーブルごとに設定された分散スタイルに従っていずれかのノードに分散される。

分散スタイルについて

  • EVEN分散 ・・・ ラウンドロビンで均等に分散される。何も指定しないときはこれ
  • KEY分散・・・ 列を1つ指定し、その値によってどのノードに配分するか決定する。
  • ALL分散 ・・・ すべてのノードに分散する。(データは全ノードで重複して持つ)。INSERTやCOPYのときのトラフィック総量がスライス数分倍増。また、並列演算のリソースも食う。

スライスごとに並列で計算するので、均等に分散ができているほど、特定のスライスに負荷がかかりすぎるなどが発生せず高速に演算できる。

再分散

2つのテーブルを結合するとき、各結合するデータ同士が同じスライスに分散されている場合は、そのまま計算するが、違うスライスにあった場合は、全ノードにばらまきなおす「再分散」が起きる。 explainでSQLの実行計画を見ると、「DS_BCAST_INNER」など「BCAST」付きの結合をしていると出力される場合がある。これは再分散が発生している。 再分散を避ける設計は2つ。

  1. 分散キーを複数テーブル間で同一に設定する
  2. inner 側の結合テーブル(OUTER JOINで NULL にならないほう)をALL分散に設定する。

分散キー

分散スタイルで「キー分散」を選ぶときは、テーブルのうち1つの項目を指定する。 一般的に、レコード数の偏りがないものを選ぶとよい。(受注番号やidなど連番で重複のないもの) 上記の再分散を避けるため、「ヘッダー」-「明細」のようによく結合すうるテーブル間で共通のキーを設定するとよい。

分散キーの効果とサマりかた

分散キーは、テーブルのJOINで結合キーに指定していると効く。 実際に効果があるかどうかは、explainで実行計画をみてテーブル同士の結合時の分散のしかたを見るとよい。

クエリプランの評価 - Amazon Redshift

上記にある通り、 DS_BCAST_INNERDS_BCAST_BOTH が出ると結合のパフォーマンスが著しく悪くなる。

いくつかSQLを作って実行計画を見た感じ、2のテーブルをJOINする程度の簡易なクエリであれば DS_BCAST_INNER は発生せず、 少し複雑になって tableX <-> tableY <-> tableZ <-> tableV で結合するSQLが分散キーで不一致になると割と出るようになる。

※最適化のしかたは模索中


チューニング:ソートキー

  • WIP

チューニング:列圧縮タイプ

列圧縮タイプの選択 - Amazon Redshift

データの物理的な保存形式を指定する。単純な圧縮ではなく、もうデータ配列の方法自体をダイナミックに設定してくれる。(詳しくは公式ドキュメント参照) 最適なものを選べば、SQLの初回実行時のパフォーマンスが大きく向上する。

  • ※2018/04/04に試した見たところ、連続で実行した場合に最適化有り/無しで結果が大差なかった。もしかしたら影響するのはディスクへの保存のされかただけで、コンピューティングノードにオンメモリになってると影響しない?

  • CREATE TABLE したとき、ソートキーを設定したものは一律 raw(圧縮なし)、それ以外のものはlzo(汎用圧縮)になる。

  • 公式ドキュメントでいう「データサイズ」≠「データブロック数」。

    • テーブルごとのディスク使用率を図りたいなら、Redshiftのデータブロック数(1個 1MB)を数えるとよい、とよく記事で見るが、恐らくこの通りではない。
    • 実際、lzoだらけのテーブルを調整した結果データブロック数が1.7倍になったのに、速度が2倍改善した。
    • きちんと裏をとってないので憶測にすぎないが、単純に圧縮してデータブロック内にしきつめるlzoのほうが各値ごとに1データブロック専有するrunlengthのよりデータブロック数の消費が軽い、しかし実際に格納している実データはrunlengthのほうが小さいのでトラフィックが軽い、などが起きたのかもしれない。
  • こちらに関して、素晴らしい記事を書かれた先人がいらっしゃいます。「redshift 列圧縮タイプ」でぐぐってくださいな。


WLM(Work Load Manager)

SQL実行したときのリソース割り当て管理をしてくれる。

Amazon Redshiftのワークロード管理(WLM)を使ってミックスワークロードを実行する | Amazon Web Services ブログ

  • 特定ユーザーグループが同時にSQLを実行できる数の上限
  • 特定ユーザーグループが利用できるメモリの上限

CPU使用率低いのにSQLふんずまってる・・・っていうボトルネックがある場合こいつを見直すとよいかもしれない。 というか、まずこいつを設定して ①アプリ用グループ ②バッチ用グループ ③保守用グループ ④特権ユーザー用グループ とか用意してもらうと下手な事故を避けれるかもしれない。

デフォルトは同時実行数 5 。理論上は500まで設定可能だが、ちょっと大きくするとリソースを使いまくってえらいことになるらしい。 こちらに関して、素晴らしい記事を書かれた先人がいらっしゃいます。「Redshift 同時実行数」でぐぐってください。

クエリがキュー待ちになっているか確認する

システムテーブル STL_WLM_QUERY から、スーパーユーザーなら全ユーザーの実行履歴、通常ユーザーならばそのユーザーの分のSQL実行履歴が見れる。

docs.aws.amazon.com

その中で total_queue_time が 0 でなければ、そのクエリは同時実行数の制限によりキュー待ちとなっている。(単位はマイクロ秒)


VACUUM と ANALYZE

Redshift公式でも、定期実行が勧められている。

VACUUM

DELETEやUPDATEによりできた不使用空間のコレクト、ソートキーに基づいた再ソートを実施する。

VACUUM - Amazon Redshift テーブルのバキューム処理 - Amazon Redshift https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/vacuum-managing-volume-of-unmerged-rows.html

  • 削除フラグがついた無駄領域を削減することで、スキャンのミス率を低減する。
  • ソートキーを設定しても更新をした時点では、新しいデータはソートキー順に並んでいない。ソートキー順に並べなおす。
  • なお、デフォルトだと、ソート率95%以上のテーブルはVACUUMをスキップする

ANALYZE

テーブルの統計情報を更新する。

マージ済みの行のボリューム管理 - Amazon Redshift

  • 統計情報は、各列ごとに作られる。
  • 統計情報はクエリの実行プランに反映。
  • テーブルの全体行数に対する変更された行の割合が、パラメータanalyze_threshold_percent以下であればスキップする。