X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 60C9ED1E93F for ; Wed, 17 Mar 2004 05:20:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 65179-06 for ; Wed, 17 Mar 2004 01:19:58 -0400 (AST) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 509F3D1E28B for ; Wed, 17 Mar 2004 01:19:57 -0400 (AST) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 1090376; Tue, 16 Mar 2004 21:15:19 -0800 Message-ID: <4057DFA3.90407@joeconway.com> Date: Tue, 16 Mar 2004 21:18:27 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Matthew T. O'Connor" Cc: Tom Lane , pgsql-performance , Josh Berkus Subject: Re: rapid degradation after postmaster restart 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> In-Reply-To: <4057DE34.1080001@zeut.net> Content-Type: multipart/mixed; boundary="------------070505040900090607040909" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/233 X-Sequence-Number: 6089 This is a multi-part message in MIME format. --------------070505040900090607040909 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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 --------------070505040900090607040909 Content-Type: text/plain; name="performance_mod.74.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="performance_mod.74.diff" 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 + #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 + #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 */ --------------070505040900090607040909--