benchmarkSQL 5.0测试mysql8.0.26文档

benchmarkSQL 5.0与mysql 8.0.26测试

1、数据库安装

rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-connector-java-8.0.26.rpm

2、数据库配置

create user ‘benchmarksql’ identified by ‘Admin@123’;创建用户和密码

create database benchmarksql;创建测试库

drop database benchmarksql;删除测试库

grant all privileges on benchmarksql.* to ‘benchmarksql’;授权库用户

flush privileges;刷新库

注：忘记或重置密码

重置数据库密码

vi /etc/my.cnf

添加 skip-grant-tables

use mysql;进入修改库

update user set authentication_string = ‘’ where user = ‘root’; 将密码置空

3、benchmarkSQL 5.0 安装与配置

上传benchmarkSQL 5.0至测试物理机

benchmarksql-5.0.zip

解压benchmarkSQL 5.0

unzip benchmarksql-5.0.zip

修改benchmarksql源码

修改benchmarksql-5.0/src/client/Jtpcc.java,增加mysql相关部分。

else if (iDB.equals(“mysql”))

dbType = DB_POSTGRES;

修改benchmarksql-5.0/src/client/jTPCCConnection.java,SQL子查询增加“AS
L”（中间有空格）别名。

" ) " +

" )AS L");

编译

编译需要ant环境

从apahce官网下载tar版ant安装包

复制到/usr下

解压tar xzf *.tar

添加权限chmod –R +x apache-ant-1.10.11

修改系统配置文件 vi /etc/profile

export ANT_HOME=/opt/apache-ant-1.10.11

export PATH=$PATH:$ANT_HOME/bin

立即生效配置文件 source /etc/profile

测试ant是否生效 ant –version

ant搬移修改后的源码，此时得到的benchmarksql版本/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar已经支持MYSQL的TPC-C测试。

准备工作

拷贝对应数据库版本jar包，数据库jar包可以去官网下载。

将mysql安装目录下的JDBA文件下的jar包拷贝到BenchmarkSQL的lib目录。

配置生成mysql测试模板

在benchmarksql/run目录下，配置props.mysql文件。

db=mysql

driver=com.mysql.cj.jdbc.Driver

conn=jdbc:mysql://127.0.0.1:3306/benchmarksql?useUnicode=true&characterEncoding=utf-8&useSSL=false

user=benchmarksql

password=Benchmarksql@123

warehouses=200

loadWorkers=12

terminals=576

//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=0

//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_p5p2 blk_sda

修改文件：benchmarksql-5.0/run/funcs.sh,添加mysql数据库类型。

mysql)

cp="…/lib/mysql/*:…/lib/*"

;;

case “$(getProp db)” in

firebird|oracle|postgres|mysql)

;;

修改benchmarksql-5.0/run/runDatabaseBuild.sh,去掉extraHistID。

BEFORE_LOAD=“tableCreates”

#AFTER_LOAD=“indexCreates foreignKeys extraHistID buildFinish”

AFTER_LOAD=“indexCreates foreignKeys buildFinish”

测试

创建表格并加载数据

./runDatabaseBuild.sh props.mysql

执行测试

./runBenchmark.sh props.mysql

清除数据

./runDatabaseDestroy.sh props.mysql