この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
Redshiftには多くのシステムビューがあり、それらを使ってRedshiftの状態を確認することができます。同じSQLをよく実行することがあるので、自分用のメモとして書き溜めていたものをブログにまとめたいと思います。
ログ記録のための STL テーブル - Amazon Redshift システムビューの一覧 - Amazon Redshift システムカタログテーブル - Amazon Redshift
SQL一覧
- 統計情報の鮮度とソートされていない行の割合を確認したい
- どの程度テーブルに削除マーク付きのレコードが含まれているかを確認したい
- VACUUMコマンドの進捗を確認したい
- SQLの開始時間と終了時間を確認したい
- SQLの開始時間と終了時間を確認したい。(全部入り)
- 実行中のクエリの進捗を確認したい
- どの程度ストレージサイズを使っているかを確認したい
- 分散キー、ソートキー、圧縮エンコードが設定されていることを確認したい
- テーブルに依存しているビューを探したい
統計情報の鮮度とソートされていない行の割合を確認したい
統計情報の鮮度とソートされていない行の割合はSVV_TABLE_INFOというシステムビューを見れば確認できます。UNSORTEDの数値が高いテーブルはVACUUMして、STATS_OFFの数値が高いテーブルはANALYZEしましょう。因みにSVV_やSVT_で始まっているものはシステムビューになります。
SVV_TABLE_INFO - Amazon Redshift
SELECT
"SCHEMA",
"TABLE",
UNSORTED,
STATS_OFF
FROM
SVV_TABLE_INFO
ORDER BY
STATS_OFF DESC;
どの程度テーブルに削除マーク付きのレコードが含まれているかを確認したい
UPDATE、DELETEを実行すると削除マーク付きのレコードが増えていきパフォーマンスが低下する恐れがあります。以下はテーブルにどの程度、削除マーク付きのレコードが含まれているかを確認するSQLです。削除マーク付きのレコードが溜まっている場合はVACUUMコマンドを実行して削除マーク付きのレコードを解放しましょう。
SELECT
ROWS1, ROWS2, 1 - ROWS1 / ROWS2 AS DELETE_MARK_RATE
FROM
(SELECT COUNT(*) AS ROWS1 FROM [スキーマ名].[テーブル名]),
(SELECT TBL_ROWS AS ROWS2 FROM SVV_TABLE_INFO
WHERE "SCHEMA"='[スキーマ名]' AND "TABLE"='[テーブル名]');
VACUUMコマンドの進捗を確認したい
あとどれくらいでVACUUMコマンドが完了するかを確認するSQLです。ただしVACUUMコマンドはいくつかのフェーズに分かれており、このSQLで確認できるのはそのフェーズがどのくらいか終わるかだけなので全体でどの程度かかるかはわかりません。STL_VACUUMテーブルを見れば前回実行時の処理時間を確認できますのでそれを見て残り時間を予想するしかありません。
SVV_VACUUM_PROGRESS - Amazon Redshift STL_VACUUM - Amazon Redshift
SELECT * FROM SVV_VACUUM_PROGRESS;
SELECT
STL_VACUUM.*,
SVV_TABLE_INFO."SCHEMA",
SVV_TABLE_INFO."TABLE"
FROM STL_VACUUM INNER JOIN SVV_TABLE_INFO
ON STL_VACUUM.TABLE_ID=SVV_TABLE_INFO.TABLE_ID
WHERE "SCHEMA"='[スキーマ名]' AND "TABLE"='[テーブル名]';
SQLの開始時間と終了時間を確認したい
実行したSQLがどの位の処理時間だったかを確認したことはあると思います。そのような場合に実行時間を表示するSQLになります。querytxtにテーブル名などのキーワードを入れてあいまい検索してください。処理時間の計算にはDATEDIFF関数を使って処理時間を計算しています。
SELECT
*,
DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF
FROM
STL_QUERY
WHERE
QUERYTXT LIKE 'SELECT * FROM%'
ORDER BY
STARTTIME DESC;
SQLの開始時間と終了時間を確認したい。(全部入り)
上のSQLはDDLなどを含んでいません。全部入りで見たい場合はSVL_STATEMENTTEXTテーブルを参照します。 以下の例はキーワードを含むSQLを処理時間の降順で表示するSQLです。STL_QUERYを参照するよりも時間がかかります。種類がTYPEカラムで分けられていますが、QUERY、DDL、UTILITYのどれかです。UTILITYに含まれるのはTRUNCATE、ANALYZE、BEGIN、COMMIT、ROLLBACKなどになります。因みにVACUUMはQUERYに含まれます。
SVL_STATEMENTTEXT - Amazon Redshift
SELECT
*,
DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF
FROM
SVL_STATEMENTTEXT
WHERE
TYPE = 'DDL' AND TEXT LIKE LOWER('CREATE %')
ORDER BY
DATEDIFF(SEC,STARTTIME,ENDTIME) DESC;
実行中のクエリの進捗を確認したい
実行中のSQLがどの程度すすんでいるのかを確認するSQLになります。処理される件数に対し、そのステップ内であればROWSカラムの値がどの程度進んでいるかでいるかで進捗が分かります。ただし、初めて実行するSQLの場合、セグメントとステップがいくつあるかは分かりませんので全体でどれくらいかかるかは確認できません。
SVV_QUERY_STATE - Amazon Redshif
SELECT
*
FROM
SVV_QUERY_STATE
WHERE
QUERY = [クエリのID]
ORDER BY
SEG, STEP;
1回実行したことのあるSQLであれば、セグメント、ステップの処理件数や時間をSQL実行後に確認したい場合はSVL_QUERY_REPORTテーブルを見れば確認できます。
SVL_QUERY_REPORT - Amazon Redshift
SELECT
USERID, QUERY, SEGMENT, STEP, MIN(START_TIME), MAX(END_TIME), SUM(ROWS) , LABEL
FROM
SVL_QUERY_REPORT
WHERE
QUERY = [クエリのID]
GROUP BY
USERID, QUERY, SEGMENT, STEP , LABEL
ORDER BY
SEGMENT, STEP;
どの程度ストレージサイズを使っているかを確認したい
どの位ストレージを使用しているかテーブル単位で降順に表示するSQLです。単位はMBになります。
STV_BLOCKLIST - Amazon Redshift
SELECT
SVV_TABLE_INFO.SCHEMA,
STV_TBL_PERM.NAME,
MB
FROM
(
SELECT
TBL,
COUNT(*) as MB
FROM
STV_BLOCKLIST
WHERE
TBL IN (SELECT ID FROM STV_TBL_PERM)
GROUP BY TBL
) BLOCK_LIST
INNER JOIN STV_TBL_PERM ON BLOCK_LIST.TBL=STV_TBL_PERM.ID
INNER JOIN SVV_TABLE_INFO ON STV_TBL_PERM.ID = SVV_TABLE_INFO.TABLE_ID
GROUP bY
TBL,
STV_TBL_PERM.NAME,
BLOCK_LIST.MB,
SVV_TABLE_INFO.SCHEMA
ORDER BY
MB DESC;
分散キー、ソートキー、圧縮エンコードが設定されていることを確認したい
分散キー、ソートキー、圧縮エンコードを確認したい場合は以下のSQLを実行します。PG_TABLE_DEFで参照できるのはSEARCH_PATHに設定されているスキーマのテーブルのみになります。SEARCH_PATHに調査したいスキーマが含まれている場合は1行目は実行しなくてもいいです。
PG_TABLE_DEF - Amazon Redshift
SET SEARCH_PATH TO '$USER','[スキーマ名]';
SELECT
*
FROM
PG_TABLE_DEF
WHERE
SCHEMANAME = LOWER('[スキーマ名]') AND TABLENAME = LOWER('[テーブル名]');
テーブルに依存しているビューを探したい
テーブルをDROPする際に依存しているビューがあると失敗します。cascadeオプションをつけると削除できますがどのビューが削除されるかを確認する必要があります。 以下のSQLのWHERE句にテーブル名を指定して実行すると依存しているビューを確認できます。
SELECT
DISTINCT PG_CLASS_2.RELNAME
FROM
PG_CLASS PG_CLASS_1
INNER JOIN PG_DEPEND PG_DEPEND_1 ON PG_CLASS_1.RELFILENODE = PG_DEPEND_1.REFOBJID
INNER JOIN PG_DEPEND PG_DEPEND_2 ON PG_DEPEND_1.OBJID = PG_DEPEND_2.OBJID
INNER JOIN PG_CLASS PG_CLASS_2 ON PG_DEPEND_2.REFOBJID = PG_CLASS_2.RELFILENODE
WHERE
PG_CLASS_1.RELNAME = '[テーブル名]' AND
PG_DEPEND_2.DEPTYPE = 'i'::"char" AND
PG_CLASS_2.RELKIND = 'v'::"char";
最後に
私がよく使っているSQLは以上になります。まだ使ったことがないシステムテーブルもありますので、これからも調査を続け便利なSQLがあったらどんどん追加していきたいと思います。あと先日、Redshiftの注意点をまとめた記事を公開しましたので、よろしければこちらもご覧ください。