Guillaume,
Given this syscat bloat, what would you recommend doing?
schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages | wastedbytes | wastedsize |
pg_catalog | pg_exttable | 7092 | 3137 | 49 | 64 | 3088 | 101187584 | 97 MB |
pg_catalog | pg_shdepend | 48674 | 2349 | 84 | 28 | 2265 | 74219520 | 71 MB |
pg_catalog | pg_shdepend | 48674 | 2349 | 84 | 28 | 2265 | 74219520 | 71 MB |
pg_catalog | gp_distribution_policy | 19810 | 2131 | 38 | 56.1 | 2093 | 68583424 | 65 MB |
pg_catalog | pg_class | 33044 | 10139 | 235 | 43.1 | 9904 | 324534272 | 310 MB |
pg_catalog | pg_class | 33044 | 10139 | 235 | 43.1 | 9904 | 324534272 | 310 MB |
pg_catalog | pg_attribute | 513871 | 98646 | 2135 | 46.2 | 96511 | 3162472448 | 3016 MB |
Thanks
Doug
-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Friday, December 03, 2010 2:55 AM
To: Little, Douglas
Cc: PgAdmin Support
Subject: Re: [pgadmin-support] fsm and vacuum
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
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
