Database connection pool configuration

Configuring a database connection pool is a crucial step to ensure efficient and stable connections between your system and the database. A well-configured connection pool not only manages the number of database connections to prevent connection exhaustion in high-concurrency scenarios, but also cleans up invalid connections based on timeout settings to maintain system performance.

This topic introduces the basic concepts of connection pools, compares different types of connection pools, provides a guide for selecting the right one, suggests optimal parameter settings, and highlights important JDBC configuration parameters to help developers optimize database access.

Basic concepts of connection pools

A database connection pool is a technology for managing database connections. It establishes a pool of connections between the application and the database by pre-creating a certain number of database connections and storing them in memory. When the application needs to access the database, it can directly obtain a connection from the connection pool. After use, the connection is returned to the pool, rather than creating a new connection each time.

The main advantages of a connection pool include:

• Improved performance: Avoids the overhead of frequently creating and destroying connections.
• Resource management: Controls the number of database connections to prevent connection exhaustion.
• Connection reuse: Enhances connection utilization and reduces resource waste.
• Connection monitoring: Provides monitoring of connection status and statistics.

Connection pool parameters

Connection pool settings

• It is recommended to maintain at least two connections in the console for daily operations. The exact number can be adjusted according to business concurrency and transaction duration.

• Set the connection idle timeout to 30 minutes.

  By default, connections are actively disconnected after 8 hours, but the client cannot detect this, which can lead to stale connections. Connection pools can use mechanisms such as heartbeats or testOnBorrow to verify whether connections are still alive. If a connection remains unused beyond the specified timeout, it should be disconnected.

JDBC configuration parameters

Several important JDBC parameters must be configured. These can be set in the connection pool’s ConnectionProperties or directly in the JdbcUrl. The specific parameters and their descriptions are shown in the table below.

JDBC configuration example

This topic describes a JDBC configuration example.

When you use JDBC to connect to a database, you must configure relevant parameters to ensure optimal performance. This topic recommends some parameter configurations.

A JDBC connection is described as follows:

conn=jdbc:oceanbase://xxx.xxx.xxx.xxx:3306/test?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=10000&connectTimeout=30000

The parameters involved in the connection are described as follows:

• rewriteBatchedStatements: We recommend that you set this parameter to TRUE.

  • By default, the OceanBase JDBC driver ignores the executeBatch() statement and sends each SQL statement in a batch to the database separately. This results in single insert operations instead of batch insert operations, causing poor performance. To enable true batch inserts, set this parameter to TRUE. The driver will then execute SQL statements in batches, combining multiple insert statements for the same table into a single insert statement with multiple values, which improves performance.

  • You must use the prepareStatement() method to prepare each insert statement before adding it to the batch. Otherwise, the statements cannot be merged for batch execution.

• allowMultiQueries: We recommend that you set this parameter to TRUE.

  The JDBC driver allows you to concatenate multiple SQL statements with semicolons (;) and send them to the server as a single SQL statement.

• useLocalSessionState: We recommend that you set this parameter to TRUE to avoid frequent session variable queries being sent to the OceanBase database.

  The relevant session variables are: autocommit, read_only, and transaction isolation.

• socketTimeout: Specifies the time that the socket waits for a SQL statement to return a result.

• connectTimeout: Specifies the time the driver waits for a connection to be established.

• useCursorFetch: We recommend that you set this parameter to TRUE.

  For queries involving large amounts of data, the database server establishes a cursor and sends data to the client based on the fetch size. When this parameter is set to TRUE, useServerPrepStms is automatically enabled.

• useServerPrepStms: Specifies whether to use the Prepared Statement (PS) protocol to send SQL statements to the database server.

  When set to TRUE, SQL statements are executed in two steps in the database:

  • The SQL statement containing ? placeholders is sent to the database server for preparation (SQL_audit: request_type=5).

  • The actual values are sent for execution in the database (SQL_audit: request_type=6).

• cachePrepStmts: Specifies whether to enable PS cache in the JDBC driver to cache Prepared Statements, thereby avoiding repeated preparation (on both the client and server sides). Setting this parameter to TRUE is especially helpful when using useServerPrepStms=TRUE and repeatedly executing batch operations on the same SQL statement.

• prepStmtCacheSQLLimit: Specifies the maximum length of an SQL statement that can be cached in the PS cache. SQL statements exceeding this length cannot be cached. prepStmtCacheSize: Specifies the maximum number of SQL statements that can be stored in the PS cache.

• maxBatchTotalParamsNum: Specifies the maximum number of parameters that a single SQL statement can support in a batch operation (i.e., the number of ? placeholders). If the number of parameters exceeds this limit, the batch SQL statement will be split.