在Oracle Linux 7.6使用benchmarksql 5.0压测MySQL 5.7
一.下载&编译安装
1.先要确定服务器是否安装了 JDK1.8
[root@localhost /]# java -version
java version "1.7.0_75"
OpenJDK Runtime Environment (rhel-2.5.4.2.0.1.el7_0-x86_64 u75-b13)
OpenJDK 64-Bit Server VM (build 24.75-b04, mixed mode)
因为自带jdk存储在/usr/lib/jvm下，需要将其删除。
[root@localhost lib]# mv jvm jvmold
[root@localhost lib]# java -version
-bash: /usr/bin/java: ???
2.安装JDK1.8
[root@localhost /]# tar -zxvf jdk-linux-x64.tar.gz
[root@localhost /]# mkdir /usr/java
[root@localhost /]# mv jdk1.8.0_131 /usr/java/
[root@localhost /]# vi /etc/profile
....
export JAVA_HOME=/usr/java/jdk1.8.0_131
export JRE_HOME=${JAVA_HOME}/jre
export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib:$CLASSPATH
export JAVA_PATH=${JAVA_HOME}/bin:${JRE_HOME}/bin
export PATH=$PATH:${JAVA_PATH}
[root@localhost /]# source /etc/profile
[root@localhost /]# java -version
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)
[root@localhost /]# unzip benchmarksql-5.0.zip
[root@localhost /]# cd benchmarksql-5.0/
修改benchmarksql源码
修改jTPCCConfig.java文件
[root@localhost benchmarksql-5.0]# vi src/client/jTPCCConfig.java
/*
* jTPCCConfig - Basic configuration parameters for jTPCC
*
* Copyright (C) 2003, Raul Barbosa
* Copyright (C) 2004-2016, Denis Lussier
* Copyright (C) 2016, Jan Wieck
*
*/
import java.text.*;
public interface jTPCCConfig
{
public final static String JTPCCVERSION = "5.0";
public final static int DB_UNKNOWN = 0,
DB_FIREBIRD = 1,
DB_ORACLE = 2,
DB_POSTGRES = 3,
DB_MYSQL =4;
public final static int NEW_ORDER = 1,
PAYMENT = 2,
ORDER_STATUS = 3,
DELIVERY = 4,
STOCK_LEVEL = 5;
public final static String[] nameTokens = {"BAR", "OUGHT", "ABLE", "PRI", "PRES", "ESE", "ANTI", "CALLY", "ATION", "EING"};
public final static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public final static int configCommitCount = 10000; // commit every n records in LoadData
public final static int configWhseCount = 10;
public final static int configItemCount = 100000; // tpc-c std = 100,000
public final static int configDistPerWhse = 10; // tpc-c std = 10
public final static int configCustPerDist = 3000; // tpc-c std = 3,000
}
修改benchmarksql-5.0/src/client/jTPCC.java，增加mysql相关部分
[root@localhost benchmarksql-5.0]# vi src/client/jTPCC.java
/*
* jTPCC - Open Source Java implementation of a TPC-C like benchmark
*
* Copyright (C) 2003, Raul Barbosa
* Copyright (C) 2004-2016, Denis Lussier
* Copyright (C) 2016, Jan Wieck
*
*/
import org.apache.log4j.*;
import java.io.*;
import java.nio.file.*;
import java.sql.*;
import java.util.*;
import java.util.regex.Pattern;
import java.text.*;
public class jTPCC implements jTPCCConfig
{
private static org.apache.log4j.Logger log = Logger.getLogger(jTPCC.class);
private static String resultDirName = null;
private static BufferedWriter resultCSV = null;
private static BufferedWriter runInfoCSV = null;
private static int runID = 0;
private int dbType = DB_UNKNOWN;
private int currentlyDisplayedTerminal;
private jTPCCTerminal[] terminals;
private String[] terminalNames;
private boolean terminalsBlockingExit = false;
private long terminalsStarted = 0, sessionCount = 0, transactionCount = 0;
private Object counterLock = new Object();
private long newOrderCounter = 0, sessionStartTimestamp, sessionEndTimestamp, sessionNextTimestamp=0, sessionNextKounter=0;
private long sessionEndTargetTime = -1, fastNewOrderCounter, recentTpmC=0, recentTpmTotal=0;
private boolean signalTerminalsRequestEndSent = false, databaseDriverLoaded = false;
private FileOutputStream fileOutputStream;
private PrintStream printStreamReport;
private String sessionStart, sessionEnd;
private int limPerMin_Terminal;
private double tpmC;
private jTPCCRandom rnd;
private OSCollector osCollector = null;
public static void main(String args[])
{
PropertyConfigurator.configure("log4j.properties");
new jTPCC();
}
private String getProp (Properties p, String pName)
{
String prop = p.getProperty(pName);
log.info("Term-00, " + pName + "=" + prop);
return(prop);
}
public jTPCC()
{
// load the ini file
Properties ini = new Properties();
try {
ini.load( new FileInputStream(System.getProperty("prop")));
} catch (IOException e) {
errorMessage("Term-00, could not load properties file");
}
log.info("Term-00, ");
log.info("Term-00, +-------------------------------------------------------------+");
log.info("Term-00, BenchmarkSQL v" + JTPCCVERSION);
log.info("Term-00, +-------------------------------------------------------------+");
log.info("Term-00, (c) 2003, Raul Barbosa");
log.info("Term-00, (c) 2004-2016, Denis Lussier");
log.info("Term-00, (c) 2016, Jan Wieck");
log.info("Term-00, +-------------------------------------------------------------+");
log.info("Term-00, ");
String iDB = getProp(ini,"db");
String iDriver = getProp(ini,"driver");
String iConn = getProp(ini,"conn");
String iUser = getProp(ini,"user");
String iPassword = ini.getProperty("password");
log.info("Term-00, ");
String iWarehouses = getProp(ini,"warehouses");
String iTerminals = getProp(ini,"terminals");
String iRunTxnsPerTerminal = ini.getProperty("runTxnsPerTerminal");
String iRunMins = ini.getProperty("runMins");
if (Integer.parseInt(iRunTxnsPerTerminal) ==0 && Integer.parseInt(iRunMins)!=0){
log.info("Term-00, runMins" + "=" + iRunMins);
}else if(Integer.parseInt(iRunTxnsPerTerminal) !=0 && Integer.parseInt(iRunMins)==0){
log.info("Term-00, runTxnsPerTerminal" + "=" + iRunTxnsPerTerminal);
}else{
errorMessage("Term-00, Must indicate either transactions per terminal or number of run minutes!");
};
String limPerMin = getProp(ini,"limitTxnsPerMin");
String iTermWhseFixed = getProp(ini,"terminalWarehouseFixed");
log.info("Term-00, ");
String iNewOrderWeight = getProp(ini,"newOrderWeight");
String iPaymentWeight = getProp(ini,"paymentWeight");
String iOrderStatusWeight = getProp(ini,"orderStatusWeight");
String iDeliveryWeight = getProp(ini,"deliveryWeight");
String iStockLevelWeight = getProp(ini,"stockLevelWeight");
log.info("Term-00, ");
String resultDirectory = getProp(ini, "resultDirectory");
String osCollectorScript = getProp(ini, "osCollectorScript");
log.info("Term-00, ");
if (iDB.equals("firebird"))
dbType = DB_FIREBIRD;
else if (iDB.equals("oracle"))
dbType = DB_ORACLE;
else if (iDB.equals("postgres"))
dbType = DB_POSTGRES;
else if (iDB.equals("mysql"))
dbType = DB_MYSQL;
else
{
log.error("unknown database type '" + iDB + "'");
return;
修改benchmarksql-5.0/src/client/jTPCCConnection.java， SQL子查询增加”AS L”别名，如下所示：
[root@localhost benchmarksql-5.0]# vi src/client/jTPCCConnection.java
switch (dbType)
{
case jTPCCConfig.DB_POSTGRES:
stmtStockLevelSelectLow = dbConn.prepareStatement(
"SELECT count(*) AS low_stock FROM (" +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +
" SELECT ol_i_id " +
" FROM bmsql_district " +
" JOIN bmsql_order_line ON ol_w_id = d_w_id " +
" AND ol_d_id = d_id " +
" AND ol_o_id >= d_next_o_id - 20 " +
" AND ol_o_id < d_next_o_id " +
" WHERE d_w_id = ? AND d_id = ? " +
" ) " +
" ) AS L");
break;
default:
stmtStockLevelSelectLow = dbConn.prepareStatement(
"SELECT count(*) AS low_stock FROM (" +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +
" SELECT ol_i_id " +
" FROM bmsql_district " +
" JOIN bmsql_order_line ON ol_w_id = d_w_id " +
" AND ol_d_id = d_id " +
" AND ol_o_id >= d_next_o_id - 20 " +
" AND ol_o_id < d_next_o_id " +
" WHERE d_w_id = ? AND d_id = ? " +
" ) " +
" )AS L"); // " )");
break;
}
[root@localhost benchmarksql-5.0]# ant
Buildfile: /benchmarksql-5.0/build.xml
init:
[mkdir] Created dir: /benchmarksql-5.0/build
compile:
[javac] Compiling 11 source files to /benchmarksql-5.0/build
[javac] /benchmarksql-5.0/src/client/jTPCCRandom.java:143: : EUC_CN [javac] * be able to represent 128 different characters. '#@!%%???'
[javac] ^
[javac] /benchmarksql-5.0/src/client/jTPCCRandom.java:143: : EUC_CN [javac] * be able to represent 128 different characters. '#@!%%???'
[javac] ^
[javac] /benchmarksql-5.0/src/client/jTPCCRandom.java:143: : EUC_CN [javac] * be able to represent 128 different characters. '#@!%%???'
[javac] ^
[javac] 3
BUILD FAILED
/benchmarksql-5.0/build.xml:24: Compile failed; see the compiler error output for details.
Total time: 1 second
修改字符集
[root@localhost benchmarksql-5.0]# cat /etc/oracle-release
Oracle Linux Server release 7.6
[root@localhost benchmarksql-5.0]# locale
LANG=zh_CN.gb2312
LC_CTYPE="zh_CN.gb2312"
LC_NUMERIC="zh_CN.gb2312"
LC_TIME="zh_CN.gb2312"
LC_COLLATE="zh_CN.gb2312"
LC_MONETARY="zh_CN.gb2312"
LC_MESSAGES="zh_CN.gb2312"
LC_PAPER="zh_CN.gb2312"
LC_NAME="zh_CN.gb2312"
LC_ADDRESS="zh_CN.gb2312"
LC_TELEPHONE="zh_CN.gb2312"
LC_MEASUREMENT="zh_CN.gb2312"
LC_IDENTIFICATION="zh_CN.gb2312"
LC_ALL=
[root@localhost benchmarksql-5.0]# cat /etc/sysconfig/i18n
cat: /etc/sysconfig/i18n: ???
[root@localhost benchmarksql-5.0]# cat cat /etc/locale.conf
cat: cat: ???
#LANG="en_US.UTF-8"
LANG="zh_CN.gb2312"
[root@localhost benchmarksql-5.0]# cat /etc/locale.conf
#LANG="en_US.UTF-8"
LANG="zh_CN.gb2312"
[root@localhost benchmarksql-5.0]# vi /etc/locale.conf
LANG="en_US.UTF-8"
#LANG="zh_CN.gb2312"
[root@localhost benchmarksql-5.0]# ant
Buildfile: /benchmarksql-5.0/build.xml
init:
compile:
[javac] Compiling 11 source files to /benchmarksql-5.0/build
dist:
[mkdir] Created dir: /benchmarksql-5.0/dist
[jar] Building jar: /benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 2 seconds
添加mysql java connector驱动，mysql-connector-java-5.1.49.zip 需自行下载。
[root@localhost benchmarksql-5.0]# cd lib
[root@localhost lib]# ls
apache-log4j-extras-1.1.jar firebird log4j-1.2.17.jar oracle postgres
[root@localhost lib]# ls -lrt
total 820
drwxr-xr-x. 2 root root 43 May 26 2016 postgres
drwxr-xr-x. 2 root root 40 May 26 2016 oracle
-rwxr-xr-x. 1 root root 489883 May 26 2016 log4j-1.2.17.jar
drwxr-xr-x. 2 root root 58 May 26 2016 firebird
-rwxr-xr-x. 1 root root 346729 May 26 2016 apache-log4j-extras-1.1.jar
[root@localhost lib]# mkdir mysql
[root@localhost /]# unzip mysql-connector-java-5.1.49.zip
[root@localhost mysql-connector-java-5.1.49]# cp mysql-connector-java-5.1.49.jar /benchmarksql-5.0/lib/mysql/
[root@localhost mysql]# pwd
/benchmarksql-5.0/lib/mysql
[root@localhost mysql]# ls -lrt
total 984
-rw-r--r--. 1 root root 1006904 Nov 11 16:39 mysql-connector-java-5.1.49.jar
修改benchmarksql-5.0/run/runDatabaseBuild.sh，去掉extraHistID
[root@localhost benchmarksql-5.0]# vi run/runDatabaseBuild.sh
#!/bin/sh
if [ $# -lt 1 ] ; then
echo "usage: $(basename $0) PROPS [OPT VAL [...]]" >&2
exit 2
fi
PROPS="$1"
shift
if [ ! -f "${PROPS}" ] ; then
echo "${PROPS}: no such file or directory" >&2
exit 1
fi
DB="$(grep '^db=' $PROPS | sed -e 's/^db=//')"
BEFORE_LOAD="tableCreates"
#AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
AFTER_LOAD="indexCreates foreignKeys buildFinish"
for step in ${BEFORE_LOAD} ; do
./runSQL.sh "${PROPS}" $step
done
./runLoader.sh "${PROPS}" $*
for step in ${AFTER_LOAD} ; do
./runSQL.sh "${PROPS}" $step
done
修改funcs.sh 增加 mysql 数据库类型的驱动类文件目录。
[root@localhost benchmarksql-5.0]# vi run/funcs.sh
# ----
# $1 is the properties file
# ----
PROPS=$1
if [ ! -f ${PROPS} ] ; then
echo "${PROPS}: no such file" >&2
exit 1
fi
# ----
# getProp()
#
# Get a config value from the properties file.
# ----
function getProp()
{
grep "^${1}=" ${PROPS} | sed -e "s/^${1}=//"
}
# ----
# getCP()
#
# Determine the CLASSPATH based on the database system.
# ----
function setCP()
{
case "$(getProp db)" in
firebird)
cp="../lib/firebird/*:../lib/*"
;;
oracle)
cp="../lib/oracle/*"
if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then
cp="${cp}:${ORACLE_HOME}/lib/*"
fi
cp="${cp}:../lib/*"
;;
postgres)
cp="../lib/postgres/*:../lib/*"
;;
mysql)
cp="../lib/mysql/*:../lib/*"
;;
esac
myCP=".:${cp}:../dist/*"
export myCP
}
# ----
# Make sure that the properties file does have db= and the value
# is a database, we support.
# ----
case "$(getProp db)" in
firebird|oracle|postgres|mysql)
;;
"") echo "ERROR: missing db= config option in ${PROPS}" >&2
exit 1
;;
*) echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
exit 1
;;
esac
"run/funcs.sh" 63L, 1177C written
配置测试文件，输入连接地址与用户名
[root@localhost run]# vi props.mysql
db=mysql
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true&useConfigs=maxPerformance&rewriteBatchedStatements=true
user=root
password=xxzx7817600
warehouses=100
loadWorkers=40
terminals=32
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//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_eth0 blk_sda
[mysql@localhost run]$ ./runDatabaseBuild.sh prop.mysql
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
Fri Nov 11 16:57:18 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Unknown database 'benchmarksql'
Starting BenchmarkSQL LoadData
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:3306/benchmarksql
user=root
password=***********
warehouses=100
loadWorkers=40
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Fri Nov 11 16:57:19 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
ERROR: Unknown database 'benchmarksql'
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
Fri Nov 11 16:57:20 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Unknown database 'benchmarksql'
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
Fri Nov 11 16:57:22 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Unknown database 'benchmarksql'
# ------------------------------------------------------------
# Loading SQL file ./sql.common/buildFinish.sql
# ------------------------------------------------------------
Fri Nov 11 16:57:23 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Unknown database 'benchmarksql'
不建议在没有服务器身份验证的情况下建立SSL连接。根据MySQL 5.5。45+, 5.6.26+和5.7。6+要求如果未设置显式选项，默认情况下必须建立SSL连接。为了符合不使用SSL的现有应用程序，verifyServerCertificate属性设置为“false”。您需要通过设置useSSL=false显式禁用SSL，或者设置useSSL=true并为服务器证书验证提供信任库。
解决方案：
在url后面加上?useSSL=false即可
jdbc:mysql://localhost:3306/userDb?useSSL=false
----------------
[mysql@localhost run]$ vi prop.mysql
db=mysql
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:3306/benchmarksql
user=root
password=xxzx7817600
warehouses=100
loadWorkers=40
terminals=32
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//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_eth0 blk_sda
[mysql@localhost run]$ ./runDatabaseBuild.sh props.mysql
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
);
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
);
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500)
);
create sequence bmsql_hist_id_seq;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sequence bmsql_hist_id_seq' at line 1
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);
create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp
);
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
);
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer
);
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24)
);
Starting BenchmarkSQL LoadData
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true&useConfigs=maxPerformance&rewriteBatchedStatements=true
user=root
password=***********
warehouses=100
loadWorkers=40
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 002: Loading Warehouse 2
Worker 003: Loading Warehouse 3
Worker 004: Loading Warehouse 4
Worker 005: Loading Warehouse 5
Worker 006: Loading Warehouse 6
Worker 007: Loading Warehouse 7
Worker 008: Loading Warehouse 8
Worker 010: Loading Warehouse 10
Worker 009: Loading Warehouse 9
Worker 011: Loading Warehouse 11
Worker 012: Loading Warehouse 12
Worker 013: Loading Warehouse 13
Worker 014: Loading Warehouse 14
Worker 015: Loading Warehouse 15
Worker 016: Loading Warehouse 16
Worker 017: Loading Warehouse 17
Worker 018: Loading Warehouse 18
Worker 019: Loading Warehouse 19
Worker 020: Loading Warehouse 20
Worker 021: Loading Warehouse 21
Worker 022: Loading Warehouse 22
Worker 023: Loading Warehouse 23
Worker 024: Loading Warehouse 24
Worker 025: Loading Warehouse 25
Worker 026: Loading Warehouse 26
Worker 027: Loading Warehouse 27
Worker 028: Loading Warehouse 28
Worker 029: Loading Warehouse 29
Worker 030: Loading Warehouse 30
Worker 032: Loading Warehouse 31
Worker 031: Loading Warehouse 32
Worker 033: Loading Warehouse 33
Worker 034: Loading Warehouse 34
Worker 035: Loading Warehouse 35
Worker 036: Loading Warehouse 36
Worker 037: Loading Warehouse 37
Worker 038: Loading Warehouse 38
Worker 039: Loading Warehouse 39
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 40
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_customer add constraint c_district_fkey
foreign key (c_w_id, c_d_id)
references bmsql_district (d_w_id, d_id);
alter table bmsql_history add constraint h_customer_fkey
foreign key (h_c_w_id, h_c_d_id, h_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
alter table bmsql_history add constraint h_district_fkey
foreign key (h_w_id, h_d_id)
references bmsql_district (d_w_id, d_id);
alter table bmsql_new_order add constraint no_order_fkey
foreign key (no_w_id, no_d_id, no_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
alter table bmsql_oorder add constraint o_customer_fkey
foreign key (o_w_id, o_d_id, o_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
alter table bmsql_order_line add constraint ol_order_fkey
foreign key (ol_w_id, ol_d_id, ol_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
alter table bmsql_order_line add constraint ol_stock_fkey
foreign key (ol_supply_w_id, ol_i_id)
references bmsql_stock (s_w_id, s_i_id);
alter table bmsql_stock add constraint s_warehouse_fkey
foreign key (s_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_stock add constraint s_item_fkey
foreign key (s_i_id)
references bmsql_item (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.common/buildFinish.sql
# ------------------------------------------------------------
-- ----
-- Extra commands to run after the tables are created, loaded,
-- indexes built and extra's created.
-- ----
[mysql@localhost run]$ ./runBenchmark.sh props.mysql
09:25:48,374 [main] INFO jTPCC : Term-00,
09:25:48,379 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
09:25:48,379 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
09:25:48,379 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
09:25:48,379 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
09:25:48,379 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
09:25:48,384 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
09:25:48,385 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
09:25:48,385 [main] INFO jTPCC : Term-00,
09:25:48,385 [main] INFO jTPCC : Term-00, db=mysql
09:25:48,385 [main] INFO jTPCC : Term-00, driver=com.mysql.jdbc.Driver
09:25:48,385 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://127.0.0.1:3306/test?useSSL=false
09:25:48,385 [main] INFO jTPCC : Term-00, user=root
09:25:48,386 [main] INFO jTPCC : Term-00,
09:25:48,386 [main] INFO jTPCC : Term-00, warehouses=40
09:25:48,386 [main] INFO jTPCC : Term-00, terminals=10
09:25:48,388 [main] INFO jTPCC : Term-00, runMins=10
09:25:48,388 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
09:25:48,388 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
09:25:48,389 [main] INFO jTPCC : Term-00,
09:25:48,389 [main] INFO jTPCC : Term-00, newOrderWeight=45
09:25:48,389 [main] INFO jTPCC : Term-00, paymentWeight=43
09:25:48,389 [main] INFO jTPCC : Term-00, orderStatusWeight=4
09:25:48,389 [main] INFO jTPCC : Term-00, deliveryWeight=4
09:25:48,389 [main] INFO jTPCC : Term-00, stockLevelWeight=4
09:25:48,389 [main] INFO jTPCC : Term-00,
09:25:48,389 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
09:25:48,390 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
09:25:48,390 [main] INFO jTPCC : Term-00,
09:25:48,529 [main] INFO jTPCC : Term-00, copied props.mysql to my_result_2022-11-14_092548/run.properties
09:25:48,529 [main] INFO jTPCC : Term-00, created my_result_2022-11-14_092548/data/runInfo.csv for runID 5
09:25:48,530 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-11-14_092548/data/result.csv
09:25:48,531 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
09:25:48,532 [main] INFO jTPCC : Term-00, osCollectorInterval=1
09:25:48,532 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
09:25:48,532 [main] INFO jTPCC : Term-00, osCollectorDevices=null
09:25:48,684 [main] INFO jTPCC : Term-00,
09:25:49,097 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 182
09:25:49,098 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 253
09:25:49,098 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 32447.84 Current tpmTOTAL: 2146668 Memory Usage: 143MB / 203MB
09:35:49,526 [Thread-6] INFO jTPCC : Term-00,
09:35:49,526 [Thread-6] INFO jTPCC : Term-00,
09:35:49,527 [Thread-6] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 14913.98
09:35:49,527 [Thread-6] INFO jTPCC : Term-00, Measured tpmTOTAL = 32102.08
09:35:49,527 [Thread-6] INFO jTPCC : Term-00, Session Start = 2022-11-14 09:25:49
09:35:49,528 [Thread-6] INFO jTPCC : Term-00, Session End = 2022-11-14 09:35:49
09:35:49,528 [Thread-6] INFO jTPCC : Term-00, Transaction Count = 321069