Re: FATAL: Sorry, too many clients already????

Dan Miller wrote:
> I have a server under very light load -- just me doing some work in php.
> I am getting this error often and don't know why:
>
> postgresql max connections Unable to connect to PostgreSQL server:
> FATAL: Sorry, too many clients already
>
> my orignal postgresql.conf had:
>
> max_connections = 8
> shared_buffers = 16
>
> I changed those to 32 and 64 and it is working so far. But I have a
> bunch of postgresses running now with all of them doing almost nothing:

Nothing to do with your message, but your shared_buffers value is too
low - probably by at least a factor of 100. Perhaps read through this
http://www.powerpostgresql.com/PerfList

> postgres 11747 0.0 0.0 8612 1904 ? S 00:27 0:00 postgres:
> stats buffer process
> postgres 11778 0.0 0.0 7620 1928 ? S 00:27 0:00 postgres:
> stats collector process
> postgres 12950 0.0 0.0 9132 4300 ? S 00:27 0:00 postgres:
> dcm1104_root mkim_org 127.0.0.1 idle
> postgres 13152 0.0 0.0 8436 3748 ? S 00:27 0:00 postgres:
> dcm1104_root mkim_org 127.0.0.1 idle

I'm guessing this was while using pg_pconnect()

> i have tried both pg_connect and pg_pconnect, and this happens either
> way. I don't understand why it keeps starting more processes when there
> are others sitting around doing nothing??? Any thoughts? I am
> tired...Maybe I'm missing something obvious....

Well, pg_pconnect() doesn't share connections between apache backends.
So - if you have 100 active apache backends you'll eventually have (at
least) that many connections. Most will be sat around idle.

Now - if you use pg_connect() then when the script for a particular
request ends, the client will disconnect automatically. However, it's
good practice to disconnect yourself. Sometimes, I've seen good
improvements by doing database work as early as possible, disconnect
then processing the results.

If you really want to limit the total number of connections, you
probably want to use some sort of connection pooling - perhaps look at
pgpool.
--
Richard Huxton
Archonet Ltd