Amazon Redshift: VACUUM処理が必要なテーブルを改めて洗い出す

超小ネタです。

以前、以下のエントリを投稿しましたが、スキーマを分割していたりすると、そもそも『このテーブル、どこのスキーマだっけ？』となる事がありました。なので、以前の内容を踏まえつつ、若干の手直しを加えたものを備忘録として投稿しておこうと思います。

• Amazon Redshift Useful SQL: VACUUM処理が必要なテーブルを洗い出す ｜ Developers.IO

手直し版が以下SQL。svv_table_infoテーブルからスキーマの情報を参照して、併せて出しています。

• SVV_TABLE_INFO - Amazon Redshift
• STV_TBL_PERM - Amazon Redshift

SELECT
  percentage_result.id,
  percentage_result.schemaname,
  percentage_result.tablename,
  percentage_result.rows,
  percentage_result.sorted_rows,
  percentage_result.sort_percentage
FROM
(SELECT
  tbl_perm_info.id,
  pg_catalog.svv_table_info.schema AS schemaname,
  tbl_perm_info.tablename,
  tbl_perm_info.rows,
  tbl_perm_info.sorted_rows,
  cast(
      cast(tbl_perm_info.sorted_rows as double precision) / cast(tbl_perm_info.rows as double precision)
      as decimal(10,3)
    ) as sort_percentage
FROM
  (SELECT
    id,
    TRIM(name) AS tablename,
    SUM(rows) AS rows,
    SUM(sorted_rows) AS sorted_rows
  FROM
    pg_catalog.stv_tbl_perm
  WHERE
    rows != 0 AND sorted_rows != 0
  GROUP BY id, name) tbl_perm_info
  INNER JOIN pg_catalog.svv_table_info ON tbl_perm_info.id = pg_catalog.svv_table_info.table_id
) percentage_result
ORDER BY
  percentage_result.sort_percentage,
  percentage_result.schemaname,
  percentage_result.tablename;

出力結果は以下の様な感じです。

  id   | schemaname  |                   tablename                    |    rows    | sorted_rows | sort_percentage 
--------+-------------+------------------------------------------------+------------+-------------+-----------------
 100001 | public     | table_aaa                                      |  100000000 |     1000000 |           0.010
 100002 | public     | table_bbb                                      | 2000000000 |  1200000000 |           0.600
 :
 :
 199999 | public     | table_xxx                                      | 1234567890 |  1234567890 |           1.000

こちらからは以上です。...と終わってしまおうかなと思いましたが、これだとあんまりにも短いので少しオマケを。上記SQLで割り出されたVACUUM対象のテーブルに対し処理する際、こちらも数が多くなるといちいちVACUUM文/ANALYZE文を手動で書くのは面倒です。

という訳で、上記SQL文を流用する形で以下SQL文を組んでみました。ソート済の割合(sort_percentage)が1.000未満、即ち『ソート済となっていない=VACUUM処理対象である』テーブルに対し、VACUUM文とANALYZE文を生成する様にしています。

SELECT
  'VACUUM ' || percentage_result.schemaname || '.' || percentage_result.tablename || ';' || ' ' ||
  'ANALYZE ' || percentage_result.schemaname || '.' || percentage_result.tablename || ';'
FROM
(SELECT
  tbl_perm_info.id,
  pg_catalog.svv_table_info.schema AS schemaname,
  tbl_perm_info.tablename,
  tbl_perm_info.rows,
  tbl_perm_info.sorted_rows,
  cast(
      cast(tbl_perm_info.sorted_rows as double precision) / cast(tbl_perm_info.rows as double precision)
      as decimal(10,3)
    ) as sort_percentage
FROM
  (SELECT
    id,
    TRIM(name) AS tablename,
    SUM(rows) AS rows,
    SUM(sorted_rows) AS sorted_rows
  FROM
    pg_catalog.stv_tbl_perm
  WHERE
    rows != 0 AND sorted_rows != 0
  GROUP BY id, name) tbl_perm_info
  INNER JOIN pg_catalog.svv_table_info ON tbl_perm_info.id = pg_catalog.svv_table_info.table_id
) percentage_result
WHERE
  sort_percentage < 1.000
ORDER BY
  percentage_result.sort_percentage,
  percentage_result.schemaname,
  percentage_result.tablename;

生成された結果は以下の様になります。至って単純なSQLですが、数が多くなってくるとこの辺りの手間も"塵も積もれば"になってくると思いますので...お使いのRedshiftクラスタでVACUUMを実行されていない場合であれば上記の『テーブル毎の割合算出』で件数や割合の状況を確認し、こちらのSQLで実行SQLを生成、タイミングを見計らって処理をまず一発かました後で(状況に拠っては、テーブル単位でのVACUUMでも相当数時間が掛かる場合もありますので実行の際はタイミング等お気を付けください)日々のバッチ処理等に組込む等の対策を立てて行くと良いのではないでしょうか。

VACUUM public.table_aaa; ANALYZE public.table_aaa;
VACUUM public.table_bbb; ANALYZE public.table_bbb;
VACUUM public.table_ccc; ANALYZE public.table_ccc;

以上、過去投稿エントリへのセルフアンサー的なエントリでした。