From: Michal Taborsky - Internet Mall <michal.taborsky@mall.cz>
To: Honza Novak <kacerr@developers.zlutazimnice.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: multiple apaches against single postgres database
Date: Wed, 24 Oct 2007 15:08:20 +0200
Message-ID: <471F43C4.5060501@mall.cz> (raw)
In-Reply-To: <471F3752.8070205@developers.zlutazimnice.cz>
References: <471F3752.8070205@developers.zlutazimnice.cz>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: michal.taborsky@mall.cz, kacerr@developers.zlutazimnice.cz
Subject: Re: multiple apaches against single postgres database
In-Reply-To: <471F43C4.5060501@mall.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox