Help Center/ GaussDB/ Developer Guide(Distributed_2.x)/ Application Development Guide/ Development Based on JDBC/ Example: Parameters for Connecting to the Database in Different Scenarios
Updated on 2024-10-14 GMT+08:00
View PDF
Share

Example: Parameters for Connecting to the Database in Different Scenarios

In the following example, host:port represents a node, where host indicates the name or IP address of the server where the database resides, and port indicates the port number of the server where the database resides.

Load Balancing

A customer has a database cluster that contains the following nodes: {node1,node2,node3,node4,node5,node6,node7,node8,node9,node10,node11,node12}.

  1. The customer establishes 120 persistent connections in application A and expects that the connections on application A can be evenly distributed on each node in the current cluster. The URL can be configured as follows. 
    jdbc:postgresql://node1,node2,node3/database?autoBalance=true
  2. The customer develops two applications B and C and wants the three applications to be evenly distributed on specified nodes. For example, the connections of application A are distributed on {node1,node2,node3,node4}. The connections of application B are distributed on {node5,node6,node7,node8}. The connections of application C are distributed on {node9,node10,node11,node12}. The URLs can be configured as follows.

    Application A: jdbc:postgresql://node1,node2,node3,node4,node5/database?autoBalance=priority4

    Application B: jdbc:postgresql://node5,node6,node7,node8,node9/database?autoBalance=priority4

    Application C: jdbc:postgresql://node9,node10,node11,node12,node1/database?autoBalance=priority4

  3. The customer develops more applications, uses the same connection configuration string, and expects that the application connections can be evenly distributed on each node in the cluster. The URL can be configured as follows. 
    jdbc:postgresql://node1,node2,node3,node4/database?autoBalance=shuffle
  4. If the customer does not want to use the load balancing function, configure the URL as follows. 
    jdbc:postgresql://node1/database

    Or

    jdbc:postgresql://node1/database?autoBalance=false

    When the autoBalance parameter is enabled, the interval for the JDBC to refresh the available CN list is 10s by default. You can use refreshCNIpListTime to set the interval:

    jdbc:postgresql://node1,node2,node3,node4/database?autoBalance=true&refreshCNIpListTime=3

Log Diagnosis

To locate faults, a customer can enable the trace log function for diagnosis. The URL can be configured as follows.

jdbc:postgresql://node1/database?loggerLevel=trace&loggerFile=jdbc.log

High Performance

A customer may execute the same SQL statement for multiple times with different input parameters. To improve the execution efficiency, the prepareThreshold parameter can be enabled to avoid repeatedly generating execution plans. The URL can be configured as follows.

jdbc:postgresql://node1/database?prepareThreshold=5

A customer queries 10 million data records at a time. To prevent memory overflow caused by simultaneous return of the data records, the defaultRowFetchSize parameter can be used. The URL can be configured as follows.

jdbc:postgresql://node1/database?defaultRowFetchSize=50000

A customer needs to insert 10 million data records in batches. To improve efficiency, the batchMode parameter can be used. The URL can be configured as follows.

jdbc:postgresql://node1/database?batchMode=on