Database connection pool configuration

Configuring a database connection pool is an important step to ensure efficient and stable connections between your system and the database. Proper connection pool settings can help you manage the number of database connections effectively, avoid connection exhaustion caused by high concurrency, and clean up invalid connections in a timely manner through appropriate timeout settings to maintain system performance.

This topic describes recommended connection pool settings and important JDBC configuration parameters to help developers optimize database access.

Connection pool parameters

Recommendations on connection pool settings

• For routine console use, keep a minimum of two connections. Adjust the setting based on business concurrency and transaction duration.

• Set the connection idle timeout period. We recommend 30 minutes.

  By default, MySQL disconnects a connection that has been idle for 8 hours. The client cannot detect this disconnection, which results in dirty connections. A connection pool can use mechanisms such as heartbeats and testOnBorrow to verify whether a connection is alive. When a connection has been idle longer than this period, it is disconnected directly.

JDBC configuration parameters

The following JDBC parameters are important and must be configured. You can specify them in the connection properties of the connection pool or in the JDBC URL. The following table describes the parameters.

JDBC configuration example

This section provides a JDBC configuration example.

When you use JDBC to connect to a database, configure the related parameters to obtain the best database performance. The following parameters are recommended.

A sample JDBC connection URL 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

The configuration parameters in this connection URL are described as follows:

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

  • By default, the OceanBase JDBC driver ignores executeBatch() and sends each SQL statement in a batch to the database individually. In this case, batch insert is actually single-row insert, which results in low performance. To perform batch insert, set this parameter to TRUE so that the driver executes SQL statements in batch. That is, use addBatch to combine multiple INSERT statements for the same table into one INSERT statement with multiple VALUES clauses to improve batch insert performance.

  • You must use prepareStatement to prepare each INSERT statement before calling addBatch. Otherwise, the statements cannot be merged for execution.

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

  The JDBC driver allows application code 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 frequently sending session variable query SQL statements to the OceanBase database during transactions.

  Session variables mainly include autocommit, read_only, and transaction isolation.

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

• connectTimeout: The time to wait when a connection is being established.

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

  For queries that return a large amount of data, the database server creates a cursor and sends data to the client based on the fetch size. When this parameter is set to TRUE, useServerPrepStms=TRUE is set automatically.

• useServerPrepStms: Controls whether the PS protocol is used to send SQL to the database server.

  When this parameter is set to TRUE, SQL execution on the database server consists of two steps:

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

  • Execute the SQL on the database server using actual values (SQL_audit: request_type=6).

• cachePrepStmts: Controls whether the JDBC driver enables PS cache to cache PreparedStatement objects and avoid repeated prepare operations on the client and server. cachePrepStmts=TRUE is helpful for scenarios that use useServerPrepStms=TRUE and repeatedly batch execute the same SQL statement. Each batch execute includes prepare and execute operations. cachePrepStmts=TRUE can avoid repeated prepare operations.

• prepStmtCacheSQLLimit: The length limit of SQL statements that can be placed in the PS cache. SQL statements that exceed this limit cannot be cached. prepStmtCacheSize: The number of SQL statements that the PS cache can store.

• maxBatchTotalParamsNum: For batch operations, the maximum number of parameters supported in a single SQL statement (that is, the number of ? placeholders in a batch). If the number of parameters exceeds the limit, the batch SQL is split.