Benchmarksql MySQL的TPC-C测试

BenchmarkSQL安装配置
 BenchmarkSQL postgresql的TPC-C测试
 BenchmarkSQL oracle的TPC-C测试
 Benchmarksql MySQL的TPC-C测试

一，简介

BenchmarkSQL本身不支持MySQL的TPC-C测试，但是我们可以通过修改源码让他支持MySQL测试。

二，源码修改

2.1，添加MySQL类型

修改源码src/client/jTPCC.java，添加mysql类型处理

117 if (iDB.equals("firebird"))

118 dbType = DB_FIREBIRD;

119 else if (iDB.equals("oracle"))

120 dbType = DB_ORACLE;

121 else if (iDB.equals("postgres"))

122 dbType = DB_POSTGRES;

123 else if (iDB.equals("mysql"))

124 dbType = DB_UNKNOWN;

125 else

126 {

127 log.error("unknown database type '" + iDB + "'");

128 return;

129 }

2.2，SQL查询添加别名

修改源码src/client/jTPCCConnection.java，给语句添加别名，给break分支SQl添加别名，仿照DB_POSTGRES

194 case jTPCCConfig.DB_POSTGRES:

195 stmtStockLevelSelectLow = dbConn.prepareStatement(

196 "SELECT count(*) AS low_stock FROM (" +

197 " SELECT s_w_id, s_i_id, s_quantity " +

198 " FROM bmsql_stock " +

199 " WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +

200 " SELECT ol_i_id " +

201 " FROM bmsql_district " +

202 " JOIN bmsql_order_line ON ol_w_id = d_w_id " +

203 " AND ol_d_id = d_id " +

204 " AND ol_o_id >= d_next_o_id - 20 " +

205 " AND ol_o_id < d_next_o_id " +

206 " WHERE d_w_id = ? AND d_id = ? " +

207 " ) " +

208 " ) AS L");

209 break;

210

211 default:

212 stmtStockLevelSelectLow = dbConn.prepareStatement(

213 "SELECT count(*) AS low_stock FROM (" +

214 " SELECT s_w_id, s_i_id, s_quantity " +

215 " FROM bmsql_stock " +

216 " WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +

217 " SELECT ol_i_id " +

218 " FROM bmsql_district " +

219 " JOIN bmsql_order_line ON ol_w_id = d_w_id " +

220 " AND ol_d_id = d_id " +

221 " AND ol_o_id >= d_next_o_id - 20 " +

222 " AND ol_o_id < d_next_o_id " +

223 " WHERE d_w_id = ? AND d_id = ? " +

224 " ) " +

225 " ) AS L");

2.3，创建mysql配置文件prop.mysql

在run目录中，拷贝props.pg为prop.mysql，编辑prop.mysql文件

db=mysql

driver=com.mysql.jdbc.Driver

conn=jdbc:mysql://192.168.30.136:3306/benchmarksql?useSSL=false

user=benchmarksql

password=benchmarksql

warehouses=1

loadWorkers=4

terminals=1

//To run specified transactions per terminal- runMins must equal zero

runTxnsPerTerminal=10

//To run for specified minutes- runTxnsPerTerminal must equal zero

runMins=0

//Number of total transactions per minute

limitTxnsPerMin=300

//Set to true to run in 4.x compatible mode. Set to false to use the

//entire configured database evenly.

terminalWarehouseFixed=true

//The following five values must add up to 100

//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec

newOrderWeight=45

paymentWeight=43

orderStatusWeight=4

deliveryWeight=4

stockLevelWeight=4

// Directory name to create for collecting detailed result data.

// Comment this out to suppress.

resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

osCollectorScript=./misc/os_collector_linux.py

osCollectorInterval=1

//[email protected]

osCollectorDevices=net_ens32 blk_sda

2.4，在脚本中添加mysql类型

编辑run/funcs.sh，

25 function setCP()

26 {

27 case "$(getProp db)" in

28 firebird)

29 cp="../lib/firebird/*:../lib/*"

30 ;;

31 oracle)

32 cp="../lib/oracle/*"

33 if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then

34 cp="${cp}:${ORACLE_HOME}/lib/*"

35 fi

36 cp="${cp}:../lib/*"

37 ;;

38 postgres)

39 cp="../lib/postgres/*:../lib/*"

40 ;;

41 mysql)

42 cp="../lib/mysql/*:../lib/*"

43 ;;

44 esac

45 myCP=".:${cp}:../dist/*"

46 export myCP

47 }

48

49 # ----

50 # Make sure that the properties file does have db= and the value

51 # is a database, we support.

52 # ----

53 case "$(getProp db)" in

54 firebird|oracle|postgres|mysql)

55 ;;

56 "") echo "ERROR: missing db= config option in ${PROPS}" >&2

57 exit 1

58 ;;

2.5，修改预加载数据脚本

在文件run/runDatabaseBuild.sh中删除AFTER_LOAD的extraHistID，不让他执行该脚本

18 BEFORE_LOAD="tableCreates"

19 #AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"

20 AFTER_LOAD="indexCreates foreignKeys buildFinish"

2.6，添加mysql的jdbc

下载mysql的jdbc，我这里使用mysql-connector-java-5.1.47.jar。

把该jdbc放到lib/mysql中，lib下不存在mysql目录，需要手动创建

$ cd lib

$ mkdir mysql

$ cp /path/to/mysql-connector-java-5.1.47.jar mysql/

$ tree

Benchmarksql MySQL的TPC-C测试

2.7,编译

直接执行ant命令，编译

$ ant

Benchmarksql MySQL的TPC-C测试

三，配置MySQL数据库

创建数据库，用户，赋予权限

mysql> create database benchmarksql;

mysql> create user 'benchmarksql'@'%' identified by 'benchmarksql';

mysql> grant all privileges on benchmarksql.* to 'benchmarksql'@'%' identified by 'benchmarksql';

可能存在MySQL密码强度检测失败问题，处理流程如下：

mysql> select @@validate_password_policy;

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+

| Variable_name | Value |

+--------------------------------------+--------+

| validate_password_dictionary_file | |

| validate_password_length | 8 |

| validate_password_mixed_case_count | 1 |

| validate_password_number_count | 1 |

| validate_password_policy | MEDIUM |

| validate_password_special_char_count | 1 |

+--------------------------------------+--------+

6 rows in set (0.08 sec)

mysql> set global validate_password_policy=0;

mysql> set global validate_password_mixed_case_count=0;

mysql> set global validate_password_number_count=3;

mysql> set global validate_password_special_char_count=0;

mysql> set global validate_password_length=3;

mysql> SHOW VARIABLES LIKE 'validate_password%';

相关参数解析：

validate_password_dictionary_file：插件用于验证密码强度的字典文件路径。
validate_password_length：密码最小长度，参数默认为8，它有最小值的限制，最小值为：validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)
validate_password_mixed_case_count：密码至少要包含的小写字母个数和大写字母个数。
validate_password_number_count：密码至少要包含的数字个数。
validate_password_policy：密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG。有以下取值：

Policy	Tests Performed
0 or LOW	Length
1 or MEDIUM	Length; numeric, lowercase/uppercase, and special characters
2 or STRONG	Length; numeric, lowercase/uppercase, and special characters; dictionary file

默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

validate_password_special_char_count：密码至少要包含的特殊字符数。

四，BenchmarkSQL测试

在run目录下执行命令

4.1，数据库预加载

$ ./runDatabaseBuild.sh props.mysql

Benchmarksql MySQL的TPC-C测试

中间可能有些慢，耐心等待

4.2，测试

$ ./runBenchmark.sh props.mysql

Benchmarksql MySQL的TPC-C测试

4.3, 清空数据库

$ ./runDatabaseDestroy.sh props.mysql

五，参考资料

benchmarksql 5.0 支持对MySQL的TPC-C测试

来自 <https://www.jianshu.com/p/622545cb1341>

Benchmarksql MySQL的TPC-C测试

相关推荐