Honza Novak napsal(a):
And my questions: 1. Does someone hes similar experience? or clue what to do with it?
Sure, this is considered "normal" behavior for web applications. The solution is to use connection pooling.
2. What is correct setup of postgresql backend serving data for many (4+) apaches? i know that there are connection pooling solutions (pgPool, pgBouncer, or apache 2.2) and i'm thinking about them, but it seems that we have other problem beside that we didn't implement any pooling solution yet.
We use pgpool running on each web server. You can have also the pgpool running on the database server or even a separate server just for that. You'll have to test to see what's best for you.
3. is there a way to somehow log what happened to the postgres server before accident? do you think that logging of all sql statements would help me? if i enable it, what will be the performance overhead?
What you are seeing is called "positive feedback". Once the server reaches a certain performance threshold, it starts to delay the queries, which causes more load, which causes further delay, until everything comes to a halt. Sometimes the system can recover from this, if you have properly setup limits (it will just refuse the requests until it can cool off), sometimes it doesn't. The point is never get over the threshold.
Also, maybe you need better hardware for that kind of load, but since you didn't provide more detail, we can't tell you.
It's quite meaningless to analyze performance once the system is overloaded. You have to analyze before that happens and identify the longest running queries under normal load and try to optimize them. Under heavy load, even the simplest query may seem to be taking long time, but it doesn't necessarily mean there is something wrong with it.
-- Michal Táborský chief systems architect Internet Mall, a.s. <http://www.MALL.cz> ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
