-2

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.

pvjhs
  • 549
  • 1
  • 9
  • 24
  • Without connection pooling, how many connections per second are you getting? If you have been running for a while, look at `SHOW GLOBAL STATUS;` and divide `Connections` by `Uptime`. If it is more than, say, 10, then you could benefit from pooling. If `Max_used_connections` is greater than, say, 200, you have other problems. – Rick James Jun 07 '18 at 03:42

1 Answers1

0

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.

Christophe Douy
  • 813
  • 1
  • 11
  • 27
  • Yes, definitely we cannot say about the best. but i think the argument u given for HikariCP as already being used. Seems a good choice to start with. – pvjhs Jun 06 '18 at 13:33
  • MariaDB driver knows how to reset connection state, and how to do a ping to verify connection validity better than any generic JDBC pool, because those parts are not in JDBC standard (or are not used by generic pool implementations) – Vladislav Vaintroub Jun 06 '18 at 19:09
  • At MariaDB-10.2.4, the "reset connection" becomes more efficient. – Rick James Jun 07 '18 at 03:49