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

Re: multiple apaches against single postgres database

From: Michal Taborsky - Internet Mall <michal(dot)taborsky(at)mall(dot)cz>
To: Honza Novak <kacerr(at)developers(dot)zlutazimnice(dot)cz>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: multiple apaches against single postgres database
Date: 2007-10-24 13:08:20
Message-ID: 471F43C4.5060501@mall.cz (view raw or flat)
Thread:
Lists: pgsql-performance
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>

In response to

Responses

pgsql-performance by date

Next:From: Jean-David BeyerDate: 2007-10-24 13:14:42
Subject: Re: 12 hour table vacuums
Previous:From: Honza NovakDate: 2007-10-24 12:15:14
Subject: multiple apaches against single postgres database

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