Best choice of connection pooling for an spring application deployed on tomcat connecting to mariadb

Question

After reading multiple documents based on jdbc connection pooling i couldnt come up to some conclusion. What connection pooling method is best for an spring application which is deployed on tomcat and connect to mariadb.

mariadb provides it own connection pooling on the other hand multiple external pooling libraries exists HikariCp, DBCP, tomcat-jdbc etc.

Answer 1

There does not seem to be any benchmark about MariaDB's connection pooling, so giving an answer would probably be highly opiniated, and it'd also depend on your use cases, as benchmarks can only be relevant if looking at the right data.

You'll find here https://mariadb.com/fr/node/1829 MariaDB's arguments for providing yet another implementation of Connection Pooling.

Here are some of the reasons:

• Reliability: When reusing a connection from pool, the connection must be like a "new freshly created" connection. Depending on connection state, frameworks may result executing multiple commands to reset state (Some frameworks even choose to skip some of those reset to avoid some performance impact). MariaDB has a dedicated command to refresh connection state permitting real reset (rollback remaining transaction, reset transaction isolation level, reset session variables, delete user variables, remove all PREPARE statement, ...) in one command.
• Performance: The pool can save some information at the first connection, allowing faster creations when making the next connection.
• Easy configuration: Solve some frequent issues, like server will close socket if not used after some time (wait_timeout default to 8h). Pool implementation avoids keeping a connection in a bad state

For the other CP implementations, there are plenty of benchmarks, from which HikariCP stands out as an overall better option, and is now the default in Spring Boot 2.

In the end, it comes down to you trying out MariaDB's connection pooling or relying on an already established implementation.