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>
