背景
PostgreSQLを使用しているのですが、本番環境でパフォーマンス劣化が顕著に現れてきました。
調査の結果、autovacuumとautoanalyzeが動いていなかったので、vacuum周りを調整しました。
環境
DB:Amazon Aurora for PostgreSQL 10.16
vacuum・analyzeの状況確認
SQLで現状の情報を取得する
PostgreSQLの公式ドキュメントより、vacuum・analyze実行有無はpg_stat_all_tablesビューで最終確認時刻が確認出来ます。
こちらのページに良いSQLが記載されていましたので、参考にさせて頂きました。
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public' -- schemaname or relnameで絞りこむと見やすい
ORDER BY relname
最後に実行された時刻を確認する
SQLから得られた情報により、last_vacuum, last_autovacuum, last_analyze, last_autoanalyzeが全てnullとなっていました。
また、n_dead_tupカラムには各テーブル別に0以上の数字が入っている状態です。
つまり、vacuumもanalyzeも実行されておらず、DBの運用上、不必要になったメモリ領域がたまり続けていることが理解出来ました。
設定変更
Aurora PostgreSQLの設定値を変更
AWSの公式ドキュメントを参考に設定値を変更します。
クラスターパラメータ
| 設定項目 | 設定値 |
|---|---|
| track_counts | 1 |
| autovacuum | 1 |
| autovacuum_analyze_scale_factor | 0.05 |
| autovacuum_analyze_threshold | 50 |
| autovacuum_freeze_max_age | 200000000 |
| autovacuum_max_workers | 3 |
| autovacuum_multixact_freeze_max_age | 400000000 |
| autovacuum_naptime | 30 |
| autovacuum_vacuum_cost_delay | 20 |
| autovacuum_vacuum_cost_limit | -1 |
| autovacuum_vacuum_scale_factor | 0.1 |
| autovacuum_vacuum_threshold | 50 |
DBパラメータ
| 設定項目 | 設定値 |
|---|---|
| track_counts | 1 |
DB再起動
「autovacuum_freeze_max_age」など、静的パラメータも含みますので、
設定変更したらDBを再起動します。
pg_stat_all_tablesビューを確認
pg_stat_all_tablesビューを確認します。
結果、
last_autovacuum、last_autoanalyzeにnullだけでなく、日付が各々に入ってきて、
vacuum、analyzeの自動実行が行われるようになりました。
ただし、nullでなくなった箇所を見ていくと、データ件数が少ないテーブルばかりに実行されていました。
これではあまり意味はないので、もう一歩踏み込んで調整を行います。
（AWSのドキュメントに記載された設定値そのままだったので当然ですね。。）
自動実行の仕組みと設定項目の見直し
まず、autovacuumとautoanalyzeの実行方法について見直します。
自動実行の仕組み
autovacuumの実行判断
PostgreSQLの公式ドキュメントより
テーブルのrelfrozenxid値がautovacuum_freeze_max_ageトランザクション年齢よりも古い場合、そのテーブルは常にバキュームされます。
（これはfreeze max ageがストレージパラメータにより変更されたテーブルに対しても適用されます。）
さもなければ、直前のVACUUMの後に不要となったタプル数が「バキューム閾値」を超えると、テーブルはバキュームされます。
このバキューム閾値は以下のように定義されます。
バキューム閾値 = バキューム基礎閾値 + バキューム規模係数 * タプル数
とのこと
上記式に設定項目を当てはめると
バキューム閾値 = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltuples
となります。
autoanalyzeの実行判断
PostgreSQLの公式ドキュメントより
autoanalyzeも下記の式の結果により実行を判断します。
解析閾値 = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltuples
とのこと
pg_class.reltuplesとは
DB内に存在する、pg_classというカタログのカラムでreltuplesというものです。
各テーブル内の行数を表しています。
詳しくは以下SQLで確認出来ます。
select relname, reltuples from pg_class;
算出式に出てきた設定項目を整理
以下、PostgreSQLの公式ドキュメントより
autovacuum_analyze_threshold
どのテーブルに対してもANALYZEを起動するのに必要な、挿入、更新、もしくは削除されたタプルの最小数を指定します。
デフォルトは50タプルです。
autovacuum_analyze_scale_factor
ANALYZEを起動するか否かを決定するときに、autovacuum_analyze_thresholdに足し算するテーブル容量の割合を指定します。
デフォルトは0.1（テーブルサイズの10%）です。
autovacuum_vacuum_scale_factor
VACUUMを起動するか否かを決定するときに、autovacuum_vacuum_thresholdに足し算するテーブル容量の割合を指定します。
デフォルトは0.2（テーブルサイズの20%）です。
autovacuum_vacuum_threshold
どのテーブルに対してもVACUUMを起動するために必要な、更新もしくは削除されたタプルの最小数を指定します。
デフォルトは50タプルです。
結論
算出されたバキューム閾値・解析閾値を超えるように設定することで自動実行がコントロール出来そうです。
vacuumは、
・autovacuum_vacuum_threshold
・autovacuum_vacuum_scale_factor
analyzeは、
・autovacuum_analyze_threshold
・autovacuum_analyze_scale_factor
上記を調整して全テーブルのバキューム閾値・解析閾値を超えるように設定します。
筆者はエクセルに式を書いてthresholdとscale_factorを調整し、全テーブル分のバキューム閾値・解析閾値を超えるように設定値を洗い出しました。
結果、全テーブルのautovacuum、autoanalyzeを実行することができました。