Re: Intermitent slow queries - Mailing list pgsql-performance

Ron:

I'm not sure how the JVM would really affect the issue as it is on a
Windows box connecting remotely.  As indicated the PG Server itself has
9 gigs of ram and it never goes up above 1.8 total usage.

If the PG driver is doing something funny (IE waiting to send requests)
that's way out past my ability to fix it, so I will hope that's not it.

You can see the CPU slamming doing the queries, then after a while it
just stops and all I get is tiny little blips on the usage.

AP

-----Original Message-----
From: Ron [mailto:rjpeace@earthlink.net]
Sent: Wednesday, May 02, 2007 2:55 PM
To: Parks, Aaron B.
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Intermitent slow queries

Among other possibilities, there's a known problem with slow memory
leaks in various JVM's under circumstances similar to those you are
describing.
The behavior you are describing is typical of this scenario.  The
increasing delay is caused by longer and longer JVM garbage
collection runs as java attempts to reclaim enough memory from a
smaller and smaller universe of available memory.

The fastest test, and possible fix, is to go and buy more RAM.  See
if 16MB of RAM, heck even 10MB, makes the problem go away or delays
it's onset.  If so, there's good circumstantial evidence that you are
being bitten by a slow memory leak; most likely in the JVM.

Cheers,
Ron Peacetree


At 11:24 AM 5/2/2007, 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.
>
>I have Java program using postgresql-8.1-409.jdbc3.jar to connect
>over the network.  In general it works very well.  I have run batch
>updates with several thousand records repeatedly that has worked fine.
>
>The Program pulls a summation of the DB and does some processing
>with it.  It starts off wonderfully running a query every .5
>seconds.  Unfortunately, after a while it will start running queries
>that take 20 to 30 seconds.
>
>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.
>
>Example:
>
>Select sum(whatever) from a inner join b on a.something=b.something
>WHERE b.day=1 and b.hour=1
>Select sum(whatever) from a inner join b on a.something=b.something
>WHERE b.day=1 and b.hour=2
>Select sum(whatever) from a inner join b on a.something=b.something
>WHERE b.day=1 and b.hour=3
>.
>.
>Select sum(whatever) from a inner join b on a.something=b.something
>WHERE b.day=1 and b.hour=23
>Select sum(whatever) from a inner join b on a.something=b.something
>WHERE b.day=1 and b.hour=24
>Select sum(whatever) from a inner join b on a.something=b.something
>WHERE b.day=2 and b.hour=1
>Select sum(whatever) from a inner join b on a.something=b.something
>WHERE b.day=2 and b.hour=2
>.
>.
>.
>
>
>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.  After the first query with the day it
>runs like a charm for 24 iterations, then slows back down again
>
>My best guess was that an index never finished running, but REINDEX
>on the table (b in this case) didn't seem to help.
>
>Ideas?
>
>AP