在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

*

*/

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

*

*/

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, ");

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,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,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

benchmarksql 5.0壓測MySQL

相關文章