2

I understand that if you don't pool database connections at all, then you bear the overhead of establishing a new database connection every time you need to query the database, which will make your app slow, and that you save that overhead by pooling.

If you set your max pool size too small - let's say, to 1 connection max - then the downside of that is your app has to share that single database connection for all requests, and requests will have to wait for the previous one to finish with the database connection before they can fetch it from the pool and re-use it.

But what if you just set your max pool size really big, like 1000 connections max. Shouldn’t it then favour re-using connections over establishing new ones, and only max-out the pool size if it actually needs to in order to service demand?

What's the down side of making connection pools bigger than they need to be to service the app's demand, assuming unused connections are closed / removed from the pool after an idle timeout?

closed as too broad by Erik Darling, LowlyDBA, McNets, Mr.Brownstone, Marco Nov 9 '17 at 10:48

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • In my app, I have only a single app server and I'm talking about bumping the max connections from 10 up to 100 - but I'm interested in the answer more generally, too. – joshua.paling Nov 8 '17 at 11:37
  • Joshua, in the future you can just edit your post instead of commenting on it if you want to add information. It seems like you might benefit from revisiting the connection pooling documentation and coming back if you have a more specific issue/question. – LowlyDBA Nov 8 '17 at 14:23
  • I responded to a comment asking about my specific situation, that was subsequently deleted. It was great to get clear answers to my specific questions below. – joshua.paling Nov 8 '17 at 21:37
3

I understand that if you don't pool database connections at all, then you bear the overhead of establishing a new database connection every time you need to query the database, which will make your app slow, and that you save that overhead by pooling.

Generally, but it depends... If it's a long lived process or connection, a dedicated connection can be more performant and less overhead.

If you set your max pool size too small - let's say, to 1 connection max - then the downside of that is your app has to share that single database connection for all requests, and requests will have to wait for the previous one to finish with the database connection before they can fetch it from the pool and re-use it.

They could also get an error. I wouldn't assume it'll multiplex perfectly. This is why setting the maximum value too low can have a detrimental effect (also setting it too high!).

But what if you just set your max pool size really big, like 1000 connections max. Shouldn’t it then favour re-using connections over establishing new ones, and only max-out the pool size if it actually needs to in order to service demand?

I'd say, if 1000 concurrent connections doesn't break anything in your app/database processing then go for it. Realistically, unless this app becomes extremely popular or there is an issue in the app/database there shouldn't be a need to hit 1000 concurrent requests from the same pool on the same app server. If it is, chances are you'll kill the app server before the database server (maybe not, depends on hardware and config) or there will be some concurrency issue at the database level (again, depends).

This will allow the pool to grow but hopefully not overwhelm the database server. If you had 10 app servers all set to this, then 10k connections may be an issue. There are a good amount of moving parts, even in this simple example but I'm trying to be optimistic.

What's the down side of making connection pools bigger than they need to be to service the app's demand, assuming unused connections are closed / removed from the pool after an idle timeout?

The downside is that you could:

  • Overwhelm the database
  • Overwhelm the application server
  • Have high overhead for setting up/tearing down connections

Eventually the connections will be removed and freed, but setting them up takes resources and time. The point of the pool is to reuse those resources to reduce the cost and resource usage, overall.

4

The default max pool size is 100 and one should generally stick with that unless there are special requirements. Setting the max connection pools size higher than that means you could have that many queries against the database concurrently and exacerbate problems like long-term blocking or long-running queries.

Unused pooled connections are always reused before a new one is made. Unused pooled connections are also physically closed if not reused for a couple of minutes, releasing server-side resources.

2

Limits are there to prevent runaway resource usage. If you're confident that your database can handle 1000 concurrent connections during the highest load periods (because it's exactly the time when the pool usage will be the highest), and that you won't have another application server running concurrently, then there's no harm in setting the pool size to 1000. You are essentially choosing what you want to bog down -- your database server or your application server.

Not the answer you're looking for? Browse other questions tagged or ask your own question.