Re: fsm and vacuum

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(at)lelarge(dot)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