----- Original Message ----- From: "Jan Wieck" <[EMAIL PROTECTED]>Tom Lane wrote: > Gaetano and a couple of other people did experiments that seemed to show > it was useful. I think we'd want to change the shape of the knob per > later suggestions (sleep 10 ms every N blocks, instead of N ms every > block) but it did seem that there was useful bang for little buck there.
I thought it was "sleep N ms every M blocks".
Have we seen any numbers? Anything at all? Something that gives us a clue by what factor one has to multiply the total time a "VACUUM ANALYZE" takes, to get what effect in return?
I have some time on sunday to do some testing. Is there a patch that I can apply that implements either of the two options? (sleep 10ms every M blocks or sleep N ms every M blocks).
I know Tom posted the original patch that sleept N ms every 1 block (where N is > 10 due to OS limitations). Jan can you post a patch that has just the sleep code in it? Or should it be easy enough for me to cull out of the larger patch you posted?
Sorry for the delay, had to finish some other concept yesterday (will be published soon).
The attached patch adds
vacuum_group_delay_size = 10 (range 1-1000)
vacuum_group_delay_msec = 0 (range 0-1000)and does the sleeping via select(2). It does it only at the same places where Tom had done the usleep() in his hack, so I guess there is still some more to do besides the documentation, before it can be added to 7.4.1. But it should be enough to get some testing done.
Jan
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
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/09 23:39:36
***************
*** 27,32 ****
--- 27,40 ----
#include "storage/smgr.h"
+ /*
+ * Variables for vacuum_group_delay option (in commands/vacuumlazy.c)
+ */
+ extern int vacuum_group_delay_size; /* vacuum N pages */
+ extern int vacuum_group_delay_msec; /* then sleep M msec */
+ extern int vacuum_group_delay_count;
+
+
/* Working state for btbuild and its callback */
typedef struct
{
***************
*** 610,615 ****
--- 618,632 ----
CHECK_FOR_INTERRUPTS();
+ if (vacuum_group_delay_msec > 0)
+ {
+ if (++vacuum_group_delay_count >=
vacuum_group_delay_size)
+ {
+ PG_DELAY(vacuum_group_delay_msec);
+ vacuum_group_delay_count = 0;
+ }
+ }
+
ndeletable = 0;
page = BufferGetPage(buf);
opaque = (BTPageOpaque) PageGetSpecialPointer(page);
***************
*** 736,741 ****
--- 753,769 ----
Buffer buf;
Page page;
BTPageOpaque opaque;
+
+ CHECK_FOR_INTERRUPTS();
+
+ if (vacuum_group_delay_msec > 0)
+ {
+ if (++vacuum_group_delay_count >= vacuum_group_delay_size)
+ {
+ PG_DELAY(vacuum_group_delay_msec);
+ vacuum_group_delay_count = 0;
+ }
+ }
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/09 23:40:13
***************
*** 88,93 ****
--- 88,100 ----
static TransactionId OldestXmin;
static TransactionId FreezeLimit;
+ /*
+ * Variables for vacuum_group_delay option (in commands/vacuumlazy.c)
+ */
+ int vacuum_group_delay_size = 10; /* vacuum N pages */
+ int vacuum_group_delay_msec = 0; /* then sleep M msec */
+ int vacuum_group_delay_count = 0;
+
/* non-export function prototypes */
static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
***************
*** 228,233 ****
--- 235,249 ----
CHECK_FOR_INTERRUPTS();
+ if (vacuum_group_delay_msec > 0)
+ {
+ if (++vacuum_group_delay_count >= vacuum_group_delay_size)
+ {
+ PG_DELAY(vacuum_group_delay_msec);
+ vacuum_group_delay_count = 0;
+ }
+ }
+
/*
* If we are close to overrunning the available space for
* dead-tuple TIDs, pause and do a cycle of vacuuming before we
***************
*** 469,474 ****
--- 485,499 ----
CHECK_FOR_INTERRUPTS();
+ if (vacuum_group_delay_msec > 0)
+ {
+ if (++vacuum_group_delay_count >= vacuum_group_delay_size)
+ {
+ PG_DELAY(vacuum_group_delay_msec);
+ vacuum_group_delay_count = 0;
+ }
+ }
+
tblk = ItemPointerGetBlockNumber(&vacrelstats->dead_tuples[tupindex]);
buf = ReadBuffer(onerel, tblk);
LockBufferForCleanup(buf);
***************
*** 799,804 ****
--- 824,838 ----
hastup;
CHECK_FOR_INTERRUPTS();
+
+ if (vacuum_group_delay_msec > 0)
+ {
+ if (++vacuum_group_delay_count >= vacuum_group_delay_size)
+ {
+ PG_DELAY(vacuum_group_delay_msec);
+ vacuum_group_delay_count = 0;
+ }
+ }
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.2.1
diff -c -b -r1.164.2.1 guc.c
*** src/backend/utils/misc/guc.c 2003/11/07 21:27:50 1.164.2.1
--- src/backend/utils/misc/guc.c 2003/11/09 23:27:49
***************
*** 73,78 ****
--- 73,80 ----
extern int CommitDelay;
extern int CommitSiblings;
extern char *preload_libraries_string;
+ extern int vacuum_group_delay_size;
+ extern int vacuum_group_delay_msec;
#ifdef HAVE_SYSLOG
extern char *Syslog_facility;
***************
*** 1188,1193 ****
--- 1190,1213 ----
},
&log_min_duration_statement,
-1, -1, INT_MAX / 1000, NULL, NULL
+ },
+
+ {
+ {"vacuum_group_delay_msec", PGC_USERSET, RESOURCES,
+ gettext_noop("Sets VACUUM's delay in milliseconds between
processing groups of pages."),
+ NULL
+ },
+ &vacuum_group_delay_msec,
+ 0, 0, 1000, NULL, NULL
+ },
+
+ {
+ {"vacuum_group_delay_size", PGC_USERSET, RESOURCES,
+ gettext_noop("Sets VACUUM's group size for the
vacuum_group_delay_msec option."),
+ NULL
+ },
+ &vacuum_group_delay_size,
+ 10, 1, 1000, NULL, NULL
},
/* End-of-list marker */
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file:
/home/pgsql/CvsRoot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.92
diff -c -b -r1.92 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample 2003/10/08 03:49:38 1.92
--- src/backend/utils/misc/postgresql.conf.sample 2003/11/09 23:04:21
***************
*** 69,74 ****
--- 69,79 ----
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
+ # - Vacuum napping -
+
+ #vacuum_group_delay_size = 10 # range 1-1000 pages ; vacuum this many pages
+ #vacuum_group_delay_msec = 0 # range 0-1000 msec ; then nap this long
+
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
Index: src/include/miscadmin.h
===================================================================
RCS file: /home/pgsql/CvsRoot/pgsql-server/src/include/miscadmin.h,v
retrieving revision 1.134
diff -c -b -r1.134 miscadmin.h
*** src/include/miscadmin.h 2003/09/24 18:54:01 1.134
--- src/include/miscadmin.h 2003/11/09 23:02:03
***************
*** 96,101 ****
--- 96,111 ----
CritSectionCount--; \
} while(0)
+ /*
+ * Macro using select(2) to nap for milliseconds
+ */
+ #define PG_DELAY(_msec) \
+ { \
+ struct timeval _delay; \
+ _delay.tv_sec = (_msec) / 1000; \
+ _delay.tv_usec = ((_msec) % 1000) * 1000; \
+ (void) select(0, NULL, NULL, NULL, &_delay);\
+ }
/*****************************************************************************
* globals.h --
*
---------------------------(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
