Search Postgresql Archives

Query performance inconsistant.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux