A connection pool manages database connections for your application. Configure pool sizes to avoid running out of connections under high concurrency, and set timeouts to remove stale connections.
This topic describes the recommended settings for connection pool parameters and important parameters for JDBC configuration, to help developers optimize database access.
Connection pool parameters
Suggestions on connection pool settings
For a management console application, a minimum pool size of two connections is generally sufficient. Adjust it for the concurrency and transaction duration of your workload.
Set the idle connection timeout. A value of 30 minutes is recommended.
By default, MySQL actively disconnects idle connections after 8 hours, which the client cannot detect, leading to stale connections. The connection pool can validate whether a connection is alive through mechanisms such as heartbeats or testOnBorrow. Connections that exceed this idle time are directly disconnected.
JDBC configuration parameters
The following JDBC parameters are essential and must be configured. They can be set in the connection properties of the connection pool or in the JDBC URL. The specific parameters and their descriptions are listed in the following table.
Parameter |
Description |
|---|---|
| socketTimeout | Defines the network socket timeout, in milliseconds. A value of 0 indicates no timeout limit. Default value: 0 (standard configuration). |
| connectTimeout | Connection timeout value, in milliseconds. A value of 0 indicates no timeout limit. Default value: 30000. |
JDBC configuration example
This topic provides an example of JDBC configuration.
When using JDBC to connect to a database, you need to configure relevant parameters to achieve optimal database performance. Here are some recommended parameter configurations.
An example of a JDBC connection 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, the involved configuration parameters are as follows:
rewriteBatchedStatements: It is recommended to set this to TRUE.By default, the OceanBase JDBC driver ignores executeBatch() statements and splits a batch of SQL statements into individual ones to send to the database. In this case, batch inserts are actually single-insert operations, directly resulting in low performance. To enable actual batch inserts, set this parameter to TRUE so that the driver executes SQL in batches. That is, use the addBatch method to combine multiple INSERT statements on the same table into one INSERT statement with multiple VALUES, improving the performance of batch inserts.
Use
prepareStatement()to prepare each INSERT statement, and then calladdBatch()to add it to the batch. Otherwise, the statements cannot be combined for execution.
allowMultiQueries: It is recommended to set this to TRUE.The JDBC driver allows application code to concatenate multiple SQL statements with semicolons (;) and send them as a single SQL statement to the server.
useLocalSessionState: Set this to TRUE to reduce queries to OceanBase Database for session variable values during frequent transactions.The main session variables are: autocommit, read_only, and transaction isolation.
socketTimeout: The amount of time the socket waits for an SQL response during execution.connectTimeout: The amount of time waited for a connection to be established.useCursorFetch: It is recommended to set this toTRUE.For queries involving large amounts of data, the database server establishes a cursor and distributes data to the client in chunks based on the FetchSize. Setting this property to
TRUEautomatically setsuseServerPrepStmts=TRUEas well.useServerPrepStmts: Controls whether to use the PS protocol to send SQL statements to the database server.When set to
TRUE, an SQL statement is executed in two steps within the database:The SQL text containing
?is sent to the database server for preparation (SQL_audit: request_type=5).The parameter values are sent to the database server to execute the prepared statement (
SQL_audit: request_type=6).
cachePrepStmts: Controls whether the JDBC driver enables a prepared statement cache to avoid repeated prepares (both on the client and server sides).cachePrepStmts=TRUEis beneficial for scenarios whereuseServerPrepStmts=TRUEis used and the same SQL statement is executed in batches repeatedly. Each batch execution involves both prepare and execute operations; settingcachePrepStmts=TRUEprevents duplicate prepares.prepStmtCacheSQLLimit: The maximum length of an SQL statement that can be cached in the prepared statement cache. SQL statements exceeding this limit cannot be cached. prepStmtCacheSize: The number of SQL statements that the prepared statement cache can store.maxBatchTotalParamsNum: For batch operations, specifies the maximum number of parameters supported per SQL statement (i.e., the number of?placeholders). If the number of parameters exceeds this limit, the batch SQL will be split.