Skip site navigation (1) Skip section navigation (2)

multiple apaches against single postgres database

From: Honza Novak <kacerr(at)developers(dot)zlutazimnice(dot)cz>
To: pgsql-performance(at)postgresql(dot)org
Subject: multiple apaches against single postgres database
Date: 2007-10-24 12:15:14
Message-ID: 471F3752.8070205@developers.zlutazimnice.cz (view raw or flat)
Thread:
Lists: pgsql-performance
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


Responses

pgsql-performance by date

Next:From: Michal Taborsky - Internet MallDate: 2007-10-24 13:08:20
Subject: Re: multiple apaches against single postgres database
Previous:From: Giulio Cesare SolaroliDate: 2007-10-24 10:44:35
Subject: Finalizing commit taking very long

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group