AWS RedshiftはパワフルなDBだが、いかんせんクエリ特性に対するパフォーマンスの影響がRDBと違い、RDB育ちのエンジニアにとってはチューニングに四苦八苦しがち。
クエリのどこが遅いかを、堅実に測定する方法を一例として紹介する。
まずはクエリ実行してログから測る
まずは、対話ツールでRedshiftへ接続し、下記とおりの順でパフォーマンスが怪しいSQLを実行してほしい。
SQLの実行
-- このセッション中でクエリ結果キャッシュを offにする SET enable_result_cache_for_session TO off; -- トランザクション開始 BEGIN; ※パフォーマンスが怪しいSQLを実行 -- ↑で最後に実行したSQLのクエリIDを取得 SELECT pg_last_query_id(); -- クエリアラートの確認 Select * from pg_catalog.stl_alert_event_log WHERE query = /*先ほど取得したクエリID */ -- クエリのほとんどの実行ステップと処理行数・バイト数、範囲限定スキャンがあったかなどを取得する SELECT * FROM pg_catalog.svl_query_report WHERE query = /* 先ほど取得したクエリID */ ORDER BY query, segment, step, slice; -- クエリのブロードキャスト分散の発生数とバイト数、行数などを取得する SELECT * FROM pg_catalog.stl_bcast WHERE query = /* 先ほど取得したクエリID */ ORDER BY query, segment,step,slice; -- クエリのDIST分散の発生数とバイト数、行数などを取得する SELECT * FROM pg_catalog.stl_dist WHERE query = /* 先ほど取得したクエリID */ ORDER BY query, segment,step,slice;
stl_alert_event_log の見方
もしレコードが出力された場合、下記URLを参考に対処してみる。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-reviewing-query-alerts.html
各ログの見方
最後のクエリログに関するシステムテーブルの結果に注目する。
starttimeやendtimeを見るとわかるが、複数のステップや分散が、ストリーム（stm）を単位に同時に動いている。
Redshiftでクエリが遅くなる時は、１つのストリームで突出して遅いものが足を引っ張っているはずで、このログ上では各レコードから「転送バイト数がギガバイトまで膨れ上がっている」「やたらとstarttimeとendtimeに開きがある」といった形で見られる。
svl_query_reportの見方
svl_query_reportの各レコードが示すステップは「label」列から見れる。
- 「scan」であればテーブルデータのスキャン
- 「aggr」であればGroup Byなどの集約
- 「hash」であれば、JOINの前に各値をハッシュ化処理
詳しくは、公式ドキュメントを参照：https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_EXPLAIN.html
stl_bcast、stl_distの見方
stl_bcast、stl_dist は、再分散による各スライスへのデータ転送を示す。再分散は、分散スタイルで予防しない限りはGROUP BYやJOINをしたときに発生する。
Redshiftはスライスやノードといった小分けにしたコンピューティングリソースにデータを配分し同時処理することでパフォーマンスをとる設計戦略をもつが、 どうしてもデータを集め直して演算する処理はこういった非効率な動きになってしまう。
explain による実行計画取得と怪しい演算の特定
上記で怪しい箇所が見れたら、 explain を用いて実行計画を取得
explain ※パフォーマンスが怪しいSQL
explainから見れるのは、SQLからコンパイルした実際の実行ステップの総覧と、行数×1レコードのバイト数をもとに演算したコスト（参考値）。
クエリプランの実行ステップは、基本的に先ほど取得したSQLと一致するので、突出して時間がかかっているところと条件句を合わせて確認する。
- 例外としてRedshift都合でまれにLIMIT句を用いたときなどで特殊な実行ステップがログのみに発生することがある。
また「DS_BCAST_INNER」といった単語は、JOINや集約関数を利用したときの再分散の発生有無をあらわす。
改善策を練る
以上が基本的なクエリのパフォーマンスの確認方法。 なお、この確認方法では列圧縮タイプなどで対策するスライス内のディスク→メモリロードまでの遅延は可視化されないので注意。
典型的な対策は下記とおり。
- JOIN少ないのに行が多すぎて演算量がすごい
- 行が多いテーブルに「この列をWHERE句に指定しないとパフォーマンスがでない」「高頻度のVACUUM必須」の縛りを設ける代わりにソートキーをつけて演算量自体をカットする
- 分散が演算時間の大半をもっていく
- 分散スタイルの見直し
- ソートキーに賭ける
- そうはいっても列が多いテーブルだしJOIN数多いし
