Common JDBC Parameters

targetServerType

Principle: If the value is master, it attempts to connect to the IP addresses configured in the string in sequence until the primary node in the cluster is connected.

If the value is slave, it attempts to connect to the IP addresses configured in the string in sequence until the standby node in the cluster is connected. The query statement is select local_role, db_state from pg_stat_get_stream_replications().

Suggestion: You are advised to set this parameter to master for services with write operations to ensure that the primary node can be properly connected after a primary/standby switchover. However, if the standby node is not completely promoted to primary during the primary/standby switchover, the connection cannot be established. As a result, service statements cannot be executed.

hostRecheckSeconds

Principle: Specifies the period during which the DN list stored in JDBC remains trusted. Within this period, the DN list is directly read from the host addresses stored in JDBC. After that (or the primary node fails to be connected within the specified period), the node status in the DN list is updated and other IP addresses are connected.

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

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.

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.

Suggestion: After this parameter is set, 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. If this parameter needs to be set in a centralized environment, you are advised to set it as follows: max(connectTimeout, socketTimeoutInConnecting) x Number of nodes. This prevents connection failures when the network is abnormal and the nth IP address is the IP address of the primary node. The default value is 0.

• 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. In a centralized environment, the last IP address is the IP address of the primary node. As a result, the automatic search for the primary node may fail.
• 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.