X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 53685D1B550 for ; Thu, 28 Aug 2003 09:29:00 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 85284-10 for ; Thu, 28 Aug 2003 06:28:30 -0300 (ADT) Received: from mta06-svc.ntlworld.com (mta06-svc.ntlworld.com [62.253.162.46]) by svr1.postgresql.org (Postfix) with ESMTP id BFF53D1B947 for ; Thu, 28 Aug 2003 06:28:27 -0300 (ADT) Received: from russell ([81.99.189.14]) by mta06-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with SMTP id <20030828092829.KUWY9588.mta06-svc.ntlworld.com@russell> for ; Thu, 28 Aug 2003 10:28:29 +0100 From: "Russell Garrett" To: Subject: Re: Queries sometimes take 1000 times the normal time Date: Thu, 28 Aug 2003 10:28:29 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <3F4E1443.7646.12E864@localhost> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/404 X-Sequence-Number: 3220 >> The web site queries will jump up one or two orders of magnitude (I >> have seen a normally 100ms query take in excess of 30 seconds) in >> duration at seemingly random points. It's not always when the >> transactions are committing, and it doesn't seem to be during >> checkpointing either. The same thing happens with WAL switched off. >> It appears to happen the first time the query runs after a while. If >> I run the same query immediately afterwards, it will take the normal >> amount of time. > > Looks like it got flushed out of every type of cache and IO scheduler > could not deliver immediately because of other loads... Yeah, I wasn't sure what (or how) Postgres caches. The db server does have 2Gb of memory, but then again the database amounts to more than 2Gb, so it's fairly possible it's getting pushed out of cache. It's also fairly possible that it's not tuned completely optimally. I wonder if FreeBSD/kernel 2.6 would perform better in such a situation... Russ