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

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

※適宜更新

  • 2018/04/04 更新
  • 2018/04/11 更新
  • 2018/10/04 更新。嘘いっぱい書いてたのを訂正。分散キーに関する項目を拡充
  • 2018/11/07 更新。列圧縮に関する嘘八百を訂正。ソートキーに関して追記

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

内部に「ノードスライス」というストレージの実体のようなものを、インスタンスタイプのコア数1つにつき1スライス持つ。

ノードスライス

コンピューティングノード内の一番最小のリソース割当単位。CPUや記憶領域(HDD・SSDやメモリ)のリソースセット。 コンピューティングノードのCPU 1 コアにつき 1 ノード存在する。テーブルの実データは、どれかのスライスに分散される。

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

システムテーブル 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. outer 側の結合テーブルをALL分散に設定する。

分散キー

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

分散キーを実行プランから測定する

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

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

上記にある通り、 DS_BCAST_INNERDS_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すると一番最後のデータブロックに追記される形でデータが格納。
  • テーブルにソートキーを設定すると、最後にVACUUMした or テーブル0件から INSERT した時点までのデータが、ソートキー順に並ぶ。
  • データブロックは1つごとにゾーンマップ(最大値、最小値、データ個数など)のメタ情報があり、スキャンする際にWHERE句などに設定される内容が最大値/最小値の範囲外であれば、データブロック(I/O)の読み込みをスキップする。
  • もちろんソートキーが設定されている項目をクエリで使ったときのみ有効。理屈では、ソートキー以外の列のゾーンマップは最大・最小値が相対的に拡大するため、そちらのみをフィルタするクエリの性能は数%劣化する。

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

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

データの物理的な保存形式を指定する。単純な圧縮ではなくデータ配列の仕方に近く、様々な記録方式がある。(詳しくは公式ドキュメント参照) 最適なものを選べば、スライス内でのディスク→メモリへのページインにおけるディスクI/Oが減る。(・・・が、ディスクI/Oあたりのメモリに展開される容量が相対的に増える)

  • CREATE TABLE したとき、ソートキーを設定したものは一律 raw(圧縮なし)、それ以外のものはlzo(汎用圧縮)になる。
  • 一般的には高圧縮であればよいが、例えば1MBのデータブロックを読み出すと10GBにメモリ展開される場合、1行欲しい場合でも1データブロック単位での展開が強要されるため過多なメモリコピーが発生&1データブロックの最大値~最小値が非常に振れ幅が大きいだろうから、読み込みスキップが起こらず一定のボトルネックになる。

  • こちらに関して、素晴らしい記事を書かれた先人がいらっしゃいます。「redshift 列圧縮タイプ」でぐぐってくださいな。

列圧縮の効果のサマりかた

ディスクサイズのとりかた = 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の際には、別途列圧縮のための演算が各コンピューティングノードにかかる。

実測値

もしかしたら記事にするかも


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

  • 統計情報は、各列ごとに作られる。
  • 統計情報はクエリの実行プランに反映。
  • 統計情報がある程度古い場合、初回SELECT時などにオートアナライズがかかる場合がある。
  • テーブルの全体行数に対する変更された行の割合が、パラメータanalyze_threshold_percent以下であればスキップする。
    • analyze_threshold_percent を 0 にすると1行でも変更したテーブルのANALYZEはスキップしなくるが、何かしら変更加えた後の初回参照時に必ずオートアナライズがかかり、都度更新がかかる環境やバッチ処理はむしろ劇的にパフォーマンス劣化する。永続的な設定をせず、SET コマンドを利用してANALYZE実行セッションのみ 0 書き換えを推奨。