I am using Hikari Pool in my Java/SpringBoot application for database connection pooling. How I can print or log the Hikari Pool Stats.
How to log in core Java and How to log in Spring boot?
1 Like
Spring Boot
Put below in your application.yaml or application.properties
logging.level.com.zaxxer=TRACE
If using any logger file (log4j2.xml) then add below in your xml file:
<Logger name="com.zaxxer" additivity="false" level="trace">
<AppenderRef ref="hikariLog"/>
<!-- <AppenderRef ref="console"/> -->
</Logger>
Core Java
Get the HikariDataSource object and pass in below method:
private void logHikariStats(HikariDataSource ds) {
HikariPoolMXBean poolMXBean = ds.getHikariPoolMXBean();
int activeConnections = poolMXBean.getActiveConnections();
int idleConnections = poolMXBean.getIdleConnections();
int totalConnections = poolMXBean.getTotalConnections();
int threadsAwaitingConnection = poolMXBean.getThreadsAwaitingConnection();
String stats = "Total[" + totalConnections + "],Active[" + activeConnections + "],Idle[" + idleConnections + "],Waiting[" + threadsAwaitingConnection + "]";
System.out.println("=== Hikari Stats=== " + stats);
}
3 Likes
DO not use TRACE in production.
Why not use HikariCP TRACE log stats in production?
Using HikariCP TRACE log stats in production can be resource-intensive and may impact performance. The TRACE level provides detailed statistics about connection pool operations, which can be valuable for debugging and optimizing during development. However, in a production environment, it’s advisable to use a lower log level (such as DEBUG or INFO) to minimize the overhead associated with logging. Excessive logging can lead to increased disk I/O and may compromise the system’s overall performance.
Hope this helps.
HikariCP does not have any TRACE logs; it has DEBUG logs which print database connection stats and housekeeping logs at a 30-second interval.
Here is logs output:
2024-01-31 15:16:08.818 DEBUG 3104 --- [nnection closer] c.z.h.p.PoolBase : HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@20c9119d: (connection has passed maxLifetime)
2024-01-31 15:16:08.878 DEBUG 3104 --- [onnection adder] c.z.h.p.HikariPool : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@35039fce
2024-01-31 15:16:09.477 DEBUG 3104 --- [nnection closer] c.z.h.p.PoolBase : HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@5464cab5: (connection has passed maxLifetime)
2024-01-31 15:16:09.595 DEBUG 3104 --- [onnection adder] c.z.h.p.HikariPool : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@413b0113
---------------
2024-01-31 15:16:14.927 DEBUG 3104 --- [l-1 housekeeper] c.z.h.p.HikariPool : HikariPool-1 - Before cleanup stats (total=2, active=0, idle=2, waiting=0)
2024-01-31 15:16:14.928 DEBUG 3104 --- [l-1 housekeeper] c.z.h.p.HikariPool : HikariPool-1 - After cleanup stats (total=2, active=0, idle=2, waiting=0)
2024-01-31 15:16:14.929 DEBUG 3104 --- [l-1 housekeeper] c.z.h.p.HikariPool : HikariPool-1 - Fill pool skipped, pool is at sufficient level.
--------------
2024-01-31 15:16:38.373 DEBUG 3104 --- [nnection closer] c.z.h.p.PoolBase : HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@35039fce: (connection has passed maxLifetime)
2024-01-31 15:16:38.441 DEBUG 3104 --- [onnection adder] c.z.h.p.HikariPool : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@7edc1725
2024-01-31 15:16:39.154 DEBUG 3104 --- [nnection closer] c.z.h.p.PoolBase : HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@413b0113: (connection has passed maxLifetime)
2024-01-31 15:16:39.188 DEBUG 3104 --- [onnection adder] c.z.h.p.HikariPool : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@a2f9ccc
---------------
2024-01-31 15:16:44.940 DEBUG 3104 --- [l-1 housekeeper] c.z.h.p.HikariPool : HikariPool-1 - Before cleanup stats (total=2, active=0, idle=2, waiting=0)
2024-01-31 15:16:44.941 DEBUG 3104 --- [l-1 housekeeper] c.z.h.p.HikariPool : HikariPool-1 - After cleanup stats (total=2, active=0, idle=2, waiting=0)
2024-01-31 15:16:44.942 DEBUG 3104 --- [l-1 housekeeper] c.z.h.p.HikariPool : HikariPool-1 - Fill pool skipped, pool is at sufficient level.
----------------
These are very useful pieces of information for observing database performance and identifying issues.
We can utilize log rotation (using logging framework) to manage log file sizes, limiting them to either 24 to 72 hours. Regularly review and clean up old log files to free up disk space.
1 Like
Thanks @naveen.gupta for highlighting this.
This information can be useful while investigating the issue in production if generated occasionally.
@Dilip_Vishwakarma what do you think?
Thanks Vinay,
I would like to add some more points in addition to what you mentioned.
- Debug level will generate extensive logs which can cause filling up Pods disk very fast and POD will get killed. Hence revert it immediatley after getting the logs.
- You should also check at the database level for connection pool.
- Also setting log file to 24 to 72 hours depends on disk size. You need to set ulimit. for disk.
- In the case of Hikari pool stats, it does not generate extensive logs as I mentioned before; it logs the stats at a 30-second interval. However, we can increase or decrease this interval.
- It is not recommended to save logs inside a pod or container; we have to create a separate volume for the logs.
- Please let us know how we can check the database connection pool stats(active, waiting, idle) at the database level.
1.There is option to generated SQL logs check properties configuration for JPA level.
2. Container Logs should not be stored for long time only for 24 Hrs log rotation size with only 5 days capacity.
3. Steps to see the how many a number of pool connections exist :
a. Select the database in which you want to run the script
b. Write the script → select * from pg_stat_activity WHERE datname = ‘’