benchmarksql 5.0压测MySQL

时间:2022-12-09 12:08:29

在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