A way to let Vacuum warn if FSM settings are low. - Mailing list pgsql-general

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