Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E06449FBC4B for ; Wed, 2 May 2007 15:18:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 78767-08 for ; Wed, 2 May 2007 15:17:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from beaver.fastcrypt.com (beaver.fastcrypt.com [64.7.141.17]) by postgresql.org (Postfix) with ESMTP id 6E42C9FB9C6 for ; Wed, 2 May 2007 15:18:00 -0300 (ADT) Received: from [192.168.1.14] (dcdsl.ebox.com [64.7.143.116]) by beaver.fastcrypt.com (Postfix) with ESMTP id F0F7F1580653; Wed, 2 May 2007 14:24:03 -0400 (EDT) In-Reply-To: <24F6514DFA6FD544BCA10A5C9A47E761028D86A8@rtpwexc06.RCC_NT.RTI.ORG> References: <24F6514DFA6FD544BCA10A5C9A47E761028D86A8@rtpwexc06.RCC_NT.RTI.ORG> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: multipart/alternative; boundary=Apple-Mail-7-106697242 Message-Id: <45815F72-41A6-4A62-8C12-E77ECF613309@fastcrypt.com> Cc: From: Dave Cramer Subject: Re: Intermitent slow queries Date: Wed, 2 May 2007 14:17:56 -0400 To: "Parks, Aaron B." X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/11 X-Sequence-Number: 24401 --Apple-Mail-7-106697242 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed On 2-May-07, at 11:24 AM, Parks, Aaron B. wrote: > My pg 8.1 install on an AMD-64 box (4 processors) with 9 gigs of =20 > ram running RHEL4 is acting kind of odd and I thought I would see =20 > if anybody has any hints. > > > > I have Java program using postgresql-8.1-409.jdbc3.jar to connect =20 > over the network. In general it works very well. I have run batch =20= > updates with several thousand records repeatedly that has worked fine. > > > > The Program pulls a summation of the DB and does some processing =20 > with it. It starts off wonderfully running a query every .5 =20 > seconds. Unfortunately, after a while it will start running =20 > queries that take 20 to 30 seconds. > > > > Looking at the EXPLAIN for the query no sequential scans are going =20 > on and everything has an index that points directly at its search =20 > criteria. > > > > Example: > > > > Select sum(whatever) from a inner join b on a.something=3Db.something =20= > WHERE b.day=3D1 and b.hour=3D1 > > Select sum(whatever) from a inner join b on a.something=3Db.something =20= > WHERE b.day=3D1 and b.hour=3D2 > > Select sum(whatever) from a inner join b on a.something=3Db.something =20= > WHERE b.day=3D1 and b.hour=3D3 > > . > > . > > Select sum(whatever) from a inner join b on a.something=3Db.something =20= > WHERE b.day=3D1 and b.hour=3D23 > > Select sum(whatever) from a inner join b on a.something=3Db.something =20= > WHERE b.day=3D1 and b.hour=3D24 > > Select sum(whatever) from a inner join b on a.something=3Db.something =20= > WHERE b.day=3D2 and b.hour=3D1 > > Select sum(whatever) from a inner join b on a.something=3Db.something =20= > WHERE b.day=3D2 and b.hour=3D2 > > . > > . > > . > > > > > > This query runs fine for a while (up to thousands of times). But =20 > what happens is that it starts to have really nasty pauses when you =20= > switch the day condition. After the first query with the day it =20 > runs like a charm for 24 iterations, then slows back down again > > > > My best guess was that an index never finished running, but REINDEX =20= > on the table (b in this case) didn=92t seem to help. I'd think it has more to do with caching data. The first query caches =20= the days data, then the next day's data has to be read from disk. > > > Ideas? > > > > AP > > --Apple-Mail-7-106697242 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=WINDOWS-1252
On 2-May-07, at = 11:24 AM, Parks, Aaron B. wrote:

My pg 8.1 install = on an AMD-64 box (4 processors) with 9 gigs of ram running RHEL4 is = acting kind of odd and I thought I would see if anybody has any = hints.=A0

I have Java program using = postgresql-8.1-409.jdbc3.jar to connect over the network.=A0 In general = it works very well.=A0 I have run batch updates with several thousand = records repeatedly that has worked fine.

=A0

The Program pulls a summation of the DB and does = some processing with it. =A0It starts off wonderfully running a query = every .5 seconds.=A0 Unfortunately, after a while it will start running = queries that take 20 to 30 seconds.

=A0

Looking at the EXPLAIN for the query no = sequential scans are going on and everything has an index that points = directly at its search criteria.

=A0

Example:

=A0

Select sum(whatever) from a inner join b on = a.something=3Db.something WHERE b.day=3D1 and b.hour=3D1Select = sum(whatever) from a inner join b on a.something=3Db.something WHERE = b.day=3D1 and b.hour=3D2

Select sum(whatever) from a inner join b on = a.something=3Db.something WHERE b.day=3D1 and b.hour=3D3..Select = sum(whatever) from a inner join b on a.something=3Db.something WHERE = b.day=3D1 and b.hour=3D23

Select sum(whatever) from a inner join b on = a.something=3Db.something WHERE b.day=3D1 and b.hour=3D24Select = sum(whatever) from a inner join b on a.something=3Db.something WHERE = b.day=3D2 and b.hour=3D1

Select sum(whatever) from a inner join b on = a.something=3Db.something WHERE b.day=3D2 and b.hour=3D2...=A0

=A0

This query runs fine for a while (up to = thousands of times). But what happens is that it starts to have really = nasty pauses when you switch the day condition.=A0 After the first query = with the day it runs like a charm for 24 iterations, then slows back = down again=A0

My best guess was that an index never finished = running, but REINDEX on the table (b in this case) didn=92t seem to = help.

I'd think it has = more to do with caching data. The first query caches the days data, then = the next day's data has to be read from disk.

Ideas?

AP



<= /HTML>= --Apple-Mail-7-106697242--