Query performance inconsistant.

From: Matthew Schumacher <matt(dot)s(at)aptalaska(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Query performance inconsistant.
Date: 2006-08-31 16:20:46
Message-ID: 44F70C5E.1050108@aptalaska.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have been having performance problems with my DB so this morning I
added some config to log queries that take more than 250ms. The result
is surprising because some queries will take as long as 10 seconds, but
then you do a explain analyze on them they show that indexes are being
used and they run very fast. Here is an example:

2006-08-31 05:55:39.560 LOG: duration: 3835.182 ms statement: select
acctMessage( <params hidden to protect the innocent> )

But the same query returns this when I explain it:

> > explain analyze select acctMessage( <params hidden to protect the
innocent> );
QUERY PLAN
--------------------------------------------------------------------------------------
Result (cost=0.00..0.03 rows=1 width=0) (actual time=26.797..26.799
rows=1 loops=1)
Total runtime: 36.838 ms

So the question is, why do some queries take a very long time? Sure,
the obvious answer is the machine was busy doing something else, but I
can't find any evidence of that:

05:40:01 AM CPU %user %nice %system %iowait %steal
%idle
05:54:01 AM all 0.58 0.00 0.47 0.50 0.00
98.45
05:55:01 AM all 0.46 0.00 0.31 3.45 0.00
95.79
05:56:01 AM all 0.75 0.00 0.25 4.32 0.00
94.69

05:40:01 AM kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree
kbswpused %swpused kbswpcad
05:54:01 AM 96376 3019292 96.91 6724 2789348 1004000
20 0.00 20
05:55:01 AM 92904 3022764 97.02 7844 2791424 1004000
20 0.00 20
05:56:01 AM 98840 3016828 96.83 9056 2784160 1004000
20 0.00 20

05:40:01 AM tps rtps wtps bread/s bwrtn/s
05:54:01 AM 21.53 4.35 17.18 67.77 344.84
05:55:01 AM 71.61 59.11 12.50 1202.79 283.57
05:56:01 AM 29.22 13.94 15.29 264.18 316.59

Any thoughts on how to track this down? I don't want to go buy a faster
server when I can't confirm that hardware performance is the problem.

Thanks,
schu

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-08-31 16:45:28 Re: Query performance inconsistant.
Previous Message Joshua D. Drake 2006-08-31 16:11:52 Thought provoking piece on NetBSD