MBL開発部 Nです。
最近、Postgresqlを運用していて気になった所を調べてみました。
どんな事かと言いますと、10GB程度あるテーブルのデータを大量に削除した際にVacuumをFullオプション付きで行っていないのにもかかわらず、テーブルサイズが縮小し、OSのディスク空き容量も増えるということが二回あり、通常vacuumではOSに領域の返却は行われないと思っていたので一度目は見間違いかなと思っていました。
ドキュメントを見てみると9系のVacuumからこのように記載されています。
（FULLが指定されていない）通常のVACUUMは、単に領域を回収し、そこを再利用可能な状態に変更します。 この形式のコマンドでは排他的ロックが取得されていないため、テーブルへの通常の読み書き操作と並行して実行することができます。 しかし余った領域はオペレーティングシステムには(ほとんどの場合)返されません。 同じテーブル内で再利用できるように保持されるだけです。
ほとんどの場合返却されませんとの事ですので、返却されること自体は稀だけどもおかしくないようです。ではどのような場合に返却されるのでしょうか。
実験用にテーブルを作成します。DBは有りものを利用したので9.4系です。
|
1 2 3 4 5 6 7 8 9 10 11 |
\d vcm_test Table "public.vcm_test" Column | Type | Modifiers --------+-----------------------------+------------------------------------------------------- id | integer | not null default nextval('vcm_test_id_seq'::regclass) col1 | text | col2 | text | col3 | integer | col4 | timestamp without time zone | Indexes: "vcm_test_pkey" PRIMARY KEY, btree (id) |
手動で行いたいのでautovacuumを停止します。
|
1 |
alter table vcm_test set (autovacuum_enabled = false,toast.autovacuum_enabled = false); |
データを投入し、1億5千万レコードで12GB程度のテーブルになりました。
|
1 2 3 4 5 6 7 8 9 10 11 |
test_db_nishifuji=# select count(*) from vcm_test; count ----------- 150000000 SELECT relname, (relpages / 128) as mbytes FROM pg_class where relname like '%vcm%' ORDER BY mbytes DESC; relname | mbytes -----------------+-------- vcm_test | 12081 vcm_test_pkey | 3213 vcm_test_id_seq | 0 |
大量に消せば回復するのかを試すためにidが偶数のものを削除して半分のレコードにしてみます。
|
1 2 |
delete from vcm_test where mod(id,2) = 0; DELETE 75000000 |
vacuum analyze して見てみます。
|
1 2 3 4 5 6 |
SELECT relname, (relpages / 128) as mbytes FROM pg_class where relname like '%vcm%' ORDER BY mbytes DESC; relname | mbytes -----------------+-------- vcm_test | 12081 vcm_test_pkey | 3213 vcm_test_id_seq | 0 |
テーブルサイズもindexも変わりませんでした。
さらに半分にしてみるために4で割って余り1になるidを削除しましたが変わらず。
|
1 2 |
delete from vcm_test where mod(id,4) = 1; DELETE 37500000 |
レコードが領域全体に散らばっている状態だと回収されないようです。
ドキュメントには
例外として、テーブルの末尾に完全に空のページが存在し、かつそのテーブルの排他ロックが容易に獲得できるような特殊な場合には、その領域を返却します。
のように記載があるので、現状残っているレコードの後半(idが大きいもの)を半分削除してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select count(*) from vcm_test; count ---------- 37500000 delete from vcm_test where id > 583306573; DELETE 18750000 select count (*) from vcm_test; count ---------- 18750000 |
vacuum analyzeを行うと
|
1 2 3 4 5 6 |
SELECT relname, (relpages / 128) as mbytes FROM pg_class where relname like '%vcm%' ORDER BY mbytes DESC; relname | mbytes -----------------+-------- vcm_test | 6040 vcm_test_pkey | 3213 vcm_test_id_seq | 0 |
テーブルサイズが縮小されました。しかしレコード数の割にサイズが大きいようです。
再度実験をします。一度全部レコードを削除し、vacuum fullをかけた状態からデータを投入します。
|
1 2 3 4 |
select count (*) from vcm_test; count ---------- 128000000 |
|
1 2 3 4 5 6 |
SELECT relname, (relpages / 128) as mbytes FROM pg_class where relname like '%vcm%' ORDER BY mbytes DESC; relname | mbytes -----------------+-------- vcm_test | 10309 vcm_test_pkey | 2741 vcm_test_id_seq | 0 |
1億2800万レコードで大体10GBになりました。
半分弱(6000万レコード)ほど消してみます。
|
1 2 3 4 5 6 7 8 |
delete from vcm_test where id > 726306574 DELETE 60000000 select count (*) from vcm_test; count ---------- 68000000 |
vacuum後サイズを確認。
|
1 2 3 4 5 6 7 8 |
vacuum ANALYZE vcm_test ; VACUUM SELECT relname, (relpages / 128) as mbytes FROM pg_class where relname like '%vcm%' ORDER BY mbytes DESC; relname | mbytes -----------------+-------- vcm_test | 5476 vcm_test_pkey | 2741 vcm_test_id_seq | 0 |
サイズが縮小しました。データ後半に空きがあればOSに領域を返却することがあるようです。データがまばらに有る領域が無いので先の実験よりレコード数と領域サイズの比率が良いようです。
その他のパターンも実験した結果下記のような傾向がありそうです。
Vacuum Fullが出来ない(停止出来ない、空き容量が殆ど無い等)場合にチャレンジしてみても良いかもしれません。
再現から始めたので10GB程度のテーブルで実験しましたが、後方領域が空いていれば100MB程度のテーブルでも減るようです。
