Thread: Behaviour patterns on pgsql (7.1.3)
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
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
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