Applies to: Semarchy xDI runtime (all versions using HikariCP for JDBC connection pooling)
Author: Michael Turchi
This article explains how Hikari connection pooling works in xDI, how to configure it at the server, runtime, and log-database level, how to monitor pool activity, and how to size pooling parameters correctly.
Connection pooling concepts
What is a connection?
A connection is the link a client uses to talk to a database server. It:
- Lets the client send commands (SQL queries) and receive results, status, and statistics.
- Is a reciprocal link between client and server.
- Handles network connectivity (opening a socket over TCP/IP).
- Manages database-specific protocol checks.
- Handles security encryption (TLS/SSL).
- Manages authentication (user/password, OAuth, IAM, etc.).
- Creates a session on the database side, with session variables, temporary objects, and permissions.
- Scopes transaction management to the connection.
- Triggers resource allocation on the server for each connection.
What is a connection pool?
A connection pool is a way to reuse connections between a client and a server. It is a logical space that keeps active and inactive connections so idle connections can be reused.
Pooling improves performance by mutualizing:
- The network handshake
- Authentication
It also optimizes database server resources by mutualizing:
- Memory structures
- Session resource allocations
How the connection pool behaves
Each time a connection is needed, the application asks Hikari if the pool already has an existing idle connection available. If all pooled connections are in use, Hikari creates a new connection if the pool size allows it; otherwise, it waits until one becomes available. After a query or transaction completes, the connection stays in the pool and becomes idle.
Connections are closed when:
- They have been inactive for too long (see
idleTimeoutandminimumIdle). - They have existed for too long, with or without activity (see
maxLifetime) — unless currently in use. - They fail a health check or test query.
- The application (xDI runtime) is shut down or restarted.
Connections are created when:
- The application starts, for the log database (see
minimumIdle). - A connection is needed, no idle connection is available, and the maximum pool size has not been reached.
- A connection is replaced (
maxLifetimeexpired, oridleTimeoutreached whileminimumIdleis not satisfied). - A new pool is created:
minimumIdleconnections are created when the first connection is needed.
Connections cannot be created when:
- The database connection limit is reached at the database or user level — this raises an error after the connection timeout.
- The pool has reached its maximum size and all connections remain in use past the connection timeout.
- Other technical errors occur (network, security, database crash, insufficient resources).
Hikari pooling parameters
| Parameter | Description |
|---|---|
| Enable/Disable Pooling | Activates or deactivates pooling at the server or runtime level. |
| Maximum Pool Size | Maximum number of connections the pool can hold (active + idle). |
| Minimum Idle | Minimum number of idle connections kept in the pool; this is also the number of connections created on the first connection request. |
| Idle Timeout | How long an idle connection can sit before being closed. |
| Max Lifetime | Maximum lifetime of a connection before it is retired. |
| Keep Alive Time | Sends periodic validation on idle connections. Does not reset maxLifetime, does not extend idleTimeout, and does not prevent a connection from being closed — it only prevents the network or database from closing the connection due to inactivity. |
| Validation Timeout | Maximum time allowed for connection validation. |
| Connection Test Query | SQL query used to validate a connection when the driver doesn't support JDBC4 isValid(). |
Configuring pooling in xDI
Pooling can be configured at three levels: the RDBMS server, the runtime, and the log database.
Server level
Pooling parameters can be set directly on an RDBMS server. These override the default or runtime-level parameters.
In xDI Studio, open the RDBMS server, go to the Advanced tab, and use the Enable Connection Pooling section to set: Idle Timeout, Max Lifetime, Keep Alive Time, Max Pool Size, Validation Timeout, and Minimum Idle (alongside the unrelated Batch Size and Fetch Size settings on the same tab).
Runtime level
Pooling parameters can be set at the runtime level for all servers that use default parameters. They are set through Java options:
-Dcom.semarchy.xdi.runtime.jdbc.disablePool (default: false)
-Dcom.semarchy.xdi.runtime.jdbc.poolMaxSize (default: 20; was 10 before 2023.1.5)
-Dcom.semarchy.xdi.runtime.jdbc.poolMinIdle (default: 0)
-Dcom.semarchy.xdi.runtime.jdbc.poolIdleTimeout (default: 600000 ms)
-Dcom.semarchy.xdi.runtime.jdbc.poolKeepAlive (default: 0, meaning disabled)
-Dcom.semarchy.xdi.runtime.jdbc.poolMaxLifetime (default: 1800000 ms)
-Dcom.semarchy.xdi.runtime.jdbc.poolValidationTimeout (default: 5000 ms)
-Dcom.semarchy.xdi.runtime.jdbc.poolValidationQuery (default: "", meaning the JDBC isValid() check is used)
Log database level
The connection to the log database has its own pooling parameters, defined in engineParameters.xml. These are not overridden by the server or runtime settings above:
<parameter name="userLogRdbmsPoolEnabled" value="true"/>
<parameter name="userLogRdbmsPoolConnectionTimeout" value="30000"/>
<parameter name="userLogRdbmsPoolIdleTimeout" value="600000"/>
<parameter name="userLogRdbmsPoolKeepAliveTime" value="0"/>
<parameter name="userLogRdbmsPoolMaxLifetime" value="1800000"/>
<parameter name="userLogRdbmsPoolMinimumIdle" value="0"/>
<parameter name="userLogRdbmsPoolMaximumSize" value="20"/>
<parameter name="userLogRdbmsPoolValidationTimeout" value="5000"/>
How to monitor pooling
Currently, the only way to monitor pooling on the xDI side is by logging pool activity through log4j2 (adding Hikari metrics to actuator/Prometheus is under consideration for a future release).
Add a dedicated Hikari appender
Add an appender that logs only connection creation and closing events from the pool:
<RollingFile fileName="log/hikari.log" filePattern="log/hikari.log.%i" immediateFlush="true" name="hikariAppender">
<PatternLayout>
<Pattern>%d{dd/MM/yyyy HH:mm:ss,SSS} [%t] %c{10} - %-5p - %m%n%throwable</Pattern>
</PatternLayout>
<SizeBasedTriggeringPolicy size="20 MB"/>
<DefaultRolloverStrategy fileIndex="min" max="6"/>
<RegexFilter regex=".*(Added connection|Closing connection).*" onMatch="ACCEPT" onMismatch="DENY"/>
</RollingFile>
Then attach the appender to Hikari's logger:
<Logger name="com.zaxxer.hikari" level="debug" additivity="false">
<AppenderRef ref="hikariAppender"/>
</Logger>
Check pool activity in hikari.log
With the appender in place, log/hikari.log records connection lifecycle events, for example:
[HikariPool-1:connection-adder] com.zaxxer.hikari.pool.HikariPool - DEBUG - HikariPool-1 - Added connection org.hsqldb.jdbc.JDBCConnection@...
[RuntimeMainThread] com.zaxxer.hikari.pool.PoolBase - DEBUG - HikariPool-2 - Closing connection org.hsqldb.jdbc.JDBCConnection@49c8f6e8: (initialization check complete and minimumIdle is zero)
[HikariPool-2:connection-adder] com.zaxxer.hikari.pool.HikariPool - DEBUG - HikariPool-2 - Added connection org.hsqldb.jdbc.JDBCConnection@...
[HikariPool-1:connection-adder] com.zaxxer.hikari.pool.HikariPool - DEBUG - HikariPool-1 - Added connection org.hsqldb.jdbc.JDBCConnection@...
[HikariPool-3:connection-adder] com.zaxxer.hikari.pool.HikariPool - DEBUG - HikariPool-3 - Closing connection conn0: url=jdbc:h2:mem:testdb user=SA: (initialization check complete and minimumIdle is zero)
[HikariPool-3:connection-adder] com.zaxxer.hikari.pool.HikariPool - DEBUG - HikariPool-3 - Added connection conn1: url=jdbc:h2:mem:testdb ...
[HikariPool-1:connection-closer] com.zaxxer.hikari.pool.PoolBase - DEBUG - HikariPool-1 - Closing connection5de15f73: (connection has passed idleTimeout)
[HikariPool-3:connection-closer] com.zaxxer.hikari.pool.PoolBase - DEBUG - HikariPool-3 - Closing connection conn1: url=jdbc:h2:mem:testdb ... (connection has passed idleTimeout)
Check pool activity from the database side
Another option is to check connections on the database side using technical tables and views — for example pg_stat_activity on PostgreSQL.
Recommendations for pooling
Determine the Maximum Pool Size
Base this on:
- The number of concurrent dataflows and concurrent connections per dataflow your runtime may run — this can be estimated from the xDI log database.
- The type of dataflow: bulk loading, data exports, complex SELECT statements, or SQL wait states can hold a connection for a long time.
- The database resources allocated to xDI — pooling also protects the database from being overloaded. Always check with your DBAs.
- The maximum number of connections allowed on the database side.
Start with lower values (10–20) and increase only if you see issues related to the pool being full.
Determine the Minimum Idle connections
This parameter determines the pool's elasticity:
- If connection allocation on the database side is not a bottleneck, set
minimumIdleequal tomaximumPoolSizefor the best performance. - Otherwise, choose a good balance between 0 and the maximum pool size — the average number of concurrent active connections (derived from log database analysis) is usually a good target.
- Keep other parameters at their defaults unless you're facing performance issues related to pool shrinking/growing.
Control the number of parallel xDI jobs
- For a single job, control parallelism using Meta-Inf.
- For web services, use the
concurrentSessionsLimitparameter inengineParameters.xml. - Load-balancing and autoscaling can reduce the risk of hitting the maximum pool size.
- Use an external scheduler's capabilities to control parallelism.