Re: rapid degradation after postmaster restart - Mailing list pgsql-performance

Matthew T. O'Connor wrote:
> 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 */