【レポート】Amazon Redshift クエリパフォーマンスチューニング Deep Dive(AWS-36) #AWSSummit

AWS Summit Tokyo 2023

suto

2023.04.23

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

どーも、データアナリティクス事業本部コンサルティングチームのsutoです。

この記事は、5月26日に行われた AWS Summit Tokyo（2023）のセッション『Amazon Redshift クエリパフォーマンスチューニング Deep Dive(AWS-36) 』のセッションレポートとなります。

セッション概要

経験や勘に頼らず、Amazon Redshift のクエリのボトルネックをシステマチックに分析してチューニングする実践的な手法を紹介します。マネジメントコンソールやシステムテーブル・ビューから時間ベースでボトルネックを特定して、チューニングを行う手法を理論と実践の両面から実例を交えて紹介します。

スピーカー

アマゾンウェブサービスジャパン合同会社プロフェッショナルサービス本部シニアビッグデータコンサルタント

畔勝洋平

セッション視聴リンク

AWS Summit Tokyoの登録を行うことで2023/6/23までオンデマンドで視聴可能です。（現地参加された方は改めての登録は不要です。）

登録済みの場合、以下のURLから直接見れます。

https://jpsummit.awsevents.com/public/session/view/555

レポート

クエリ単体性能のパフォーマンスチューニングを話す

Amazon Redshift進化の歴史とアーキテクチャ

2012年の「Amazon Redshift発表」からやく10年の歴史
- RA3インスタンスの登場
  - 従来のDC2と違い、ストレージノードをS3としてリーダーノードやコンピュートノードと分離
- シェアードナッシング＋MPP（Massively Parallel Processing）
  - ストレージをノード間で共有しない
  - 1つのクエリを複数ノードで並列分散実行し、ノード数をスケールアウトすることでパフォーマンスを向上させる
- スライス：ノード内でCPU、メモリ、ストレージを論理的に分割した処理単位
  - 各スライスに割り当てられたリソースが、配下に分散されたデータに対して並列処理
クエリの流れについて（本セッションのワードを理解できるために、特にコンパイルの流れを詳しく説明している）

クライアントからクエリ受信
コンパイル・コードの配信
1. Parserがクエリ解析
2. 初期クエリツリーを作成してオプティマイザに渡す
3. 最適化のためクエリ書き換え
4. クエリプラン作成
5. 実行エンジンがクエリプランをステップ・セグメント・ストリームに変換し、C++コード、オブジェクトコードにコンパイル
6. コンピュートノードにコンパイル済みコードが渡されて並列実行
スライスで並列実行
リーダーノードに結果を集約
結果をクライアントに返す

分析クエリは仕事量が多いため、Redshiftでは高速化するアプローチを行なっている
- I/O量削減
  - 列志向ストレージ、圧縮エンコード、ゾーンマップ、ソートキー
- 並列化
  - シェアードナッシング、MPP、分散スタイル
- 高速化
  - SIMD Vectorized Scans、AQUA、CaaS（参考リンク）
圧縮エンコード、ソートキー、分散スタイルのアプローチをカバーできる『テーブル設計』は重要

クエリのボトルネック解析

よくある誤解
- コストが大きいオペレーションがボトルネック
- 非効率とされるオペレーションがボトルネック
  - 上記2つがボトルネックとは限らないので、クエリプラン（STL_EXPLAIN）を読めば良い
クエリチューニングで大切なこと
- 時間ベース分析：どこに時間がかかっているか特定

- マネジメントコンソールで、クラスターパフォーマンス、クエリ履歴（ロード時間など）、データベースパフォーマンス（レイテンシーなど）、ワークロードコンカーレンシーを確認できる
- システムテーブル・ビュー（STL、STV、SVL、SVV）を確認。過去７日間まで保存されている
具体的にどの観点からどの順番で計測していくか
- SQLクライアントなど：クライアントから見た総実行時間
  - マネジメントコンソールのQuery historyから「Duration」で経過時間がわかる
- STL_QUERY：Redshift側でどのくらい時間がかかっているか（Rewritten queryの時間など）
  - クエリの開始・終了時刻やクエリ文字列、Conccurrency Scaling実行状況など
  - ユーザークエリが分割されていることがあるので、Query historyから「Query type」列でRewritten queryごとの経過時間を確認
  - さらにQuery planでチューニングアドバイスも確認できる
- STL_WLM_QUERY：対象のクエリが遅いか（Queue time、Exec timeを確認）
  - キュー待ち時間、実行時間、クエリスロット数はいくつだったか、実行されたWLMキュー、クエリの優先度
  - Service_class、total_queue_time、total_exec_timeあたりを見る
- 以下のテーブルでどのセグメントで時間がかかっているか、スライスで偏りがあるか確認
- SVL_QUERY_METRICS_SUMMARY
  - query_blocks_read：ストレージからの読込（MB）
  - query_temp_blocks_to_disk：ストレージに書き出した中間結果のサイズ（MB）
  - Segment_execution_time：最も時間がかかっているセグメントの値
  - Cpu_skew、io_skew：この値が高いとスライスに偏りがある
- SVL_QUERY_METRICS
  - クエリ、セグメント、ステップレベルでのメトリクス。中間結果書き出しサイズ、スライス間の処理の偏り、セグメント毎の実行時間など
- SVL_QUERY_SUMMARY
  - １ステップ1行。遅いセグメントの特定、スライスで処理の偏りがないか、中間結果の書き出しがないか、不要なデータを読んでいないか確認
  - maxtimeでセグメントの最大時間、avgtimeでセグメントの平均時間を見て、双方の差が大きいとスライスで偏りがある
  - Rows_pre_filter-rowが大きいと不要な読み込みが多い
  - is_diskbasedが"t"（true）でworkmemの値が大きいと、メモリで収まらず中間結果をストレージで書き出ししていることを表している
- SVL_QUERY_REPORT
  - 1スライス1行。SVL_QUERY_SUMMARYより詳細にスライス別の情報を確認できる
  - Start_time、end_timeからセグメントが並列実行されているか確認、同じステップでrows、bytesいん偏りがあるとスライスで処理に偏りがあるとわかる
ボトルネックを特定できたら
- 遅いステップからクエリプラン・クエリ分への紐付け
- STL_EXPLAINで、クエリの実行計画、アラートがないか、非効率な操作がないか
  - https://docs.aws.amazon.com/redshift/latest/dg/query-plan-summary-map.html
- 確認ポイント
  - コストが急激に大きくなっている操作はボトルネックの可能性あり
  - Merge Join：最も効率が良い
  - Hash Join：Merge Joinでないときに選択される
  - Nested Loop：非常に効率が悪い。結合条件が抜けてないか確認する
  - DS_BCAST_INNER：内部表全体を全ノードに転送。改善が必要
  - DS_DIST_ALL_INNER：内部表全体を単一ノードに転送（外部表がALL分散のため）。改善が必要
  - DS_BCAST_BOTH：両方の表を転送。改善が必要

具体的なチューニング例の紹介

わざと効率の悪い検証データを用意してこれまで紹介した内容からパフォーマンス改善を行なった結果を紹介されていた。

本ブログでは、ボトルネックの解析→チューニング案の検討の流れをサマリー形式で記載させていただきます。

スライス間で処理量に偏りがある場合

【ボトルネックの解析】
- SVL_QUERY_METRICSテーブルでsegment_execution_timeの大きいセグメントのcpu_skewやio_skewの値が大きいことを確認
- SVL_QUERY_SUMMARYテーブルからmaxtimeとavgtimeの差が大きいことを確認
- SVL_QUERY_REPORTテーブルのelapsed_time、rows、rows_pre_filter列から最も処理時間が多いスライスを見つける
【チューニング案の検討】
- 分散キーを値の種類が多く、各値に偏りがない列に変更する
- 値の種類は、”select count(distinct 列)”で比較
- 値の偏りは、"select 列, count(*) … group by 列 order by count( * ) desc;"などで確認

ストレージからの余計な読込がある場合

【ボトルネックの解析】
- SVL_QUERY_METRICSテーブルでsegment_execution_timeの大きいセグメントの特定
- SVL_QUERY_SUMMARYテーブルからrows_pre_filter/rowsの計算結果を確認。割合の値を大きいほど不要な読み込みが多いことを確認
【チューニング案の検討】
- ソートキーは値の種類（”select count(distinct 列)”）の数値が低い順に指定するように変更

ノード間で大量のデータ転送がある場合

【ボトルネックの解析】
- SVL_QUERY_METRICSテーブルでsegment_execution_timeの大きいセグメントの特定
- SVL_QUERY_SUMMARYテーブルのlabel列でDIST（再分散）によるノード間通信があることを発見
- STL_EXPLAINテーブルでDS_DIST_BOTHがあることを確認。ネックとなっている結合キーを特定
【チューニング案の検討】
- 特定した結合キーを分散キーに指定して解消するか検証する
- STL_EXPLAINテーブルでDS_DIST_NONEになったことを確認

中間結果のストレージ書き出しが大きい場合

【ボトルネックの解析】
- SVL_QUERY_METRICSテーブルのquery_temp_blocks_to_diskのqueryの値を確認、segment_execution_timeの大きいセグメントの特定
- SVL_QUERY_SUMMARYテーブルのworkmemの大きい値、is_diskbased = "t"を確認。labelでハッシュ結合になっていることを確認
- クエリの結合列にcollate関数を使用している。結合キーが分散キーかつソートキーであるがマージ結合になっていない
【チューニング案の検討】
- collate関数なしで大文字・小文字を区別しないようデータベースかテーブルでcase_insentiveを指定

最後に

私はこれまでRedshiftのパフォーマンス向上のためのアーキテクチャ改善の話をre:Inventのセッション等で学習していたので、クエリプランの読み方などについてもちしきはあったのですが、実際にパフォーマンス改善を手動で実践するにはどのような方法と手順でアプローチしてよいかよくわかっていませんでした。

なので本セッションの内容でその懸念がほぼ全て払拭できるくらいわかりやすく、ボリュームもある内容で、聞けてよかったと感じました。

最近のRedshiftのこのようなパフォーマンスチューニングはAuto設定があるので、ある程度使い倒していけばいい感じの性能になるわけですが、本セッションの内容は知っていて損はないはずです。

【レポート】Amazon Redshift クエリパフォーマンスチューニング Deep Dive(AWS-36) #AWSSummit