Re: CPU bound at 99% - Mailing list pgsql-performance

On Apr 22, 2008, at 10:31 AM, Bryan Buecking wrote:

> Hi,
>
> I'm running into an performance problem where a Postgres db is running
> at 99% CPU (4 cores) with about 500 concurrent connection doing
> various
> queries from a web application. This problem started about a week ago,
> and has been steadily going downhill. I have been tweaking the
> config a
> bit, mainly shared_memory but have seen no noticeable improvements.
>
> at any given time there is about 5-6 postgres in startup
> (ps auxwww | grep postgres | grep startup | wc -l)
>
> about 2300 connections in idle
> (ps auxwww | grep postgres | idle)
>
> and loads of "FATAL: sorry, too many clients already" being logged.
>
> The server that connects to the db is an apache server using
> persistent
> connections. MaxClients is 2048 thus the high number of connections
> needed. Application was written in PHP using the Pear DB class.

Are you referring to PHP's persistent connections?  Do not use those.
Here's a thread that details the issues with why not:
http://archives.postgresql.org/pgsql-general/2007-08/msg00660.php 
.  Basically, PHP's persistent connections are NOT pooling solution.
Us pgpool or somesuch.

<snip>

>
> max_connections = 2400

That is WAY too high.  Get a real pooler, such as pgpool, and drop
that down to 1000 and test from there.  I see you mentioned 500
concurrent connections.  Are each of those connections actually doing
something?  My guess that once you cut down on the number actual
connections you'll find that each connection can get it's work done
faster and you'll see that number drop significantly.  For example,
our application does anywhere from 200 - 600 transactions per second,
dependent on the time of day/week, and we never need more that 150 to
200 connections (although we do have the max_connections set to 500).

<snip>

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com