Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.

On Wed, 23 Feb 2005, Tom Lane wrote:
> Ron Mayer  writes:
> > +    if (needed > MaxFSMPages)... ereport(WARNING,  ...
> 
> An unconditional WARNING seems a bit strong to me for a case that is not
> necessarily wrong. 

How about a HINT, not unlike the "checkpoints are occurring too 
frequently" one?


> Depending on the needs of the installation, this might be a perfectly 
> acceptable situation --- for example if you have lots of large 
> read-mostly tables. 

OTOH, for other installations, it's nice to have some quick 
way for our customers to know when to call us.  For a system 
we install somewhere and may not have access to, would this 
be a safe, overly-conservative test that could be put in place?
Unfortunately we really don't know exactly how fast their 
tables will be growing, and I'm hoping some sort of monitoring 
like this - even if it's too conservative - can catch things 
before they cause problems.

I basing the comparison on this older thread:
 http://archives.postgresql.org/pgsql-www/2004-11/msg00078.php
if better comparisons can be made, I'd be interested into 
looking into them if someone points me in the right direction.


> On the other side of the coin, the test could pass (ie no warning) in
> situations where in fact MaxFSMPages is too small ...

I was hoping that second message would have caught this - but I 
clearly didn't understand what comparison to use there either. :(
Would the fixed (with == instead of > ) test on MaxFSMRelations 
catch most cases where it gives a false negative?


> > +    if (numRels > MaxFSMRelations) ...
> This part is just plain dead code, since it's not possible for numRels
> to exceed MaxFSMRelations.

oops.  Sorry.

> I think it might be useful to warn when numRels == MaxFSMRelations,
> since if you don't have even one spare fsmrel slot then you probably
> have too few (it's unlikely you got it on the nose).  But I don't know
> how to produce a warning about MaxFSMPages that's worth anything.

I'm not sure I understood the false-positive with many large 
read-mostly tables you mentioned above.  

Even in that case, how would I know that the limited FSM space I 
had was used on the tables that needed it?

The database I have matches that description - 90 GB of GIS data
that changes rarely (when cities close streets, etc) - and a few
tables of quickly changing data (recent crimes in the cities
being analyzed).   I want to make sure the quickly changing
tables don't get starved for fsm space from the large tables.
Would this comparison insure against that risk?  (or am I 
misunderstanding completely, and I'll drop the subject)

   Ron