Hi all,
i'm looking for correct or at least good enough solution for use of
multiple apaches with single postgres database. (apaches are 2.0.x, and
postgres is 8.1.x)
At this moment i'm involved in management of a website where we have
large user load on our web servers. Apaches are set up to be able to
answer 300 requests at the same time and at the moment we have 4
apaches. Eaxh of these apaches handles about 100 requests
simultaneously at average.We have no connection pooling setup between
apaches and postgresql. Postgres accepts up to 200 connections and
normaly there is about 20 used connections (although, there is quite a
lot of traffic between postgres and apaches, queries are simple enough,
so postgres handles it nicely)
But sometimes (i don't know exactly for what reason) some queries gets
stuck (mostly they are inserts or updates, but realy simple) and
postgres is unable to answer in time, which starts a "wave" because
queries from apaches are delayed, which means that there is bigger
number of user request in process, which means more connections to
postgres, until we reach connection limit. But there is something even
worse and that is, that i don't know why postmaster process probably
forks itself ending with lots of (some hunreds) of postmasters running.
When we kill all these postmasters and start postgres again, it ends the
same because apaches probably overloads database server with their
waiting requests. In this case we first need to stop apaches, start
postgres, and then apaches and everything works fine ...... until next
problem, which can occur in hours, days or weeks.
And my questions:
1. Does someone hes similar experience? or clue what to do with it?
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.
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?
I might be asking too much, but i appreciate any help, hint, or
direction what to explore.
Thanks, i'm looking forward for answers.
Honza
