Common JDBC Parameters

allowReadOnly

Principle: Checks whether the transaction access mode can be modified through setReadOnly. If the value is true, the transaction access mode can be modified. If the value is false, the transaction access mode cannot be modified through this API. To modify the transaction access mode, execute the statements: SET SESSION CHARACTERISTICS AS TRANSACTION + READ ONLY / READ WRITE.

Suggestion: The default value true is recommended.

autoBalance

Principle: If this parameter is set to true, balance, or roundrobin, the JDBC load balancing function is enabled to balance multiple connections of applications to available CNs in the database cluster. When a connection is established for the first time, the IP address configured in the JDBC connection string is used to establish the connection. As long as the connection can be established using one of the IP addresses, the internal available CN list is updated. Then, connections are established based on the CN list instead of the IP addresses in the JDBC connection string.

Suggestion: The recommended value is true.

refreshCNIpListTime

Principle: When a new connection is established, the system checks whether the interval between the current time and the last update time is greater than the value of this parameter. If the interval is greater than this value, the system queries the available CN list in the database and updates the CN list of JDBC. If the interval exceeds the value of this parameter but no new connection is established, the system does not update the CN list.

Suggestion: The default value is 10s. You are advised to adjust the value based on service requirements. This parameter must be used together with autoBalance.

fetchsize

Principle: After fetchsize is set to n and the database server executes a query, JDBC communicates with the server when the caller executes resultset.next(), fetches n pieces of data to the JDBC client, and returns the first piece of data to the caller. When the caller fetches the (n+1)th data record, the caller fetches data from the database server again.

Function: Prevents the database from transmitting all results to the client at a time, which exhausts the memory resources of the client.

Suggestion: You are advised to set this parameter based on the amount of data queried by services and the memory of the client. When setting fetchsize, disable automatic commit (set autocommit to false). Otherwise, the setting of fetchsize does not take effect.

defaultRowFetchSize

Function: The default value of fetchsize is 0. Setting defaultRowFetchSize will change the default value of fetchsize.

batchMode

Function: This parameter specifies whether to connect the database in batch mode. The default value is on. After the function is enabled, the batch update performance is improved, and the return value is also batch updated. For example, if three data records are inserted in batches, the return value is [3,0,0] when the function is enabled, and the return value is [1,1,1] when the function is disabled.

Suggestion: If the service framework (such as hibernate) checks the return value during batch update, you can set this parameter to solve the problem.

loginTimeout

Function: Controls the time for establishing a connection with the database. The time includes connectTimeout and socketTimeoutInConnecting. If the time elapsed exceeds the threshold, the connection exits. The calculation formula is as follows: loginTimeout = (connectTimeout + Connection authentication time + Initialization statement execution time) x Number of nodes. The default value is 0.

Suggestion: After the configuration, an asynchronous thread is started each time a connection is established. If there are a large number of connections, the pressure on the client may increase. You can adjust the value based on service requirements. You are advised to set it as follows: max(connectTimeout, socketTimeoutInConnecting) x Number of nodes.

• This parameter sets the time for attempting to connect to all IP addresses in a list. If this parameter is set to a small value, the subsequent IP addresses in the list may fail to be connected. For example, if three IP addresses are set, loginTimeout is set to 5s, and it takes 5s to connect to the first two IP addresses, the third IP address cannot be connected.
• When any of the CPU, memory, and I/O load approaches 100%, the connection is slow, which may cause the connection time to exceed the threshold. You can locate the fault as follows:
  1. Log in to a physical machine with slow connections or use a management tool to query the resource load. You can run the top command to check the CPU usage, run the free command to check the memory usage, and run the iostat command to check the I/O load. In addition, you can check the monitoring logs in the CM Agent and the monitoring records on the database O&M platform.
  2. For peak load scenarios caused by a large number of slow queries in a short period of time, you can use the port specified by [Port number of the database server + 1] to query the pg_stat_activity view. For slow queries, you can use the system function pg_terminate_backend(pid int) to kill sessions.
  3. If service overloading exists for a long time (that is, there is no obvious slow query, or new queries still become slow after slow queries are killed), reduce the service load and increase database resources.

cancelSignalTimeout

Function: A cancel message may cause a block. This parameter controls connect timeout and socket timeout in a cancel command, in seconds. It is used to prevent timeout detection from being performed when the connection is canceled due to timeout.

Suggestion: The default value is 10s. You are advised to adjust the value based on service requirements.

connectTimeout

Function: Controls the socket timeout threshold during connection setup. In this case, this timeout threshold is the time when the JDBC connects to the database through the socket, not the time when the connection object is returned. If the time elapsed exceeds the threshold, JDBC searches for the next IP address.

Suggestion: This parameter determines the maximum timeout interval for establishing a TCP connection on each node. If a network fault occurs on a node, it attempts to connect to the node until the time specified by connectTimeout elapses, and then attempts to connect to the next node. Considering the network jitter and delay, you are advised to set this parameter to 3s.

socketTimeout

Function: Controls the timeout threshold of socket operations. If the time of executing service statements or reading data streams from the network exceeds the threshold (that is, when the statement execution time exceeds the specified threshold and no data is returned), the connection is interrupted.

Suggestion: This parameter specifies the maximum execution time of a single SQL statement. If the execution time of a single SQL statement exceeds the value of this parameter, an error is reported and the statement exits. You are advised to set this parameter based on service characteristics. If this parameter is not set, the default value 0 is used, indicating that the execution of SQL statement does not time out. If this parameter is not set, the client waits for a long time when the database process is abnormal. You are advised to set this parameter based on the SQL execution time acceptable to services.

socketTimeoutInConnecting

Function: Controls the socket operation timeout value during connection establishment. If the time of reading data streams from the network exceeds the threshold, it attempts to search for the next node for connection.

Suggestion: This parameter affects only the socket timeout value during the connection establishment. If this parameter is not set, the default value 5s is used.

autosave

Function: If the value is always, you can set a savepoint before each statement in a transaction. If an error is reported during statement execution in a transaction, the system returns to the latest savepoint. In this way, subsequent statements in the transaction can be properly executed and committed. If the value is conservative, a savepoint is set for each query. However, the system rolls back and retries only when there is an invalid statement. If the value is never, there is no savepoint.

Suggestion: You are advised not to set this parameter to avoid severe performance deterioration.

currentSchema

Function: Specifies the schema of the current connection. If this parameter is not set, the default schema is the username used for the connection.

Suggestion: You are advised to set this parameter to the schema where the service data is located. If the schema name contains special characters except letters, digits, and underscores (_), you are advised to enclose the schema name in quotation marks. Note that the schema name is case sensitive after quotation marks are added. If multiple schemas need to be configured, separate them with commas (,). Schemas containing special characters also need to be enclosed in quotation marks.

prepareThreshold

Function: The default value is 5. If an SQL statement is executed for multiple consecutive times in a session and the number of execution times specified by prepareThreshold is reached, JDBC does not send the PARSE command to the SQL statement but caches the SQL statement to improve the execution speed.

Suggestion: The default value is 5. Adjust the value based on service requirements.

preparedStatementCacheQueries

Function: Specifies the number of queries cached in each connection. The default value is 256. If more than 256 different queries are used in the prepareStatement() call, the least recently used query cache will be discarded.

Suggestion: The default value is 256. Adjust the value based on service requirements. This parameter is used together with prepareThreshold.

blobMode

Function: Sets the setBinaryStream method to assign values to different types of data. The value on indicates that values are assigned to BLOB data. The value off indicates that values are assigned to bytea data. The default value is on. For example, you can assign values to parameters in the preparestatement and callablestatement objects.

Suggestion: The default value is on.

setAutocommit

Function: If the value is true, a transaction is automatically started when each statement is executed. After the execution is complete, the transaction is automatically committed. That is, each statement is a transaction. If the value is false, a transaction is automatically started. However, you need to manually commit the transaction.

Suggestion: Adjust the value based on service characteristics. If autocommit needs to be disabled for performance or other purposes, the application must ensure that transactions can be committed. For example, explicitly commit translations after specifying service SQL statements. Particularly, ensure that all transactions are committed before the client exits.