Search code examples
javaconnection-pooling

What does "setMaxTotal" actually do? (MySQL & Java & Apache's BasicDataSource connection pool)

Having a MySQL database on AWS, I see that the max number of connections it allows is 60 when doing show variables like 'max_connections':

enter image description here

So when using Apache's javax.sql.DataSource implementation as my connection pool, I can set the max number of active connections to 60.

...

connectionPool = new BasicDataSource();
connectionPool.setUrl("...");
connectionPool.setMaxTotal(60);
...

However, I still get sometimes TooManyConnectionsDatabaseException exceptions in my code.

This makes me wonder; what does this practically actually do for a connection pool implementation?

Let's say that a server can only handle 100 active connections, and I create 500 connections from a connection pool, then what happens?

  • Does the connection pool on the client side create an internal queue of 400 and halts/sleep the 400 connection threads?

  • Or will it try and send 500 requests to the MySQL database regardless of setting the max number of connections? (if so, what's the point of the limit)

  • Or will it throw a coding exception for the other 400 connections?

What exactly does setting the max number of active connections of a connection pool do? How does a connection pool behave in the code wants to create more connections than the max value you've set?

UPDATE AFTER RIGHT ANSWER

This is based on @mark-rotteveel's answer.

By printing connectionPool.getMaxWaitMillis(), I see that the max wait time is -1 ms, which means Use -1 to make the pool wait indefinitely..

So, I tried changing the connection init code to:

connectionPool.setMaxTotal(1); // Only have 1 active connection at the same time
connectionPool.setMaxWaitMillis(1); // Only wait 1ms for a free connection

And my code immediately got

java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object

Which is expected and makes sense!

Solution

  • The setMaxTotal sets the maximum number of connections that specific instance of the connection pool can create. If you set it to 60, and your server also has a maximum of 60, it might work, or it might fail if there are also other things that create connections (e.g. monitoring tools, other instances of the application, etc.).

    The setMaxTotal only concerns the specific instance of the connection pool, and it does not track or monitor the number of connections allowed by the server: it is your responsibility to configure it (or the MySQL server) appropriately to not try and allocate too much (or to ensure that the MySQL server configuration has sufficient slack).

    So, if you use setMaxTotal with value 60, and you have two instances of the application running, it will mean that together, they can allocate a maximum of 120 connections (assuming of course, your MySQL server has a maximum of 120 or greater, otherwise it will fail).

    The setMaxTotal is the upper limit of connections the connection pool will allocate. That means, if set to 60, it can have 60 connections leased at a time. If the application calls DataSource.getConnection() when all 60 connections are leased, that call will wait for the configured timeout to see if a connection becomes available within that timeout, or otherwise throw an exception that it timed out.

    If the connection pool hasn't reached max total yet, and all currently allocated connections are already leased, it will create a new connection and hand that out.

    This is why it is important that your code closes a connection as soon as possible. Closing a connection from a connection pool is what returns it to the pool for reuse.