Database connection pool configuration

Database connection pool configuration is a crucial step to ensure efficient and stable connections between the system and the database. Properly configured connection pools can effectively manage the number of database connections, preventing issues like connection exhaustion during high concurrency. Additionally, appropriate timeout settings help in promptly cleaning up invalid connections, ensuring optimal system performance.

This topic introduces the basic concepts of connection pools, compares different connection pool types, provides a selection guide, suggests recommended settings for connection pool parameters, and highlights important JDBC configuration parameters to help developers optimize database access.

Basic concepts of connection pools

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 directly obtain a connection from the connection pool and return it after use, 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: Enhances connection utilization and reduces resource waste.
• Connection monitoring: Provides connection status monitoring and statistical information.

Connection pool parameters

Connection pools typically include the following core parameters:

• Initial connection count: The number of connections created when the connection pool is initialized.
• Minimum connection count: The minimum number of connections maintained in the pool.
• Maximum connection count: The maximum number of connections allowed in the pool.
• Connection timeout: The maximum waiting time for obtaining a connection.
• Idle timeout: The maximum time a connection can remain idle in the pool before being recycled.
• Detection interval: The interval at which idle connections are checked.
• Detection query: An SQL query used to check if a connection is valid.

Recommended connection pool settings

• For the management console, maintaining a minimum of two connections is typically sufficient, though adjustments should be made based on business concurrency and transaction duration.

• Set the idle timeout to 30 minutes.

  By default, MySQL disconnects connections after 8 hours, and clients cannot detect this, leading to stale connections. Connection pools can use mechanisms like heartbeats and testOnBorrow to verify connection validity. If a connection is not used within this time, it will be disconnected.

JDBC configuration parameters

JDBC is the Java Database Connectivity API, and OceanBase provides a JDBC driver to support Java applications in connecting to seekdb. The download link for the JDBC driver is: OceanBase JDBC driver.

Several important JDBC parameters must be set. These can be configured in the connection pool's ConnectionProperties or in the JdbcUrl. The specific parameters and their descriptions are as follows:

Parameter	Description
socketTimeout	Defines the network socket timeout in milliseconds. A value of 0 indicates no timeout limit. You can also use the system variable max_statement_time to limit query time. Default value: 0 (standard configuration).
connectTimeout	The connection timeout 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 for database connections, relevant parameters should be configured to achieve optimal performance. Here are some recommended parameter settings.

Here is an example of a JDBC connection:

conn=jdbc:oceanbase://xxx.xxx.xxx.xxx:2881/test?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=10000&connectTimeout=30000

The configuration parameters involved in this connection are as follows:

• rewriteBatchedStatements: Recommended to set to TRUE.

  • By default, the OceanBase JDBC driver ignores executeBatch() statements and sends each SQL statement individually to the database. This results in lower performance for batch inserts. To enable actual batch inserts, set this parameter to TRUE. This allows the driver to execute SQL statements in batches. Even if you use the addBatch method to combine multiple insert statements for the same table into a single insert statement with multiple values, batch insert performance will be improved.

  • Each insert statement must be prepared using prepareStatement before being added to the batch. Otherwise, they cannot be executed together.

• allowMultiQueries: Recommended to set 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: Recommended to set to TRUE to avoid frequent session variable queries to the OB database.

  Session variables include autocommit, read_only, and transaction isolation.

• socketTimeout: The time the socket waits for SQL results.

• connectTimeout: The time to wait for a connection to be established.

• useCursorFetch: Recommended to set to TRUE.

  For large query statements, the database server creates a cursor and distributes data to the client based on the FetchSize. Setting this property to TRUE automatically sets useServerPrepStmts=TRUE.

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

  When set to TRUE, SQL statements are executed in two steps:

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

  • Real values are used for execution in the database (SQL_audit: request_type=6).

• cachePrepStmts: Controls whether to enable PS cache to cache PreparedStatements, avoiding repeated preparation (on both client and server sides). cachePrepStmts=TRUE helps when using useServerPrepStmts=TRUE and repeatedly executing the same SQL statement in batches. Each batch execute includes prepare and execute operations, which cachePrepStmts=TRUE avoids by caching prepared statements.

• prepStmtCacheSQLLimit: The maximum length of SQL statements that can be cached in the PS cache. Statements longer than this limit cannot be cached. prepStmtCacheSize: The maximum number of SQL statements that can be cached in the PS cache.

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