Database connection pool configuration is crucial for ensuring efficient and stable connections between the system and the database. Proper connection pool settings can effectively manage database connections, prevent connection exhaustion due to high concurrency, and ensure system performance through reasonable timeout settings.
This topic introduces the basic concepts, comparisons, selection guide, recommended parameter settings, and important JDBC parameters of connection pools to help developers optimize database access.
Basic concepts of a connection pool
A connection pool is a technique used to manage database connections. It establishes a pool of connections 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 directly obtain a connection from the pool and return the connection to the pool after use, without the need to create 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 exhaustion.
- Connection reuse: Enhances connection utilization and reduces resource waste.
- Connection monitoring: Provides connection status monitoring and statistics.
Connection pool settings recommendations
A connection pool has 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 waiting time for obtaining a connection.
Idle timeout: the maximum idle time of a connection in the pool before it is recycled.
Detection interval: the interval at which idle connections are checked.
Detection query: an SQL query used to check the validity of a connection.
You can retain a minimum of two connections for daily operations in the console. You can adjust the settings based on the business concurrency and transaction duration.
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 by using heartbeats or the testOnBorrow mechanism. When the connection has been idle for this period, the connection is disconnected.
JDBC configuration parameters
The following JDBC parameters are important and must be configured. These parameters can be specified in the connection properties of the connection pool or in the JDBC URL. The table below provides descriptions of the parameters.
Parameter |
Description |
|---|---|
| socketTimeout | The network socket timeout period, 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. |
JDBC configuration example
This topic describes how to configure JDBC.
When you connect to a database by using JDBC, you must configure related parameters to ensure optimal database performance. The following parameters are recommended.
The following code shows an example of a JDBC connection:
conn=jdbc:oceanbase://xxx.xxx.xxx.xxx:3306/test?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=10000&connectTimeout=30000&useCursorFetch=TRUE
The following table describes the parameters involved in the connection.
rewriteBatchedStatements: set the value to TRUE.By default, the JDBC driver of OceanBase Database ignores the executeBatch() statement, sends a group of SQL statements for batch processing as separate SQL statements to the database. In this case, batch insertion is actually single insertion, resulting in low performance. To enable batch insertion, set the parameter to TRUE. Then, the driver can send SQL statements for batch processing. Even if you use the addBatch method to combine multiple insert statements for the same table into one statement with multiple values, you must enable batch insertion for improved performance of batch insert operations.
You must use prepareStatement to prepare each insert statement before adding the statement to the batch. Otherwise, the statements cannot be executed as a batch.
allowMultiQueries: set the value to TRUE.The JDBC driver allows you to concatenate multiple SQL statements with semicolons (;) and send the resulting SQL statement to the server.
useLocalSessionState: set the value to TRUE to avoid frequent session variable queries to the OceanBase database.Session variables include autocommit, read_only, and transaction isolation.
socketTimeout: the time, in seconds, that the socket waits for an SQL response during SQL execution.connectTimeout: the time, in seconds, that the system waits for a connection during connection establishment.useCursorFetch: set the value toTRUE.For a query statement that involves a large amount of data, the database server establishes a cursor and sends data to the client based on the fetch size. If you set this parameter to
TRUE,useServerPrepStmsis set toTRUEautomatically.useServerPrepStms: specifies whether to use the PS protocol to send SQL statements to the database server.If you set the value to
TRUE, SQL statements are executed in two steps in the database:The SQL statement that contains
?is sent to the database server for preparation (SQL_audit: request_type=5).The prepared SQL statement is executed with real values (
SQL_audit: request_type=6).
cachePrepStmts: specifies whether to enable the PS cache of the JDBC driver to cache prepared statements and avoid repeated preparation of statements on both the client and server sides. This parameter is useful in the following scenario: You repeatedly execute the same SQL statement in batches by usinguseServerPrepStms=TRUE. In each batch execution, preparation is performed before execution (executecachePrepStmts=TRUEcan be set to avoid repeated preparation.prepStmtCacheSQLLimit: the maximum length of an SQL statement that can be cached in the PS cache. An SQL statement whose length exceeds the specified limit cannot be cached.prepStmtCacheSize: the maximum number of SQL statements that the PS cache can save.maxBatchTotalParamsNum: the maximum number of parameters supported by one SQL statement in batch operations. In other words, it is the maximum number of?in one SQL statement for batch operations. If the number of parameters exceeds the limit, the SQL statement will be split.