From: | Jason Long <mailing(dot)list(at)supernovasoftware(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Query sometimes takes down server |
Date: | 2009-01-16 00:44:37 |
Message-ID: | 496FD875.6000805@supernovasoftware.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am having a serious problem with my application and I hope someone can
help me out.
This could not happen at a worse time as a consulting firm is at my
clients to recommend a new financial system and the inventory
system(which I developed) keeps locking up.
I have a dynamically built query that will periodically(2 times a day
and becoming more frequent) make my server totally unresponsive.
The query that hangs the system is requesting a count(*)
based on some parameters the users selects.
Since I set my timeout to 2 minutes(the only way I have been able to
deal with this so far) I see the offending query in the log.
I took the query from the logs and pasted it into pgAdmin and ran it a
few times.
Sometime is takes 700-900 ms, but others it takes 60-100 seconds.
Other times it never stops(I waited 10 minutes).
If I run 10 times I get
8 less that 1 sec
2 5-10 sec
And maybe 1 in 20 will not complete.
Dev Server specs
1 CPU Xeon 5472 Quad core 3.0 GHz 1600MHz FSB
2x10k Raptor Raid 1
DB 50 mb with a lot of table joins
These queries are being run with nothing else running on the server.
My guess it that the planner is picking an inefficient plan sometimes.
I have not looked into the detail of the explain, and I do see visually
that very different plans are being chosen.
How can I see the plan that was chosen when the time it very high?
Is there a way to print the query plan chosen when the query times out
as well?
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2009-01-16 01:12:17 | Re: Query sometimes takes down server |
Previous Message | Justin Pasher | 2009-01-16 00:05:39 | Re: Autovacuum daemon terminated by signal 11 |