how to check HikariCP connection pooling is working or not in Java?

Question

I have written following properties in my configuration files I am using Log4j in my application When I am running a project.

I am getting following message.does that mean connection pooling is configured in my project? if not then how it will be?

INFO: internal.ConnectionProviderInitiator - HHH000130: Instantiating explicit connection provider: com.zaxxer.hikari.hibernate.HikariConnectionProvider

I have referred following link also

link here

Datasource settings

hibernate.datasource.driver-class-name=com.mysql.jdbc.Driver
hibernate.datasource.url=jdbc:mysql://localhost:3306/mydb
hibernate.datasource.username=root
hibernate.datasource.password=root

HikariCP Settings

hibernate.hikari.dataSource.url=jdbc:mysql://localhost:3306/mydb
hibernate.hikari.idleTimeout=10
hibernate.hikari.maximumPoolSize=30
hibernate.hikari.minimumIdle=15
hibernate.connection.provider_class=com.zaxxer.hikari.hibernate.HikariConnectionProvider
hibernate.hikari.dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource

May be this will useful:https://stackoverflow.com/questions/5134515/how-would-you-test-a-connection-pool — soorapadman, Jul 02 '18 at 13:14
@soorapadman INFO: internal.ConnectionProviderInitiator - HHH000130: Instantiating explicit connection provider: com.zaxxer.hikari.hibernate.HikariConnectionProvider does this means connection pooling is established.? — Tejal, Jul 02 '18 at 13:15
Please add poolname and check weather its initiating or not . Obviously initialize connection pool one time only. — soorapadman, Jul 02 '18 at 13:20
@soorapadman yes right after loading I am getting INFO: internal.ConnectionProviderInitiator - HHH000130.... into console — Tejal, Jul 02 '18 at 13:23
@soorapadman yes I am not getting any error while loading application but how I will know that connection pooling is properly configured in my project. — Tejal, Jul 02 '18 at 13:35
Did you add poolname in properties? once you add you could see started and completed message . — soorapadman, Jul 02 '18 at 13:46
Check this link if you want test :https://dzone.com/articles/database-connection-pooling-in-java-with-hikaricp — soorapadman, Jul 02 '18 at 13:46
So … was all of this prior to Springboot 2.0+? I read elsewhere that basically hikari is used essentially by default in 2.0+ — dlamblin, Mar 03 '21 at 08:04
@dlamblin Thanks for replying , sometimes what we read on internet is not always right and in spring boot project with minimal configuration changes we can add HikariCp . — Tejal, Mar 04 '21 at 04:52
Thanks Tejal, but I suppose from your question and its answers that you were not using springboot nor spring at all. — dlamblin, Mar 06 '21 at 08:35

LMC · Answer 1 · 2021-09-14T20:57:30.627

First, configuration is no consistent since maximum < minimumIdle. Those should be set at most to the same value.

hibernate.hikari.maximumPoolSize=10
hibernate.hikari.minimumIdle=10

If the pools is working you should see 10 ESTABLISHED connections to port 3306 (or mssql 1433 in the example below).

lsof -nP -i :1433 -sTCP:ESTABLISHED
COMMAND  PID       USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
java    1596 lmc  260u  IPv6 1624799      0t0  TCP 127.0.0.1:43022->127.0.0.1:1433 (ESTABLISHED)
java    1596 lmc  265u  IPv6 1626072      0t0  TCP 127.0.0.1:43026->127.0.0.1:1433 (ESTABLISHED)
java    1596 lmc  266u  IPv6 1630933      0t0  TCP 127.0.0.1:43030->127.0.0.1:1433 (ESTABLISHED)
java    1596 lmc  267u  IPv6 1631705      0t0  TCP 127.0.0.1:43034->127.0.0.1:1433 (ESTABLISHED)
java    1596 lmc  268u  IPv6 1632268      0t0  TCP 127.0.0.1:43038->127.0.0.1:1433 (ESTABLISHED)
java    1596 lmc  269u  IPv6 1632273      0t0  TCP 127.0.0.1:43042->127.0.0.1:1433 (ESTABLISHED)
java    1596 lmc  270u  IPv6 1632278      0t0  TCP 127.0.0.1:43046->127.0.0.1:1433 (ESTABLISHED)

Using ss (socket statistics)

ss -46 -np state established dport = :1433 | grep 'java' | sort -r -k 3,3 | nl
     1  tcp    0       0          [::ffff:127.0.0.1]:43158     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=273))                                               
     2  tcp    0       0          [::ffff:127.0.0.1]:43154     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=272))                                               
     3  tcp    0       0          [::ffff:127.0.0.1]:43150     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=271))                                               
     4  tcp    0       0          [::ffff:127.0.0.1]:43142     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=270))                                               
     5  tcp    0       0          [::ffff:127.0.0.1]:43138     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=269))                                               
     6  tcp    0       0          [::ffff:127.0.0.1]:43134     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=268))                                               
     7  tcp    0       0          [::ffff:127.0.0.1]:43130     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=267))                                               
     8  tcp    0       0          [::ffff:127.0.0.1]:43126     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=266))                                               
     9  tcp    0       0          [::ffff:127.0.0.1]:43122     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=265))                                               
    10  tcp    0       0          [::ffff:127.0.0.1]:43118     [::ffff:127.0.0.1]:1433   users:(("java",pid=1596,fd=260))

Using netstat (deprecated on some distros in favor of ss)

netstat -ant | grep 3306
tcp        0      0 127.0.0.1:41722     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41730     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41728     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41726     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41716     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41732     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41720     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41736     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41718     127.0.0.1:3306      ESTABLISHED 
tcp        0      0 127.0.0.1:41724     127.0.0.1:3306      ESTABLISHED

An excellent way to test. Thanks a lot. – coretechie Nov 17 '22 at 15:36 — coretechie, Nov 17 '22 at 15:36

score 5 · Answer 2 · answered Aug 29 '18 at 10:16

See HikariCP note about MySQL:

The MySQL DataSource is known to be broken with respect to network timeout support. Use jdbcUrl configuration instead.

You need to remove the below line and Hikari will find the driver

hibernate.datasource.driver-class-name=com.mysql.jdbc.Driver

jdbcUrl This property directs HikariCP to use "DriverManager-based" configuration. We feel that DataSource-based configuration (above) is superior

Also try adding the following as suggested when using Hibernate4:

hibernate.hikari.dataSource.url=jdbc:mysql://localhost/database
hibernate.hikari.dataSource.user=bart
hibernate.hikari.dataSource.password=51mp50n

For posterity, I found what appears to be the originating MySQL ticket behind that "_known to be broken_" comment: [Incorrect implementation of Connection.setNetworkTimeout()](https://bugs.mysql.com/bug.php?id=75615). The ticket is in limbo. The most recent commentary I could find was this thread from 3 years ago: [MySQL datasource](https://groups.google.com/g/hikari-cp/c/hwsWs42CqVA/m/6gPoORAYAQAJ): "_If in doubt, use JDBC-URL based configuration, instead of DataSource configurations._" — andrewJames, Oct 30 '21 at 20:12

score 2 · Answer 3 · edited Jul 03 '18 at 07:02

2

Have you tried using the app to insert/update something in the database? If it fails then it's not working.
Another way to test it is change the datasource you provided here: hibernate.hikari.dataSource.url to a non-existing database.
Finally, change the <Configuration status="WARN"> to <Configuration status="DEBUG">

edited Jul 03 '18 at 07:02

MWiesner

8,868
11
36
70

answered Jul 02 '18 at 12:59

Ignacio Mosca

39
1
3

1. yes I tried inserting data into db its working fine but how I will know connection pooling is established? 2) .can you elaborate more 3).yes I tried this also – Tejal Jul 02 '18 at 13:08
Try changing hibernate.hikari.dataSource.url from jdbc:mysql://localhost:3306/mydb to jdbc:mysql://idontexisthost:3306/mydb to check if everything goes down. Also, do you see stuff logging when you insert something into the database? – Ignacio Mosca Jul 02 '18 at 14:19

score 0 · Answer 4 · answered Sep 07 '21 at 14:35

You can open MYSQL console and query by typing this query. as an example, I have added 10 connections for the pool. the username of the connection is mafei_connection_test. then you can see the all connection that the MySQL server created and currently opening.

SHOW PROCESSLIST;

mysql> SHOW PROCESSLIST;
+------+-----------------------+------------------+--------------------+---------+------+------------------------+------------------+
| Id   | User                  | Host             | db                 | Command | Time | State                  | Info             |
+------+-----------------------+------------------+--------------------+---------+------+------------------------+------------------+
|    5 | event_scheduler       | localhost        | NULL               | Daemon  | 6545 | Waiting on empty queue | NULL             |
| 1315 | mafei_connection_test | 172.17.0.1:58828 | NULL               | Sleep   |   57 |                        | NULL             |
| 1316 | mafei_connection_test | 172.17.0.1:58832 | NULL               | Sleep   |   59 |                        | NULL             |
| 1317 | mafei_connection_test | 172.17.0.1:58836 | NULL               | Sleep   |   59 |                        | NULL             |
| 1318 | mafei_connection_test | 172.17.0.1:58840 | NULL               | Sleep   |   59 |                        | NULL             |
| 1319 | mafei_connection_test | 172.17.0.1:58844 | NULL               | Sleep   |   59 |                        | NULL             |
| 1320 | mafei_connection_test | 172.17.0.1:58848 | NULL               | Sleep   |   59 |                        | NULL             |
| 1321 | mafei_connection_test | 172.17.0.1:58852 | NULL               | Sleep   |   59 |                        | NULL             |
| 1322 | mafei_connection_test | 172.17.0.1:58856 | NULL               | Sleep   |   59 |                        | NULL             |
| 1323 | mafei_connection_test | 172.17.0.1:58860 | NULL               | Sleep   |   59 |                        | NULL             |
| 1324 | mafei_connection_test | 172.17.0.1:58864 | NULL               | Sleep   |   59 |                        | NULL             |
| 1326 | mafei_connection_test | 172.17.0.1:58872 | information_schema | Query   |    0 | init                   | SHOW PROCESSLIST |
| 1327 | mafei_connection_test | 172.17.0.1:58876 | NULL               | Sleep   |   11 |                        | NULL             |
+------+-----------------------+------------------+--------------------+---------+------+------------------------+------------------+
13 rows in set (0.05 sec)

how to check HikariCP connection pooling is working or not in Java?

Datasource settings

HikariCP Settings

4 Answers4

Linked