Re: Performance features the 4th - Mailing list pgsql-hackers

Matthew T. O'Connor wrote:

> ----- Original Message -----
> From: "Jan Wieck" <JanWieck@Yahoo.com>
>> 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.                                  #
#================================================== JanWieck@Yahoo.com #
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 --                                                             *