From: | Jean-Philippe Côté <jean-philippe(dot)cote(at)crt(dot)umontreal(dot)ca> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Extremely irregular query performance |
Date: | 2006-01-11 19:29:03 |
Message-ID: | 20060111192039.12341384122@crt0.crt.umontreal.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
I'm running version 8.1 on a dedicated Sun v20 server (2 AMD x64's)
with 4Gb of RAM. I have recently noticed that the performance of
some more complex queries is extremely variable and irregular.
For example, I currently have a query that returns a small number
of rows (5) by joining a dozen of tables. Below are the running times
obtained by repeatedly lauching this query in psql:
Time: 424.848 ms
Time: 1615.143 ms
Time: 15036.475 ms
Time: 83471.683 ms
Time: 163.224 ms
Time: 2454.939 ms
Time: 188.093 ms
Time: 158.071 ms
Time: 192.431 ms
Time: 195.076 ms
Time: 635.739 ms
Time: 164549.902 ms
As you can see, the performance is most of the time pretty good (less
than 1 second), but every fourth of fifth time I launch the query
the server seems to go into orbit. For the longer running times,
I can see from top that the server process uses almost 100% of
a CPU.
This is rather worrisome, as I cannot be confident of the overall performance
of my application with so much variance in query response times.
I suspect a configuration problem related to the cache mechanism
(shared_buffers? effective_cache_size?), but to be honest I do not know
where to start to diagnose it.
Any help would be greatly appreciated.
Thanks in advance,
J-P
From | Date | Subject | |
---|---|---|---|
Next Message | Bendik Rognlien Johansen | 2006-01-11 19:55:32 | Re: Slow query with joins |
Previous Message | Jim C. Nasby | 2006-01-11 19:02:09 | Re: Postgres8.0 planner chooses WRONG plan |