Overview

Hikari is a battle-proven, lightweight, high performance connection pool library for Java. It's also the default connection pool in Spring Boot. This article will dive into some configuration settings that are relevant for CockroachDB.

Why a pool?

Connection pooling is fundamental for high performance since opening and closing database connections are expensive operations. Connections are opened and closed for each transaction, whether its an implicit (auto-commit) or explicit (begin+commit/rollback) transaction. Since transactional SQL databases should strive for short-lived transactions, this overhead would be significant unless using a common technique to mange expensive resources: resource pooling.

How does pooling work?

Whenever you open a JDBC connection through a pooled javax.sql.DataSource interface, its actuially claimed from a pool of already pre-opened connections and borrowed to the calling thread. When you close the connection (wrapped in a proxy) it's returned back to the pool rather than being actually closed.

If you happen to drain or exhaust the pool of available connections, the calling thread will have to wait until a connection becomes available offering simple back-pressure mechanism to control resource usage. If connections get trashed, the pool will do housekeeping and backfill with new valid connections. In addition, idle connections in the pool will have a maximum lifetime until they are closed and the pool gets backfilled.

There are different settings for tweaking the pool behavior for different workload characteristics, so lets look into that next.

Configuring Hikari

HikariCP is included automatically with Spring Boot, so there's no extra Maven configuration needed. You can however override the dependency to use a more recent version.

Maven dependency

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

Configuration Parameters

In Spring, the Hikari configuration settings are located under spring.datasource.hikari.*.

The default values are optimized for short-lived, high-frequency transactions that benefits from a fixed-sized pool. These settings are often good enough but sometimes you may want to tweak things, and then it's good to know what the different knobs mean.

These are the most important ones, as described in the Hikari github repo:

spring.datasource.hikari.autoCommit

This property controls the default auto-commit behavior of connections returned from the pool. It is a boolean value. Default: true

If you change this to false, then you should also set hibernate.connection.provider_disables_autocommitto true. This tells Hibernate that auto-commit is already disabled when a connection is acquired, and some operations can be avoided for performance.

One reason you would want to do this is when always using explicit transactions through @Transactional. If you also use many implicit, read-only transactions then its better to stick with the default.

spring.datasource.hikari.connectionTimeout

This property controls the maximum number of milliseconds that a client will wait for a connection from the pool. If this time exceeds without a connection becoming available, a SQLException will be thrown. Default: 30000 (30 seconds)

A shorter timeout is also possible, like 10 seconds.

spring.datasource.hikari.idleTimeout

This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. A value of 0 means that idle connections are never removed from the pool. The minimum allowed value is 10000ms (10 seconds). Default: 600000 (10 minutes)

This only applies if minimumIdle is less than maximumPoolSize, when it's not a fixed-size pool.

spring.datasource.hikari.keepaliveTime

This property controls how frequently HikariCP will attempt to keep a connection alive, in order to prevent it from being timed out by the database or network infrastructure. This value must be less than the maxLifetime value. A “keepalive” will only occur on an idle connection. The minimum allowed value is 30000ms (30 seconds), but a value in the range of minutes is most desirable. Default: 0 (disabled)

Setting this to a value higher or equal to 30 seconds will make the pool periodically call the JDBC4 connection method isValid. This method in turn is implemented in the PostgreSQL JDBC driver by passing an empty statement.

It's advised also to align the keep alive time with the load balancer's TCP client keep-alive timeout. In HAProxy for example, a timeout of 5min is common (timeout client).

spring.datasource.hikari.maxLifetime

This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. The minimum allowed value is 30000ms (30 seconds). Default: 1800000 (30 minutes)

spring.datasource.hikari.minimumIdle

This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently. Default: same as maximumPoolSize

For a bursty workload, this value should be set to the same value as maximumPoolSize to form a fixed-sized connection pool.

spring.datasource.hikari.maximumPoolSize

This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Basically this value will determine the maximum number of actual connections to the database backend. Default: 10

For a bursty workload, this value should be set to the same value as minimumIdle to form a fixed-sized connection pool.

The maximumPoolSize should reflect the total number of vCPUs for the CockroachDB cluster multiplied by 4 and divided by number of pool instances. The formula is cluster_total_vcpus * 4 / num_pool_instances.

For example, a minimum CockroachDB cluster size of 3 nodes x 4 vCPUs would yield 12 * 4 with a single app instance / pool. If there are instead 4 connection pools, then its 12 * 4 / 4.

This is just a rule of thumb assuming that each VM and connection pool is evenly utilized. Ideally, the total number of active connections shouldn't open more than 4 times the total vCPU count of the CockroachDB cluster.

spring.datasource.hikari.poolName

This property represents a user-defined name for the connection pool and appears mainly in logging and JMX management consoles to identify pools and pool configurations. Default: auto-generated.

YAML Configuration

Example configuration for a spring boot application.yml:

spring:
  datasource:
    hikari:
      maximum-pool-size: 12
      minimum-idle: 12
      max-lifetime: 1800000
      connection-timeout: 10000

See common application properties for more details.

Programmatic Configuration

It's also possible to configure Hikari programmatically, perhaps in combination with the YAML and only override values if they need more dynamic settings.

    @Bean
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.hikari")
    public HikariDataSource hikariDataSource() {
        HikariDataSource ds = dataSourceProperties()
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
        // Configured via application.yml and CLI override
        ds.setMaximumPoolSize(50);
        ds.setMinimumIdle(25);
        // Applies if min idle < max pool size
        ds.setKeepaliveTime(60000);
        ds.setMaxLifetime(1800000);
        ds.setConnectionTimeout(10000);
        ds.setPoolName("spring-boot-pooling");
        // Paired with Environment.CONNECTION_PROVIDER_DISABLES_AUTOCOMMIT=true
        ds.setAutoCommit(false);
        // Batch inserts (PSQL JDBC driver specific, case-sensitive)
        ds.addDataSourceProperty("reWriteBatchedInserts", "true");
        // For observability in DB console
        ds.addDataSourceProperty("application_name", "Spring Boot Pooling");

        return ds;
    }

CLI Configuration

When using the YAML approach its easy to override the default settings through the CLI:

java -jar app.jar \
--spring.datasource.hikari.maximum-pool-size=45 \
--spring.datasource.hikari.minimum-idle=25 \
--spring.datasource.hikari.max-lifetime=1800000 \
"$@"

Explicit vs Implicit Transactions

When dealing with both implicit and explicit transactions in the same application, it's better to stick with the default autoCommit=true connection setting. All read-only transactions will then be implicit rather than explicit and can benefit from CockroachDB's server-side retries and time-travel queries.

Implicit transaction example

    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public Long sumTotalInventory() {
        Assert.isTrue(!TransactionSynchronizationManager.isActualTransactionActive(), "Tx active");
        return productRepository.sumTotalInventory();
    }

Hint: When using JDBC, you can also execute writes in implicit transactions.

Explicit transaction example

    @Transactional(propagation = Propagation.REQUIRES_NEW, readOnly = true)
    public ProductEntity findById(UUID id) {
        Assert.isTrue(TransactionSynchronizationManager.isActualTransactionActive(), "Tx not active");
        return productRepository.findById(id).orElseThrow(
                () -> new IllegalArgumentException("No such product: " + id));
    }

Demo

The following sample application in Github is using Spring Boot with HikariCP.

It provides a few REST API endpoints for managing product's, available via http://localhost:8090/. lt also exposes Spring actuator endpoints that can be used to monitor the Hikario pool stats. To interact with the API the easiest way is to use cURL.

curl -s http://localhost:8090/actuator/metrics | jq
{
  "names": [
    "application.ready.time",
    "application.started.time",
    "disk.free",
    "disk.total",
    "executor.active",
    "executor.completed",
    "executor.pool.core",
    "executor.pool.max",
    "executor.pool.size",
    "executor.queue.remaining",
    "executor.queued",
    "hikaricp.connections",
    "hikaricp.connections.acquire",
    "hikaricp.connections.active",
    "hikaricp.connections.creation",
    "hikaricp.connections.idle",
    "hikaricp.connections.max",
    "hikaricp.connections.min",
    "hikaricp.connections.pending",
    "hikaricp.connections.timeout",
    "hikaricp.connections.usage",
    "http.server.requests",
    "jdbc.connections.max",
    "jdbc.connections.min",
    "jetty.connections.bytes.in",
    "jetty.connections.bytes.out",
    "jetty.connections.current",
    "jetty.connections.max",
    "jetty.connections.messages.in",
    "jetty.connections.messages.out",
    "jetty.connections.request",
    "jetty.threads.busy",
    "jetty.threads.config.max",
    "jetty.threads.config.min",
    "jetty.threads.current",
    "jetty.threads.idle",
    "jetty.threads.jobs",
    "jvm.buffer.count",
    "jvm.buffer.memory.used",
    "jvm.buffer.total.capacity",
    "jvm.classes.loaded",
    "jvm.classes.unloaded",
    "jvm.gc.live.data.size",
    "jvm.gc.max.data.size",
    "jvm.gc.memory.allocated",
    "jvm.gc.memory.promoted",
    "jvm.gc.overhead",
    "jvm.gc.pause",
    "jvm.memory.committed",
    "jvm.memory.max",
    "jvm.memory.usage.after.gc",
    "jvm.memory.used",
    "jvm.threads.daemon",
    "jvm.threads.live",
    "jvm.threads.peak",
    "jvm.threads.states",
    "logback.events",
    "process.cpu.usage",
    "process.files.max",
    "process.files.open",
    "process.start.time",
    "process.uptime",
    "spring.data.repository.invocations",
    "system.cpu.count",
    "system.cpu.usage",
    "system.load.average.1m"
  ]
}

To zoom in on one of the Hikari metrics:

curl -s http://localhost:8090/actuator/metrics/hikaricp.connections.active | jq

Hint: install json processor (jq) via homebrew:

brew install jq

Conclusion

In this article, we configured the Hikari connection pool DataSource implementation in a Spring Boot application. We also learned about the main configuration parameters and how to optimize for different transaction patterns.