我们都知道Greenplum架构中的每个segment都是postgresql的实例，而vacuum是postgresql中经常使用的数据库保护的命令，所以Greenplum数据库也使用vacuum来定期清算表，以避免表由于insert，update等操作致使表过度膨胀。
vacuum的作用是回收已删除元组占据的存储空间。由于MVCC机制，delete和update的元组是没有从表中物理删掉的，在完成vacuum之前1直存在并占据空间。vacuum时使用是读取锁，它会简单地删掉数据页上这些失效的元组，但数据页还是不变，这时候就需要fsm(Free Space Map)来记录哪些数据页上有由于vacuum而存在的空闲的空间，可以再插入数据。vacuum full会使用排它锁，但它其实不是只简单地删掉失效的无组，还会移动数据块来尽量地使用最少的数据页，所以使用vacuum full命令时可以疏忽fsm的影响。但是要尽可能避免使用vacuum full命令来操作数据库。
其实我们也能够通过vaccum表时的反馈信息来统计表的大小。
ann_db_safe=> VACUUM VERBOSE test_bigtable ;
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg11 SZB-L0029556:40003 pid=5215)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg10 SZB-L0029556:40002 pid=5213)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg33 SZB-L0029562:40001 pid=3286)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg34 SZB-L0029562:40002 pid=3288)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg38 SZB-L0029563:40002 pid=29732)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg39 SZB-L0029563:40003 pid=29734)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg14 SZB-L0029557:40002 pid=2018)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg15 SZB-L0029557:40003 pid=2020)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg17 SZB-L0029558:40001 pid=505)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg21 SZB-L0029559:40001 pid=2998)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg25 SZB-L0029560:40001 pid=28299)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg27 SZB-L0029560:40003 pid=28303)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg36 SZB-L0029563:40000 pid=29728)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg4 SZB-L0029555:40000 pid=22111)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg6 SZB-L0029555:40002 pid=22115)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg8 SZB-L0029556:40000 pid=5209)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg9 SZB-L0029556:40001 pid=5211)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg16 SZB-L0029558:40000 pid=503)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg20 SZB-L0029559:40000 pid=2996)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg32 SZB-L0029562:40000 pid=3284)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg37 SZB-L0029563:40001 pid=29730)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg1 SZB-L0029554:40001 pid=16022)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg3 SZB-L0029554:40003 pid=16026)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg5 SZB-L0029555:40001 pid=22113)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg7 SZB-L0029555:40003 pid=22117)
INFO: vacuuming "ann_gbs_safe.test_bigtable" (seg13 SZB-L0029557:40001 pid=2015)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg19 SZB-L0029558:40003 pid=509)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg22 SZB-L0029559:40002 pid=3000)
INFO: vacuuming "ann_gbs_safe.test_bigtable" (seg35 SZB-L0029562:40003 pid=3290)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg12 SZB-L0029557:40000 pid=2013)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg18 SZB-L0029558:40002 pid=507)
INFO: vacuuming "ann_gbs_safe.test_bigtable" (seg26 SZB-L0029560:40002 pid=28301)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg28 SZB-L0029561:40000 pid=7609)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg29 SZB-L0029561:40001 pid=7611)
INFO: vacuuming "ann_gbs_safe.test_bigtable" (seg30 SZB-L0029561:40002 pid=7613)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg24 SZB-L0029560:40000 pid=28297)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg31 SZB-L0029561:40003 pid=7615)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg23 SZB-L0029559:40003 pid=3002)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg0 SZB-L0029554:40000 pid=16020)
INFO: vacuuming"ann_gbs_safe.test_bigtable" (seg2 SZB-L0029554:40002 pid=16024)
INFO: "test_bigtable": found 0 removable,24999874 nonremovable row versions in 27503 pages (seg9 SZB-L0029556:40001 pid=5211)
#注释：SZB-L0029556节点的seg9实例上有27503 pages(数据页)，其中0个元组可移除，24999874不可移除。
DETAIL: 0 dead row versions cannot be removed yet. #0个失效元组不能被移除
There were 0 unuseditem pointers. #这里有0个没有被使用的指针
1 pages containuseful free space. #1个pages包括可用的空间
0 pages areentirely empty. #0个数据页完全是空的
CPU 0.27s/0.42u secelapsed 0.69 sec.
INFO: "test_bigtable": found 0 removable,25000052 nonremovable row versions in27503 pages (seg31 SZB-L0029561:40003 pid=7615)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.45u secelapsed 0.75 sec.
INFO: "test_bigtable": found 0 removable,24999973 nonremovable row versions in27503 pages (seg11 SZB-L0029556:40003 pid=5215)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.27s/0.48u secelapsed 0.76 sec.
INFO: "test_bigtable": found 0 removable,25000096 nonremovable row versions in27503 pages (seg8 SZB-L0029556:40000 pid=5209)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.27s/0.48u secelapsed 0.76 sec.
INFO: "test_bigtable": found 0 removable,24999865 nonremovable row versions in27503 pages (seg28 SZB-L0029561:40000 pid=7609)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.29s/0.45u secelapsed 0.76 sec.
INFO: "test_bigtable": found 0 removable,24999944 nonremovable row versions in27503 pages (seg10 SZB-L0029556:40002 pid=5213)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.49u secelapsed 0.77 sec.
INFO: "test_bigtable": found 0 removable,25000116 nonremovable row versions in27503 pages (seg19 SZB-L0029558:40003 pid=509)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.48u secelapsed 0.77 sec.
INFO: "test_bigtable": found 0 removable,25000006 nonremovable row versions in27503 pages (seg18 SZB-L0029558:40002 pid=507)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.27s/0.49u secelapsed 0.77 sec.
INFO: "test_bigtable": found 0 removable,24999973 nonremovable row versions in27503 pages (seg13 SZB-L0029557:40001 pid=2015)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.30s/0.47u secelapsed 0.77 sec.
INFO: "test_bigtable": found 0 removable,25000192 nonremovable row versions in27503 pages (seg29 SZB-L0029561:40001 pid=7611)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.50u secelapsed 0.78 sec.
INFO: "test_bigtable": found 0 removable,24999826 nonremovable row versions in27503 pages (seg7 SZB-L0029555:40003 pid=22117)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.27s/0.50u secelapsed 0.78 sec.
INFO: "test_bigtable": found 0 removable,25000048 nonremovable row versions in27503 pages (seg35 SZB-L0029562:40003 pid=3290)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.50u secelapsed 0.78 sec.
INFO: "test_bigtable": found 0 removable,25000153 nonremovable row versions in27503 pages (seg15 SZB-L0029557:40003 pid=2020)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.29s/0.49u secelapsed 0.79 sec.
INFO: "test_bigtable": found 0 removable,24999772 nonremovable row versions in27503 pages (seg3 SZB-L0029554:40003 pid=16026)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.26s/0.52u secelapsed 0.80 sec.
INFO: "test_bigtable": found 0 removable,25000070 nonremovable row versions in27503 pages (seg6 SZB-L0029555:40002 pid=22115)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.27s/0.52u secelapsed 0.80 sec.
INFO: "test_bigtable": found 0 removable,25000055 nonremovable row versions in27503 pages (seg34 SZB-L0029562:40002 pid=3288)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.31s/0.48u secelapsed 0.80 sec.
INFO: "test_bigtable": found 0 removable,24999978 nonremovable row versions in27503 pages (seg22 SZB-L0029559:40002 pid=3000)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.29s/0.50u secelapsed 0.80 sec.
INFO: "test_bigtable": found 0 removable,24999885 nonremovable row versions in27503 pages (seg1 SZB-L0029554:40001 pid=16022)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.50u secelapsed 0.80 sec.
INFO: "test_bigtable": found 0 removable,25000002 nonremovable row versions in27503 pages (seg37 SZB-L0029563:40001 pid=29730)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.30s/0.50u secelapsed 0.80 sec.
INFO: "test_bigtable": found 0 removable,24999803 nonremovable row versions in27503 pages (seg39 SZB-L0029563:40003 pid=29734)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.52u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,25000061 nonremovable row versions in27503 pages (seg4 SZB-L0029555:40000 pid=22111)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.33s/0.46u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,25000052 nonremovable row versions in27503 pages (seg0 SZB-L0029554:40000 pid=16020)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.29s/0.50u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,25000040 nonremovable row versions in27503 pages (seg36 SZB-L0029563:40000 pid=29728)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.29s/0.51u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,24999868 nonremovable row versions in27503 pages (seg32 SZB-L0029562:40000 pid=3284)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.31s/0.49u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,25000109 nonremovable row versions in27503 pages (seg38 SZB-L0029563:40002 pid=29732)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.30s/0.50u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,25000116 nonremovable row versions in 27503 pages (seg2 SZB-L0029554:40002 pid=16024)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.29s/0.51u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,24999927 nonremovable row versions in 27503 pages (seg30 SZB-L0029561:40002 pid=7613)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.27s/0.53u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,25000163 nonremovable row versions in 27503 pages (seg33 SZB-L0029562:40001 pid=3286)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.31s/0.50u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,24999879 nonremovable row versions in 27503 pages (seg26 SZB-L0029560:40002 pid=28301)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.31s/0.49u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,24999917 nonremovable row versions in 27503 pages (seg14 SZB-L0029557:40002 pid=2018)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.30s/0.50u secelapsed 0.81 sec.
INFO: "test_bigtable": found 0 removable,24999756 nonremovable row versions in 27503 pages (seg5 SZB-L0029555:40001 pid=22113)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.29s/0.52u secelapsed 0.82 sec.
INFO: "test_bigtable": found 0 removable,25000114 nonremovable row versions in 27503 pages (seg16 SZB-L0029558:40000 pid=503)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.29s/0.52u secelapsed 0.82 sec.
INFO: "test_bigtable": found 0 removable,24999870 nonremovable row versions in 27503 pages (seg24 SZB-L0029560:40000 pid=28297)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.33s/0.48u secelapsed 0.82 sec.
INFO: "test_bigtable": found 0 removable,25000091 nonremovable row versions in 27503 pages (seg27 SZB-L0029560:40003 pid=28303)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.33s/0.50u secelapsed 0.83 sec.
INFO: "test_bigtable": found 0 removable,25000089 nonremovable row versions in 27503 pages (seg25 SZB-L0029560:40001 pid=28299)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.33s/0.50u secelapsed 0.84 sec.
INFO: "test_bigtable": found 0 removable,25000076 nonremovable row versions in 27503 pages (seg21 SZB-L0029559:40001 pid=2998)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.32s/0.52u secelapsed 0.84 sec.
INFO: "test_bigtable": found 0 removable,25000083 nonremovable row versions in 27503 pages (seg20 SZB-L0029559:40000 pid=2996)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.57u secelapsed 0.86 sec.
INFO: "test_bigtable": found 0 removable,24999951 nonremovable row versions in 27503 pages (seg12 SZB-L0029557:40000 pid=2013)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.34s/0.51u secelapsed 0.85 sec.
INFO: "test_bigtable": found 0 removable,24999989 nonremovable row versions in 27503 pages (seg17 SZB-L0029558:40001 pid=505)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.28s/0.58u secelapsed 0.87 sec.
INFO: "test_bigtable": found 0 removable,25000166 nonremovable row versions in 27503 pages (seg23 SZB-L0029559:40003 pid=3002)
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unuseditem pointers.
1 pages containuseful free space.
0 pages areentirely empty.
CPU 0.30s/0.55u secelapsed 0.87 sec.
VACUUM
我们的环境有40个segment实例，每一个都有27503 pages，而且对Greenplum来讲，每一个数据页大小都是32K，因此我们可以计算出表的大小为：
ann_db_safe=>select 27503*40*32*1024::bigint;
?column?
-------------
36048732160
(1 row)
下面我们再使用pg_relation_size函数来统计1下表的大小，看是不是1致：
ann_db_safe=>select pg_relation_size('test_bigtable') ;
pg_relation_size
------------------
36048732160
(1 row)
结果比较是1致的。
与max_fsm_pages相干的还有1个参数叫做max_fsm_relations，要求max_fsm_pages值必须大于 16 * max_fsm_relations，而且max_fsm_pages最小必须为100。
max_fsm_relations的含义：设置自由空间将在同享地自由空间映照里跟踪的最大数目的关系（表和索引）。 每一个槽位大概要使用510字节左右。缺省是1000。
max_fsm_pages在Greenplum中的默许值是200000，greenplum中每张数据页的大小是32K，200000*32K约为6.1G，这个值为每一个实例上的数值。对我的环境来讲，Greenplum集群有1共有40个实例。假设在履行vacuum命令时提示fsm不足，那末将说明对表履行delete或update的失效信息大小已超过了6.1*40=244G。
如果提示relation "XXX" contains more than "max_fsm_pages"pages with useful free space，那就需要对表履行vacuum full命令了，但切记1定找个空闲的时间段来履行这个命令。
程序员人生，我编程，我富裕，记住wfuyu网，php教程，php学习，php手册，CMS模版制作
声明：本站大部分内容是作者原创，少部分收集于互联网供大家一起学习，原版权很多不明，如有侵权请联系本站，谢谢！
粤ICP备14040726号-1 2015-2020 程序员人生 版权所有
