Calling PostgreSQL Experts
Entrep
I have a pretty decent server - Dual Xeon 2.8 with 3GB RAM.
I use it to run two sites based on PGSQL. Current load is always around
0.1 or less.
However, sometimes I get the following error:
> Unable to connect to PostgreSQL server: FATAL: sorry, too many clients
> already
My postgresql.conf currently has:
> max_connections = 300
>
> ...
>
> # - Memory -
>
> shared_buffers = 183500 # min 16 or max_connections*2,
> 8KB each
> temp_buffers = 80000 # min 100, 8KB each
> #max_prepared_transactions = 5 # can be 0 or more
> # note: increasing max_prepared_transactions costs ~600 bytes of shared
> memory
> # per transaction slot, plus lock space (see
> max_locks_per_transaction).
> #work_mem = 1024 # min 64, size in KB
> #maintenance_work_mem = 16384 # min 1024, size in KB
> #max_stack_depth = 2048 # min 100, size in KB
>
> # - Free Space Map -
>
> #max_fsm_pages = 20000 # min max_fsm_relations*16, 6
> bytes each
> #max_fsm_relations = 1000 # min 100, ~70 bytes each
>
> # - Kernel Resource Usage -
>
> #max_files_per_process = 1000 # min 25
> #preload_libraries = ''
I am positive all this shouldnt be happening with 3GB RAM - how can I
optimize the PGSQL install better to make use of the 3GB?
--
Entrep
------------------------------------------------------------------------
Entrep's Profile: http://www.dbtalk.net/m23
View this thread: http://www.dbtalk.net/t291582
Laurenz Albe
> I use it to run two sites based on PGSQL. Current load is always around
> 0.1 or less.
>
> However, sometimes I get the following error:
>
>> Unable to connect to PostgreSQL server: FATAL: sorry, too many clients
>> already
>
> My postgresql.conf currently has:
>
>> max_connections = 300
How many client processes are there?
To see these, find out the PID of the postmaster, then
ps -ef|grep <pid>
on the database server.
Maybe there are many clients that are idle, cause no system load and just
'forgot' to disconnect.
Yours,
Laurenz Albe
Andreas Kretschmer
>> My postgresql.conf currently has:
>>
>>> max_connections = 300
> How many client processes are there?
> To see these, find out the PID of the postmaster, then
> ps -ef|grep <pid>
> on the database server.
other / better solution:
psql <your_db> -c "select * from pg_stat_activity"
end
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Entrep
Thanks for the response.
I think there is too many idle clients.
Is there a setting in PGSQL that changes how long a client will be
inactive before it closes the connection? Or does it not work like
that?
I am pretty sure the code on the site is fine, and the sites should get
more than 300 connections at once.
Justin L. Kennedy
> Is there a setting in PGSQL that changes how long a client will be
> inactive before it closes the connection? Or does it not work like
> that?
What kind of client? If it is PHP, you can use pg_connect which
automatically closes the connection when the page is done generating. We
had similar problems when someone used pg_pconnect which couldn't be
closed by any other method than logging into the server and killing the
idle processes.
--
Justin Kennedy
Entrep
Justin L. Kennedy Wrote:
>
> What kind of client? If it is PHP, you can use pg_connect which
> automatically closes the connection when the page is done generating.
> We
> had similar problems when someone used pg_pconnect which couldn't be
> closed by any other method than logging into the server and killing
> the
> idle processes.
>
> --
> Justin Kennedy
Cheers for that, yes it is PHP
Sorry, I really dont know much about PostgreSQL (my websites devs chose
to use it, not me) how do I use pg_connect to do that? Could I setup a
cron?
Ringhio
> I think there is too many idle clients.
Why don't take a look to pg_pool? Pooling connections from webserver to
dbserver.
Bye
Daniel
Ringhio
> Why don't take a look to pg_pool? Pooling connections from webserver to
> dbserver.
Sorry, pgpool (without _).
http://pgpool.projects.postgresql.org/
Daniel