How to configure and monitor the Hikari JDBC pool
Connect2id server deployments with an SQL database to persist data use the high performance HikariCP to pool and manage the connections.
HikariCP configuration
HikariCP comes with defaults that will perform well in most Connect2id server deployments. For this reason the number of Java system properties made available to override the default HikariCP configuration is small.
dataSource.maxPoolSize
The maximum size the connection pool is allowed to reach, including both idle and in-use connections. The default size is 5, which is optimal for a Connect2id server using 2 CPU cores.
dataSource.maxPoolSize=5
Deployments with more than 2 CPU cores can use the following formula, where the maximum number of connections is proportional to the number of threads the CPU is able to run simultaneously.
pool size = ( CPU core count X 2 ) + 1
The HikariCP guide explains how this value was determined and why making it larger than necessary destroys performance.
dataSource.maxLifetime
The maximum lifetime of connections in the pool. When the lifetime of a connection in the pool expires and it is currently not being used, the connection is closed. The default is 1800000 milliseconds (30 minutes).
dataSource.maxLifetime=1800000
The lifetime should be shorter, by several seconds, than the connection lifetime imposed by the database. If this isn't observed the Connect2id server may require extra time to obtain a connection from the pool, if the connection turns out to be closed by the database. The occurrence of this is logged at WARN level like this:
WARN c2id.sqlStore - Failed to validate connection ... (Closed Connection). Possibly consider using a shorter maxLifetime value.
Database information:
MySQL --
wait_timeoutconfigures the number of seconds the server waits for activity on a non-interactive connection before closing it. The default is 28800 seconds (8 hours)PostgreSQL -- no timeout.
Microsoft SQL Server -- no timeout.
Oracle Database -- configured by
CONNECT_TIMEandIDLE_TIME.
Note that firewalls and database gateways may impose their own timeouts on TCP connections to the database.
Monitoring
The monitoring endpoint of the Connect2id server includes SQL store metrics.
SQL store performance
For every map / cache and its corresponding database table the following timers are made available:
[infinispan-cache-name].sqlStore.loadTimer Times loading of individual records from the SQL store.
[infinispan-cache-name].sqlStore.writeTimer Times writing of individual records to the SQL store.
[infinispan-cache-name].sqlStore.deleteTimer Times deleting of individual records from the SQL store.
[infinispan-cache-name].sqlStore.processTimer Times processing of records from the SQL store for stream operations.
[infinispan-cache-name].sqlStore.purgeTimer Times purging of expired records from the SQL store.
The times include any time waiting to obtain an SQL connection from the HikariCP pool.
Example snapshot for the sessionStore.sessionMap which persists its data to
a subject_sessions table:
{
"timers": {
"sessionStore.sessionMap.sqlStore.loadTimer": {
"count": 58,
"max": 0.00198883,
"mean": 4.52221796535505E-4,
"min": 1.3761E-4,
"p50": 3.26166E-4,
"p75": 5.16456E-4,
"p95": 0.001465911,
"p98": 0.001925349,
"p99": 0.00198883,
"p999": 0.00198883,
"stddev": 3.68484024413342E-4,
"m15_rate": 0.060125944063675385,
"m1_rate": 0.37299055047166263,
"m5_rate": 0.15737213504152456,
"mean_rate": 0.2201006215611179,
"duration_units": "seconds",
"rate_units": "calls/second"
},
"sessionStore.sessionMap.sqlStore.writeTimer": {
"count": 0,
"max": 0.0,
"mean": 0.0,
"min": 0.0,
"p50": 0.0,
"p75": 0.0,
"p95": 0.0,
"p98": 0.0,
"p99": 0.0,
"p999": 0.0,
"stddev": 0.0,
"m15_rate": 0.0,
"m1_rate": 0.0,
"m5_rate": 0.0,
"mean_rate": 0.0,
"duration_units": "seconds",
"rate_units": "calls/second"
},
"sessionStore.sessionMap.sqlStore.deleteTimer": {
"count": 53,
"max": 8.57551E-4,
"mean": 2.0525613514308364E-4,
"min": 6.0003E-5,
"p50": 1.78247E-4,
"p75": 2.30045E-4,
"p95": 5.56442E-4,
"p98": 6.323660000000001E-4,
"p99": 8.57551E-4,
"p999": 8.57551E-4,
"stddev": 1.4040535554512906E-4,
"m15_rate": 0.05494712047460359,
"m1_rate": 0.34141422165259117,
"m5_rate": 0.1438431559588522,
"mean_rate": 0.20112653151347162,
"duration_units": "seconds",
"rate_units": "calls/second"
},
"sessionStore.sessionMap.sqlStore.processTimer": {
"count": 0,
"max": 0.0,
"mean": 0.0,
"min": 0.0,
"p50": 0.0,
"p75": 0.0,
"p95": 0.0,
"p98": 0.0,
"p99": 0.0,
"p999": 0.0,
"stddev": 0.0,
"m15_rate": 0.0,
"m1_rate": 0.0,
"m5_rate": 0.0,
"mean_rate": 0.0,
"duration_units": "seconds",
"rate_units": "calls/second"
},
"sessionStore.sessionMap.sqlStore.purgeTimer": {
"count": 0,
"max": 0.0,
"mean": 0.0,
"min": 0.0,
"p50": 0.0,
"p75": 0.0,
"p95": 0.0,
"p98": 0.0,
"p99": 0.0,
"p999": 0.0,
"stddev": 0.0,
"m15_rate": 0.0,
"m1_rate": 0.0,
"m5_rate": 0.0,
"mean_rate": 0.0,
"duration_units": "seconds",
"rate_units": "calls/second"
}
}
}
Connection pool usage and performance
The SQL store metrics also includes metrics obtained from the HikariCP connection pool.
[pool-name].sqlStore.pool.Wait Times the wait time to obtain a connection from the pool.
[pool-name].sqlStore.pool.ConnectionTimeoutRate Meters the rate of timeouts waiting to obtain a connection from the pool.
[pool-name].sqlStore.pool.ConnectionCreation Histogram of connection creation time.
[pool-name].sqlStore.pool.Usage Histogram of connection usage time. This is the connection "out of pool" or "in-use" time.
[pool-name].sqlStore.pool.TotalConnections A cached gauge, refreshed on demand at 1 second resolution, indicating the total number of connections in the pool.
[pool-name].sqlStore.pool.IdleConnections A cached gauge, refreshed on demand at 1 second resolution, indicating the number of idle connections in the pool.
[pool-name].sqlStore.pool.ActiveConnections A cached gauge, refreshed on demand at 1 second resolution, indicating the number of active (in-use) connections in the pool.
[pool-name].sqlStore.pool.PendingConnections A cached gauge, refreshed on demand at 1 second resolution, indicating the number of threads awaiting a connection from the pool.
[pool-name].sqlStore.pool.MinConnections The minimum number of connections in the pool.
[pool-name].sqlStore.pool.MaxConnections The maximum number of connections in the pool.
In Connect2id server 14.x (regular edition) the pool name bears the name
sessionStore.sessionMap.
Example snapshot:
{
"timers": {
"sessionStore.sessionMap.sqlStore.pool.Wait": {
"count": 1798,
"max": 4.2781E-5,
"mean": 3.526603840318475E-6,
"min": 3.0000000000000004E-7,
"p50": 1.7330000000000002E-6,
"p75": 3.4160000000000004E-6,
"p95": 1.4077000000000001E-5,
"p98": 2.3374E-5,
"p99": 2.4397E-5,
"p999": 4.2781E-5,
"stddev": 5.195869488863918E-6,
"m15_rate": 3.491988758363185,
"m1_rate": 9.599166809002018,
"m5_rate": 5.646058344484582,
"mean_rate": 6.828364231015878,
"duration_units": "seconds",
"rate_units": "calls/second"
}
},
"meters": {
"sessionStore.sessionMap.sqlStore.pool.ConnectionTimeoutRate": {
"count": 0,
"m15_rate": 0.0,
"m1_rate": 0.0,
"m5_rate": 0.0,
"mean_rate": 0.0,
"units": "events/second"
}
},
"histograms": {
"sessionStore.sessionMap.sqlStore.pool.ConnectionCreation": {
"count": 4,
"max": 1,
"mean": 0.25,
"min": 0,
"p50": 0.0,
"p75": 1.0,
"p95": 1.0,
"p98": 1.0,
"p99": 1.0,
"p999": 1.0,
"stddev": 0.4330127018922193
},
"sessionStore.sessionMap.sqlStore.pool.Usage": {
"count": 1798,
"max": 5,
"mean": 0.03802863768550912,
"min": 0,
"p50": 0.0,
"p75": 0.0,
"p95": 0.0,
"p98": 1.0,
"p99": 1.0,
"p999": 3.0,
"stddev": 0.25071998078520036
}
},
"gauges": {
"sessionStore.sessionMap.sqlStore.pool.TotalConnections": {
"value": 5
},
"sessionStore.sessionMap.sqlStore.pool.IdleConnections": {
"value": 5
},
"sessionStore.sessionMap.sqlStore.pool.ActiveConnections": {
"value": 0
},
"sessionStore.sessionMap.sqlStore.pool.PendingConnections": {
"value": 0
},
"sessionStore.sessionMap.sqlStore.pool.MinConnections": {
"value": 5
},
"sessionStore.sessionMap.sqlStore.pool.MaxConnections": {
"value": 5
}
}
}
Connection pool health
The health check resource includes an SQL connection "aliveness" check.
[pool-name].sqlStore.pool.ConnectivityCheck {object} A JSON object representing the health of the connection pool when an SQL store is configured. The check obtains a JDBC connection from the pool and immediately returns it.
healthy {true|false} Indicates the health.
[ message ] {string} Diagnostic message if healthy is
false.duration {number} Time it took to perform the check, in milliseconds.
timestamp {string} Time when the check was completed.
Tips
Which JDBC metrics are critical?
The following metrics will indicate that availability of the Connect2id server has suffered:
- [pool-name].sqlStore.pool.ConnectionTimeoutRate A positive count indicates that the Connect2id server timed out waiting for a usable SQL connection to be retrieved from the pool.
Example metrics snapshot showing 34 failed SQL connection retrievals due to a timeout:
{
"sessionStore.sessionMap.sqlStore.pool.ConnectionTimeoutRate": {
"count": 34,
"m15_rate": 0.018033965350978964,
"m1_rate": 0.03826410221380928,
"m5_rate": 0.024038553284218757,
"mean_rate": 0.01849791621511582,
"units": "events/second"
}
}
How to open a console to the embedded H2 database?
Locate the H2 library JAR in the deployed Connect2id server and start it as an application, for example:
java -jar tomcat/webapps/c2id/WEB-INF/lib/h2-2.2.220.jar
This will open the system browser.
- Under "JDBC URL" enter
jdbc:h2:file:/some/path/connect2id-server-14.10/h2/c2idfor the path to the shipped H2 database file. - Under "User name" enter
c2id - Under "Password" enter
secret
Make sure the Connect2id server is shut down prior to clicking on "Connect" or "Test Connection". Otherwise the login page will display an error message that there is another file connection open to the database.