If memory serves, the problem is that you actually sleep 10ms even when you set it to 1. One of the thing changed in Jan's later patch was the ability to specify how many pages to work on before sleeping, rather than how long to sleep inbetween every 1 page. You might be able to do a quick hack and have it do 10 pages or so before sleeping.
I thought I remembered something about that.
It turned out to be less difficult than I first thought to extract the vacuum delay stuff from Jan's performance patch. I haven't yet tried it out, but it's attached in case you are interested. I'll report back once I have some results.
Joe
Index: src/backend/access/nbtree/nbtree.c
===================================================================
RCS file: /home/pgsql/CvsRoot/pgsql-server/src/backend/access/nbtree/nbtree.c,v
retrieving revision 1.106
diff -c -b -r1.106 nbtree.c
*** src/backend/access/nbtree/nbtree.c 2003/09/29 23:40:26 1.106
--- src/backend/access/nbtree/nbtree.c 2003/11/03 17:56:54
***************
*** 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,39 ----
#include "storage/smgr.h"
+ extern int vacuum_page_delay;
+ extern int vacuum_page_groupsize;
+ extern int vacuum_page_groupcount;
+
+
/* Working state for btbuild and its callback */
typedef struct
{
***************
*** 610,615 ****
--- 617,631 ----
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 0;
+ usleep(vacuum_page_delay * 1000);
+ }
+ }
+
ndeletable = 0;
page = BufferGetPage(buf);
opaque = (BTPageOpaque) PageGetSpecialPointer(page);
***************
*** 736,741 ****
--- 752,768 ----
Buffer buf;
Page page;
BTPageOpaque opaque;
+
+ CHECK_FOR_INTERRUPTS();
+
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 0;
+ usleep(vacuum_page_delay * 1000);
+ }
+ }
buf = _bt_getbuf(rel, blkno, BT_READ);
page = BufferGetPage(buf);
Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /home/pgsql/CvsRoot/pgsql-server/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.32
diff -c -b -r1.32 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 2003/09/25 06:57:59 1.32
--- src/backend/commands/vacuumlazy.c 2003/11/03 17:57:27
***************
*** 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,99 ----
static TransactionId OldestXmin;
static TransactionId FreezeLimit;
+ int vacuum_page_delay = 0; /* milliseconds per page group */
+ int vacuum_page_groupsize = 10; /* group size */
+ int vacuum_page_groupcount = 0; /* current group size count */
+
/* non-export function prototypes */
static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
***************
*** 228,233 ****
--- 234,248 ----
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 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 ****
--- 484,498 ----
CHECK_FOR_INTERRUPTS();
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 0;
+ usleep(vacuum_page_delay * 1000);
+ }
+ }
+
tblk = ItemPointerGetBlockNumber(&vacrelstats->dead_tuples[tupindex]);
buf = ReadBuffer(onerel, tblk);
LockBufferForCleanup(buf);
***************
*** 799,804 ****
--- 823,837 ----
hastup;
CHECK_FOR_INTERRUPTS();
+
+ if (vacuum_page_delay > 0)
+ {
+ if (++vacuum_page_groupcount >= vacuum_page_groupsize)
+ {
+ vacuum_page_groupcount = 0;
+ usleep(vacuum_page_delay * 1000);
+ }
+ }
blkno--;
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/pgsql/CvsRoot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.164
diff -c -b -r1.164 guc.c
*** src/backend/utils/misc/guc.c 2003/10/18 22:59:09 1.164
--- src/backend/utils/misc/guc.c 2003/11/03 21:10:52
***************
*** 70,78 ****
--- 70,80 ----
extern int PreAuthDelay;
extern int AuthenticationTimeout;
extern int CheckPointTimeout;
extern int CommitDelay;
extern int CommitSiblings;
extern char *preload_libraries_string;
+ extern int vacuum_page_delay;
+ extern int vacuum_page_groupsize;
#ifdef HAVE_SYSLOG
extern char *Syslog_facility;
***************
*** 1188,1193 ****
--- 1199,1222 ----
},
&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
+ },
+
+ {
+ {"vacuum_page_groupsize", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Sets VACUUM's delay group size."),
+ NULL
+ },
+ &vacuum_page_groupsize,
+ 10, 1, 1000, NULL, NULL
},
/* End-of-list marker */
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
