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 D085AD1B580 for ; Wed, 17 Mar 2004 18:11:33 +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 10328-09 for ; Wed, 17 Mar 2004 14:11:24 -0400 (AST) Received: from fep04-mail.bloor.is.net.cable.rogers.com (fep04-mail.bloor.is.net.cable.rogers.com [66.185.86.74]) by svr1.postgresql.org (Postfix) with ESMTP id C6514D1B4D3 for ; Wed, 17 Mar 2004 14:11:16 -0400 (AST) Received: from phlogiston.dydns.org ([65.49.121.67]) by fep04-mail.bloor.is.net.cable.rogers.com (InterMail vM.5.01.05.12 201-253-122-126-112-20020820) with ESMTP id <20040317181039.NESF3598.fep04-mail.bloor.is.net.cable.rogers.com@phlogiston.dydns.org> for ; Wed, 17 Mar 2004 13:10:39 -0500 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id BA8A23DC8; Wed, 17 Mar 2004 13:11:15 -0500 (EST) Date: Wed, 17 Mar 2004 13:11:15 -0500 From: Andrew Sullivan To: pgsql-performance Subject: Re: rapid degradation after postmaster restart Message-ID: <20040317181115.GA7805@phlogiston.dyndns.org> Mail-Followup-To: pgsql-performance References: <4052661D.5060505@joeconway.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4052661D.5060505@joeconway.com> User-Agent: Mutt/1.5.5.1+cvs20040105i X-Authentication-Info: Submitted using SMTP AUTH LOGIN at fep04-mail.bloor.is.net.cable.rogers.com from [65.49.121.67] using ID at Wed, 17 Mar 2004 13:10:39 -0500 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/241 X-Sequence-Number: 6097 Sorry I haven't had a chance to reply to this sooner. On Fri, Mar 12, 2004 at 05:38:37PM -0800, Joe Conway wrote: > The problem is this: the application runs an insert, that fires off a > trigger, that cascades into a fairly complex series of functions, that > do a bunch of calculations, inserts, updates, and deletes. Immediately > after a postmaster restart, the first insert or two take about 1.5 > minutes (undoubtedly this could be improved, but it isn't the main > issue). However by the second or third insert, the time increases to 7 - > 9 minutes. Restarting the postmaster causes the cycle to repeat, i.e. > the first one or two inserts are back to the 1.5 minute range. The vacuum delay stuff that you're working on may help, but I can't really believe it's your salvation if this is happening after only a few minutes. No matter how much you're doing inside those functions, you surely can't be causing so many dead tuples that a vacuum is necessary that soon. Did you try not vacuuming for a little while to see if it helps? I didn't see it anywhere in this thread, but are you quite sure that you're not swapping? Note that vmstat on multiprocessor Solaris machines is not notoriously useful. You may want to have a look at what the example stuff in the SE Toolkit tells you, or what you get from sar. I believe you have to use a special kernel setting on Solaris to mark shared memory as being ineligible for swap. A -- Andrew Sullivan | ajs@crankycanuck.ca This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie