Thread: Behaviour patterns on pgsql (7.1.3)

Behaviour patterns on pgsql (7.1.3)

From
Denis Braekhus
Date:
Hi all,

Anyone experienced similar patterns on their Web-DB setups ?

Short setup description :

X frontend webservers (Apache 1.3.x) with php and mod_perl dynamic content.
1 backend db server (Postgresql 7.1.3).

The webservers are _very_ busy, but send only a limited amount of their
requests to the backend (most requests are served using static content).

Every now and then postgresql gets "too busy" and these patterns emerge :

- some connections are just idling and not being reaped (presumably either the
browser/client or the apache webserver timed out the request)
- these connections stack up to a point where the other "live" queries are
handled so inefficiently that pgsql enters a "bad downwards spiral".
(Meaning it cannot serve enough requests fast enough due to high load. The
number of connections increases, giving again more load, and so on ... )

I do VACUUM every night, and with Postgresql 7.1.3 cannot VACUUM during daily
usage.

I know upgrading to the 7.2 series is a sane way out (possibly) of this, but
due to development reasons, cannot do that right now (tm). I have to wait at
least another month or so..

Any clues to possible fixes / workarounds ?
Timeout settings someplace ?

BTW: The server has 1GB RAM, which seems sufficient to loading the entire DB
dataset into memory, disk issues should not be a performance hindering factor
here.

cheers!
--
Denis

Re: Behaviour patterns on pgsql (7.1.3)

From
Tom Lane
Date:
Denis Braekhus <denis@startsiden.no> writes:
> Every now and then postgresql gets "too busy" and these patterns emerge :

> - some connections are just idling and not being reaped (presumably either the
> browser/client or the apache webserver timed out the request)
> - these connections stack up to a point where the other "live" queries are
> handled so inefficiently that pgsql enters a "bad downwards spiral".
> (Meaning it cannot serve enough requests fast enough due to high load. The
> number of connections increases, giving again more load, and so on ... )

This strikes me as a fault on the client side, not in Postgres --- to
wit, poor connection management.  It is not Postgres' job to kill idle
client connections.

But having said that, I do not think that idle connections per se would
cause any performance degradation on the server side.  Perhaps they also
have open transactions that are holding locks that other queries need?
That again is really a client-side bug...

            regards, tom lane

Re: Behaviour patterns on pgsql (7.1.3)

From
Denis Braekhus
Date:
On Wednesday 30 October 2002 20:09, Tom Lane wrote:
> This strikes me as a fault on the client side, not in Postgres --- to
> wit, poor connection management.  It is not Postgres' job to kill idle
> client connections.

I suppose you are right. The thing is we have never used persistant
connections (tried to once, learned from the mistake..) and all the mod_php
scripts (4.1.x version of PHP) do explicitly close their connections..

This might definitely be an apache problem of some kind. It seems to be less
grave if I drop the MaxRequestsperChild config value for apache to an abysmal
100.. (Should _not_ be necessary, and is affecting our performance..)

> But having said that, I do not think that idle connections per se would
> cause any performance degradation on the server side.  Perhaps they also
> have open transactions that are holding locks that other queries need?
> That again is really a client-side bug...

Well, believe me, it does.. It only begins to be a real _problem_ not a
symptom after 10 or so idle connections, but indeed postgresql drops in
performance with too many open connections.

I figured this part of the problem could possibly be lack of memory for the
system, and I think I need to play with the postgresql.conf settings.

I checked back in the archive for hints and tips, and found some, the box has
1GB of RAM, so I should be able to find some nice balance for it !

Regards
--
Denis Braekhus - ABC Startsiden AS
http://www.startsiden.no