Is there a limit to the number of database connections from a single machine in PostgreSQL? [closed]

Posted on

Question :

We are planning on setting up multiple front ends behind a load balancer.

I think our database can handle the load because I’m currently measuring a database TPS of about 450 for approximately 42000 web requests (93000 transactions).

Our requirements are 278 messages/second average for the front end. I can only get around 92 non-idle queries running in the database, at a given time, and I was wondering if there’s some limit to the number of connections from a single machine.

I have tried upping various connection pools and thread pools. I was able to up the query count from 33 to 92, but I’m unable to get higher. I am not even sure how many queries from a single machine are reasonable. I read connections should be (2 * core_count) + effective_spindle_count.

I have been getting errors from the front end stating that it failed in getting a connection (“The connection attempt failed”), but I don’t know yet if this is a failure from the front end (by hitting a limit) or the database (a configuration on the database).

I believe the max # of connections for the database is 2000 last I checked.

Is there another limit to the number of connections the database will accept from one machine?

The database is running Linux 2.6.18 on x64.

I guess I’m also wondering if the number of connections is limiting. I don’t think it is, but we did get the connection attempt failures. The front end is running at 300% CPU (8 processors) and 25% memory. I would think we could get more bang out of both our front end and our database, but with two test servers, we normally get 250 tests/second (maxed out at 254/s for one server and 334/sec for the other ).

Answer :

You probably don’t want more than 100 or so concurrent queries running on the DB. Use a connection pooler like pgbouncer if you need one and your app doesn’t have one built-in.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Setting a high max_connections won’t make things go faster. It’ll make things go slower.

Leave a Reply

Your email address will not be published. Required fields are marked *