From: Joe Conway <mail@joeconway.com>
To: Matthew T. O'Connor <matthew@zeut.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: Josh Berkus <josh@agliodbs.com>
Subject: Re: rapid degradation after postmaster restart
Date: Tue, 16 Mar 2004 21:18:27 -0800
Message-ID: <4057DFA3.90407@joeconway.com> (raw)
In-Reply-To: <4057DE34.1080001@zeut.net>
References: <4052661D.5060505@joeconway.com>
<14884.1079150555@sss.pgh.pa.us>
<4055F343.6070600@joeconway.com>
<405614CA.5030101@zeut.net>
<405689A4.4080406@joeconway.com>
<16735.1079414723@sss.pgh.pa.us>
<4057D8BD.4080902@joeconway.com>
<4057DE34.1080001@zeut.net>
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
Attachments:performance_mod.74.diff (text/plain, 4.9K) download
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 */
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mail@joeconway.com, matthew@zeut.net, tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: rapid degradation after postmaster restart
In-Reply-To: <4057DFA3.90407@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox