I'm not an expert, but I've been hunting down a killer performance problem for a while now. It seems this may be the cause.
At peak load, our database slows to a trickle. The CPU and disk utilization are normal - 20-30% used CPU and disk performance good.
For a peak load 20-30% used CPU this mean you reached your IO bottleneck.
All of our "postgres" processes end up in the "semwai" state - seemingly waiting on other queries to complete. If the system isn't taxed in CPU or disk, I have a good feeling that this may be the cause. I didn't know that planning queries could create such a gridlock, but based on Mr Pink's explanation, it sounds like a very real possibility.
We're running on SELECT's, and the number of locks on our "high traffic" tables grows to the hundreds. If it's not the SELECT locking (and we don't get that many INSERT/UPDATE on these tables), could the planner be doing it?
At peak load (~ 1000 queries/sec on highest traffic table, all very similar), the serialized queries pile up and essentially create a DoS on our service - requiring a restart of the PG daemon. Upon stop & start, it's back to normal.
Give us informations on this queries, a explain analyze could be a good start point.
I've looked at PREPARE, but apparently it only lasts per-session - that's worthless in our case (web based service, one connection per data-requiring connection).
Trust me the PREPARE is not doing miracle in shenarios like yours . If you use postgres in a web service environment what you can use is a connection pool ( look for pgpoll IIRC ), if you use a CMS then try to enable the cache in order to avoid to hit the DB for each request.
Regards Gaetano Mendola
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
