Обсуждение: 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

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