Database connection pool configuration

Configuring the database connection pool is essential for maintaining efficient and stable connections between the system and the database. Proper connection pool settings not only help manage the number of database connections effectively but also prevent connection exhaustion caused by high concurrency. Additionally, appropriate timeout settings ensure invalid connections are promptly cleared, thereby ensuring system performance.

This topic introduces the basic concepts of connection pools, comparisons of different connection pools, selection guidelines, recommended connection pool parameter settings, and important JDBC configuration parameters to help developers optimize database access.

Connection pool basic concepts

A database connection pool is a technology used to manage database connections. It establishes a connection pool between the application and the database, pre-creates a certain number of database connections, and stores these connections in memory. When the application needs to access the database, it can obtain a connection directly from the connection pool. After use, the connection is returned to the pool instead of creating a new connection each time.

The main advantages of connection pools 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: Improves connection utilization and reduces resource waste.
• Connection monitoring: Provides connection status monitoring and statistics.

Connection pool parameters

Connection pools typically include the following core parameters:

• Initial connections: The number of connections created when the connection pool is initialized.
• Minimum connections: The minimum number of connections maintained in the connection pool.
• Maximum connections: The maximum number of connections allowed in the connection pool.
• Connection timeout: The maximum wait time for obtaining a connection.
• Idle timeout: The maximum time a connection can remain idle in the pool. Connections exceeding this time are recycled.
• Detection interval: The interval for checking idle connections.
• Detection query: The SQL query used to verify whether a connection is valid.

Connection pool settings recommendations

• You can retain a minimum of two connections for the console and adjust the configuration based on the business concurrency and transaction duration.

• We recommend that you set the idle connection timeout period to 30 minutes.

  By default, MySQL disconnects a connection that has been idle for 8 hours, which cannot be sensed by the client. This results in dirty connections. The connection pool can check whether a connection is alive through mechanisms such as heartbeats or testOnBorrow. When the connection has been idle for this period, the connection is disconnected.

JDBC configuration parameters

JDBC is the Java Database Connectivity interface. OceanBase provides the JDBC driver, which supports Java applications connecting to OceanBase Database. The download address for the JDBC driver is: OceanBase JDBC Driver.

Several important JDBC parameters must be configured. These parameters can be set either in the connection pool's ConnectionProperties or directly in the JdbcUrl. The table below provides descriptions of the parameters.

Example of JDBC configuration

This section provides an example of JDBC configuration.

When you connect to a database using JDBC, you need to configure relevant parameters to achieve optimal database performance. The following recommendations are provided for your reference.

A JDBC connection example is 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

In this connection string, the relevant parameters 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 a batch of SQL statements to the database one by one. In this case, batch inserts are actually executed as individual inserts, leading to lower performance. To enable true batch inserts, set this parameter to TRUE. When enabled, the driver will execute SQL statements in batches. Specifically, the addBatch method can group multiple INSERT statements for the same table into a single INSERT statement with multiple VALUES, significantly improving batch insert performance.

  • Note that you must use the prepareStatement method to prepare each INSERT statement before adding it to the batch. Otherwise, the statements cannot be merged for 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 as a single SQL statement to the server.

• useLocalSessionState: We recommend that you set this parameter to TRUE to avoid frequently sending session variable query SQL statements to OceanBase Database during transactions.

  The session variables include autocommit, read_only, and transaction isolation.

• socketTimeout: The time that the socket waits for the SQL execution result to return.

• connectTimeout: The time to wait when establishing a connection.

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

  For queries involving large amounts of data, the database server will create a cursor and distribute data to the client based on FetchSize. When this property is set to TRUE, it will automatically set useServerPrepStmts=TRUE.

• useServerPrepStmts: Specifies whether to use the PS protocol to send SQL statements to the database server.

  If set to TRUE, the SQL statement is executed in two steps in the database:

  • Send the SQL text containing ? to the database server for preparation (SQL_audit: request_type=5).

  • Execute the prepared SQL with actual values in the database (SQL_audit: request_type=6).

• cachePrepStmts: Specifies whether to enable the PS cache for the JDBC driver to cache prepared statements and avoid repeated preparation on both the client and server sides. Setting cachePrepStmts=TRUE is helpful in scenarios where useServerPrepStmts=TRUE is used and the same SQL is repeatedly executed in batches. Each batch execution typically includes preparation, but enabling cachePrepStmts=TRUE can eliminate redundant preparation operations.

• prepStmtCacheSQLLimit: The maximum length of an SQL statement that can be cached in the PS cache. An SQL statement longer than this length cannot be cached.

• prepStmtCacheSize: The maximum number of SQL statements that the PS cache can save.

• maxBatchTotalParamsNum: The maximum number of parameters supported by a single SQL statement for batch operations (the maximum number of ? in a batch SQL statement). If the number of parameters exceeds the limit, the SQL statement will be split.