Experimental patch for inter-page delay in VACUUM

Attached is an extremely crude prototype patch for making VACUUM delay
by a configurable amount between pages, in hopes of throttling its disk
bandwidth consumption.  By default, there is no delay (so no change in
behavior).  In some quick testing, setting vacuum_page_delay to 10
(milliseconds) seemed to greatly reduce a background VACUUM's impact
on pgbench timing on an underpowered machine.  Of course, it also makes
VACUUM a lot slower, but that's probably not a serious concern for
background VACUUMs.

I am not proposing this for application to the master sources yet, but
I would be interested to get some feedback from people who see serious
performance degradation while VACUUM is running.  Does it help?  What do
you find to be a good setting for vacuum_page_delay?

Assuming that this is found to be useful, the following issues would
have to be dealt with before the patch would be production quality:

1. The patch depends on usleep() which is not present on all platforms,
   and may have unwanted side-effects on SIGALRM processing on some
   platforms.  We'd need to replace that with something else, probably
   a select() call.

2. I only bothered to insert delays in the processing loops of plain
   VACUUM and btree index cleanup.  VACUUM FULL and cleanup of non-btree
   indexes aren't done yet.

3. No documentation...

The patch is against CVS tip, but should apply cleanly to any recent
7.4 beta.  You could likely adapt it to 7.3 without much effort.

            regards, tom lane

*** src/backend/access/nbtree/nbtree.c.orig    Mon Sep 29 19:40:26 2003
--- src/backend/access/nbtree/nbtree.c    Thu Oct 30 21:02:55 2003
***************
*** 18,23 ****
--- 18,25 ----
   */
  #include "postgres.h"

+ #include <unistd.h>
+
  #include "access/genam.h"
  #include "access/heapam.h"
  #include "access/nbtree.h"
***************
*** 27,32 ****
--- 29,37 ----
  #include "storage/smgr.h"


+ extern int    vacuum_page_delay;
+
+
  /* Working state for btbuild and its callback */
  typedef struct
  {
***************
*** 610,615 ****
--- 615,623 ----

              CHECK_FOR_INTERRUPTS();

+             if (vacuum_page_delay > 0)
+                 usleep(vacuum_page_delay * 1000);
+
              ndeletable = 0;
              page = BufferGetPage(buf);
              opaque = (BTPageOpaque) PageGetSpecialPointer(page);
***************
*** 736,741 ****
--- 744,754 ----
          Buffer        buf;
          Page        page;
          BTPageOpaque opaque;
+
+         CHECK_FOR_INTERRUPTS();
+
+         if (vacuum_page_delay > 0)
+             usleep(vacuum_page_delay * 1000);

          buf = _bt_getbuf(rel, blkno, BT_READ);
          page = BufferGetPage(buf);
*** src/backend/commands/vacuumlazy.c.orig    Thu Sep 25 10:22:58 2003
--- src/backend/commands/vacuumlazy.c    Thu Oct 30 21:07:58 2003
***************
*** 37,42 ****
--- 37,44 ----
   */
  #include "postgres.h"

+ #include <unistd.h>
+
  #include "access/genam.h"
  #include "access/heapam.h"
  #include "access/xlog.h"
***************
*** 88,93 ****
--- 90,97 ----
  static TransactionId OldestXmin;
  static TransactionId FreezeLimit;

+ int        vacuum_page_delay = 0;    /* milliseconds per page */
+

  /* non-export function prototypes */
  static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
***************
*** 228,233 ****
--- 232,240 ----

          CHECK_FOR_INTERRUPTS();

+         if (vacuum_page_delay > 0)
+             usleep(vacuum_page_delay * 1000);
+
          /*
           * If we are close to overrunning the available space for
           * dead-tuple TIDs, pause and do a cycle of vacuuming before we
***************
*** 469,474 ****
--- 476,484 ----

          CHECK_FOR_INTERRUPTS();

+         if (vacuum_page_delay > 0)
+             usleep(vacuum_page_delay * 1000);
+
          tblk = ItemPointerGetBlockNumber(&vacrelstats->dead_tuples[tupindex]);
          buf = ReadBuffer(onerel, tblk);
          LockBufferForCleanup(buf);
***************
*** 799,804 ****
--- 809,817 ----
                      hastup;

          CHECK_FOR_INTERRUPTS();
+
+         if (vacuum_page_delay > 0)
+             usleep(vacuum_page_delay * 1000);

          blkno--;

*** src/backend/utils/misc/guc.c.orig    Sun Oct 19 19:47:54 2003
--- src/backend/utils/misc/guc.c    Thu Oct 30 21:15:46 2003
***************
*** 73,78 ****
--- 73,79 ----
  extern int    CommitDelay;
  extern int    CommitSiblings;
  extern char *preload_libraries_string;
+ extern int    vacuum_page_delay;

  #ifdef HAVE_SYSLOG
  extern char *Syslog_facility;
***************
*** 1188,1193 ****
--- 1189,1203 ----
          },
          &log_min_duration_statement,
          -1, -1, INT_MAX / 1000, NULL, NULL
+     },
+
+     {
+         {"vacuum_page_delay", PGC_USERSET, CLIENT_CONN_STATEMENT,
+             gettext_noop("Sets VACUUM's delay in milliseconds between processing successive pages."),
+             NULL
+         },
+         &vacuum_page_delay,
+         0, 0, 100, NULL, NULL
      },

      /* End-of-list marker */