Connection Pool Parameter Governance: How to Configure HikariCP for Stability in KingbaseES Scenarios

An application being able to connect to a database is only the first step. Under real concurrent access, the connection pool is the most critical buffer layer between the application and the database. If the pool is too small, interfaces will queue; if too large, the database will be dragged down by numerous connections; unreasonable timeout settings will cause request pile-ups; undetected connection leaks will make the system run slower and slower.

Spring Boot defaults to using HikariCP as the connection pool. This article focuses on a local Windows 11 development environment and a KingbaseES database on a CentOS 7.6 server, explaining how to understand, configure, and verify the key parameters of HikariCP. The example connection continues to use the kb_app and app_user created in the first article.

@[toc]

---

1. Why You Can't Just Use Defaults

Many projects only write this when first connecting to a database:

spring:
  datasource:
    url: jdbc:kingbase8://192.168.10.101:54321/kb_app
    username: app_user
    password: App_user_123

This indeed allows the application to start and access interfaces. But the default connection pool parameters may not suit your business:

• Under high concurrency, connections are insufficient, and requests will wait.
• An excessively large maximum connection count will squeeze database session resources.
• A connection acquisition timeout that is too long will cause user requests to hang for an extended period.
• Keeping too many idle connections wastes database connections.
• Without connection leak detection, problems will only be exposed when they become production incidents.

A connection pool is not better just because it's larger; it must be designed together with the application thread count, the database's maximum connections, and the average business SQL execution time.

2. First, Check How Many Connections the Database Can Handle

Before tuning HikariCP, first check the maximum connection count inside the KingbaseES database:

SHOW max_connections;

Note: For the session status views used below for observation, please refer to your current KingbaseES version and product manual; if the view names or field names differ, you can first confirm them in ksql using \d, graphical tool metadata, or the administrator's manual before substituting.

Next, check the current connection status:

SELECT state, COUNT(*)
FROM sys_stat_activity
GROUP BY state
ORDER BY COUNT(*) DESC;

If the view names or fields differ in your environment, the actual version shall prevail. The core idea is to confirm two things:

• The maximum number of connections the database allows.
• How many connections are currently in use.

For example, if the database's maximum connection count is 100, but the same database also serves background tasks, reporting tools, operations tools, and other applications, then a single application cannot monopolize all connections. A relatively safe starting point is to give a single application 10 to 30 connections first, then adjust based on stress test results.

3. HikariCP Core Parameters

A basic configuration example:

spring:
  datasource:
    url: jdbc:kingbase8://192.168.10.101:54321/kb_app
    username: app_user
    password: App_user_123
    hikari:
      pool-name: kb-app-pool
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 3000
      idle-timeout: 600000
      max-lifetime: 1800000
      validation-timeout: 2000
      leak-detection-threshold: 10000

Explanations follow one by one.

1. maximum-pool-size

The maximum connection count determines how many database connections this application can occupy simultaneously at most.

Don't arbitrarily set it to 100; you should consider:

• Database max_connections.
• The number of other applications on the same database.
• The number of application instances.
• Interface concurrency.
• Average SQL execution time.

For example, if there are 4 application instances, each configured with maximum-pool-size=50, theoretically they could occupy up to 200 database connections. Many connection exhaustion problems are amplified by configurations like this.

2. minimum-idle

The minimum idle connection count indicates how many idle connections the connection pool tries to retain.

In a development environment, it can be smaller, for example, 2 to 5. In a production environment, it should be determined based on traffic fluctuations. If the business has obvious peak times, retaining a certain number of idle connections can reduce the cost of temporary connection establishment, but don't let idle connections remain excessive for long periods.

3. connection-timeout

The maximum waiting time to acquire a connection from the pool. In the example, it is configured as 3000 milliseconds.

This parameter is not recommended to be too long. If an interface cannot get a connection, it means the system is already congested. Letting a request hang for 30 seconds before failing will only drag down application threads. Generally, you can start from 3 to 5 seconds and then adjust based on business tolerance.

4. idle-timeout

The survival time for idle connections. Idle connections exceeding this time may be reclaimed.

If the business has long low-peak periods, it can be appropriately shortened to reduce idle connection occupation. Don't frequently reclaim and then frequently create, otherwise it will increase the pressure of database connection establishment.

5. max-lifetime

The maximum lifecycle of a connection. After exceeding this time, the connection will be replaced by the pool.

This value should be less than the time at which network devices, databases, or middle layers might actively disconnect. A common starting point is 30 minutes. Don't set it to infinite.

6. leak-detection-threshold

The connection leak detection threshold. If a connection is borrowed and not returned for longer than the specified time, a warning log will be printed.

It is recommended to enable this in development and testing environments, for example, 10 seconds or 30 seconds. Whether to enable it in production requires a comprehensive evaluation of log volume and performance impact. When code has unclosed connection issues, the warning logs from this parameter can directly pinpoint the call stack.

4. How to Estimate the Maximum Connection Count

A simple estimation approach can be used:

Single instance max connections = Target concurrent requests * Database occupation time per request / Total request time

Example:

• Single instance target concurrent requests: 100
• Average time per request: 200ms
• Database operation time within that: 40ms

Then the number of requests simultaneously occupying database connections is approximately:

100 * 40 / 200 = 20

At this point, the single-instance connection pool maximum can be initially set to 20 to 30, then observed through stress testing. This estimation is not an absolute formula, but it is more reliable than directly setting it to 100.

5. Observing Connection Pool Effects on the Database Side

After the application starts, we need to check inside the database to see exactly where these connections are coming from. You can execute the following SQL:

SELECT usename, application_name, client_addr, state, COUNT(*)
FROM sys_stat_activity
GROUP BY usename, application_name, client_addr, state
ORDER BY COUNT(*) DESC;

Actually, I suggest using the ApplicationName parameter in the JDBC URL to set an identifier for the application. This makes it much easier to see the connection source from the database side:

jdbc:kingbase8://192.168.10.101:54321/kb_app?ApplicationName=kb-app-demo

After you set this, the sys_stat_activity.application_name field will display the corresponding application name. Generally, if there are multiple applications or multiple instances sharing the same database, this method is particularly useful.

So you need to focus on these things:

• How many connections app_user currently has.
• Whether idle connections are excessive for a long time.
• Whether active connections are consistently near the pool's upper limit.
• Whether there are connections that haven't been released for a long time.

If the application establishes a large number of connections right after starting, check the minimum-idle parameter. If a large number of requests report connection acquisition timeouts during stress testing, you need to investigate SQL execution time, the connection pool upper limit, and the application's thread count.

6. Connection Pool and Application Thread Pool Must Be Considered Together

When many projects tune the database connection pool, they often only look at the connection pool itself, ignoring the Web container's thread pool. This is a problem.

Why does this happen? Think about it. If Tomcat's maximum thread count is 100, and Hikari's maximum connection count is only 10, then in a situation where a large number of interfaces need to access the database, those 90 threads might have to queue up waiting for connections.

The reverse is also true. If Tomcat's maximum thread count is 100, and Hikari's maximum connection count is also 100, if the database cannot handle so many connections, the database itself will become the bottleneck.

So what is a more robust approach?

• First, control the Web thread pool; don't accept requests without limit.
• Next, control the database connection pool; don't let the database be overwhelmed.
• Finally, through stress testing, examine the average response time, P95, connection pool wait time, and the database's active sessions.

7. Recommended Configuration for Development Environment

For a local Windows 11 development environment, the configuration can be conservative:

spring:
  datasource:
    hikari:
      pool-name: kb-dev-pool
      maximum-pool-size: 5
      minimum-idle: 1
      connection-timeout: 3000
      idle-timeout: 300000
      max-lifetime: 1800000
      leak-detection-threshold: 10000

Actually, in a development environment, the focus is really not on throughput. The focus is on discovering connection leaks, incorrect account permissions, and SQL errors as early as possible.

8. Suggestions for Testing and Production Environments

For a testing environment, you can refer to this:

spring:
  datasource:
    hikari:
      pool-name: kb-test-pool
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 3000
      idle-timeout: 600000
      max-lifetime: 1800000
      leak-detection-threshold: 30000

For a production environment, it must be based on stress test results. I do not recommend copying directly. At least you must confirm the following points:

• What is the database's maximum connection count?
• How many application instances are there?
• Are there also reports, tasks, or management tools sharing this database?
• During peak times, what is the average SQL execution time, and how many slow SQLs are there?
• Is the number of connection acquisition timeouts high?

9. Common Configuration Mistakes

1. Thinking a Larger Maximum Connection Count is Always Better

If the connection count is too large, the pressure on database scheduling increases. Very often, reducing slow SQLs is much more effective than increasing the connection count.

2. Setting a Very Long Connection Acquisition Timeout

This will cause user requests to hang continuously and also occupy application threads. When the system is congested, it should actually fail fast and then trigger an alert.

3. Not Enabling Leak Detection in the Development Environment

The earlier a connection leak is discovered, the lower the cost. In a development environment, leak detection should be turned on.

4. Not Aggregating Connection Counts Across Multiple Application Instances

30 connections per instance, 10 instances means 300 connections. When scaling out applications, you must recalculate the database's connection budget.

10. Summary

HikariCP's parameters are not isolated configurations. You must consider them together with the database's maximum connection count, the number of application instances, the Web thread pool, and the average SQL execution time. A robust approach is to start conservatively, then verify with stress testing, observe database sessions, and finally adjust gradually.

In the next article, we will move closer to production issues. When the connection count suddenly spikes and interfaces start to hang, how to investigate from three lines simultaneously: database sessions, the application thread pool, and connection pool logs.