Benchmarksql MySQL的TPC-C测试

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

资源：benchmarksql-5.0-pg-oracle-mysql.tar.gz

一，简介

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://127.0.0.1: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

//osCollectorSSHAddr=user@dbhost

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

 

 

2.7,编译

直接执行ant命令，编译

$ ant

 

 

 

三，配置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

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

 

4.2，测试

$ ./runBenchmark.sh props.mysql

 

4.3, 清空数据库

$ ./runDatabaseDestroy.sh props.mysql

 

五，参考资料

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

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