vacuum与max_fsm_pages关系的分析

vacuum是postgreSQL和greenplum数据库维护时常用的命令，定期vacuum数据库中的表是防止表过度‘膨胀’的有效方法。

本文就vacuum与max_fsm_pages的关系进行分析，来说明为什么有时vacuum时会出现relation "XXX" contains more than "max_fsm_pages" pages with useful free space这种错误。

1，vacuum的作用是回收已删除元组占据的存储空间。因为MVCC机制，delete和update的元组是没有从表中物理删掉的，在完成vacuum之前一直存在并占据空间。vacuum时使用是读取锁，它会简单地删掉数据页上这些失效的元组，但数据页还是不变，这时就需要fsm(Free Space Map)来记录哪些数据页上有因为vacuum而存在的空闲的空间，可以再插入数据。

vacuum full会使用排它锁，但它并不是只简单地删掉失效的无组，还会移动数据块来尽可能地使用最少的数据页，所以使用vacuum full命令时可以忽略fsm的影响。

greenplum中除了heap表还有appendonly表。

greenplum 中对分布键重新设置的功能：

ALTER TABLE [ONLY] name SET 
     DISTRIBUTED BY (column, [ ... ] ) 
   | DISTRIBUTED RANDOMLY 
   | WITH (REORGANIZE=true|false) 

有一个隐含的作用就是vacuum full 该表。

2，实验用表是官方文档中的faa_otp_load，并在此基础上新建一表faa来进行分析。

2.1
-- 查看表的大小，其实表的大小可以直接根据下面vacuum显示的信息计算出。
- -(5575+5278)*32*1024=355631104，5575和5278是该表在两个实例上分别的数据页，greenplum中每张数据页的大小是32K。
tutorial=# select pg_relation_size('faa');               
 pg_relation_size 
------------------
        355631104
(1 row)


--查看vacuum时的详细信息。
tutorial=# vacuum verbose faa;
INFO:  vacuuming "faa.faa"  (seg0 sdw1:40000 pid=2785)
INFO:  vacuuming "faa.faa"  (seg1 sdw2:40000 pid=2770)
INFO:  "faa": found 0 removable, 526707 nonremovable row versions in 5575 pages  (seg0 sdw1:40000 pid=2785)
--这句的意思是现在在主机sdw1上的seg0实例上的5575张数据页上有0个元组可移除，526707不能移除。
DETAIL:  0 dead row versions cannot be removed yet.
--0个失效元组不能被移除。
There were 0 unused item pointers.
--0个没有被使用的指针。
59 pages contain useful free space.
- -59个页面有可用的空间。
0 pages are entirely empty.
- -0张数据页完全是空的。
CPU 0.28s/0.00u sec elapsed 1.66 sec.
INFO:  vacuuming "pg_toast.pg_toast_860902"  (seg0 sdw1:40000 pid=2785)
INFO:  index "pg_toast_860902_index" now contains 0 row versions in 1 pages  (seg0 sdw1:40000 pid=2785)
-- 本表中没有出现大字段，为了简洁，关于大字段toast表和索引的记录在后面的文中不再列出。
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_860902": found 0 removable, 0 nonremovable row versions in 0 pages  (seg0 sdw1:40000 pid=2785)
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "faa": found 0 removable, 497845 nonremovable row versions in 5278 pages  (seg1 sdw2:40000 pid=2770)
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
64 pages contain useful free space.
0 pages are entirely empty.
CPU 0.15s/0.06u sec elapsed 1.72 sec.
INFO:  vacuuming "pg_toast.pg_toast_860902"  (seg1 sdw2:40000 pid=2770)
INFO:  index "pg_toast_860902_index" now contains 0 row versions in 1 pages  (seg1 sdw2:40000 pid=2770)
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.09 sec.
INFO:  "pg_toast_860902": found 0 removable, 0 nonremovable row versions in 0 pages  (seg1 sdw2:40000 pid=2770)
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.09 sec.
VACUUM


--查看当前 max_fsm_pages和max_fsm_relations的值，并设置max_fsm_pages为5000以低于之前看到的在每个实例上的数据页。
--max_fsm_pages必须大于 16 * max_fsm_relations，且max_fsm_pages最小必须为100，否则数据库无法启动，这里设为200。
--设置完成后重启数据库以使其生效。
[gpadmin@mdw ~]$ gpconfig -s max_fsm_pages
Values on all segments are consistent
GUC          : max_fsm_pages
Master  value: 200000
Segment value: 200000
[gpadmin@mdw ~]$ gpconfig -s max_fsm_relations
Values on all segments are consistent
GUC          : max_fsm_relations
Master  value: 1000
Segment value: 1000
[gpadmin@mdw ~]$ gpconfig -c max_fsm_relations -v 200
20150701:20:09:46:003601 gpconfig:mdw:gpadmin-[INFO]:-completed successfully
[gpadmin@mdw ~]$ gpconfig -c max_fsm_pages -v 5000        
20150701:20:10:04:003681 gpconfig:mdw:gpadmin-[INFO]:-completed successfully
--这里重启数据库。
[gpadmin@mdw ~]$ gpconfig -s max_fsm_pages
Values on all segments are consistent
GUC          : max_fsm_pages
Master  value: 5000
Segment value: 5000
[gpadmin@mdw ~]$ gpconfig -s max_fsm_relations
Values on all segments are consistent
GUC          : max_fsm_relations
Master  value: 200
Segment value: 200


2.2 
--首先看一下将1张表，全删掉时将其vacuum的情况。
tutorial=# delete from faa;       
DELETE 1024552
tutorial=# select pg_relation_size('faa');               
 pg_relation_size 
------------------
        355631104
(1 row)


----------------------
tutorial=# vacuum verbose faa;
INFO:  vacuuming "faa.faa"  (seg0 sdw1:40000 pid=3842)
INFO:  vacuuming "faa.faa"  (seg1 sdw2:40000 pid=4253)
INFO:  "faa": removed 497845 row versions in 5278 pages  (seg1 sdw2:40000 pid=4253)
--移除了 5278 张数据页中的 497845个元组。
INFO:  "faa": found 497845 removable, 0 nonremovable row versions in 5278 pages  (seg1 sdw2:40000 pid=4253)
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
5278 pages contain useful free space.
0 pages are entirely empty.
CPU 0.11s/0.06u sec elapsed 3.83 sec.
WARNING:  relation "faa.faa" contains more than "max_fsm_pages" pages with useful free space  (seg1 sdw2:40000 pid=4253)
--报错提示max_fsm_pages的值过低。
HINT:  Consider compacting this relation or increasing the configuration parameter "max_fsm_pages".
INFO:  "faa": truncated 5278 to 0 pages  (seg1 sdw2:40000 pid=4253)
--这里直接truncated 了5278张数据页。
DETAIL:  CPU 0.07s/0.00u sec elapsed 0.26 sec.
INFO:  "faa": removed 526707 row versions in 5576 pages  (seg0 sdw1:40000 pid=3842)
INFO:  "faa": found 526707 removable, 0 nonremovable row versions in 5576 pages  (seg0 sdw1:40000 pid=3842)
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
5576 pages contain useful free space.
0 pages are entirely empty.
CPU 0.23s/0.07u sec elapsed 7.19 sec.
WARNING:  relation "faa.faa" contains more than "max_fsm_pages" pages with useful free space  (seg0 sdw1:40000 pid=3842)
HINT:  Consider compacting this relation or increasing the configuration parameter "max_fsm_pages".
INFO:  "faa": truncated 5576 to 0 pages  (seg0 sdw1:40000 pid=3842)
DETAIL:  CPU 0.08s/0.00u sec elapsed 0.37 sec.
VACUUM
tutorial=# select pg_relation_size('faa');
 pg_relation_size 
------------------
                0
(1 row)
-- 由上可以看出当表中没有数据时，vacuum不仅移除了失效的所有元组还直接truncated整张表。
--由此可以看出当需要清空整张表时，直接用truncate以直接释放物理空间。


2.2
--当表中有有效的数据时，vacuum时的情况。
--这里先将表中数据删除掉，再加入数据。
tutorial=# insert into faa select * from faa_otp_load ;
INSERT 0 1024552
tutorial=# delete from faa;
DELETE 1024552
tutorial=# insert into faa select * from faa_otp_load;
INSERT 0 1024552
tutorial=# select pg_relation_size('faa');
 pg_relation_size 
------------------
        711262208
(1 row)


tutorial=# vacuum verbose faa;
INFO:  vacuuming "faa.faa"  (seg0 sdw1:40000 pid=4570)
INFO:  vacuuming "faa.faa"  (seg1 sdw2:40000 pid=4726)
INFO:  "faa": removed 497845 row versions in 5278 pages  (seg1 sdw2:40000 pid=4726)
INFO:  "faa": found 497845 removable, 497845 nonremovable row versions in 10555 pages  (seg1 sdw2:40000 pid=4726)
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
5331 pages contain useful free space.
0 pages are entirely empty.
CPU 1.50s/0.45u sec elapsed 99.03 sec.
WARNING:  relation "faa.faa" contains more than "max_fsm_pages" pages with useful free space  (seg1 sdw2:40000 pid=4726)
HINT:  Consider compacting this relation or increasing the configuration parameter "max_fsm_pages".
INFO:  "faa": removed 526707 row versions in 5576 pages  (seg0 sdw1:40000 pid=4570)
INFO:  "faa": found 526707 removable, 526707 nonremovable row versions in 11151 pages  (seg0 sdw1:40000 pid=4570)
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
5627 pages contain useful free space.
0 pages are entirely empty.
CPU 1.55s/0.50u sec elapsed 114.73 sec.
WARNING:  relation "faa.faa" contains more than "max_fsm_pages" pages with useful free space  (seg0 sdw1:40000 pid=4570)
HINT:  Consider compacting this relation or increasing the configuration parameter "max_fsm_pages".
VACUUM
- -由上可以看出当max_fsm_pages不足时，表中的无效元组还是删除掉了的。




2.3
--此时fsm的作用可通过下面的分析得出。
--现在每个实例上的fsm记录了5000张数据页的情况，则现在向表中插入数据，会先插入fsm上有记录的空闲空间，然后堆在表的后面。
--插入数据后，如果继续对表分析，则fsm会记录之前漏过的有空闲空间的数据页，因其数量已减少，此时可能不会再报错了。


tutorial=# insert into faa select * from faa_otp_load;
INSERT 0 1024552
tutorial=# select pg_relation_size('faa');
 pg_relation_size 
------------------
        902168576
--与之前的相法一致，所用空间并不是355631104*3=1066893312，原表中总数据页为10853，这次插入只额外增加了5027张数据页。


tutorial=# vacuum verbose faa;
INFO:  vacuuming "faa.faa"  (seg0 sdw1:40000 pid=6090)
INFO:  vacuuming "faa.faa"  (seg1 sdw2:40000 pid=6252)
INFO:  "faa": found 0 removable, 995690 nonremovable row versions in 13319 pages  (seg1 sdw2:40000 pid=6252)
DETAIL:  0 dead row versions cannot be removed yet.
There were 263643 unused item pointers.
2854 pages contain useful free space.
0 pages are entirely empty.
CPU 0.59s/0.07u sec elapsed 38.39 sec.
INFO:  "faa": found 0 removable, 1053414 nonremovable row versions in 14213 pages  (seg0 sdw1:40000 pid=6090)
DETAIL:  0 dead row versions cannot be removed yet.
There were 292112 unused item pointers.
3148 pages contain useful free space.
0 pages are entirely empty.
CPU 0.47s/0.10u sec elapsed 41.51 sec.
VACUUM
--果然，继续vacuum并不会报错，因为表中需要记录有空间的数据页的张数已少于5000.


3. max_fsm_pages在postgreSQL中的默认值是20000，postgreSQL中每张数据页的大小是8K，20000*8K约为156M，
这意味着postgreSQL中某张表的delete或update信息超过156M时，vacuum时就会提示fsm不足。
 max_fsm_pages在greenplum中的默认值是200000，greenplum中每张数据页的大小是32K，200000*32K约为6.1G，而且这还是每个实例上的数值。
如果某个greenplum集群有40个实例，但在vacuum时还提示fsm不足，则说明表中的delete或update的失效信息已经超过了244G。
如果提示relation "XXX" contains more than "max_fsm_pages" pages with useful free space，也不用太担心，找个业务不太繁忙的时间vacuum full该表就行了。