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
