一、配置集群
1. 配置svc.conf
[root@localhost dm]# cat /etc/dm_svc.conf
TIME_ZONE=(480)
LANGUAGE=(CN)DM=(192.168.112.139:5236,192.168.112.140:5236)
[DM]
LOGIN_MODE=(1)
SWITCH_TIME=(300)
SWITCH_INTERVAL=(200)二、编译sysbench
2.1 配置环境变量
[dmdba@~]# vi ~/.bash_profile --修改[dmdba@~]# export DM_HOME="/opt/dmdbms"
[dmdba@~]# export PATH=$PATH:/opt/dmdbms/bin[dmdba@~]# source ~/.bash_profile
2.2 编译sysbench
2.2.1 配置
## 赋予权限
[root@localhost sysbench-master-dpi]# chmod 755 * -R
## 生成confifure
[root@localhost sysbench-master-dpi]# ./autogen.sh
## 执行配置文件
## x86平台编译方式
[root@localhost sysbench-master-dpi]# ./configure --without-mysql --with-dm## arm平台编译方式
## [root@localhost sysbench-master-dpi]# ./configure --without-mysql --with-dm --build=aarch64-unkown-linux-gnu2.2.2.2 修改dpi源码
将dm_drv_connect函数的自动提交由默认的非自动提交改为自动提交，如不改 跑insert update相关的语句 事务会回滚
# 修改
sysbench-master-dpi/src/drivers/dm/drv_dpi_dm.c将dm_drv_connect函数的自动提交由默认的非自动提交改为自动提交//rc = dpi_set_con_attr(dm_con->hdbc, DSQL_ATTR_AUTOCOMMIT, DSQL_AUTOCOMMIT_OFF, 0);rc = dpi_set_con_attr(dm_con->hdbc, DSQL_ATTR_AUTOCOMMIT, DSQL_AUTOCOMMIT_ON, 0);
2.2.3 编译
## 编译
[root@localhost sysbench-master-dpi]# make## 拷贝sysbench到src/lua下进行测试
[root@localhost src]# cd /opt/sysbench-master-dpi/src
[root@localhost src]# cp sysbench lua/三、压测sysbench
3.1 准备数据
## 准备数据
./sysbench oltp_read_write.lua --tables=5 --table-size=200000 --db_driver=dm --dm-db=DM --dm-user=SYSDBA --dm-password=SYSDBA --auto-inc=0 --threads=4 --time=180 --report-interval=1 prepare
3.2 开始压测
导入oltp_insert_reconnect.lua脚本到lua目录下
sysbench.cmdline.options = {table_size = {"Number of rows per table", 5000},tables = {"Number of tables", 1},auto_inc = {"Use auto-incremenit IDs", false},
}local drv = sysbench.sql.driver()
local con-- 最大重连次数
local MAX_RETRIES = 30function connect_db()local retry_count = 0local max_retries = MAX_RETRIES or 5 while retry_count < max_retries dolocal status, err = pcall(function()con = drv:connect()end)-- 如果连接成功，跳出循环if status thenprint("Database connection established.")returnelseretry_count = retry_count + 1print(string.format("Connection failed: %s. Retrying %d/%d...", err, retry_count, max_retries))os.execute("sleep 1")endend-- 如果达到最大重试次数，抛出错误error("Failed to connect to the database after multiple retries.")
end-- 断开连接函数
function disconnect_db()if con thencon:disconnect()con = nilprint("Database connection closed.")end
endfunction sysbench.hooks.before_connect()connect_db()
endfunction sysbench.hooks.after_disconnect()disconnect_db()
end-- 执行事务的函数，包含重连检测
function execute_query(query)local status, result = pcall(function()return con:query(query)end)-- 如果查询失败，尝试重连if not status then-- 具体细化的话 可以根据错误码做重连逻辑-- 这里统一做重连逻辑disconnect_db() os.execute("sleep 1")connect_db()execute_query(query)endreturn result
end-- 10 groups, 119 characters
local c_value_template = "###########-###########-###########-" .."###########-###########-###########-" .."###########-###########-###########-" .."###########"-- 5 groups, 59 characters
local pad_value_template = "###########-###########-###########-" .."###########-###########"function get_c_value()return sysbench.rand.string(c_value_template)
endfunction get_pad_value()return sysbench.rand.string(pad_value_template)
end-- 执行 write only 测试
function event()local table_name = "sbtest" .. sysbench.rand.uniform(1, sysbench.opt.tables)
execute_query("SELECT c FROM sbtest1 WHERE id = 1")
local k_val = sysbench.rand.default(1, sysbench.opt.table_size)
local c_val = get_c_value()
local pad_val = get_pad_value()if (sysbench.opt.auto_inc) theni = 0
else-- Convert a uint32_t value to SQL INTi = sysbench.rand.unique() - 2147483648
endexecute_query(string.format("INSERT INTO %s (id, k, c, pad) VALUES " .."(%d, %d, '%s', '%s')",table_name, i, k_val, c_val, pad_val))end3.2.1 运行文件
./sysbench oltp_insert_reconnect.lua --auto-inc=0 --tables=5 --table-size=5000 --db_driver=dm --dm-db=DM --dm-user=SYSDBA --dm-password=SYSDBA --threads=4 --time=180 --report-interval=1 --db-debug=on --debug=on run#./sysbench oltp_insert_reconnect.lua --db_driver=dm --dm-db=DM --dm-user=SYSDBA --dm-password=SYSDBA --threads=4 --time=180 --report-interval=1 --db-debug=on --debug=on run
3.2.2 进行集群切换测试
[root@localhost bin]# ./dmmonitor ./dmmonitor_noc.iniSWITCHOVER
3.3 清理数据
## 清理数据
./sysbench oltp_insert_reconnect.lua --db_driver=dm --dm-db=DM --dm-user=SYSDBA --dm-password=SYSDBA --threads=4 --time=180 --report-interval=1 --auto-inc=0 --tables=5 --table-size=5000 --db-debug=on --debug=on cleanup
4. 附录
4.1 dpi中的reconnect代码
/* Reset connection to the server */
static int dm_drv_reconnect(db_conn_t *sb_conn)
{dm_conn_t *dm_con = sb_conn->ptr;DPIRETURN rc;sdbyte err_msg[512];sdint2 msg_len;sdint4 err_code;// log_text(LOG_DEBUG, "Reconnecting");if (dm_con == NULL){return 1;}if (dm_con->hdbc != NULL){dpi_logout(dm_con->hdbc);}/* rc = dpi_login(dm_con->hdbc, (sdbyte *)args.db, (sdbyte *)args.user, (sdbyte *)args.password);if (rc != DSQL_SUCCESS){dpi_get_diag_rec(DSQL_HANDLE_DBC, dm_con->hdbc, 1, &err_code, err_msg, sizeof(err_msg), &msg_len);log_text(LOG_ALERT, "Error - DM_%d_%s", err_code, err_msg); return 1;}*/while (1) {rc = dpi_login(dm_con->hdbc, (sdbyte *) args.db, (sdbyte *) args.user, (sdbyte *) args.password);log_text(LOG_DEBUG, "rc= %d",rc);usleep(1000);if (rc != DSQL_SUCCESS) {dpi_get_diag_rec(DSQL_HANDLE_DBC, dm_con->hdbc, 1, &err_code, err_msg, sizeof(err_msg), &msg_len);// log_text(LOG_DEBUG, "ERR dm_drv_reconnect fail - DM_%d_%s", err_code, err_msg);}else{// log_text(LOG_DEBUG, "SUC dm_drv_reconnect");break;}}return 0;
}4.2 TPS计算逻辑
- 每次会调用lua脚本的 event函数 函数执行玩了以后 会调用左边这个sb_event_stop函数
- 这个函数里面 会根据线程号 总值+1
4.2 QPS计算逻辑
- dm提供的驱动中在dm_drv_prepare方法中使用get_stmt_type函数方法给stmt->counter属性赋值
- sysbench在执行execute方法时在进行取stmt->counter属性自增
4.3 mysql的重连逻辑
每次check_error的时候根据错误码判断类型 如果是CR_SERVER_LOST_EXTENDED 则 进行 reconnect的计数器+1 同时 执行重连函数
4.4 dpi的读写分离如何配置
dpi_set_con_attr(dm_con->hdbc, DSQL_ATTR_RWSEPARATE, DSQL_RWSEPARATE_ON, 0);
dpi_set_con_attr(dm_con->hdbc, DSQL_ATTR_RWSEPARATE_PERCENT, 25);
