LoginSignup
9
7

More than 3 years have passed since last update.

@i_learnin(Manabu Inoue)

Redshift クエリのパフォーマンス分析

Last updated at Posted at 2019-07-06

Redshiftで遅いSELECT文のパフォーマンス分析した時の手順等メモ。

1. 分析対象SQLの実行

-- このセッション中でクエリ結果キャッシュを無効にする
SET enable_result_cache_for_session TO off;

-- 分析対象SQLを実行
-- SQLのコンパイル時間を除くため、分析対象SQLを再度実行

-- 現在のセッションで最後に実行されたクエリのクエリIDを取得
SELECT pg_last_query_id();

すでに実行されたSQLを分析したい場合は、そのSQLを実行した接続ユーザ（もしくはスーパーユーザ）でRedshiftに接続し、

SELECT userid, query, starttime, endtime, substring
FROM svl_qlog
ORDER BY query
DESC LIMIT 10;

など、絞れる条件でsvl_qlogを検索してquery(クエリID)を特定する。

2. 調査用データの収集

-- アラートが出てないか
-- https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_ALERT_EVENT_LOG.html
SELECT *
FROM stl_alert_event_log
WHERE query = クエリID;

-- 実行計画
-- https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-the-query-plan.html
SELECT plannode || ' ' || info
FROM stl_explain
WHERE query = クエリID
ORDER BY nodeid;

-- SELECT文全体の開始・終了時刻など（キュー待ち時間等は含まれずクエリの処理時間がわかる）
-- https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_QUERY.html
SELECT *, DATEDIFF(microsecond, starttime, endtime) AS micro_secs
FROM stl_query
WHERE query = クエリID;

-- クエリの処理ステップ毎の情報
-- https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_SVL_QUERY_SUMMARY.html
-- https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_my_last_query_summary.sql
SELECT
    userid,
    query,
    stm,
    seg,
    step,
    maxtime, 
    avgtime, 
    rows, 
    bytes,
    rate_row,
    rate_byte, 
    lpad(' ', stm+seg+step) || label AS label, 
    is_diskbased, 
    workmem, 
    is_rrscan, 
    is_delayed_scan, 
    rows_pre_filter
FROM svl_query_summary
WHERE query = クエリID
ORDER BY stm, seg, step;

-- svl_query_summary のスライス毎の内訳が見れる
-- https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_SVL_QUERY_REPORT.html
SELECT *, DATEDIFF(microsecond, starttime, endtime) AS micro_secs
FROM svl_query_report
WHERE query = クエリID
ORDER BY segment, step, slice;

同時に複数のクエリが実行されている状況で実行されたクエリを確認する場合は以下も。

-- 同時にクエリが実行されている状況の場合、これを見ることでクエリがどれだけの時間WLMキュー待ちだったのかわかる
-- https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_WLM_QUERY.html
SELECT *
FROM stl_wlm_query
WHERE query = クエリID;

3. 収集したデータの分析

svl_query_summary, svl_query_report の見方

  • stmが同じsegmentは同時に開始される。ただし、開始されるものの、前のsegmentの結果が必要な場合は待つのでその待ち時間も含まれる。
  • maxtime, avgtime, start_time, end_time, elapsed_time はすべてsegment単位の値で1つのsegment内ではすべて同じ値が入る。stepやslice単位の値ではないことに注意。つまり、同一segment内での各処理にかかった時間はわからない。
  • elapsed_time の単位はマイクロ秒。
  • svl_query_reportのrows は「ステップが生成した行の数 (スライスあたり)。この数値はステップを実行した結果生成されたスライスの行数を表すもので、ステップが受け取った、または処理した行の数ではありません。すなわち、この数値はステップの実行後、次のステップに渡された行の数」
    cf. https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_SVL_QUERY_REPORT.html
  • labelの意味は https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/query-plan-summary-map.html を参照。これを元に実行計画と見比べることでも、ある程度どの処理が何かの把握が可能。

  • labelの3桁のテーブルIDは通常、一時テーブルを指す。例えば、HASH JOINを行う場合、最初にテーブルをスキャン(scan)して、ハッシュキーとする列を選択(project)し、ハッシュテーブルを作成(hash)し、HASH JOINを行う(hjoin)。これが

    label
    scan tbl=167891 name=テーブル名
    project
    hash tbl=415
    ...
    hjoin tbl=415

    のようにしてlabelに出てくる。scan, project, hashはだいたい一連で出てくるのでhashのテーブルがどのテーブルなのかは直前やその少し前のstepやsegmentをrowsが一致するscanを頼りに探す。

分析ポイント

その他

インターリーブソートキーの注意点

ソートキーのタイプと列の選択

  • https://aws.typepad.com/sajp/2016/12/amazon-redshift-engineerings-advanced-table-design-playbook-compound-and-interleaved-sort-keys.html
    https://d0.awsstatic.com/events/jp/2017/summit/slide/D3T1-5.pdf を参照。
  • フィルタ条件（WHERE句に記述されて件数を絞る条件）でなく、GROUP BYやORDER BY等でソートに使われるわけでもない結合条件の列は、MERGE JOINを狙うのでない限りはソートキー列にしても意味がないはず。Redshiftの結合には、通常遅い順にネステッドループ結合、ハッシュ結合、マージ結合の3つがあるが、結合条件のないクロスジョインとなるようなSQLでない限りはほぼネステッドループにはならず、ハッシュ結合ではその処理上1、結合条件列のゾーンマップを使える処理はないはず2なので。なお、ディメンションテーブルとファクトテーブルとの結合の場合、ディメンションテーブルをALL分散にすることも多いと思うが、そうするとMERGE JOINは狙えなくなる（かといってMERGE JOINを狙うために再分配を発生させるのは逆効果だと思う）。

  1. これはRDBのハッシュ結合で結合条件列のインデックスが使われないのと同じ。https://use-the-index-luke.com/ja/sql/join/hash-join-partial-objects ↩

  2. フィルタ条件は、ハッシュテーブルを作る前のテーブルscan時点でゾーンマップが使えるのでハッシュ結合にも効く。 ↩

9
7
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
9
7