LoginSignup
1
1
@jnitin株式会社BeeX

Redshiftのパフォーマンス関連メモ

Last updated at Posted at 2023-09-25

はじめに

  • 本記事は個人メモです。

    • いろいろ書いていますが、私自身はRedshiftのパフォーマンス関連の経験値は低いです。
    • 随時内容を追記していきます。粒度にムラがあります。長くなったら記事を分割するかもしれません。

  • RedshiftはSnowflakeなどに比べるとチューニングで考えるべきポイントが多い印象です。

    • 学習による認知負荷が極めて高いため、本記事のように学習内容をまとめておく必要があると感じました。

本記事の構造と注意点

  • 本記事の構造

    • 本記事に記載する情報源やTipsは、「テーブル設計」と「クエリパフォーマンス」で分けて記載しています。

  • 注意点

    • 本記事は主にRedshiftクラスタ(Provisioned)版（つまりRedshiftサーバレスではない）を念頭に記載しています。
      • 記事中に断りがなければRedshiftクラスタ版を指しているものとお考えください。
    • パフォーマンスチューニングはワークロードによってアプローチが全く異なるため、銀の弾丸は存在しないと考えています。
      • そのためここに記載するメモも、特定のワークロードに依拠した内容になっています。
    • Redshiftは日々機能追加が行われているため、参照する情報がいつ書かれたものであるかを多少気にしておく必要があります。
    • 全般的にインフラ視点で記載しています。SQL文チューニングなどアプリ視点は弱いです。

情報源

Redshiftテーブル設計関連の情報源

Redshiftクエリパフォーマンス関連の情報源

パフォーマンスチューニングTips

パフォーマンスチューニングのアプローチ

  • 正攻法としては以下のような順番でしょうか。

    • テーブル設計を最適化する
      • スキーマ構造(DWHなスノーフレークスキーマ、データマートなスタースキーマ)
      • Redshift特有のテーブル物理設計
    • クエリパフォーマンスを最適化する
      • 単体クエリパフォーマンス
      • 同時実行性能
    • (マテリアライズド・ビューを使う)

  • Redshiftのコンソールで確認できるアプローチとしては、以下のような感じになるかと思います。

    • 物理設計（インフラ面）
      • 「クラスターのパフォーマンス」を見る
    • 論理設計（アプリ面）
      • 全体的なチューニング観点
        • 「アドバイザー」を見る(後述)
      • 各クエリのチューニング観点
        • 「クエリとロード」を見る(後述)

  • テーブル設計の見直しは、DDL文にソートキーなどを追加するようなRedshift特有の変更であればたいしたことはありませんが、正規化・非正規化を伴うような場合は大手術になります。スキーマ構造の見直しが現実的でない場合は「クエリパフォーマンス」の対応でなんとかせざるを得ないことがあります。

  • 効率的に対応するためには「木を見て森を見ず」にならないように意識がすることが大事です。

テーブル設計関連

Redshift特有のテーブル物理設計

  • データの性質やクエリパターンがわからない初期はAUTOで設定してもよいかと思います。
  • シンプルに考える場合は以下のような設計になるかと思います。
    • WHERE句で使用するカラム：sortkey　（例）timestamp
    • 一番大きいマスターテーブルとのJOINで使用するカラム：distkey (例)商品コード
    • 小さいマスターテーブル：diststyle:all

アドバイザーを利用したRedshiftテーブル設計の見直し

  • Redshiftコンソール画面左下にある「アドバイザー」をクリックすると、すでに何らかのワークロードがあり性能上の改善点が発生しているとRedshiftがみなした場合は、優先度ごとに推奨事項が表示されます。
    • 具体的にはWLM、圧縮エンコード、統計情報、ソートキー、分散キー、データ型、COPY文など多岐にわたるトピックで助言と、具体的なSQLコマンドが表示されます。
    • https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/advisor-recommendations.html
    • 上記URLにも記載されていますが、対策を行う場合はワークロードが軽い時間帯で実施してください。特にALTER TABLE文を伴うものはテーブルロックが伴うため注意が必要です。
    • 感覚値としては、[クエリとロード]に表示されるクエリ単位のチューニング項目よりも、全体的なアドバイザのほうが、実テーブル定義情報に基づいているため、より効果的な内容が表示されている感じがします。
    • 例えば圧縮エンコードにおけるソートキーについては、第一ソートキーは未圧縮のままで、第二ソートキーは圧縮すべき、というようなアドバイスが表示されたりします。デフォルトのコンパウンドソートキーを使っている場合などは、そもそも現時点のソートキーの順番が適切であるか確認してからアドバイスに従う、という順番にしたほうがよいと思います。

単体クエリパフォーマンス関連

ソートキー（後日詳細記載）

圧縮エンコード（後日詳細記載）

  • 列圧縮はカラムナデータベースにおいてはパフォーマンス上の重要な要素になります。とくにレコード数が多い場合への影響が大きいです。
  • https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_Compression_encodings.html
  • テーブル定義で圧縮系エンコードを何も指定しない場合
    • デフォルト「ENCODE AUTO」となり、すべてのカラムの圧縮が自動で管理されます。
  • テーブル定義で圧縮系エンコードを何か指定した場合
    • 逆に１つのカラムでも圧縮エンコードを指定すると、「ENCODE AUTO」が解除となります。
    • テーブル内で明示的にユーザが圧縮エンコードを指定していないカラムでも、Redshiftが自動的に圧縮エンコードを割り当てます（以下は上記公式ページからの引用です）
      • ソートキーとして定義されている列には、RAW 圧縮が割り当てられます。
      • BOOLEAN、REAL、または DOUBLE PRECISION データ型として定義されている列には、RAW 圧縮が割り当てられます。
      • SMALLINT、INTEGER、BIGINT、DECIMAL、DATE、TIMESTAMP、または TIMESTAMPTZ データ型として定義された列には AZ64 圧縮が割り当てられます。
      • CHAR または VARCHAR データ型として定義された列には、LZO 圧縮が割り当てられます。
    • なお、コンパウンドソートキーは第１カラムを圧縮すべきではなく、第２カラムは圧縮してもよいようです（Redshiftのアドバイザーでも第１カラム圧縮は提言されない。逆に第１カラムや単一のソートキーが圧縮されているとrawへの戻しを提言される）

パフォーマンス助言

  • [クエリとロード]画面では前記事にも記載したとおり、遅く改善の余地があるクエリについて、Redshiftからのアドバイスを確認することができます。
    image.png

  • 助言にたいしての対策はAWS公式にまとまっていません(AWSさんなんとかしてください)。

  • 助言と対策を少し無理やり表にしてみました。

# 英語表記 日本語表記 対策(概要)
1 Very selective query filter 非常に選択的なフィルター ソートキー見直しとVACUUMの実行
2 Distributed a large number of rows across the network サイズの大きな分散 分散スタイルの確認
3 Broadcasted a large number of rows across the network サイズの大きなブロードキャスト 分散スタイルの確認
4 DS_DIST_ALL_INNER for Hash Join in the query plan 直列実行 分散スタイルの確認
5 Missing query planner statistics 見つからない統計 データロードまたは大規模な更新の後で ANALYZE を実行
6 Nested Loop Join in the query plan Nested Loop クロス結合を確認し、可能であれば削除
7 Scanned a large number of deleted rows 過剰な数の非実体行 VACUUM コマンドを実行
  • 体感として「クエリとロード」に表示されるアドバイスは、「テーブル定義などをRedshiftがチェックしたうえで助言している」のではなく、「統計情報を元に実処理で発生している課題点」という観点でチェックしているように見えます。
  • 例えば「サイズの大きな分散」というアドバイスが出た場合に、JOINしているテーブルの定義情報から分散スタイルや分散キーを確認しても、適切に設定されているため対処が難しい場合などがあります。
  • そのため、必ずしもRedshiftのテーブル定義だけで対処できるものとは限らず、データマートのテーブル設計や、クエリ発行側の改善など、広い視野で対処が必要なアドバイスが出る、という点には注意してください。

同時実行性能

Redshiftを使うと同時実行性能のチューニングを考える必要が出てくるのは、ある意味Redshiftのアーキテクチャの宿痾ともいえます。SnowflakeやBigQueryと比較すると、クエリ受付レイヤのリソースがシングルテナントのリソースに限定されてしまうからです。

SQAによるショートクエリの自動振り分け

  • デフォルトで有効になっているキュー振り分け機能としてSQAがあります。

    • 下図はAmazon Redshift 運用管理からの引用です。

    • image.png

    • 仕組みとしては以下のように、AIが予想した時間とキュー待ち状況に基づいてWLMからクエリを引きはがすようです。

    • また、SQAは後述するWLMと自動で連携するため、キュー管理機能のバッティングを気にする必要はありません。

    • SQAは機械学習を利用しているので使うほど精度があがるようです。基本的にショートクエリはSQAに任せることにして、ワークロードに違いがある場合(バッチ用・BI用等）は、後述のWLMのカスタマイズがよいと思います。

WLMによるクエリキューの振り分け

事後的にクエリの長さでAIが自動でキューを判断するSQAと異なり、WLMは事前にユーザが設定した優先順位に基づき、キューの細かい振り分けができます。

WLMの理解

  • 全般

    • ワークロード管理 (WLM) とは
      • クエリのキューが効率よく実行できるように順番やメモリ量などを管理できる機能です。
      • リーダーノードにクエリが到達する前に、クエリの同時実行数や総メモリに基づいてキューの振り分けを自動で行います。
    • 自動WLM（デフォルト）
      • デフォルトでは、自動 WLM (クエリ優先度なし )の設定になっています。ただ以下の記事にあるように本番においては全体的な最適化がされるにしても、ビジネス視点ではムラが発生することもしばしばなようです。
    • 自動WLM（クエリ優先度あり）
      • 自動WLMでは、メモリ量などは自動化の恩恵を受けつつ、人間がクエリを分類しHIGHEST,NORMAL,LOWなど優先度だけ設定することができます。
      • 例えば、同一の負荷・同一のメモリ使用量のクエリが２つあったとして、人間がどちらのクエリを重視しているかはコンピュータには知りようがありません。それを教えてあげるのが、クエリ優先度の設定になります。
      • 下図はAmazon Redshift 運用管理からの引用です。
        • image.png

  • クエリキューの調整

    • クエリキューの調整は、自動WLMと手動WLMのどちらでも可能です。自動のほうが設定が楽です。
    • https://repost.aws/ja/knowledge-center/redshift-wlm-etl-queues
      • image.png
    • クエリキュー調整の選択肢は以下の３つです。
      • [1] 自動WLM（デフォルト）
      • [2] 自動WLM（クエリ優先度あり）
      • [3] 手動WLM（クエリ優先度あり(きめ細やか)）
    • クエリのキューへの割り当て方法
      • [2] 自動WLMの場合は、相対的な優先度を決めます。
      • [3] 手動WLMの場合は、メモリ割り当て量などを決める必要があります。
    • 割り当て対象
      • ユーザーロール、ユーザーグループ、クエリグループのいずれかに割り当てます。
        • ユーザーロールやユーザーグループでの割り当ては、データベースユーザ設計時に事前に取り決める必要があります。一般にはロールよりもグループのほうがシンプルに理解できると思います。
        • クエリグループでの割り当ては、クエリの先頭にSET文が必要になるので、SQL文を発行するアプリやBI側での対応（改修等)が必要になります。
      • つまりワークロードにあわせたクエリキューの調整を行うには、それなりの事前準備が必要になります。
    • その他知識
      • 共通(自動WLM/手動WLM)
        • 最大8個までキューを作成できる
        • 最大50個までスロットを作成できる。ただし15個以下に抑えたほうがよい。
          • 増やしすぎると1スロットあたりで使えるメモリが減り、悪影響もある。
        • 実行時間が長いクエリを制限するようにクエリモニタリングルール (QMR) を設定することもできる
      • 自動WLM
        • 自動WLMの場合、デフォルト状態ではデフォルトキュー１つだけ存在し、スロット数(同時実行クエリ数)やメモリ量は自動で増減します。
        • [メモリ] フィールドと [Concurrency on main (メインでの同時実行数)] フィールドはいずれも、[Auto (自動)] に設定されます
        • 自動WLMで複数のキューを作成し、全て同一の優先度とした場合でも、特定のキューが無駄に未使用なメモリ領域を確保することはなく、各キューが横断的にクラスタ全体のメモリを利用します。
      • 手動WLM
        • [メモリ] フィールドおよび [Concurrency on main (メインでの同時実行数)] フィールドの値を指定する必要があります。
        • 手動WLMの場合、デフォルト状態ではデフォルトキュー１つだけ存在し、キュー内で5クエリを並列実行できます。
        • https://www.slideshare.net/AmazonWebServicesJapan/amazon-redshift-118303349#42
        • image.png
        • [Concurrency on main (メインでの同時実行数)]とは
          • 「Concurrency on main」とは、キュー内における「スロット数」を指します。手動WLMでは意識する必要はあります。

クエリキューの設計

ConcurrencyScaling

（後日記載）

その他(Redhsift機能がらみ)

「アドバイザー」を利用した調査（後日詳細記載）

クエリエディタ(Redshift query editor v2)

  • SQL文字数の上限

    • 実行できる文字数と保存できる文字数に違いがあります。
    • https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/query-editor-v2-using.html

      • 最大 300,000 文字のクエリを実行できます。

      • 最大 30,000 文字のクエリを保存できます。

    • 長文のクエリを複数実行した場合、（基本的に警告を出してくれますが）、実行後セッションに再接続するとSQL文が全て消えていたなんてこともあります。

  • 「クエリエディタの履歴」「クエリとロード」どちらを見るべきか？

    • 基本的に情報が豊富な「クエリとロード」を見ましょう。
    • クエリの実行履歴を見る場合は以下の2つの方法があります。
      • マネジメントコンソールの「クエリとロード」
        • クラスタに対して実行されたクエリが表示されます。
        • クラスタの再起動で履歴が消えます。
        • システムで最も長く実行されている最大 100 件のクエリが表示されます。
      • クエリエディタの「履歴」
        • こちらはクエリエディタつまりSQLクライアント単体の実行履歴という扱いです。
        • 過去にクエリエディタで実行したクエリの所要時間を確認する程度であれば役に立ちます。
        • クラスタ再起動でも履歴は残ります。
        • 直近の 1000 個までのクエリ実行記録を保持します。

  • 注意事項

    • クエリエディタは内部で複数のAPIなどを実行することから表示がおかしくなることがあります(2023年8月時点)。
      • たとえばマネジメントコンソールの「クエリとロード」上では状態が「中止済み」となっているクエリのIDが、クエリエディタ上だと「実行中」になっている場合などがあります。
      • 私が確認した限りでは「クエリとロード」のほうが正しい状態を表示しますので、「クエリとロード」を信用するようにしましょう。

「クエリとロード」

概要

  • 概要
    • Redshiftのマネジメントコンソールで利用できる「クエリとロード」機能はパフォーマンスをチェックするのに非常にそこそこ役立ちます。
    • 仕様上の注意

      • 公式情報では「システムで最も長く実行されている最大 100 件のクエリが表示されます。」と記載されていますが、実際には「すべてのクエリ」を選択すれば100件以上のクエリを確認することができます。

      • image.png

        • デフォルトでは「期間別の上位100件のクエリ」が選択されており、クエリを見逃す可能性があるため注意してください。

      • クラスタを再起動すると、「クエリとロード」の履歴は消えるので注意が必要です。

クエリのチューニング（後日詳細記載）

WLMキューのモニタリング

前述しましたように、自動WLMでキューを複数作った場合は各キューの優先度が適切にパフォーマンスを発揮しているかモニタリングしていく必要があります。

  • 確認するにはクラスタを選択後、「クエリのモニタリング」-> 「ワークロードの同時実行」を選択します。
    • image.png
    • 例えばワークロード管理のパラメータグループで、以下のように自動WLMのキューを作成したとします。
      • bi
      • dwh
      • dataengineer
      • Default queue
    • image.png
    • 上記のような構成で、以下のように作成したキューが「WLMサービスクラスID」で色分けされて表示されます。
    • image.png
    • 2023/10/12時点では残念ながらグラフ上にサービスクラス名（上記でいうと「bi」など）が表示されることはなく、サービスクラスIDで表示されてしまいます。
    • また、「Default queue」は追加キューがない状態だとグラフ上で「Default queue」としてサービスクラス名が表示されるのですが、自分でキューを追加するとグラフ上もサービスクラスIDで表示されるようになってしまいます。
    • サービスクラスIDを確認するには、別途SQL文で調査する必要があります。調査はシステムテーブルのSTV_WLM_SERVICE_CLASS_CONFIGを参照します。
select
  service_class,
  rtrim(name) as name,
  rtrim(concurrency_scaling) as concurrency_scaling,
  rtrim(query_priority) as query_priority
from
  stv_wlm_service_class_config
where
  service_class >= 100; -- ユーザ追加キューはservice_classが100以上の値となる

出力結果例
image.png

そのほか、過去7日分のキューの状況はシステムテーブルのSTL_WLM_QUERYから調査することができます。

select
  query,
  service_class,
  rtrim(service_class_name) as service_class_name,
  slot_count,
  CONVERT_TIMEZONE('Asia/Tokyo', queue_start_time) as queue_start_time,
  CONVERT_TIMEZONE('Asia/Tokyo', queue_end_time) as queue_end_time,
  total_queue_time,
  CONVERT_TIMEZONE('Asia/Tokyo', exec_start_time) as exec_start_time,
  CONVERT_TIMEZONE('Asia/Tokyo', exec_end_time) as exec_end_time,
  total_exec_time,
  CONVERT_TIMEZONE('Asia/Tokyo', service_class_end_time) as service_class_end_time,
  rtrim(query_priority) as query_priority
from
  STL_WLM_QUERY
where
  service_class >= 100;

出力結果例（一部の列のみ抜粋）
image.png

  • 各キューの以下の時間を見比べることでキュー待ち状況を確認することができます。

    • 「キューに入って実行待ちになっている時間( queue_start_time から queue_end_time )」
      • 上記２つの差分が「total_queue_time」
    • 「実行時間( exec_start_time から exec_end_time )」
      • 上記２つの差分が「total_exec_time」

  • 調査方法は他にも公式のサンプルクエリもあるので確認してください。

その他(Tableauがらみ)

Tableauクエリのパフォーマンス調査アプローチ

TableauなどBIツールからRedshiftを参照することが多いと思います。そのような場合、Tableau側とRedshift側のどちらで対処すべきパフォーマンス問題なのか、確認が必要になります。

  • Tableau側では1つのダッシュボードの表示であっても、複数のレポートが含まれていたり、各種フィルタがかかっている場合などに、内部で複数のクエリが並列実行されます。
  • まずTableau側で時間がかかる処理を行う際に、「パフォーマンスの記録」情報を取得してください。
    • 取得する際は、Tableau側とRedshift側のキャッシュをOFFにします。
    • 可能であれば、同じ操作を複数回記録してください。同じ操作であっても、Tableau側で並列実行されるクエリの順番が異なることが多いため、クリティカルパスも毎回変化します。複数回の記録を確認したうえで、効果の高そうなクエリを複数、選定します。
    • Tableau Desktopにおける「パフォーマンスの記録」方法は以下。
    • パフォーマンスの結果は、Tableauのワークブック(.twbx)として出力されます。
    • 出力されたファイルをTableauDesktopで確認してください(TableauPublicでは閲覧不可)

カーソルの調査

  • Tableau からRedshiftへのクエリはカーソルで実行されることがしばしばあります。Redshiftの「クエリとロード」画面では以下のように表示され、チューニングしようにも元のSQL文がわかりません。
fetch 10000 in "(カーソル名称)";

そこで、クエリを復元する必要がでてきます。

所要時間から調査するアプローチ

  • 「クエリとロード」画面にて、所要時間の遅いカーソル利用クエリをつぶしていく場合は、カーソル名称とプロセスID(PID)をメモしておきます。
    • image.png
    • 注：なお、ここで見つけづらい場合はSTL_QUERY テーブルを直接参照するのも一つの手です。

次に以下のSQL文を実行します。("カーソル名称" と "PID" はご自身の情報に置き換えてください。)

SELECT
  *
FROM
  (
    SELECT
      DISTINCT xid,
      pid,
      CONVERT_TIMEZONE('Asia/Tokyo', starttime) as starttime,
      LISTAGG(rtrim(text)) WITHIN GROUP (
        ORDER BY
          sequence
      ) OVER (PARTITION BY starttime) AS sql_text
    FROM
      svl_statementtext
    GROUP BY
      xid,
      pid,
      starttime,
      sequence,
      text
  ) as t
WHERE
  sql_text like 'declare%<カーソル名称>%'
  AND pid = '<PID>';

  • ポイント

    • カーソルの宣言箇所「declare」を元に「svl_statementtext」テーブルに保存されたクエリ(ただし1SQL文は往々にして複数行に分割されている)を復元しています。
    • PIDを限定することで、Tableau側で同一のカーソル名称が使い回されている場合でも特定できるようにしています。
    • テキストのスペースをrtirm関数で除外することで、LISTAGG関数の文字制限(65535)にできるだけ引っかからないようにしています。
    • 注意点として、複数の行を強引に結合しているため、結合箇所の文字列が一部欠損します。

  • 結果として、クエリの実行時間(JST)とSQL本文が表示されます。

    • image.png

  • なお上記SQLは以下の記事をアレンジしたものです。執筆者様に深く感謝します。

Tableauの操作時間から調査するアプローチ

Tableau側の特定の操作時間がわかる場合は、「クエリとロード」を見る前に、先程のSQL文のWHERE句を以下例のように書き換えて実行してください。(時間は要書き換え。少なくともクエリエディタからの場合は、JSTベースで扱ってくれます)

WHERE
  sql_text like 'declare%SQL_CUR%'
  AND  starttime between '2023-10-31 11:55:00' and '2023-10-31 12:00:00'
order by starttime asc;

該当する時間の複数のクエリが出力されますので、そこからTableau側のクエリと合致しそうなものを抜き出し、pidを確認します。その後「クエリとロード」から該当するpidを検索し、アドバイス内容を確認します。

Tableauによるクエリの調査

  • あとはクエリプランのアドバイス内容をもとに対処が必要そうな処理を特定します。そしてSQL本文をもとにテーブル名を特定します。テーブルの定義情報を確認し、ソートキーや分散キーの見直しを検討していきます。
    • image.png
  • ただし、Tableauでは計算フィールド、フィルタ、LOD表現を使う場合などに、複数のJOINやサブクエリを用いるような複雑なクエリが生成されることが多く、調査は茨の道になりがちです。
  • 正論・理想論にはなってしまいますが、BI側で複雑な処理をするよりも、可能な限りRedshift側でテーブル設計の見直し（ETLで集計済みデータマート用テーブルを作る、マテリアライズド・ビューを作る、など）をしたほうが望ましいです。

おわりに

各クラウドデータウェアハウスサービスの使い分け

  • 私の印象としてはインフラ運用の簡便さで考えるとSnowflake、性能で考えるとBigQuery、常時起動をベースとしたコストパフォーマンスで考えるとRedshift、という使い分けがよい気がしています。(Azure Synapse Analyticsは触ったことがない)
    • なお、Clouderaスポンサーのレポートではコスパの良さを比較していますが、Redshiftは良い成績を記録しています。(あくまでコスパであり、速度ではない点に注意）

以上です

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1