Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	b=Zw9Y+WE+WnvNzpm9AR00fBsQSgHxcUcv5yfqdX5P1QXQhjhHnB6VYpfPMqx7H8UTwQ6gah5pT9d1N4sDdUU/McnjldQ0X+ueBcrXa7WSkU9iJvtUGcvzx5MFsUaKg66XQJfo56p3TnowCXIa7WrMOEZoEPsZlvTChe/jhnPObSQ=
	;
Message-ID: <20050421225926.11937.qmail@web51404.mail.yahoo.com>
Date: Thu, 21 Apr 2005 15:59:26 -0700 (PDT)
From: Bill Chandler <billybobc1210@yahoo.com>
Subject: Re: Index bloat problem?
To: Mischa Sandberg <mischa.sandberg@telus.net>,
	pgsql-perform <pgsql-performance@postgresql.org>
In-Reply-To: 6667
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Mischa,

Thanks.  Yes, I understand that not having a large
enough max_fsm_pages is a problem and I think that it
is most likely the case for the client.  What I wasn't
sure of was if the index bloat we're seeing is the
result of the "bleeding" you're talking about or
something else.

If I deleted 75% of the rows but had a max_fsm_pages
setting that still exceeded the pages required (as
indicated in VACUUM output), would that solve my
indexing problem or would I still need to REINDEX
after such a purge?

regards,

Bill

--- Mischa Sandberg <mischa.sandberg@telus.net> wrote:
> Quoting Bill Chandler <billybobc1210@yahoo.com>:
> 
> > ... The normal activity is to delete 3-5% of the
> rows per day,
> > followed by a VACUUM ANALYZE. 
> ...
> > However, on occasion, deleting 75% of rows is a 
> > legitimate action for the client to take.  
> 
> > > In case nobody else has asked: is your
> max_fsm_pages
> > > big enough to handle all the deleted pages, 
> > > across ALL tables hit by the purge?
> 
> > This parameter is most likely set incorrectly.  So
> > that could be causing problems.  Could that be a
> > culprit for the index bloat, though?
> 
> Look at the last few lines of vacuum verbose output.
> It will say something like:
> 
> free space map: 55 relations, 88416 pages stored;
> 89184 total pages needed
>   Allocated FSM size: 1000 relations + 1000000 pages
> = 5920 kB shared memory.
> 
> "1000000" here is [max_fsm_pages] from my
> postgresql.conf.
> If the "total pages needed" is bigger than the pages
> 
> fsm is allocated for, then you are bleeding.
> -- 
> "Dreams come true, not free." -- S.Sondheim, ITW
> 
> 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com