Обсуждение: FATAL: Sorry, too many clients already????
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:
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
postgres 13216 0.0 0.0 8436 3644 ? S 00:27 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
postgres 13410 0.0 0.0 8036 2952 ? S 00:27 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
postgres 13474 0.0 0.0 8436 3940 ? S 00:27 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
postgres 13664 0.0 0.0 8420 3796 ? S 00:27 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
postgres 13824 0.0 0.0 8040 3024 ? S 00:27 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
postgres 13890 0.0 0.0 8436 3984 ? S 00:27 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
postgres 2466 0.0 0.0 8432 3764 ? S 00:31 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
postgres 2535 0.0 0.0 8568 3960 ? S 00:31 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
postgres 19026 0.0 0.0 8040 2940 ? S 01:11 0:00 postgres:
dcm1104_root mkim_org 127.0.0.1 idle
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....
Thanks
Dan
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