Thread: Maximum number of client connection supported by Postgres 8.4.6
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
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 Vyaswrote:> please let me know what is the maximum number of concurrent clientThere is no error message "running out of connection" in the code base.
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.
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