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:guilla...@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
