Обсуждение: fsm and vacuum

<div class="WordSection1"><p class="MsoNormal">We’re new to Greenplum, based on PG (8.2.13).  we weren’t advised to
increasethe max_fsm_relations switch as our db has grown.<p class="MsoNormal">Currently we’re nearly 14k tables/indexes
andthe switch is set to 1000.<p class="MsoNormal">We’ve got it updated now, but wondering about the effect &
recovery.<pclass="MsoNormal"> <p class="MsoNormal">We’ve regularly been reloading tables with CTAS.<p
class="MsoNormal">I’vedone some experiments and want to confirm my understanding.<p class="MsoNormal"> <p
class="MsoNormal">1.Vacuum full only compacted the  oldest? tables (reducing pages from 109 to 5)<p
class="MsoNormal">2. vacuum full frees pages, but doesn’t reduce the file size (newer tables).  Table needed to be
recreatedto reduce size<p class="MsoNormal"> <p class="MsoNormal">I’ve searched the archives and haven’t found what I’m
lookingfor.  I hope someone would just dash off a simple explanation.<p class="MsoNormal"> <p class="MsoNormal">1. 
whenare relations added to the fsm? (at create, or some other time)<p class="MsoNormal">2.  will vacuum (full) add a
relationto the fsm - ?  <p class="MsoNormal">3.  if bloated table is vacuum full’d,  will the free space pages be
use?<pclass="MsoNormal"> <p class="MsoNormal">My assumptions are:<p class="MsoNormal">1. relation added to fsm list at
createtime.<p class="MsoNormal">2. fsm_pages keep track of free space for the relations being tracked in the
fsm_relationlist<p class="MsoNormal">3. table recreate is the only way to have a relation tracked.<p
class="MsoNormal"> <pclass="MsoNormal">Thanks in advance.<p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Doug
Little</span></b><pclass="MsoNormal"><b><span style="font-size:12.0pt"> </span></b><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Sr.Data Warehouse Architect | Enterprise Data Management |
OrbitzWorldwide </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">500 W.
Madison,Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741</span><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""></span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><ahref="mailto:Douglas.Little@orbitz.com"
title="mailto:dlittle@orbitz.com"><b><spanstyle="color:blue">Douglas.Little@orbitz.com</span></b></a></span><b><u><span
style="font-size:13.5pt;font-family:"TimesNew Roman","serif";color:blue"></span></u></b><p class="MsoNormal"> <img
alt="cid:image001.jpg@01CABEC8.D4980670"border="0" height="61" id="Picture_x0020_1"
src="cid:image001.jpg@01CB922D.0A2B3AE0"width="83" />  <a href="http://www.orbitz.com/"
title="http://www.orbitz.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">orbitz.com</span></a><span
style="color:blue">|</span><a href="http://www.ebookers.com/" title="http://www.ebookers.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">ebookers.com</span></a><span
style="color:blue">|</span><a href="http://www.hotelclub.com/" title="http://www.hotelclub.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">hotelclub.com</span></a><span
style="color:blue">|</span><a href="http://www.cheaptickets.com/" title="http://www.cheaptickets.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">cheaptickets.com</span></a><span
style="color:blue">|</span><a href="http://www.ratestogo.com/" title="http://www.ratestogo.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">ratestogo.com</span></a><spanstyle="color:blue">
|</span><a href="http://www.asiahotels.com/" title="http://www.asiahotels.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">asiahotels.com</span></a><p
class="MsoNormal"> </div>

Le 02/12/2010 22:00, Little, Douglas a écrit :
> We're new to Greenplum, based on PG (8.2.13).  we weren't advised to increase the max_fsm_relations switch as our db
hasgrown.
 
> Currently we're nearly 14k tables/indexes and the switch is set to 1000.
> We've got it updated now, but wondering about the effect & recovery.
> 
> We've regularly been reloading tables with CTAS.
> I've done some experiments and want to confirm my understanding.
> 
> 1. Vacuum full only compacted the  oldest? tables (reducing pages from 109 to 5)
> 2.  vacuum full frees pages, but doesn't reduce the file size (newer tables).  Table needed to be recreated to reduce
size
> 
> I've searched the archives and haven't found what I'm looking for.  I hope someone would just dash off a simple
explanation.
> 
> 1.  when are relations added to the fsm? (at create, or some other time)

During vacuum, if there are dead spaces in the table.

> 2.  will vacuum (full) add a relation to the fsm - ?

Yes, for tables with dead spaces.

> 3.  if bloated table is vacuum full'd,  will the free space pages be use?

If vacuum full did its job, it shouldn't be.

> My assumptions are:
> 1. relation added to fsm list at create time.

Nope.

> 2. fsm_pages keep track of free space for the relations being tracked in the fsm_relation list

At vacuum time.

> 3. table recreate is the only way to have a relation tracked.

Nope.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Hi,

Le 03/12/2010 00:19, Little, Douglas a écrit :
> [...]
> Thanks for the response.

No problem, but keep your anwser to the list, even if it's not the good
one :)

> Still a bit confused.
> Q: The guk settings  max_fsm_relations/pages are used by the db engine to set the size of the freespace map.

In memory, yes.

> Q: vacuum scans thru the file and adds free slots to the map when a table is vacuumed

Yes.

> Q: the map is used by the engine when inserting a row (new or versioned).

Yes.

> So is the only way to initialize the fsm to run vacuum?

Yes.

> We're experiencing problems using vacuum full.  GP recommends ctas/truncate/reload as alternative.
> Obviously won't work for system tables.
> My thought is vacuum full isn't working because the fsm was undersized.

vacuum full first scans the whole table to find free space, and then
scans backward to move every still-in-use space at the beginning of the
table. I don't know if vacuum full puts its information in the fsm, but
I believe so. So, if the fsm is undersized, you risk to have a not fully
effective vacuum full.

Anyway, you should probably not use vacuum full, unless you have a
*really* good reason.

Remember to REINDEX after your VACUUM FULL. Meaning you should probably
use CLUSTER, which will be fully effective and quicker. But you need an
index.

> Anything in the developers docs that would help me understand how it works?

This could be of interest:
 http://wiki.postgresql.org/wiki/VACUUM_FULL


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

<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> 

Le 03/12/2010 14:40, Little, Douglas a écrit :
> [...]
> Given this syscat bloat, what would you recommend doing?
> 

Sorry but that's really unreadable.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 03/12/2010 15:17, Michael Shapiro a écrit :
> The document http://wiki.postgresql.org/wiki/VACUUM_FULL says:
> 
> VACUUM FULL, unlike VACUUM, tuples data that has not been deleted, moving it
> into spaces earlier in the file that have been freed. Once it's created a
> free space at the end of the file, it truncates the file so that the OS
> knows that space is free and may be reused for other things. Moving in-use
> data around this way has some major downsides and side-effects, especially
> the way VACUUM FULL does it. There are better ways to free space if you need
> to and better ways to optimize tables (see below) so *you should essentially
> never use VACUUM FULL*.
> 
> 
> PgAdmin does not give the user a comparable warning when it goes to execute
> a VACCUM FULL. Given the potential problems with the FULL option, would it
> make sense for PgAdmin to issue a warning to this effect?
> 

I'm not sure this is the role of pgAdmin to warn people they are doing
potentially stupid things.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

I understand, but in this case, since the option is offered next to the safe one, most people won't know it isn't
safe.<br/>I certainly didn't until I read this posting. I know generally what vacuuming does, but I had no idea that
postgresoffered a potentially damaging option. Also, PgAdmin sometimes tells me that a table needs vacuuming, so it is
already"advising" people in that area ...<br /><br /><div class="gmail_quote">On Fri, Dec 3, 2010 at 9:19 AM, Guillaume
Lelarge<span dir="ltr"><<a href="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left:1ex;"> Le 03/12/2010 15:17, Michael Shapiro a écrit :<br /> > The document <a
href="http://wiki.postgresql.org/wiki/VACUUM_FULL"target="_blank">http://wiki.postgresql.org/wiki/VACUUM_FULL</a>
says:<br/> ><br /> > VACUUM FULL, unlike VACUUM, tuples data that has not been deleted, moving it<br /> > into
spacesearlier in the file that have been freed. Once it's created a<br /> > free space at the end of the file, it
truncatesthe file so that the OS<br /> > knows that space is free and may be reused for other things. Moving
in-use<br/> > data around this way has some major downsides and side-effects, especially<br /> > the way VACUUM
FULLdoes it. There are better ways to free space if you need<br /> > to and better ways to optimize tables (see
below)so *you should essentially<br /> > never use VACUUM FULL*.<br /> ><br /> ><br /> > PgAdmin does not
givethe user a comparable warning when it goes to execute<br /> > a VACCUM FULL. Given the potential problems with
theFULL option, would it<br /> > make sense for PgAdmin to issue a warning to this effect?<br /> ><br /><br />
I'mnot sure this is the role of pgAdmin to warn people they are doing<br /> potentially stupid things.<br /><font
color="#888888"><br/><br /> --<br /> Guillaume<br />  <a href="http://www.postgresql.fr"
target="_blank">http://www.postgresql.fr</a><br/>  <a href="http://dalibo.com" target="_blank">http://dalibo.com</a><br
/></font></blockquote></div><br/> 

Le 03/12/2010 16:25, Michael Shapiro a écrit :
> I understand, but in this case, since the option is offered next to the safe
> one, most people won't know it isn't safe.

Both are safe. They don't offer the same service. VACUUM will allow
PostgreSQL to reuse dead space, VACUUM FULL will free space on the hard
drive (and have a negative effect on the table's indexes if you don't do
a REINDEX).

> I certainly didn't until I read this posting. I know generally what
> vacuuming does, but I had no idea that postgres offered a potentially
> damaging option. Also, PgAdmin sometimes tells me that a table needs
> vacuuming, so it is already "advising" people in that area ...

Yea, the guru hints. Don't like that and don't really want to extend them.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 03/12/2010 16:24, Little, Douglas a écrit :
> Michael,
> 
> I hear that vacuum full shouldn't be used.
> But what impact does the bloat have on performance?

Bigger on your hard, so slower to scan. And bigger in memory, so you
need to push blocks out to scan it. Other than that, not much.

It's better to not have a lot of bloat. I mean, if you deleted half of
your 20 GB table, it makes sens to do a VACUUM FULL.

> And,   what do we do about the bloat in the pg tables?

Depends on the percentage of bloat and table size.

> I envision this would be a 1 time fix, now that we've got the fsm_relations set properly.

Could be.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 03/12/2010 16:28, Little, Douglas a écrit :
> Somehow this moved to the pgadmin list.  It was intended for pgsql-admin.  My apologies.
> This is a dba task,  I'd never expect pgadmin would do this.
> 

It didn't *move* to the pgadmin list, your first mail was sent there.
pgAdmin doesn't do it itself, but it allows the user to ask for it.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 03/12/2010 16:19, Little, Douglas a écrit :
> 
> 
> Sorry,  outlook must of clobbered the table.
> 
> [cid:image001.png@01CB92CB.11EA5F60]
> 
> 
> 
> Pg_attribute  has 513871 tuples and should take up 2135 pages.  It's currently allocated to 98646 pages.
> 
> Given that much bloat,  what would you suggest doing?
> 
> My guess is nothing.  The fsm/vacuum process will reuse empty slots over time.
> 

There's 96% of bloat? VACUUM FULL is needed here. According to your
numbers, it means you have a 770MB table of which only 16MB are actual data.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com