Typical Application Scenarios and Configurations

Log Diagnosis

ODBC logs are classified into unixODBC driver manager logs and gsqlODBC driver logs. The former is used to trace whether the application API is successfully executed, and the latter is used to locate problems based on DFX logs generated during underlying implementation.

1
2
3
4
5
6
7

[ODBC]
Trace=Yes
TraceFile=/path/to/odbctrace.log

[GaussMPP]
Driver64=/usr/local/lib/gsqlodbcw.so
setup=/usr/local/lib/gsqlodbcw.so

[gaussdb]
Driver=GaussMPP
Servername=10.10.0.13 # Database server IP address
...
Debug=1 # Enable the debug log function of the driver.

The unixODBC logs are generated in the path configured by TraceFile. The gsqlODBC generates the mylog_xxx.log file in the /tmp/ directory.

Load Balancing

Example:

Six CNs, namely, CN1, CN2, CN3, CN4, CN5, and CN6, are configured in the cluster, and four CNs, namely, CN1, CN2, CN3, and CN4, are configured in the configuration file.

Example content of the odbc.ini file:

[gaussdb]
Driver=GaussMPP
Servername=10.145.130.26,10.145.130.27,10.145.130.28,10.145.130.29  # IP address of the database server.
Database=db1  # Database name.
Username=omm  # Database username.
Password=  # Database user password.
Port=8000  # Database listening port.
Sslmode=allow
AutoBalance=1
RefreshCNListTime=3
Priority=1

If the configuration file and cluster environment are the same as those in the example, concurrent connections are randomly and evenly distributed to CN1, CN2, CN3, and CN4. When CN1, CN2, CN3, and CN4 are all unavailable, concurrent connections are randomly and evenly sent to CN5 and CN6. If any CN among CN1, CN2, CN3, and CN4 becomes available, the connections are not sent to CN5 and CN6 but to the available CN.

Read on the Standby Node

Configure the CN in the configuration file and set StandbyRead to 1 to enable read on the standby node in a distributed system.

Example content of the odbc.ini file:

[gaussdb]
Driver=GaussMPP
Servername=10.145.130.26,10.145.130.27,10.145.130.28,10.145.130.29  # IP address of the database server.
Database=db1  # Database name.
Username=omm  # Database username.
Password=  # Database user password.
Port=8000  # Database listening port.
StandbyRead=1  # Enable read on the standby node.
Sslmode=allow
AutoBalance=1  # Enable the load balancing mode.

Connection Pool Scenario

The connection pool allows applications to reuse pre-established connections without re-establishing connections each time. Once a connection is created and put into the connection pool, the application can reuse the connection, avoiding repeated execution of the complete connection process.

The use of connection pool can significantly improve performance, especially for middle-layer applications or applications requiring network connections that need to establish and close connections frequently.

In addition to the performance advantage, the connection pool architecture enables connections in the environment to be shared by multiple components in a single process. This means that different components in the same process can share connections in the connection pool without interfering with each other, further improving system efficiency and resource utilization.

In a connection pool, an open connection may be reused by multiple users. If your application script changes the database connection status, data leakage may occur. For security purposes, exercise caution when using a connection pool.

Configuration in the Linux OS

Enable the connection pool in the odbcinst.ini configuration file. The reference configuration of the connection pool is as follows:

[ODBC]
Pooling=Yes # Enable the connection pool.
[GaussMPP]
CPTimeout=60 # Timeout for releasing a connection that is not reused in the connection pool. The default value is 0. To enable the connection pool, set this parameter to a value greater than 0.
CPTimeToLive=60 # Lifetime of the connection pool under the driver.
[GaussMPP2]
CPTimeout=0 # Disable the connection pool.

Configuration in the Windows OS

On the Connection Pool tab, double-click GaussDB Unicode, and select Pool Connections to this driver (the default value is 60s). This parameter is the same as CPTimeout configured in the Linux OS. The reference figure is as follows.

1. To configure the connection pool parameters in the application, you need to call SQLSetEnvAttr to set the connection pool parameters before creating the environment handle. The environment handle must be set to null, which makes SQL_ATTR_CONNECTION_POOLING a process-level attribute. Currently, SQL_ATTR_CONNECTION_POOLING can be set to either of the following values on Windows:
   • SQL_CP_OFF: The connection pool is disabled. This is the default value.
   • SQL_CP_ONE_PER_DRIVER: Enable the connection pool. Each driver supports a connection pool, and all connections in the driver share the same connection pool.
2. When an application calls SQLConnect or SQLDriverConnect, the connection is extracted from the connection pool. If the connection times out or no connection matches the request in the pool, a new connection is opened. The connection pool is transparent to the application.
3. When an application calls SQLDisconnect, the connection is not released but put back to the connection pool for the next use.
4. Before SQLFreeHandle is called in the environment to release the environment handle, all environment attributes that are successfully set by the application persist.
5. If a connection of an application is inactive (not used) for a period of time, the connection is deleted from the pool. The size of the connection pool is limited only by the memory and server.