Re: fsm and vacuum - Mailing list pgadmin-support

<div class="WordSection1"><p class="MsoPlainText">Guillaume,<p class="MsoPlainText"> <p class="MsoPlainText">Given this
syscatbloat, what would you recommend doing? <p class="MsoPlainText"> <table border="0" cellpadding="0" cellspacing="0"
class="MsoNormalTable"style="width:624.0pt;border-collapse:collapse" width="832"><tr style="height:15.0pt"><td nowrap
style="width:79.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="105"><p class="MsoNormal"><span
style="color:black">schemaname</span><spanstyle="color:black"></span></td><td nowrap style="width:124.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="165"><p class="MsoNormal"><span
style="color:black">tablename</span><spanstyle="color:black"></span></td><td nowrap style="width:59.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="79"><p class="MsoNormal"><span
style="color:black">reltuples</span><spanstyle="color:black"></span></td><td nowrap style="width:57.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="76"><p class="MsoNormal"><span
style="color:black">relpages</span><spanstyle="color:black"></span></td><td nowrap style="width:.5in;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="48"><p class="MsoNormal"><span style="color:black">otta</span><span
style="color:black"></span></td><tdnowrap style="width:45.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="60"><p class="MsoNormal"><span style="color:black">tbloat</span><span
style="color:black"></span></td><tdnowrap style="width:78.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="104"><p class="MsoNormal"><span style="color:black">wastedpages</span><span
style="color:black"></span></td><tdnowrap style="width:77.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="103"><p class="MsoNormal"><span style="color:black">wastedbytes</span><span
style="color:black"></span></td><tdnowrap style="width:69.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="92"><p class="MsoNormal"><span style="color:black">wastedsize</span><span
style="color:black"></span></td></tr><trstyle="height:15.0pt"><td nowrap style="width:79.0pt;padding:0in 5.4pt 0in
5.4pt;height:15.0pt"valign="bottom" width="105"><p class="MsoNormal"><span style="color:black">pg_catalog</span><span
style="color:black"></span></td><tdnowrap style="width:124.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="165"><p class="MsoNormal"><span style="color:black">pg_exttable</span><span
style="color:black"></span></td><tdnowrap style="width:59.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="79"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">7092</span><spanstyle="color:black"></span></td><td nowrap style="width:57.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="76"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">3137</span><spanstyle="color:black"></span></td><td nowrap style="width:.5in;padding:0in 5.4pt 0in
5.4pt;height:15.0pt"valign="bottom" width="48"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">49</span><spanstyle="color:black"></span></td><td nowrap style="width:45.0pt;padding:0in 5.4pt 0in
5.4pt;height:15.0pt"valign="bottom" width="60"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">64</span><spanstyle="color:black"></span></td><td nowrap style="width:78.0pt;padding:0in 5.4pt 0in
5.4pt;height:15.0pt"valign="bottom" width="104"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">3088</span><spanstyle="color:black"></span></td><td nowrap style="width:77.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="103"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">101187584</span><spanstyle="color:black"></span></td><td nowrap style="width:69.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="92"><p class="MsoNormal"><span style="color:black">97
MB</span><spanstyle="color:black"></span></td></tr><tr style="height:15.0pt"><td nowrap style="width:79.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="105"><p class="MsoNormal"><span
style="color:black">pg_catalog</span><spanstyle="color:black"></span></td><td nowrap style="width:124.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="165"><p class="MsoNormal"><span
style="color:black">pg_shdepend</span><spanstyle="color:black"></span></td><td nowrap style="width:59.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="79"><p align="right" class="MsoNormal"
style="text-align:right"><spanstyle="color:black">48674</span><span style="color:black"></span></td><td nowrap
style="width:57.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="76"><p align="right"
class="MsoNormal"style="text-align:right"><span style="color:black">2349</span><span
style="color:black"></span></td><tdnowrap style="width:.5in;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" valign="bottom"
width="48"><palign="right" class="MsoNormal" style="text-align:right"><span style="color:black">84</span><span
style="color:black"></span></td><tdnowrap style="width:45.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="60"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">28</span><spanstyle="color:black"></span></td><td nowrap style="width:78.0pt;padding:0in 5.4pt 0in
5.4pt;height:15.0pt"valign="bottom" width="104"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">2265</span><spanstyle="color:black"></span></td><td nowrap style="width:77.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="103"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">74219520</span><spanstyle="color:black"></span></td><td nowrap style="width:69.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="92"><p class="MsoNormal"><span style="color:black">71
MB</span><spanstyle="color:black"></span></td></tr><tr style="height:15.0pt"><td nowrap style="width:79.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="105"><p class="MsoNormal"><span
style="color:black">pg_catalog</span><spanstyle="color:black"></span></td><td nowrap style="width:124.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="165"><p class="MsoNormal"><span
style="color:black">pg_shdepend</span><spanstyle="color:black"></span></td><td nowrap style="width:59.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="79"><p align="right" class="MsoNormal"
style="text-align:right"><spanstyle="color:black">48674</span><span style="color:black"></span></td><td nowrap
style="width:57.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="76"><p align="right"
class="MsoNormal"style="text-align:right"><span style="color:black">2349</span><span
style="color:black"></span></td><tdnowrap style="width:.5in;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" valign="bottom"
width="48"><palign="right" class="MsoNormal" style="text-align:right"><span style="color:black">84</span><span
style="color:black"></span></td><tdnowrap style="width:45.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="60"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">28</span><spanstyle="color:black"></span></td><td nowrap style="width:78.0pt;padding:0in 5.4pt 0in
5.4pt;height:15.0pt"valign="bottom" width="104"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">2265</span><spanstyle="color:black"></span></td><td nowrap style="width:77.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="103"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">74219520</span><spanstyle="color:black"></span></td><td nowrap style="width:69.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="92"><p class="MsoNormal"><span style="color:black">71
MB</span><spanstyle="color:black"></span></td></tr><tr style="height:15.0pt"><td nowrap style="width:79.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="105"><p class="MsoNormal"><span
style="color:black">pg_catalog</span><spanstyle="color:black"></span></td><td nowrap style="width:124.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="165"><p class="MsoNormal"><span
style="color:black">gp_distribution_policy</span><spanstyle="color:black"></span></td><td nowrap
style="width:59.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="79"><p align="right"
class="MsoNormal"style="text-align:right"><span style="color:black">19810</span><span
style="color:black"></span></td><tdnowrap style="width:57.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="76"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">2131</span><spanstyle="color:black"></span></td><td nowrap style="width:.5in;padding:0in 5.4pt 0in
5.4pt;height:15.0pt"valign="bottom" width="48"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">38</span><spanstyle="color:black"></span></td><td nowrap style="width:45.0pt;padding:0in 5.4pt 0in
5.4pt;height:15.0pt"valign="bottom" width="60"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">56.1</span><spanstyle="color:black"></span></td><td nowrap style="width:78.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="104"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">2093</span><spanstyle="color:black"></span></td><td nowrap style="width:77.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="103"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">68583424</span><spanstyle="color:black"></span></td><td nowrap style="width:69.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="92"><p class="MsoNormal"><span style="color:black">65
MB</span><spanstyle="color:black"></span></td></tr><tr style="height:15.0pt"><td nowrap style="width:79.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="105"><p class="MsoNormal"><span
style="color:black">pg_catalog</span><spanstyle="color:black"></span></td><td nowrap style="width:124.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="165"><p class="MsoNormal"><span
style="color:black">pg_class</span><spanstyle="color:black"></span></td><td nowrap style="width:59.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="79"><p align="right" class="MsoNormal"
style="text-align:right"><spanstyle="color:black">33044</span><span style="color:black"></span></td><td nowrap
style="width:57.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="76"><p align="right"
class="MsoNormal"style="text-align:right"><span style="color:black">10139</span><span
style="color:black"></span></td><tdnowrap style="width:.5in;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" valign="bottom"
width="48"><palign="right" class="MsoNormal" style="text-align:right"><span style="color:black">235</span><span
style="color:black"></span></td><tdnowrap style="width:45.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="60"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">43.1</span><spanstyle="color:black"></span></td><td nowrap style="width:78.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="104"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">9904</span><spanstyle="color:black"></span></td><td nowrap style="width:77.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="103"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">324534272</span><spanstyle="color:black"></span></td><td nowrap style="width:69.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="92"><p class="MsoNormal"><span style="color:black">310
MB</span><spanstyle="color:black"></span></td></tr><tr style="height:15.0pt"><td nowrap style="width:79.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="105"><p class="MsoNormal"><span
style="color:black">pg_catalog</span><spanstyle="color:black"></span></td><td nowrap style="width:124.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="165"><p class="MsoNormal"><span
style="color:black">pg_class</span><spanstyle="color:black"></span></td><td nowrap style="width:59.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="79"><p align="right" class="MsoNormal"
style="text-align:right"><spanstyle="color:black">33044</span><span style="color:black"></span></td><td nowrap
style="width:57.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="76"><p align="right"
class="MsoNormal"style="text-align:right"><span style="color:black">10139</span><span
style="color:black"></span></td><tdnowrap style="width:.5in;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" valign="bottom"
width="48"><palign="right" class="MsoNormal" style="text-align:right"><span style="color:black">235</span><span
style="color:black"></span></td><tdnowrap style="width:45.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="60"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">43.1</span><spanstyle="color:black"></span></td><td nowrap style="width:78.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="104"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">9904</span><spanstyle="color:black"></span></td><td nowrap style="width:77.0pt;padding:0in 5.4pt
0in5.4pt;height:15.0pt" valign="bottom" width="103"><p align="right" class="MsoNormal" style="text-align:right"><span
style="color:black">324534272</span><spanstyle="color:black"></span></td><td nowrap style="width:69.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="92"><p class="MsoNormal"><span style="color:black">310
MB</span><spanstyle="color:black"></span></td></tr><tr style="height:15.0pt"><td nowrap style="width:79.0pt;padding:0in
5.4pt0in 5.4pt;height:15.0pt" valign="bottom" width="105"><p class="MsoNormal"><b><span
style="color:black">pg_catalog</span></b><b><spanstyle="color:black"></span></b></td><td nowrap
style="width:124.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="165"><p
class="MsoNormal"><b><spanstyle="color:black">pg_attribute</span></b><b><span style="color:black"></span></b></td><td
nowrapstyle="width:59.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="79"><p align="right"
class="MsoNormal"style="text-align:right"><b><span style="color:black">513871</span></b><b><span
style="color:black"></span></b></td><tdnowrap style="width:57.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="76"><p align="right" class="MsoNormal" style="text-align:right"><b><span
style="color:black">98646</span></b><b><spanstyle="color:black"></span></b></td><td nowrap
style="width:.5in;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="48"><p align="right"
class="MsoNormal"style="text-align:right"><b><span style="color:black">2135</span></b><b><span
style="color:black"></span></b></td><tdnowrap style="width:45.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="60"><p align="right" class="MsoNormal" style="text-align:right"><b><span
style="color:black">46.2</span></b><b><spanstyle="color:black"></span></b></td><td nowrap
style="width:78.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="104"><p align="right"
class="MsoNormal"style="text-align:right"><b><span style="color:black">96511</span></b><b><span
style="color:black"></span></b></td><tdnowrap style="width:77.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt"
valign="bottom"width="103"><p align="right" class="MsoNormal" style="text-align:right"><b><span
style="color:black">3162472448</span></b><b><spanstyle="color:black"></span></b></td><td nowrap
style="width:69.0pt;padding:0in5.4pt 0in 5.4pt;height:15.0pt" valign="bottom" width="92"><p class="MsoNormal"><b><span
style="color:black">3016MB</span></b><b><span style="color:black"></span></b></td></tr></table><p
class="MsoPlainText"><b> </b><pclass="MsoPlainText"><b>Thanks</b><p class="MsoPlainText"><b>Doug</b><p
class="MsoPlainText"><b> </b><pclass="MsoPlainText"><b> </b><p class="MsoPlainText">-----Original Message-----<br
/>From:Guillaume Lelarge [mailto:guillaume@lelarge.info] <br />Sent: Friday, December 03, 2010 2:55 AM<br />To: Little,
Douglas<br/>Cc: PgAdmin Support<br />Subject: Re: [pgadmin-support] fsm and vacuum<p class="MsoPlainText"> <p
class="MsoPlainText">Hi,<pclass="MsoPlainText"> <p class="MsoPlainText">Le 03/12/2010 00:19, Little, Douglas a écrit
:<pclass="MsoPlainText">> [...]<p class="MsoPlainText">> Thanks for the response.<p class="MsoPlainText"> <p
class="MsoPlainText">Noproblem, but keep your anwser to the list, even if it's not the good<p class="MsoPlainText">one
:)<pclass="MsoPlainText"> <p class="MsoPlainText">> Still a bit confused.<p class="MsoPlainText">> Q: The guk
settings max_fsm_relations/pages are used by the db engine to set the size of the freespace map.<p
class="MsoPlainText"> <pclass="MsoPlainText">In memory, yes.<p class="MsoPlainText"> <p class="MsoPlainText">> Q:
vacuumscans thru the file and adds free slots to the map when a table is vacuumed<p class="MsoPlainText"> <p
class="MsoPlainText">Yes.<pclass="MsoPlainText"> <p class="MsoPlainText">> Q: the map is used by the engine when
insertinga row (new or versioned).<p class="MsoPlainText"> <p class="MsoPlainText">Yes.<p class="MsoPlainText"> <p
class="MsoPlainText">>So is the only way to initialize the fsm to run vacuum?<p class="MsoPlainText"> <p
class="MsoPlainText">Yes.<pclass="MsoPlainText"> <p class="MsoPlainText">> We're experiencing problems using vacuum
full. GP recommends ctas/truncate/reload as alternative.<p class="MsoPlainText">> Obviously won't work for system
tables.<pclass="MsoPlainText">> My thought is vacuum full isn't working because the fsm was undersized.<p
class="MsoPlainText"> <pclass="MsoPlainText">vacuum full first scans the whole table to find free space, and then<p
class="MsoPlainText">scansbackward to move every still-in-use space at the beginning of the<p
class="MsoPlainText">table.I don't know if vacuum full puts its information in the fsm, but<p class="MsoPlainText">I
believeso. So, if the fsm is undersized, you risk to have a not fully<p class="MsoPlainText">effective vacuum full.<p
class="MsoPlainText"> <pclass="MsoPlainText">Anyway, you should probably not use vacuum full, unless you have a<p
class="MsoPlainText">*really*good reason.<p class="MsoPlainText"> <p class="MsoPlainText">Remember to REINDEX after
yourVACUUM FULL. Meaning you should probably<p class="MsoPlainText">use CLUSTER, which will be fully effective and
quicker.But you need an<p class="MsoPlainText">index.<p class="MsoPlainText"> <p class="MsoPlainText">> Anything in
thedevelopers docs that would help me understand how it works?<p class="MsoPlainText"> <p class="MsoPlainText">This
couldbe of interest:<p class="MsoPlainText"> <p class="MsoPlainText">  http://wiki.postgresql.org/wiki/VACUUM_FULL<p
class="MsoPlainText"> <pclass="MsoPlainText"> <p class="MsoPlainText">-- <p class="MsoPlainText">Guillaume<p
class="MsoPlainText">http://www.postgresql.fr<p class="MsoPlainText"> http://dalibo.com</div>