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

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

## 1. 分析対象SQLの実行

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

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

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

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

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

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

## 2. 調査用データの収集

```sql
-- アラートが出てないか
-- 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;
```

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

```sql
-- 同時にクエリが実行されている状況の場合、これを見ることでクエリがどれだけの時間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を頼りに探す。

### 分析ポイント

- stl_alert_event_log がある場合は https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_ALERT_EVENT_LOG.html を参照。
- stl_wlm_queryのtotal_queue_timeが0でない場合はWLMキュー待ちが発生している。リソースに余裕がある場合はWLMの同時実行レベルを増やす。即応性の不要な重いクエリのせいで即応性が必要なクエリが待たされている場合はキューを追加して別々のキューを使うようにする。  
  cf. https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/workload-mgmt-config.html  
  https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/cm-c-defining-query-queues.html
- 実行計画を "DS_" でgrepし、DS_DIST_ALL_NONE、DS_DIST_NONE以外がないか確認する。ある場合は、ブロードキャストや再分散が発生しているので、https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_data_redistribution.html を参照し、その内容に応じて、そのテーブルの分散スタイルや分散キーを見直す。  
cf. https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/using-SVL-Query-Report.html
- 実行計画でcostが跳ね上がる行を探し、https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-the-query-plan.html や、infoの情報、svl_query_summaryの情報と合わせて処理内容や対象件数等を確認する。
- svl_query_report について、同一segment, stepにおける各sliceの処理時間とrowsを見て、slice間での差が大きい処理がないか確認する。差が大きい場合、lableを見て処理内容を確認し、分散データが偏っている可能性がないか確認する。なお、テーブル毎のデータ分散状況は https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_analyzing-table-design.html のSQLで確認可能。
- svl_query_summaryのis_rrscanが`f`(false)のものはテーブルスキャン時にソートキーによるスキャン範囲の限定が行われていない。SQLでテーブルの取得行を絞る条件があるのに、labelが`scan tbl=そのテーブルのID name=そのテーブル名`でis_rrscanが`f`の行があれば、ソートキーを見直す（ソートキーの列を変える、複数列の場合は順序を変えたり、インターリーブソートキーにする等）。
- svl_query_summaryのis_diskbasedが`t`(true)のものはクエリに割り当てられたメモリが不足し、中間結果のディスク書き出しが発生している。対応は https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/query-performance-improvement-opportunities.html#insufficient-memory-allocated-to-the-query を参照。
- svl_query_summaryのmaxtimeの大きいsegを確認する。  
  cf. https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/using-SVL-Query-Summary.html
- その他すでに挙げているものもあるが、以下を参考に確認する。  
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/using-SVL-Query-Summary.html  
https://aws.typepad.com/sajp/2015/12/top-10-performance-tuning-techniques-for-amazon-redshift.html  
http://packpak.hatenablog.com/entry/2019/02/27/072449  
http://packpak.hatenablog.com/entry/2019/05/08/020305  
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_designing-queries-best-practices.html

### その他

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

- はれるのは最大8列まで。
- ID列、日付、タイムスタンプなど一定間隔で増加する属性を持つ列では使わないこと。  
  https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Sorting_data.html#t_Sorting_data-interleaved
- バキュームでVACUUM REINDEXが必要となり、より時間がかかるようになる。  
  https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Sorting_data.html
- 同時実行スケーリング機能が使えない。  
  https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/concurrency-scaling.html
- インターリーブソートキーがどういうものかは https://qiita.com/t11a/items/a0b175e66ebc623f5cbe がわかりやすかった。

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

- 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時点でゾーンマップが使えるのでハッシュ結合にも効く。