Amazon Redshift: 『パフォーマンスチューニングテクニック Top 10』手順の実践(7).ディスクベースのクエリー

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

当エントリは先日投稿したシリーズ『『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』手順実践』の第7弾です。課題#7の『ディスクベースのクエリー』について内容を見て行きたいと思います。


7番目のこのトピックは1つ前の6つ目『キュースロットをウエイトしているクエリ』で紹介している対策と併せて考慮する必要があるものとなっています。

  • 1.6番目の対策で『並列性』を増やす必要のあるキューを探しだす
  • 2.RedshiftクラスタのWLMを調整
  • 3.調整した事により『ディスクベースのI/Oを必要とする』クエリが出てくる可能性がある

対策を行う事で、手順3.にて出現する可能性のあるクエリを特定する為の便利系クエリが当エントリで紹介する内容、となる流れです。

条件を満たすクエリを洗い出すSQL文は以下となります。

SELECT
  q.query,
  trim(q.cat_text)
FROM
  (SELECT
     query,
     replace( listagg(text,' ') WITHIN GROUP (ORDER BY sequence), '\\n', ' ') AS cat_text
   FROM
     stl_querytext
   WHERE
     userid>1
   GROUP BY
     query) q
  JOIN
    (SELECT distinct
       query
     FROM
       svl_query_summary
     WHERE
       is_diskbased='t'
       AND (LABEL LIKE 'hash%' OR LABEL LIKE 'sort%' OR LABEL LIKE 'aggr%')  
       AND userid > 1) qs
  ON qs.query = q.query
;

ただこちらのクエリ、状況によっては以下のエラーが発生する場合があります。LISTAGG関数で結果セットのサイズが既定を超えてしまうと発生するものの様で、長いクエリ等は割とこの条件に引っ掛かってしまいそうです。

  -----------------------------------------------
  error:  Result size exceeds LISTAGG limit
  code:      8001
  context:   LISTAGG limit: 65535
  query:     4234948
  location:  2.cpp:278
  process:   query1_517 [pid=115214]
  -----------------------------------------------

という訳でLISTAGG関数を使っている箇所を除去し、代わりにsequence(クエリテキストを構成する例に行を並び替える為の順番(行番号みたいなもの))を追加したSQL文を表示させるようにしました。r_SVL_QUERY_SUMMARYテーブルの is_diskbasedという列を判定に利用しているのがポイントです。

SELECT
  q.query,
  q.sequence,
  trim(q.cat_text)
FROM
  (SELECT
     query,
     sequence,
     replace(text, '\\n', ' ') AS cat_text
   FROM
     stl_querytext
   WHERE
     userid>1
   ) q
JOIN
  (SELECT distinct
     query
   FROM
     svl_query_summary
   WHERE
     is_diskbased='t'
     AND (LABEL LIKE 'hash%' OR LABEL LIKE 'sort%' OR LABEL LIKE 'aggr%')
     AND userid > 1) qs
   ON qs.query = q.query
ORDER BY
  query,
  sequence
;

結果は以下の様な形で出力されます。クエリの内容から、どのタイミングで実施されたどのSQL文かを特定出来るかと思います。

  query  | sequence |                                    btrim                                                                                       
---------+----------+--------------------------------------------------------------------------------------------------------------------
 1234567 |        0 | COPY "xxxxxxxxxxxx" FROM 's3://xyzxyz(以下略)
 1234567 |        1 | CREDENTIALS '' GZIP DELIMITER '\\t' NULL '\\N' ESCAPE TRUNCATECOLUMNS ACCEPTINVCHARS STATUPDATE OFF COMPUPDATE OFF
 9876543 |        0 | SELECT (以下略)
 9876543 |        1 |  :
 9876543 |        2 |  :
 9876543 |       16 | )) AS "xxxxx"

これで対象となるクエリが特定出来ましたので、以後はクエリが実行されているキューの設定を動的に変更する事で状況を改善出来る可能性があります。ただこちらの作業についても、変更を行う事で影響が出てくる部分もありますので変更・作業には十分注意を払って行う必要がありそうです。

ユーザの定義もしくはキュー割り当てのルールをベースに、選択したキューでクエリーが実行完了までにディスクへのI/Oを発生させないよう、メモリの割り当てを増加させることが可能です。セッションごとにWLM_QUERY_SLOT_COUNTを増加させることが可能で、デフォルトである1からキューでの最大並列度数まで調整可能です。課題#6に示したように、これはクエリをキューイングさせます、つまり使用には注意が必要です。

まとめ

以上、『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』トピック7つめ、"ディスクベースのクエリー"に関する対処方法のご紹介でした。8つ目以降のトピックについても、こんな感じで読み解きつつ実践して行きたいと思います。