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 recommended connection pool settings and key JDBC configuration parameters to help developers optimize database access.
Connection pool parameters
Recommended connection pool settings
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
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.
| Parameter | Description |
|---|---|
| socketTimeout | The network socket timeout, in ms. The value 0 specifies not to set a timeout period. You can also set the system variable max_statement_time to limit the query time. Default value: 0 (standard). |
| connectTimeout | The connection timeout period, in ms. The value 0 specifies not to set a timeout period. Default value: 30000. |
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 toTRUE.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 toTRUE, it will automatically setuseServerPrepStmts=TRUE.useServerPrepStms: 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 useServerPrepStms=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.