追記型のRBDであるPostgreSQLにはautovacuum（オートバキューム）という便利な機能があり、updateやdeleteなどで不要な領域が一定の割合に達すると、自動で不要な領域を掃除してくれます。 その閾値はautovacuum_vacuum_scale_factorとautovacuum_vacuum_thresholdパラメータで設定されますが、デフォルトでは一律で約20%となっています。
テーブルごとに閾値を設定することも可能ですので、本来であればテーブルサイズを予測して、vacuumのタイミングも設計すべきなのでしょうが、なかなかそこまでは難しいと思います。
いざ運用を始めてみると、ある日突然巨大なテーブルでautovacuumが走り出し、数時間止まらずにあたふたしたDB管理者は多いと思います。私もその一人です。 autovacuumのプロセスをpg_cancel_backendでキャンセルしてもautovacuum_naptimeで設定された間隔(デフォルト1分)でvacuumのチェック処理が走りますので、 その時間になりますと再度autovacuumが実行されてしまい、autovacuumプロセスのキャンセルだけでは止まりません。
処理中のautovacuumを停止する方法は、ドキュメントやネット検索でもそれらしい記述は見当たりませんが、以下の方法で可能です。
該当DBに接続して管理者ユーザ(postgres)で実行して下さい。
- autovacuumのプロセスIDを調べる
SQL> select datid,datname,pid,usename,query_start,state,query from pg_stat_activity ; datname | pid | usename | state | query -----------+-------+----------+--------+------------------------------------------- test_db01 | 9999 | postgres | active | autovacuum: VACUUM ANALYZE public.test_tbl01
- autovacuumのプロセスをキャンセルする
１で調べたpidを引数にしてpg_cancel_backendを実行する。SQL> SELECT pg_cancel_backend(9999);
- テーブルに対してautovacuum除外設定を入れる
１でautovacuum実行中の対象テーブルが分かりますので、そのテーブルに対するautovacuum除外設定を入れます。 サイズの小さいテーブルの場合、vacuum処理が直ぐに終わってしまい、テーブルが変わってしまう場合もありますので注意。SQL> alter table test_tbl01 set (autovacuum_enabled = false,toast.autovacuum_enabled = false);
上記設定が反映されているか確認する方法はpg_classテーブルのreloptionsカラムで確認出来ますので、以下のSQLでチェック出来ます。
SQL> select relname, reloptions from pg_class where reloptions is not null; relname | reloptions ----------------+---------------------------- test_tbl01 | {autovacuum_enabled=false} pg_toast_53756 | {autovacuum_enabled=false} (2 rows)
これで、該当テーブルに対するautovacuum処理を停止することが出来ます。 但しこの場合、設定を解除しない限り該当テーブルに対するautovacuumは実行されませんので、戻し忘れにご注意下さい。 戻す方法は以下のSQLを実行することで可能です。
SQL> alter table test_tbl01 reset (autovacuum_enabled ,toast.autovacuum_enabled);
さらに、処理中のautovacuumを停止して、尚且つ全てのテーブルに対するautovacuumも停止する手順は以下になります。
- autovacuumのプロセスIDを調べる
SQL> select datid,datname,pid,usename,query_start,state,query from pg_stat_activity ; datname | pid | usename | state | query -----------+-------+----------+--------+------------------------------------------- test_db01 | 9999 | postgres | active | autovacuum: VACUUM ANALYZE public.test_tbl01
- postgresql.confの設定を変更する
autovacuum = on を autovacuum = off にする
- postgresql.confをリロードする
pg_ctl -D /path/to/data reload
これで設定上autovacuumは停止されますが、処理中のautovacuumは停止されませんので次の４のコマンドで処理中のautovacuumを停止します。
- autovacuumのプロセスをキャンセルする
SELECT pg_cancel_backend(＜１で調べたプロセスID＞);
この場合はpostgresql.confの設定を戻さない限りautovacuumは実行されませんので、戻し忘れにご注意下さい。
【おすすめ参考書】
売り上げランキング: 34,018
photo by Harsh Agrawal