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 073F9D1B4D3 for ; Wed, 17 Mar 2004 19:21:20 +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 35586-09 for ; Wed, 17 Mar 2004 15:21:11 -0400 (AST) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 47CBED1DB53 for ; Wed, 17 Mar 2004 15:21:09 -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 1092020; Wed, 17 Mar 2004 11:16:31 -0800 Message-ID: <4058A4C8.7090108@joeconway.com> Date: Wed, 17 Mar 2004 11:19:36 -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: Andrew Sullivan Cc: pgsql-performance Subject: Re: rapid degradation after postmaster restart References: <4052661D.5060505@joeconway.com> <20040317181115.GA7805@phlogiston.dyndns.org> In-Reply-To: <20040317181115.GA7805@phlogiston.dyndns.org> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/243 X-Sequence-Number: 6099 Andrew Sullivan wrote: > Sorry I haven't had a chance to reply to this sooner. > 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 discussed it later in the thread, but we're adding about 400K rows per hour and deleting most of them after processing (note this is a commercial app, written and maintained by another department -- I can recommend changes, but this late into their release cycle they are very reluctant to change the app). This is 7 x 24 data collection from equipment, so there is no "slow" time to use as a maintenance window. But since the server in question is a test machine, I was able to shut everything off long enough to do a full vacuum -- it took about 12 hours. > 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. I'm (reasonably) sure there is no swapping. Minimum free memory (from top) is about 800 MB, and "vmstat -S" shows no swap-in or swap-out. I've been playing with a version of Jan's performance patch in the past few hours. Based on my simulations, it appears that a 1 ms delay every 10 pages is just about right. The performance hit is negligible (based on overall test time, and cpu % used by the vacuum process). I still have a bit more analysis to do, but this is looking pretty good. More later... Joe