典型应用场景配置
日志诊断场景
ODBC日志分为unixODBC驱动管理器日志和psqlODBC驱动端日志。前者可以用于追溯应用程序API的执行成功与否,后者是底层实现过程中的一些DFX日志,用来帮助定位问题。
1 2 3 4 5 6 7 |
[ODBC] Trace=Yes TraceFile=/path/to/odbctrace.log [GaussMPP] Driver64=/usr/local/lib/psqlodbcw.so setup=/usr/local/lib/psqlodbcw.so |
[gaussdb] Driver=GaussMPP Servername=10.10.0.13(数据库Server IP) ... Debug=1(打开驱动端debug日志)
![](https://support.huaweicloud.com/distributed-devg-v2-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
unixODBC日志将会生成在TraceFile配置的路径下,psqlODBC会在系统/tmp/下生成mylog_xxx.log。
高性能场景
- 需要设置批量绑定odbc.ini设置UseBatchProtocol=1数据库设置support_batch_bind。
- ODBC程序绑定类型要和数据库中类型一致。
- 客户端字符集和数据库字符集一致。
- 事务改成手动提交。
[gaussdb] Driver=GaussMPP Servername=10.10.0.13(数据库Server IP) ... UseBatchProtocol=1 (默认打开) ConnSettings=set client_encoding=UTF8 (设置客户端字符编码,保证和server端一致)
#ifdef WIN32 #include <windows.h> #endif #include <stdio.h> #include <stdlib.h> #include <sql.h> #include <sqlext.h> #include <string.h> #include <sys/time.h> #define MESSAGE_BUFFER_LEN 128 SQLHANDLE h_env = NULL; SQLHANDLE h_conn = NULL; SQLHANDLE h_stmt = NULL; void print_error() { SQLCHAR Sqlstate[SQL_SQLSTATE_SIZE+1]; SQLINTEGER NativeError; SQLCHAR MessageText[MESSAGE_BUFFER_LEN]; SQLSMALLINT TextLength; SQLRETURN ret = SQL_ERROR; ret = SQLGetDiagRec(SQL_HANDLE_STMT, h_stmt, 1, Sqlstate, &NativeError, MessageText, MESSAGE_BUFFER_LEN, &TextLength); if ( SQL_SUCCESS == ret) { printf("\n STMT ERROR-%05d %s", NativeError, MessageText); return; } ret = SQLGetDiagRec(SQL_HANDLE_DBC, h_conn, 1, Sqlstate, &NativeError, MessageText, MESSAGE_BUFFER_LEN, &TextLength); if ( SQL_SUCCESS == ret) { printf("\n CONN ERROR-%05d %s", NativeError, MessageText); return; } ret = SQLGetDiagRec(SQL_HANDLE_ENV, h_env, 1, Sqlstate, &NativeError, MessageText, MESSAGE_BUFFER_LEN, &TextLength); if ( SQL_SUCCESS == ret) { printf("\n ENV ERROR-%05d %s", NativeError, MessageText); return; } return; } /* 期盼函数返回SQL_SUCCESS */ #define RETURN_IF_NOT_SUCCESS(func) \ {\ SQLRETURN ret_value = (func);\ if (SQL_SUCCESS != ret_value)\ {\ print_error();\ printf("\n failed line = %u: expect SQL_SUCCESS, but ret = %d", __LINE__, ret_value);\ return SQL_ERROR; \ }\ } /* 期盼函数返回SQL_SUCCESS */ #define RETURN_IF_NOT_SUCCESS_I(i, func) \ {\ SQLRETURN ret_value = (func);\ if (SQL_SUCCESS != ret_value)\ {\ print_error();\ printf("\n failed line = %u (i=%d): : expect SQL_SUCCESS, but ret = %d", __LINE__, (i), ret_value);\ return SQL_ERROR; \ }\ } /* 期盼函数返回SQL_SUCCESS_WITH_INFO */ #define RETURN_IF_NOT_SUCCESS_INFO(func) \ {\ SQLRETURN ret_value = (func);\ if (SQL_SUCCESS_WITH_INFO != ret_value)\ {\ print_error();\ printf("\n failed line = %u: expect SQL_SUCCESS_WITH_INFO, but ret = %d", __LINE__, ret_value);\ return SQL_ERROR; \ }\ } /* 期盼数值相等 */ #define RETURN_IF_NOT(expect, value) \ if ((expect) != (value))\ {\ printf("\n failed line = %u: expect = %u, but value = %u", __LINE__, (expect), (value)); \ return SQL_ERROR;\ } /* 期盼字符串相同 */ #define RETURN_IF_NOT_STRCMP_I(i, expect, value) \ if (( NULL == (expect) ) || (NULL == (value)))\ {\ printf("\n failed line = %u (i=%u): input NULL pointer !", __LINE__, (i)); \ return SQL_ERROR; \ }\ else if (0 != strcmp((expect), (value)))\ {\ printf("\n failed line = %u (i=%u): expect = %s, but value = %s", __LINE__, (i), (expect), (value)); \ return SQL_ERROR;\ } // prepare + execute SQL语句 int execute_cmd(SQLCHAR *sql) { if ( NULL == sql ) { return SQL_ERROR; } if ( SQL_SUCCESS != SQLPrepare(h_stmt, sql, SQL_NTS)) { return SQL_ERROR; } if ( SQL_SUCCESS != SQLExecute(h_stmt)) { return SQL_ERROR; } return SQL_SUCCESS; } // execute + commit 句柄 int commit_exec() { if ( SQL_SUCCESS != SQLExecute(h_stmt)) { return SQL_ERROR; } // 手动提交 if ( SQL_SUCCESS != SQLEndTran(SQL_HANDLE_DBC, h_conn, SQL_COMMIT)) { return SQL_ERROR; } return SQL_SUCCESS; } int begin_unit_test() { SQLINTEGER ret; /* 申请环境句柄 */ ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &h_env); if ((SQL_SUCCESS != ret) && (SQL_SUCCESS_WITH_INFO != ret)) { printf("\n begin_unit_test::SQLAllocHandle SQL_HANDLE_ENV failed ! ret = %d", ret); return SQL_ERROR; } /* 进行连接前必须要先设置版本号 */ if (SQL_SUCCESS != SQLSetEnvAttr(h_env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0)) { print_error(); printf("\n begin_unit_test::SQLSetEnvAttr SQL_ATTR_ODBC_VERSION failed ! ret = %d", ret); SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_ERROR; } /* 申请连接句柄 */ ret = SQLAllocHandle(SQL_HANDLE_DBC, h_env, &h_conn); if (SQL_SUCCESS != ret) { print_error(); printf("\n begin_unit_test::SQLAllocHandle SQL_HANDLE_DBC failed ! ret = %d", ret); SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_ERROR; } /* 建立连接 */ ret = SQLConnect(h_conn, (SQLCHAR*) "gaussdb", SQL_NTS, (SQLCHAR*) NULL, 0, NULL, 0); if (SQL_SUCCESS != ret) { print_error(); printf("\n begin_unit_test::SQLConnect failed ! ret = %d", ret); SQLFreeHandle(SQL_HANDLE_DBC, h_conn); SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_ERROR; } /* 申请语句句柄 */ ret = SQLAllocHandle(SQL_HANDLE_STMT, h_conn, &h_stmt); if (SQL_SUCCESS != ret) { print_error(); printf("\n begin_unit_test::SQLAllocHandle SQL_HANDLE_STMT failed ! ret = %d", ret); SQLFreeHandle(SQL_HANDLE_DBC, h_conn); SQLFreeHandle(SQL_HANDLE_ENV, h_env); return SQL_ERROR; } return SQL_SUCCESS; } void end_unit_test() { /* 释放语句句柄 */ if (NULL != h_stmt) { SQLFreeHandle(SQL_HANDLE_STMT, h_stmt); } /* 释放连接句柄 */ if (NULL != h_conn) { SQLDisconnect(h_conn); SQLFreeHandle(SQL_HANDLE_DBC, h_conn); } /* 释放环境句柄 */ if (NULL != h_env) { SQLFreeHandle(SQL_HANDLE_ENV, h_env); } return; } int main() { // begin test if (begin_unit_test() != SQL_SUCCESS) { printf("\n begin_test_unit failed."); return SQL_ERROR; } // 句柄配置同前面用例 int i = 0; SQLCHAR* sql_drop = "drop table if exists test_bindnumber_001"; SQLCHAR* sql_create = "create table test_bindnumber_001(" "f4 number, f5 number(10, 2)" ")"; SQLCHAR* sql_insert = "insert into test_bindnumber_001 values(?, ?)"; SQLCHAR* sql_select = "select * from test_bindnumber_001"; SQLLEN RowCount; SQL_NUMERIC_STRUCT st_number; SQLCHAR getValue[2][MESSAGE_BUFFER_LEN]; /* step 1. 建表 */ RETURN_IF_NOT_SUCCESS(execute_cmd(sql_drop)); RETURN_IF_NOT_SUCCESS(execute_cmd(sql_create)); /* step 2.1 通过SQL_NUMERIC_STRUCT结构绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); //第一行: 1234.5678 memset(st_number.val, 0, SQL_MAX_NUMERIC_LEN); st_number.precision = 8; st_number.scale = 4; st_number.sign = 1; st_number.val[0] = 0x4E; st_number.val[1] = 0x61; st_number.val[2] = 0xBC; RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, sizeof(SQL_NUMERIC_STRUCT), 4, &st_number, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, sizeof(SQL_NUMERIC_STRUCT), 4, &st_number, 0, NULL)); // 关闭自动提交 SQLSetConnectAttr(h_conn, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); //第二行: 12345678 memset(st_number.val, 0, SQL_MAX_NUMERIC_LEN); st_number.precision = 8; st_number.scale = 0; st_number.sign = 1; st_number.val[0] = 0x4E; st_number.val[1] = 0x61; st_number.val[2] = 0xBC; RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, sizeof(SQL_NUMERIC_STRUCT), 0, &st_number, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, sizeof(SQL_NUMERIC_STRUCT), 0, &st_number, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); //第三行: 12345678 memset(st_number.val, 0, SQL_MAX_NUMERIC_LEN); st_number.precision = 0; st_number.scale = 4; st_number.sign = 1; st_number.val[0] = 0x4E; st_number.val[1] = 0x61; st_number.val[2] = 0xBC; RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, sizeof(SQL_NUMERIC_STRUCT), 4, &st_number, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, sizeof(SQL_NUMERIC_STRUCT), 4, &st_number, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); /* step 2.2 第四行通过SQL_C_CHAR字符串绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); SQLCHAR* szNumber = "1234.5678"; RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_NUMERIC, strlen(szNumber), 0, szNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_NUMERIC, strlen(szNumber), 0, szNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); /* step 2.3 第五行通过SQL_C_FLOAT绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); SQLREAL fNumber = 1234.5678; RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_NUMERIC, sizeof(fNumber), 4, &fNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_NUMERIC, sizeof(fNumber), 4, &fNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); /* step 2.4 第六行通过SQL_C_DOUBLE绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); SQLDOUBLE dNumber = 1234.5678; RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE, SQL_NUMERIC, sizeof(dNumber), 4, &dNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_DOUBLE, SQL_NUMERIC, sizeof(dNumber), 4, &dNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); SQLBIGINT bNumber1 = 0xFFFFFFFFFFFFFFFF; SQLBIGINT bNumber2 = 12345; /* step 2.5 第七行通过SQL_C_SBIGINT绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_NUMERIC, sizeof(bNumber1), 4, &bNumber1, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_NUMERIC, sizeof(bNumber2), 4, &bNumber2, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); /* step 2.6 第八行通过SQL_C_UBIGINT绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_UBIGINT, SQL_NUMERIC, sizeof(bNumber1), 4, &bNumber1, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_UBIGINT, SQL_NUMERIC, sizeof(bNumber2), 4, &bNumber2, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); SQLLEN lNumber1 = 0xFFFFFFFFFFFFFFFF; SQLLEN lNumber2 = 12345; /* step 2.7 第九行通过SQL_C_LONG绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_NUMERIC, sizeof(lNumber1), 0, &lNumber1, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_NUMERIC, sizeof(lNumber2), 0, &lNumber2, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); /* step 2.8 第十行通过SQL_C_ULONG绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_NUMERIC, sizeof(lNumber1), 0, &lNumber1, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_NUMERIC, sizeof(lNumber2), 0, &lNumber2, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); SQLSMALLINT sNumber = 0xFFFF; /* step 2.9 第十一行通过SQL_C_SHORT绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT, SQL_NUMERIC, sizeof(sNumber), 0, &sNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_SHORT, SQL_NUMERIC, sizeof(sNumber), 0, &sNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); /* step 2.10 第十二行通过SQL_C_USHORT绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_USHORT, SQL_NUMERIC, sizeof(sNumber), 0, &sNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_USHORT, SQL_NUMERIC, sizeof(sNumber), 0, &sNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); SQLCHAR cNumber = 0xFF; /* step 2.11 第十三行通过SQL_C_TINYINT绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_TINYINT, SQL_NUMERIC, sizeof(cNumber), 0, &cNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_TINYINT, SQL_NUMERIC, sizeof(cNumber), 0, &cNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); /* step 2.12 第十四行通过SQL_C_UTINYINT绑定参数 */ RETURN_IF_NOT_SUCCESS(SQLPrepare(h_stmt, sql_insert, SQL_NTS)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 1, SQL_PARAM_INPUT, SQL_C_UTINYINT, SQL_NUMERIC, sizeof(cNumber), 0, &cNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(SQLBindParameter(h_stmt, 2, SQL_PARAM_INPUT, SQL_C_UTINYINT, SQL_NUMERIC, sizeof(cNumber), 0, &cNumber, 0, NULL)); RETURN_IF_NOT_SUCCESS(commit_exec()); RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(1, RowCount); /* 用字符串类型统一进行期盼 */ SQLCHAR* expectValue[14][2] = {{"1234.5678", "1234.57"}, {"12345678", "12345678"}, {"0", "0"}, {"1234.5678", "1234.57"}, {"1234.5677", "1234.57"}, {"1234.5678", "1234.57"}, {"-1", "12345"}, {"18446744073709551615", "12345"}, {"-1", "12345"}, {"4294967295", "12345"}, {"-1", "-1"}, {"65535", "65535"}, {"-1", "-1"}, {"255", "255"}, }; RETURN_IF_NOT_SUCCESS(execute_cmd(sql_select)); while ( SQL_NO_DATA != SQLFetch(h_stmt)) { RETURN_IF_NOT_SUCCESS_I(i, SQLGetData(h_stmt, 1, SQL_C_CHAR, &getValue[0], MESSAGE_BUFFER_LEN, NULL)); RETURN_IF_NOT_SUCCESS_I(i, SQLGetData(h_stmt, 2, SQL_C_CHAR, &getValue[1], MESSAGE_BUFFER_LEN, NULL)); //RETURN_IF_NOT_STRCMP_I(i, expectValue[i][0], getValue[0]); //RETURN_IF_NOT_STRCMP_I(i, expectValue[i][1], getValue[1]); i++; } RETURN_IF_NOT_SUCCESS(SQLRowCount(h_stmt, &RowCount)); RETURN_IF_NOT(i, RowCount); SQLCloseCursor(h_stmt); /* step final. 删除表还原环境 */ RETURN_IF_NOT_SUCCESS(execute_cmd(sql_drop)); end_unit_test(); }
![](https://support.huaweicloud.com/distributed-devg-v2-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
比如这个用例中定义了number列,调用SQLBindParameter接口时,绑定SQL_NUMERIC会比SQL_LONG性能高一些。因为如果是char,在数据库服务端插入数据时需要进行数据类型转换,从而引发性能瓶颈。
负载均衡场景
- 负载均衡即为将并发连接随机分发到所有CN上,避免单个CN负载过大,达到高性能的目的。
- 配置参数AutoBalance=1,开启负载均衡功能。
- 参数RefreshCNListTime=5可以选择性配置,默认刷新时间为10秒。
- 参数Priority=1可以选择性配置,意为并发连接优先发送到配置文件中配置的CN上,当配置的CN全部不可连接时,连接才会被分发到剩余CN上。
示例场景:
集群环境有6个CN,CN1,CN2,CN3,CN4,CN5和CN6;配置文件配置4个CN,为CN1,CN2,CN3和CN4。
配置文件示例:
[gaussdb] Driver=GaussMPP Servername=10.145.130.26,10.145.130.27,10.145.130.28,10.145.130.29(数据库Server IP) Database=postgres (数据库名) Username=omm (数据库用户名) Password= (数据库用户密码) Port=8000 (数据库侦听端口) Sslmode=allow AutoBalance=1 RefreshCNListTime=3 Priority=1
当配置文件和集群环境如示例时,并发连接会随机、平均发送到CN1,CN2,CN3和CN4上,连接数均衡。当CN1,CN2,CN3和CN4全部不可用时,并发连接会随机、平均发送到CN5和CN6上。如果此时CN1,CN2,CN3和CN4中有CN重新可用时,连接则不会再发送到CN5和CN6上,而重新发送到重新可用的CN上。