我們都知道Greenplum架構中的每個segment都是postgresql的實例，而vacuum是postgresql中經(jīng)常使用的數(shù)據(jù)庫保護的命令，所以Greenplum數(shù)據(jù)庫也使用vacuum來定期清算表，以避免表由于insert，update等操作致使表過度膨脹。
vacuum的作用是回收已刪除元組占據(jù)的存儲空間。由于MVCC機制，delete和update的元組是沒有從表中物理刪掉的，在完成vacuum之前1直存在并占據(jù)空間。vacuum時使用是讀取鎖，它會簡單地刪掉數(shù)據(jù)頁上這些失效的元組，但數(shù)據(jù)頁還是不變，這時候就需要fsm(Free Space Map)來記錄哪些數(shù)據(jù)頁上有由于vacuum而存在的空閑的空間，可以再插入數(shù)據(jù)。vacuum full會使用排它鎖，但它其實不是只簡單地刪掉失效的無組，還會移動數(shù)據(jù)塊來盡量地使用最少的數(shù)據(jù)頁，所以使用vacuum full命令時可以疏忽fsm的影響。但是要盡可能避免使用vacuum full命令來操作數(shù)據(jù)庫。
其實我們也能夠通過vaccum表時的反饋信息來統(tǒng)計表的大小。
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節(jié)點的seg9實例上有27503 pages(數(shù)據(jù)頁)，其中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個數(shù)據(jù)頁完全是空的
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
我們的環(huán)境有40個segment實例，每一個都有27503 pages，而且對Greenplum來講，每一個數(shù)據(jù)頁大小都是32K，因此我們可以計算出表的大小為：
ann_db_safe=>select 27503*40*32*1024::bigint;
?column?
-------------
36048732160
(1 row)
下面我們再使用pg_relation_size函數(shù)來統(tǒng)計1下表的大小，看是不是1致：
ann_db_safe=>select pg_relation_size('test_bigtable') ;
pg_relation_size
------------------
36048732160
(1 row)
結果比較是1致的。
與max_fsm_pages相干的還有1個參數(shù)叫做max_fsm_relations，要求max_fsm_pages值必須大于 16 * max_fsm_relations，而且max_fsm_pages最小必須為100。
max_fsm_relations的含義：設置自由空間將在同享地自由空間映照里跟蹤的最大數(shù)目的關系（表和索引）。 每一個槽位大概要使用510字節(jié)左右。缺省是1000。
max_fsm_pages在Greenplum中的默許值是200000，greenplum中每張數(shù)據(jù)頁的大小是32K，200000*32K約為6.1G，這個值為每一個實例上的數(shù)值。對我的環(huán)境來講，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網(wǎng)，php教程，php學習，php手冊，CMS模版制作
聲明：本站大部分內容是作者原創(chuàng)，少部分收集于互聯(lián)網(wǎng)供大家一起學習，原版權很多不明，如有侵權請聯(lián)系本站，謝謝！
粵ICP備14040726號-1?? 2015-2020 程序員人生 版權所有
