PostgreSQL碎片空间回收---vacuum

cxg518

　　

一般来讲，PG表中的数据在删除后会被标记为dead，除非进行自动的autovacuum或者是手动的vacuum，否则数据块不会被回收，直观的看来就是表的体积大，操作系统里表的文件臃肿不减。

下面我们来做一个大表清空数据后的信息统计和碎片回收实验：

一张2 千万数据的表：
music=# select count(*) from test;
  count  
---------
22000005
(1 行记录)

容量大概有700MB：
music=# select pg_size_pretty(pg_relation_size('test'));
-[ RECORD 1 ]--+-------
pg_size_pretty | 691 MB

清除表中所有数据：
music=# delete from test;
DELETE 22000005




再确认一下表的体积：
music=# select pg_size_pretty(pg_relation_size('test'));
-[ RECORD 1 ]--+-------
pg_size_pretty | 691 MB

纹丝不动...


查看表的状态：
music=# select * from pg_stat_user_tables where relname = 'test';
-[ RECORD 1 ]-------+------------------------------
relid               | 16391
schemaname          | public
relname             | test
seq_scan            | 11
seq_tup_read        | 52000055
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 56929554
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 22000005
n_dead_tup          | 34929554
n_mod_since_analyze | 20000000
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    | 2015-07-30 13:21:36.385515+08
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 1


从输出结果上看，此表仍然占用了大部分 ”空闲“ 数据块。

n_live_tup的数量是当前表的数据量。
n_dead_tup的数据量是未回收的空间。
该表之前做过其他的测试，清理过数据，因此dead tuple比2千万多。


查看磁盘空间：
[iyunv@dbserver music]# df -h
文件系统              容量  已用 可用 已用% 挂载点
/dev/mapper/VolGroup00-LogVol00
                       26G   20G  3.9G  84% /
/dev/sda1              99M   21M   73M  23% /boot
tmpfs                 1.3G  248M  1.1G  19% /dev/shm
none                  1.3G  104K  1.3G   1% /var/lib/xenstored



我们进行一下手工的清理：

music=# vacuum test;
VACUUM


可以看到有相应的后台进程启动：

[iyunv@dbserver music]# ps -ef|grep VACUUM
postgres  6649  3540 13 13:55 ?        00:00:05 postgres: postgres music [local] VACUUM                 
root      6662  5515  0 13:55 pts/1    00:00:00 grep VACUUM
[iyunv@dbserver music]#


再次查看一下表的状态，发现n_tup_del变成了刚才表的数量，活的和死的tuple都已成为0，下面的信息可以看到分析、自动清理和手动清理的时间。（系统为自己测试环境，时间不是北京时间，但是可以判断先后顺序）

music=# select * from pg_stat_user_tables where relname = 'test';
-[ RECORD 1 ]-------+------------------------------
relid               | 16391
schemaname          | public
relname             | test
seq_scan            | 12
seq_tup_read        | 74000060
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 56929554
n_tup_upd           | 0
n_tup_del           | 22000005
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         | 2015-07-30 13:56:16.714987+08
last_autovacuum     | 2015-07-30 13:47:05.532724+08
last_analyze        |
last_autoanalyze    | 2015-07-30 13:56:36.112974+08
vacuum_count        | 1
autovacuum_count    | 1
analyze_count       | 0
autoanalyze_count   | 3


n_dead_tup和n_live_tup变成了0，空间已被回收。








查看一下表的体积：
music=# select pg_size_pretty(pg_relation_size('test'));
-[ RECORD 1 ]--+--------
pg_size_pretty | 0 bytes



查看磁盘空间：
[iyunv@dbserver music]# df -h
文件系统              容量  已用 可用 已用% 挂载点
/dev/mapper/VolGroup00-LogVol00
                       26G   20G  4.8G  81% /
/dev/sda1              99M   21M   73M  23% /boot
tmpfs                 1.3G  248M  1.1G  19% /dev/shm
none                  1.3G  104K  1.3G   1% /var/lib/xenstored　　




可以看到系统空间已下降。

OK~