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

On Wed, 23 Feb 2005, Tom Lane wrote:
> Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> 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