#背景
PostgreSQLを使用しているのですが、本番環境でパフォーマンス劣化が発生しました。
調査の結果、autovacuumは動いているのですが、不要行の削除が行われず空振りしている状態でした。
#環境
DB:Amazon Aurora for PostgreSQL 10.16
#vacuum・analyzeの状況確認
##SQLで現状の情報を取得する
こちらの記事で利用したSQLで各テーブルのvacuum状況を確認します。
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'hogeschema'
ORDER BY relname
実行した結果、last_autovacuumカラムに直近の日付が表示されているにも拘らず、
n_dead_tupカラムの不要行数が異常に多い状態でした。
##vacuum実行時の詳細を取得
vacuumにVERBOSEパラメータを指定してVACUUM対する詳細を取得します。
hogedb=> VACUUM (VERBOSE, ANALYZE) hogetable;
INFO: vacuuming "hogeschema.hogetable"
INFO: index "hogetable_pkc" now contains 8476 row versions in 35 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "hogetable": found 0 removable, 99226 nonremovable row versions in 1711 out of 1711 pages
DETAIL: 99198 dead row versions cannot be removed yet, oldest xmin: 134763458
There were 86 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
INFO: analyzing "hogeschema.hogetable"
INFO: "hogetable": scanned 1711 of 1711 pages, containing 28 live rows and 99198 dead rows; 28 rows in sample, 28 estimated total rows
VACUUM
上記より、不要行は99198行あるが一番古いトランザクションID「134763458」があるため削除出来ないとのこと。
#古いトランザクションIDについて
色々と調べていると、こちらのページより、
古いトランザクションが有効な状態で残っているとVACUUMを実行しても不要行を削除しなくなるようです。
下記SQLを実行してvacuumをブロックしているデータベースセッションを確認しました。
結果、vacuumの詳細に記載されていたものと同じトランザクションIDがactive状態で存在していることが確認出来ました。
SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
vacuumをブロックしているデータベースセッションを終了させてみました。
※ユーザー操作で本当に利用中かもしれないため、実行は稼働時間外に行いました。
pg_terminate_backend()の引数には上記SQLで取得出来たプロセスIDを指定します。
select pg_terminate_backend(23398);
#再度、vacuumを実行してみると
上記、手順でデータベースセッションを終了させた後に再度vacuumを実行すると無事に不要行が削除されていました。
#参考文献