|
Dave: Thinks for the thought, but I’m not sure
how to fix that. I’m going to increase the shared memory pages to 5K as soon
as my latest vacuum finishes to see if that helps. AP From: Dave Cramer
[mailto:pg@xxxxxxxxxxxxx] On 2-May-07, at 11:24 AM,
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. 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
|
