Amazon Redshiftで扱う”カーソル”の設定について

Amazon Redshiftでは『カーソル(CURSOR)』という概念があります。

• DECLARE - Amazon Redshift
• FETCH - Amazon Redshift

先日、ノードタイプ毎に定められていたこの『カーソル』に関連する要素の値がパラメータグループの設定によって調整可能となりましたので実際に触ってみたいと思います。

• Amazon Redshift Parameter Groups - Amazon Redshift

目次

• カーソル結果セットの最大サイズ構成について
• 現在のカーソル結果セット(ResultSet)の確認
• カーソルに関する設定値を変更してみる
• まとめ

カーソル結果セットの最大サイズ構成について

max_cursor_result_set_sizeでは、個々のカーソル毎に保存する事が出来る結果セット(ResultSet)の最大値(※メガバイト単位)を定義しています。このパラメータ値は、クラスタに対して同時実行可能なアクティブなカーソルの数影響を与えます。この値を増減させる事で、クラスタにおける『結果セット(ResultSet)の最大値』と『同時実行数』との理想的なバランスを達成する事が出来ます。

Amazon Redshiftクラスタにはその最大値は現在アクティブな全てのカーソルで格納し得る最大サイズがあります。この制限値は設定変更する事は出来ず、またノードタイプ毎に値は異なります。クラスタ毎に持つ事が出来る同時実行可能なカーソルの数は、ノードタイプ毎に許可されている最大値の合計サイズをmax-cursor-result-set-sizeパラメータで割る事で決まります。

例えば、dw1 XLのシングルノードクラスタ構成の場合、デフォルト値としてカーソルを2つ利用出来、カーソル辺り32000MB(32GB)のデータを扱えます。メガバイト比は64000/32000=2となり、これは即ち『32000MB(32GB)まで格納出来るカーソルを最大2個利用出来る』事を意味します。また、同じノードタイプでもし同時利用したいカーソル数を4つに増やしたい場合は、max_cursor_result_set_sizeを16000に変更する事で比率は64000/16000=4となり、目標を達成出来ます。

同時実行可能なカーソルの数は、同時実行クエリ数の上限を超える事は出来ません。ちなみに現時点では、Amazon Redshiftはこの値を15に定めており、セットした同時実行可能なカーソル数が増える場合はmax_cursor_result_set_sizeの値に従い調整されます。

以下表がそれぞれのノードタイプ・構成毎に定められている数値となります。

パラメータグループを作成する際、max_cursor_result_set_sizeに対して許可されている範囲内で任意の数値を設定する事が出来ます。しかし、ひとたびパラメータグループがクラスタに紐付けられると、Amazon Redshiftはmax_cursor_result_set_sizeパラメータが許容範囲内である事をバリデーションを実行します。

このバリデーション処理はクラスタの作成、変更、リサイズ時やクラスタに紐付いているパラメータグループを変更した場合に発生します。パラメータグループが複数のクラスタに関連付けられている場合、Amazon Redshiftは全てのクラスタの中で最も小さな(許可されている)値を採用します。値が有効でない場合は、max_cursor_result_set_sizeパラメータがクラスタの許容範囲外の値であるが為にパラメータグループがクラスタとの互換性が無い、という事を示すエラーが返ってきます。デフォルト設定に戻したい場合はパラメータ値を"0"に変更してリセットします。

この値を変更してクラスタを再起動後、STV_CURSOR_CONFIGURATIONを参照する事で変更内容を確認する事が出来ます。

クエリ実行時に以下のエラーが発生した場合はmax_cursor_result_set_sizeの値を減らす事を検討してみてください。

逆に、クエリ実行時に以下エラーが出た場合はmax_cursor_result_set_sizeの値を大きくする事を検討してみてください。

現在のカーソル結果セット(ResultSet)の確認

カーソルの利用サイズに関しては、以下STV_ACTIVE_CURSORSで確認する事が出来ます。しかし！名前にACTIVEとあるように、この内容はカーソルが有効になっている間(カーソル宣言〜カーソルを閉じるまで)しか参照する事が出来ません。

• STV_ACTIVE_CURSORS - Amazon Redshift

一方、実行された各種クエリ情報については幾つか履歴を管理しているテーブルがあります。今回はその中でSVL_STATEMENTTEXTを参照してみる事にします。

• SVL_STATEMENTTEXT - Amazon Redshift

上記2テーブルを組み合わせたクエリを組んでみました。ひとまず項目は全部盛り込んでます。また、容量を示す部分がバイト数表記だったのでそれぞれ把握しやすい単位での表記も追記しました。(カーソル名はもしかしたら、お使いの環境に拠って異なっているかもしれないので適宜読み替えて下さい。今回、Tableau Desktopから操作を行った際のケースで検証を行い、Tableauから自動的に発行されるカーソルの名称がこのような形でしたので条件指定もそれに倣いました。)

SELECT
  STV_ACTIVE_CURSORS.userid,
  STV_ACTIVE_CURSORS.name,
  STV_ACTIVE_CURSORS.pid,
  STV_ACTIVE_CURSORS.starttime as zstarttime,
  STV_ACTIVE_CURSORS.row_count,
  STV_ACTIVE_CURSORS.byte_count,
  (CAST(STV_ACTIVE_CURSORS.byte_count as double precision) / 1024) as kbyte_count,
  (CAST(STV_ACTIVE_CURSORS.byte_count as double precision) / 1048576) as mbyte_count,
  (CAST(STV_ACTIVE_CURSORS.byte_count as double precision) / 1073741824) as gbyte_count,
  STV_ACTIVE_CURSORS.fetched_rows,
  SVL_STATEMENTTEXT.userid,
  SVL_STATEMENTTEXT.xid,
  SVL_STATEMENTTEXT.pid,
  SVL_STATEMENTTEXT.label,
  SVL_STATEMENTTEXT.starttime,
  SVL_STATEMENTTEXT.endtime,
  SVL_STATEMENTTEXT.sequence,
  SVL_STATEMENTTEXT.type,
  SVL_STATEMENTTEXT.text
FROM
  SVL_STATEMENTTEXT
  LEFT OUTER JOIN STV_ACTIVE_CURSORS
    ON (
    SVL_STATEMENTTEXT.userid = STV_ACTIVE_CURSORS.userid
    AND SVL_STATEMENTTEXT.xid  = STV_ACTIVE_CURSORS.xid
    AND SVL_STATEMENTTEXT.pid  = STV_ACTIVE_CURSORS.pid
  )
WHERE
  text like'%SQL_CUR%'
  AND STV_ACTIVE_CURSORS.fetched_rows IS NOT NULL
ORDER BY
  starttime DESC;

実行〜カーソル発行に併せて、クエリを実行してみます。しばらくするとサイズ部分が0の状態で行が表示され、実行を続けていると数値が上昇しました！これで実行クエリの使用状況ががおおよそ分かりそうです。

カーソルに関する設定値を変更してみる

Redshiftクラスタを立ち上げてみます。dw2 Large x 2ノードで構成してみました。パラメータグループも別途用意しています。

パラメータグループはデフォルト値で構成しています。max_cursor_result_set_sizeもデフォルト値、一番大きな許容値となるDW1 8XL multiple nodesの14400000(約14.4TB)が設定されています。

Redshiftのデータベースにログインして現在の設定を確認してみます。dw2 Large x 2ノードなのでデフォルトの設定値は以下のようになっています。

$ psql -h tblab-201-redshift-cluster-XXXXXXXXXXX.us-west-2.redshift.amazonaws.com -U root -d cmtableaudb -p 5439
Password for user root: 
psql (9.1.5, server 8.0.2)
WARNING: psql version 9.1, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

cmtableaudb=# SELECT * FROM STV_CURSOR_CONFIGURATION;
 maximum_cursor_count | maximum_cursor_size 
----------------------+---------------------
                    2 |              192000
(1 row)

cmtableaudb=# 

パラメータ変更を行います。初期値ではdefaultが設定されていますので…

設定値を64000に変更してみます。カーソル数を384000 / 192000 = 2から、384000 / 64000 = 6に変更する想定です。変更を[Save Changes]で保存します。

変更完了後。

変更が完了したタイミングに併せて、クラスタの変更も始まります。

変更が反映された時点でクラスタの再起動を促されるので従いましょう。

Redshiftクラスタのデータベースに再度アクセスして内容を確認してみます。想定通りにカーソル数上限が変更されていますね！

$ psql -h tblab-201-redshift-cluster-XXXXXXXXXXX.us-west-2.redshift.amazonaws.com -U root -d cmtableaudb -p 5439
Password for user root: 
psql (9.1.5, server 8.0.2)
WARNING: psql version 9.1, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

cmtableaudb=# SELECT * FROM STV_CURSOR_CONFIGURATION;
 maximum_cursor_count | maximum_cursor_size 
----------------------+---------------------
                    6 |               64000
(1 row)

cmtableaudb=# 

まとめ

以上、Redshiftに於ける『カーソル』についての設定値について触れてみました。カーソルを用いなければならない局面が発生した場合、値の調整を行う事で利用カーソル数を増やす事が出来るのは嬉しいところです。

また、Redshiftクラスタに対するカーソルの利用については、以下ページに記載されているように非常に大きな結果セットに対してはカーソルの使用を控えた方が良いようです。用途と状況に応じてこの辺使い分けを行えるようにしておきたいですね。

• DECLARE/カーソルを使用するときのパフォーマンスに関する考慮事項- Amazon Redshift