Using HikariCP with Mysql: who should implement the threadpool

Question

I'm running 2 instances of a Java app using HikariCP with a maximum pool size of 30 each. They connect to a single MariaDB database which also has thread_handling = pool-of-threads set. I have been getting some connection timeouts from Hikari when there is a high number of simulataneious DB reads.

2024-03-20 00:09:17.551, qtp1047503754-22 , [DEBUG] Get team query time, 8ms
2024-03-20 00:09:18.432, pool-3-thread-3 , [DEBUG] Get team query time, 5ms
2024-03-20 00:09:52.305, qtp1047503754-29 , [DEBUG] Get team query time, 6ms
2024-03-20 00:09:56.720, qtp1047503754-27 , [DEBUG] Get team query time, 5ms
2024-03-20 00:10:00.232, qtp1047503754-24 , [DEBUG] Get team query time, 4ms
2024-03-20 00:10:01.329, pool-3-thread-4 , [DEBUG] Get team query time, 4ms
2024-03-20 00:10:04.553, qtp1047503754-25 , [DEBUG] Get team query time, 4ms
2024-03-20 00:10:05.787, pool-3-thread-3 , [DEBUG] Get team query time, 6ms
2024-03-20 00:10:06.353, pool-3-thread-4 , [DEBUG] Get team query time, 3ms
2024-03-20 00:10:14.178, qtp1047503754-22 , [DEBUG] Get team query time, 293ms
2024-03-20 00:10:14.184, qtp1047503754-27 , [DEBUG] Get team query time, 953ms
2024-03-20 00:10:14.185, qtp1047503754-29 , [DEBUG] Get team query time, 739ms
2024-03-20 00:10:14.197, qtp1047503754-24 , [DEBUG] Get team query time, 523ms
2024-03-20 00:10:14.201, qtp1047503754-28 , [DEBUG] Get team query time, 92ms
2024-03-20 00:10:15.414, qtp1047503754-25 , [DEBUG] Get team query time, 861ms
2024-03-20 00:10:15.425, qtp1047503754-167 , [DEBUG] Get team query time, 417ms
2024-03-20 00:10:16.177, qtp1047503754-26 , [DEBUG] Get team query time, 1838ms

The query time keeps increasing and eventually HikariCP times out at 30s. The query time goes back to normal after the request count goes back to normal. For context: I'm just reading a row from a simple table with a WHERE on the primary key.

Should I just let HikariCP handle the connection pool and set the Mariadb thread handling to thread per connection or do I have to match the sizes of the pools?

Answer 1

If you have to use more than 30 connections simultaneously, and you got only 30 connections in connection pool, you'll have a problem. Just increase that number. Also on MariaDB server side, thread_pool_max_threads .It can be set to hundreds if not thousands, it does not really affect the performance that much.