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 */
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
