Short summary:

I find this tiny (9-line) patch useful to help my clients know
when FSM settings may need updating.

Some of the more frequently asked questions here are in regards to FSM
settings. One hint I've seen is to run "vacuum verbose;". At the end
of thousands of lines of INFO and DETAIL messages vacuum verbose has 2
separate lines with some numbers to compare ("total pages needed" and
"FSM size...pages") that help indicate too low fsm settings.


I've gotten into the habit of always installing the following patch
(below) that automatically does this comparison for me, and if
max_fsm_pages is too small, it logs a warning as shown here:

patched=# vacuum;
WARNING: max_fsm_pages(1601) is smaller than total pages needed(2832)
VACUUM

I find this much nicer than the existing output (
clean=# vacuum verbose;
[......... thousands of lines of INFO and DETAIL messages ........]
INFO: free space map: 77 relations, 470 pages stored; 2832 total pages needed
DETAIL: Allocated FSM size: 100 relations + 1601 pages = 19 kB shared memory.
) for many reasons:
* First, because it's a warning, lots of people will notice it before
their asking the FAQ again.
* Second, because all the information is on a single line and actually
contains the string "max_fsm_relations", it gives people a clue what
to do about it. (note that vacuum verbose uses similar phrases but
from the number of questions here, it must not be obvious)
* Third, I don't need the 'verbose' setting.
* And most importantly, our clients let us know about WARNINGs,
but not about INFOs or DETAILs in their log page; so it gives
us a chance to respond before their system drags to a halt.

If a patch like this could get into the standard distro, that'd be
awesome - just let me know what additional work is needed (I didn't
look at docs or internationalization yet). If not, I'd like to post
it here to patches just in case anyone else will benefit from the
same thing.


==================================================
% diff -u postgresql-8.0.1/src/backend/storage/freespace/freespace.c postgresql-patched/src/backend/storage/freespace/freespace.c
--- postgresql-8.0.1/src/backend/storage/freespace/freespace.c 2004-12-31 14:00:54.000000000 -0800
+++ postgresql-patched/src/backend/storage/freespace/freespace.c 2005-02-23 14:58:50.638745744 -0800
@@ -704,6 +704,15 @@

/* Convert stats to actual number of page slots needed */
needed = (sumRequests + numRels) * CHUNKPAGES;
+
+ if (needed > MaxFSMPages)
+ ereport(WARNING,
+ (errmsg("max_fsm_pages(%d) is smaller than total pages needed(%.0f)",
+ MaxFSMPages, needed)));
+ if (numRels > MaxFSMRelations)
+ ereport(WARNING,
+ (errmsg("max_fsm_relations(%d) is smaller than the number of relations (%d)",
+ MaxFSMRelations, numRels)));

ereport(elevel,
(errmsg("free space map: %d relations, %d pages stored; %.0f total pages needed",
==================================================


Thoughts?
Ron


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> + if (needed > MaxFSMPages)
> + ereport(WARNING,
> + (errmsg("max_fsm_pages(%d) is smaller than total pages needed(%.0f)",
> + MaxFSMPages, needed)));

An unconditional WARNING seems a bit strong to me for a case that is not
necessarily wrong. 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.

On the other side of the coin, the test could pass (ie no warning) in
situations where in fact MaxFSMPages is too small, because what we are
comparing it to is the number of pages requested for relations that are
being tracked. If MaxFSMRelations is too small then we can't really
tell whether MaxFSMPages is adequate.

> + if (numRels > MaxFSMRelations)
> + ereport(WARNING,
> + (errmsg("max_fsm_relations(%d) is smaller than the number of relations (%d)",
> + MaxFSMRelations, numRels)));

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

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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

> I find this tiny (9-line) patch useful to help my clients know
> when FSM settings may need updating.
>
> Some of the more frequently asked questions here are in regards to FSM
> settings. One hint I've seen is to run "vacuum verbose;". At the end
> of thousands of lines of INFO and DETAIL messages vacuum verbose has 2
> separate lines with some numbers to compare ("total pages needed" and
> "FSM size...pages") that help indicate too low fsm settings.
>
>
> I've gotten into the habit of always installing the following patch
> (below) that automatically does this comparison for me, and if
> max_fsm_pages is too small, it logs a warning as shown here:
>
> patched=# vacuum;
> WARNING: max_fsm_pages(1601) is smaller than total pages needed(2832)
> VACUUM

I think this patch is great. I can never figure out how to set those
settings easily.

Chris

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Wed, 2005-02-23 at 19:31 -0500, Tom Lane wrote:
> Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> > + if (needed > MaxFSMPages)
> > + ereport(WARNING,
> > + (errmsg("max_fsm_pages(%d) is smaller than total pages needed(%.0f)",
> > + MaxFSMPages, needed)));
>
> An unconditional WARNING seems a bit strong to me for a case that is not
> necessarily wrong. 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.

The patch seems very useful to me. I had been thinking about doing
something like that myself.

VACUUM uses an INFO to provide the "total pages needed", so it should be
a simple matter to change the ereport to an INFO rather than WARNING as
well.

It would be great to have both lines of INFO, so that VACUUM would
produce output like this:

patched=# vacuum;
INFO: free space map: 77 relations, 470 pages stored
INFO: max_fsm_pages(1601) is smaller than total pages needed(2832)
DETAIL: Allocated FSM size: 100 relations + 1601 pages = 19 kB shared
memory.
VACUUM

....where the second info line was conditional...like this...

+ if (numRels == MaxFSMRelations)
+ ereport(WARNING,
+ (errmsg("max_fsm_relations(%d) may be set too low",
+ MaxFSMRelations)));
+ else
+ if (needed > MaxFSMPages)
+ ereport(INFO,
+ (errmsg("max_fsm_pages(%d) is smaller than total pages
needed(%.0f)",
+ MaxFSMPages, needed)));

ereport(elevel,
(errmsg("free space map: %d relations, %d pages stored;
%.0f total pages needed",

Which goes more towards Tom's gripes.

The manual could have a line added to explain that if max_fsm_relations
is set too low, then max_fsm_pages may also inadvertently be too low,
yet not be obvious that that is the case.

Best Regards, Simon Riggs


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thanks everyone for the feedback on my patch.

Objections I've heard (both online and in email) included:

* WARNING is too strong for possibly OK behavior
* It's similar to "checkpoints occuring too frequently...
consider increasing...checkpoint_segments" which
is a LOG not a WARNING.
* The end user can't do anything anyway; so the LOG
file would be a better place.
* My comparison for numRels was broken.
* If we're hiting the user to do something (change
settings) I should make it put a HINT in the log file.

Praise I've heard included:

* Even if it's too conservative, some admins want to know.
* Unlike the current VACUUM VERBOSE info, all info is on
one line, so automated log monitoring software can more
easily catch it.
* Unlike the current VACUUM VERBOSE info, this one points
the user in the right direction.

Would the updated patch below address most of the concerns?

The output now looks like:
LOG: max_fsm_pages(1601) is smaller than the actual number of page slots needed(2832)
HINT: You may want to increase max_fsm_pages to be larger than 2832
and only goes in the log file (like the "checkpoints" hint).


I think Tom's outstanding comment that "Depending on the installation,
this might be a perfectly acceptable situation ... I don't know how
toproduce a warning about MaxFSMPages that's worth anything" is the
only objection left unaddressed. I guess my defense to that statement
would be that I think for some installations this does provide value,
so by making it a LOG instead of a WARNING are both needs met?

Thanks,
Ron

================================================== ==========
% diff -U 10 postgresql-8.0.1/src/backend/storage/freespace/freespace.c postgresql-patched/src/backend/storage/freespace/freespace.c
--- postgresql-8.0.1/src/backend/storage/freespace/freespace.c 2004-12-31 14:00:54.000000000 -0800
+++ postgresql-patched/src/backend/storage/freespace/freespace.c 2005-02-24 13:44:52.361669928 -0800
@@ -704,20 +704,32 @@

/* Convert stats to actual number of page slots needed */
needed = (sumRequests + numRels) * CHUNKPAGES;

ereport(elevel,
(errmsg("free space map: %d relations, %d pages stored; %.0f total pages needed",
numRels, storedPages, needed),
errdetail("Allocated FSM size: %d relations + %d pages = %.0f kB shared memory.",
MaxFSMRelations, MaxFSMPages,
(double) FreeSpaceShmemSize() / 1024.0)));
+
+ if (needed > MaxFSMPages)
+ ereport(LOG,
+ (errmsg("max_fsm_pages(%d) is smaller than the actual number of page slots needed(%.0f)",
+ MaxFSMPages, needed),
+ errhint("You may want to increase max_fsm_pages to be larger than %.0f",needed)));
+ if (numRels == MaxFSMRelations)
+ ereport(LOG,
+ (errmsg("max_fsm_relations(%d) is equal than the number of relations vacuum checked (%d)",
+ MaxFSMRelations, numRels),
+ errhint("You probably have more than %d relations. You should increase max_fsm_relations. Pages needed for max_fsm_pages may have been underestimated as well. ",numRels)));
+
}

/*
* DumpFreeSpaceMap - dump contents of FSM into a disk file for later reload
*
* This is expected to be called during database shutdown, after updates to
* the FSM have stopped. We lock the FreeSpaceLock but that's purely pro
* forma --- if anyone else is still accessing FSM, there's a problem.
*/
void
================================================== ==========




---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> Would the updated patch below address most of the concerns?

I preferred Simon's idea of not trying to produce a warning for pages
when we've detected relation overflow.

Making it a LOG rather than WARNING does address the issue of being
too much in-your-face for an uncertain condition, though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Thu, 24 Feb 2005, Tom Lane wrote:
> I preferred Simon's idea of not trying to produce a warning for pages
> when we've detected relation overflow.

Sounds good. I'll make that update.

Should the relation overflow be a WARNING or a LOG? It sounds like
if you have that problem it's almost certainly a problem, right?

> Making it a LOG rather than WARNING does address the issue of being
> too much in-your-face for an uncertain condition, though.

Great.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> Should the relation overflow be a WARNING or a LOG? It sounds like
> if you have that problem it's almost certainly a problem, right?

I'd go for making them both LOG, I think. More consistent.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Thu, 24 Feb 2005, Ron Mayer wrote:
> Should the relation overflow be a WARNING or a LOG? It sounds like
> if you have that problem it's almost certainly a problem, right?

And while I'm at it... what's the convention for INFOs vs LOGs?
The "checkpoint...too frequent" seemed similar, and is a LOG.

And do people think the HINT's I added add value or just noise?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Thu, 24 Feb 2005, Tom Lane wrote:
> I'd go for making them both LOG, I think. More consistent.

Ok, here's another try With a couple more questions...

1. If I read Simon's email correctly, it implied that he wanted to see
the "free space map" message for a VACUUM even when VERBOSE is turned off.
I could just tweak it in PrintFreeSpaceMapStastics() as shown here...
but now elevel (which depended on VACUUM VERBOSE or not) is no longer
needed by PrintFreeSpaceMapStastics.
1a. Is that desired to always show this line as an INFO instead of
a DEBUG2 (which it currently is when VERBOSE is not selected)?
1b. Should I tweak vacuum.c (feels cleaner) or just freespace.c (minimal
changes).

2. If I read Simon's email correctly, it implied that he wanted to see
these new lines when you type VACUUM. This would suggest making
them INFOs. Making them INFOs would slightly contradict another
goal of wanting to see them in the LOG for automated log
grepping scripts to find, since that would require turning on INFOs
that I think commonly aren't logged. Also making them LOGs is
consistent with the checkpoint hint. I suppose they could be
made NOTICEs; but that isn't consistent with either.



diff -u postgresql-8.0.1/src/backend/storage/freespace/freespace.c postgresql-patched/src/backend/storage/freespace/freespace.c
--- postgresql-8.0.1/src/backend/storage/freespace/freespace.c 2004-12-31 14:00:54.000000000 -0800
+++ postgresql-patched/src/backend/storage/freespace/freespace.c 2005-02-24 14:54:36.619566040 -0800
@@ -705,12 +705,25 @@
/* Convert stats to actual number of page slots needed */
needed = (sumRequests + numRels) * CHUNKPAGES;

- ereport(elevel,
+ ereport(INFO,
(errmsg("free space map: %d relations, %d pages stored; %.0f total pages needed",
numRels, storedPages, needed),
errdetail("Allocated FSM size: %d relations + %d pages = %.0f kB shared memory.",
MaxFSMRelations, MaxFSMPages,
(double) FreeSpaceShmemSize() / 1024.0)));
+
+ if (numRels == MaxFSMRelations)
+ ereport(LOG,
+ (errmsg("max_fsm_relations(%d) is equal than the number of relations vacuum checked (%d)",
+ MaxFSMRelations, numRels),
+ errhint("You probably have more than %d relations. You should increase max_fsm_relations. Pages needed for max_fsm_pages may have been underestimated. ",numRels)));
+ else
+ if (needed > MaxFSMPages)
+ ereport(LOG,
+ (errmsg("max_fsm_pages(%d) is smaller than the actual number of page slots needed(%.0f)",
+ MaxFSMPages, needed),
+ errhint("You may want to increase max_fsm_pages to be larger than %.0f",needed)));
+
}


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org