Maximum number of client connection supported by Postgres 8.4.6

Hello

2011/7/21 Jenish Vyas <jenishvyas@gmail.com>:

Hi All,

please let me know what is the maximum number of concurrent client
connection supported by Postgres 8.4.6

max_connections = ????

For my database,

If I am running the test for more then 1000 concurrent active user it is
showing me error “running out of connection”

I have set max_connections = 1200.

Thanks & regards,
JENISH VYAS

this is just note - maximum connections should be related to number
of processors on the server - optimum is about 20-30. Very high number
of current concurrent connections is very ineffective - there is very
high content switch overhead. If you need it, the use some connection
pool sw like pgPool or pgBouncer

http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling#Connection_Pooling_and_Acceleration

Regards

Pavel Stehule

Jenish Vyaswrote:

please let me know what is the maximum number of concurrent client

connection supported by Postgres

8.4.6

max_connections = ????

For my database,

If I am running the test for more then 1000 concurrent active user it

is showing me error "running out

of connection"

I have set max_connections = 1200.

There is no error message "running out of connection" in the code base.
Could you quote the exact message?

Maybe you are hitting a kernel resource limit, see
http://www.postgresql.org/docs/8.4/static/kernel-resources.html
In that case you might have to increase SEMMNS or SEMMNI.

Yours,
Laurenz Albe

Hi All,

Exact Error Message is as follow..

[ERROR] Error getting DB connection: The connection attempt failed.
[ERROR] Action commit error: Out of database connections.

This is the output I am getting form application server, On database end I
am getting nothing.

plz suggest.
If possible guide me how to calculate max_connections based on available
hardware.

Thanks & regards,
JENISH VYAS

On Thu, Jul 21, 2011 at 4:28 PM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:

Jenish Vyas wrote:
[unexpectedly runs out of connections]

Exact Error Message is as follow..

[ERROR] Error getting DB connection: The connection attempt failed.
[ERROR] Action commit error: Out of database connections.

This is the output I am getting form application server, On database

end I am getting nothing.

plz suggest.

Please try not to top-post.

Both of those error messages are not form PostgreSQL, so they don't help
much.
You might get more in the database log if you set
log_connections = on
in postgresql.conf and reload (make sure that log_min_messages is
"fatal" or lower).

It also wouldn't hurt to try and count the actual connections when you
hit the
problem (SELECT count(*) FROM pg_stat_activity) and check if that's
close to
max_connections.

Have you considered the possibility that the limit and the error do not
originate in that database, but in the application server?

If possible guide me how to calculate max_connections based on

available hardware.

It is almost unlimited on any hardware. That does not mean that things
will
perform well beyond a certain limit. The limiting factor I mentioned is
the
operating system, and these limits can usually be adjusted.

Yours,
Laurenz Albe

On 22 Červenec 2011, 10:29, Albe Laurenz wrote:

Have you considered the possibility that the limit and the error do not
originate in that database, but in the application server?

If the max_connections is 1200 and you get that error with 1000 of them,
it's probably a problem with a connection pool in your application server
(not such whit platform you're working on).

If possible guide me how to calculate max_connections based on

available hardware.

It is almost unlimited on any hardware. That does not mean that things
will
perform well beyond a certain limit. The limiting factor I mentioned is
the
operating system, and these limits can usually be adjusted.

Theoretically it's unlimited, in practice the optimal value is much lower.
The general rule of thmub is usually

max_connections = number of cores + number of drives

so with a 4-core CPU and 10 drives you'll get about 14 connections. That's
very rough - it might be a bit higher, but I don't expect to grow it above
30.

So having 1200 connections is a bit extreme - if the connections are
active all the time (not idle, doing something), the overhead of managing
them will be severe. Don't forget each connection is equal to a separate
process, so it's not exactly cheap.

Do you really need that number of connections?

What I'd suggest is to run a series of pgbench tests with various "-c"
values (10, 20, 30, ...) to get some basic starting point. Then I'd set
pgbouncer with this number of db connections and 1000 of client
connections, and pool_mode=transaction.

Tomas