【Redshift】ANALYZE、VACUUMメモ

2019/06/27 誤りを修正。

各実施で起こること

VACUUM

  • ''DELETE'' や ''UPDATE'' で発生した、削除フラグ付きの領域の掃除。(SELECTで検索するとき、この領域も一応見に行って検索ミスする)
    • 2018年11月ぐらいのアップデートで、削除フラグ付き領域の削除が、ほんとすぐにバックグラウンドで自動実行されるようになった。この機能は実質無視していい。
  • テーブルの ''DISTSTYLE'' の設定に従ったデータのスライス間再分散
    • ''DISTSTYLE EVEN'' だとけっこー軽い
  • テーブルに ''SORTKEY'' が設定されている場合、未ソート領域のソート実施、および未ソート領域のデータがソート済み領域に割入って挿入される場合はソート済み領域へのマージ。
    • めちゃくちゃ重い。列が多いテーブルで、下手すると数十時間かかる。
  • 前回VACUUM実施時からの変更行数が全体の5%以下のとき、内部でVACUUMがスキップされる。
  • テーブルを占有ロックする。参照ロック先行でかかってたら待つし、その後のトランザクションをみんな待たせる。CPU使用リソースとディスクリソースを結構使う。
  • 1つのクラスタで、同時に実行できる明示的なVACUUMは1つのみ。

ANALYZE

  • テーブルの統計情報(このディスクにこの範囲の値のデータがこんだけあってなどの情報)の刷新
    • SELECTでサンプリングしてガリガリ統計とるらしい(AWS公式ドキュメントより)
  • 前回ANALYZE実施時から、パラメータ analyze_threshold_percent の割合以上の行の変更以下の場合、内部でAnalyzeがスキップされる。
  • 裏でSELECTするクエリでテーブルを参照ロックする。CPUリソースを結構使う。(30%ぐらい。全データ300GBぐらいのテーブルだと 十数分かかる)
    • 実行するのは、userid=1 の特殊なユーザー

確認/設定変更SQL

ANALYZE履歴取得

-- ANALYZEの実行履歴を確認するSQL。要スーパーユーザー
-- すでにDROPしたテーブルは名前空欄になるかも
-- 実テーブルあるけどANALYZEログないものはそもそもレコードが出ない
SELECT 
  stl_analyze.userid,            -- ANALYZE実行起因となったクエリを実行したユーザーID
  stl_analyze.xid,               -- ANALYZE実行起因となったクエリのトランザクションID
  stl_analyze.database,          -- 対象テーブルのあるdatabase
  stl_analyze.table_id,          -- 対象テーブルID
  tbl_perm_info.tablename,       -- 対象テーブル名。ログにtable_id残ってるけどシステムテーブルに名前ないものはNULL
  stl_analyze.status,            -- ANALYZE実行したか否か。Full = 全量、Skipped = コマンド実行したけど内部スキップ
  stl_analyze.rows,              -- 実行前の全体のテーブル統計サイズ。削除してcollectしていない領域も含まれる
  stl_analyze.modified_rows,     -- 実行前の最終ANALYZE実施以来の変更行数
  stl_analyze.threshold_percent, -- 実行時の、実施 or Skippedの判断となる変更行割合閾値。パラメータanalyze_threshold_percentの値。
  stl_analyze.is_auto,           -- 自動Analyzeか否か。SELECTしたとき、たまに走る
  convert_timezone('Asia/Tokyo', stl_analyze.starttime),         -- Analyze実行日時
  convert_timezone('Asia/Tokyo', stl_analyze.endtime),           -- Analyze終了日時
  convert_timezone('Asia/Tokyo', stl_analyze.prevtime)           -- Analyze前回実行日時
FROM
  -- システムテーブル
  pg_catalog.stl_analyze
  -- テーブルIDとテーブル名の組を取るサブクエリ。取るとこ間違ってる気がするけど運用できるしGoogle先生もそう言ってる
  LEFT JOIN (SELECT id, TRIM(name) AS tablename FROM pg_catalog.stv_tbl_perm GROUP BY id, name) tbl_perm_info ON (stl_analyze.table_id = tbl_perm_info.id)
--WHERE
--  convert_timezone('Asia/Tokyo', stl_analyze.starttime) >= '2018-10-10'
ORDER BY DATABASE, table_id, starttime;

ANALYZE実行閾値の取得と変更

-- 前回ANALYZE実施以降、全体の行数に対する変更行数がこの%以下であればANALYZEを内部でSkipする
SHOW analyze_threshold_percent;

-- このセッション中だけ一時的に書き換える。
-- 0だと更新がなくても絶対ANALYZE。auto ANALYZEでも実施されるのでバッチがむしろめっちゃ遅くなる。
SET analyze_threshold_percent to 0.01;

-- ユーザーの設定を恒久的に書き換える
ALTER USER username SET analyze_threshold_percent = 0.01;
COMMIT;

-- クラスタ全体の設定変更は、AWS WebConsoleからパラメータを設定する

ANALYZE / VACUUM 実施SQL

VACUUMは、各テーブルの所有ユーザーで実施必須。

ANALYZE実施

個別テーブル

-- このセッションのみAnalyze実施行数の閾値を 0 に
-- 元の値も確認しておく
SHOW analyze_threshold_percent;
SET analyze_threshold_percent TO 0;
SHOW analyze_threshold_percent;

-- Analyze実施
ANAYLZE <schema.tablename>;

-- 任意:Analyze実施行数の閾値を戻す。(セッションを切れば破棄される)
SHOW analyze_threshold_percent;
RESET analyze_threshold_percent;
SHOW analyze_threshold_percent;

入っているユーザーが権限を持つ全テーブル

-- このセッションのみAnalyze実施行数の閾値を 0 に
-- 元の値も確認しておく
SHOW analyze_threshold_percent;
SET analyze_threshold_percent TO 0;
SHOW analyze_threshold_percent;

-- ANALYZE実施
ANAYLZE;

VACUUM実施

個別テーブル

-- トランザクションを切っておかないとVACUUMが失敗する
END;

-- VACUUM実施
-- ※ インターリーブソートキーを設定しているテーブルは FULL → REINDEX にしないとだめ。
VACUUM FULL <schema.tablename>;

-- トランザクションをいちおう始めておく(後続のSQLをrollback可能にする
BEGIN;

入っているユーザーが権限を持つ全テーブル

-- トランザクションを切っておかないとVACUUMが失敗する
END;

-- VACUUM実施
-- ※ インターリーブソートキーを設定しているテーブルは FULL → REINDEX にしないとだめ。
VACUUM FULL;

-- トランザクションをいちおう始めておく(後続のSQLをrollback可能にする
BEGIN;