Downloads and runs the DBT2 benchmark for MySQL.

dbt2.patch

From 664f364c5c08a4bcb51d8ebdda2f72b9e8f8d491 Mon Sep 17 00:00:00 2001
From: Benjamin Morel <benjamin.morel@gmail.com>
Date: Sun, 17 Sep 2023 14:50:28 +0200
Subject: [PATCH] Patch

---
 scripts/mysql/mysql_load_db.sh | 5 ++---
 1 file changed, 2 insertions(+), 3 deletions(-)

diff --git a/scripts/mysql/mysql_load_db.sh b/scripts/mysql/mysql_load_db.sh
index 486a952..b1aae8b 100755
--- a/scripts/mysql/mysql_load_db.sh
+++ b/scripts/mysql/mysql_load_db.sh
@@ -100,7 +100,6 @@ else
   TABLES="customer district history item new_order order_line orders stock warehouse"
 fi
 
-SET_BATCH_SIZE="set ndb_batch_size=2*1024*1024;"
 for TABLE in $TABLES ; do
   COLUMN_NAMES=
   if [ "$TABLE" = "history" ]; then
@@ -113,7 +112,7 @@ for TABLE in $TABLES ; do
     else
       FN="$TABLE"
     fi
-    command_exec "$MYSQL $DB_NAME -e \"$SET_BATCH_SIZE LOAD DATA $LOCAL INFILE \
+    command_exec "$MYSQL --local-infile=1 $DB_NAME -e \"LOAD DATA $LOCAL INFILE \
               \\\"$DB_PATH/$FN.data\\\" \
               INTO TABLE $TABLE $LATIN1 FIELDS TERMINATED BY ',' ${COLUMN_NAMES} \""
   fi
@@ -495,7 +494,7 @@ if [ ! -f "$MYSQL" ]; then
 fi
 
 if [ "$DB_PASSWORD" != "" ]; then
-  MYSQL_ARGS="-p $DB_PASSWORD"
+  MYSQL_ARGS="--password=$DB_PASSWORD"
 fi
 
 MYSQL_ARGS="$MYSQL_ARGS -h $DB_HOST -u $DB_USER"
-- 
2.39.2

dbt2.sh

#!/bin/sh

# Downloads and runs the DBT2 benchmark for MySQL.
# ================================================
#
# Every version of the DBT2 benchmark seems to be broken in some way, so I maintain this script that patches a
# couple things and runs every step of the benchmark.
#
# On a bare metal server, Xeon E5-1650v4 6x (12x HT) 3.60GHz, 64GB DDR4 2666, NVMe drive, with:
#  - innodb_buffer_pool_size = 32GB
#  - innodb_log_file_size = 1G
# I'm getting 91161 new-order transactions per minute (NOTPM).
#
# Doing better? Please let me know your setup & NOTPM score in the comments!
#
# INSTRUCTIONS - Before running this script:
# ------------------------------------------
#
# - Download dbt2.patch and this script in the same directory
#
# - Install required packages:
#     yum: $ sudo yum install -y git wget gcc make autoconf mysql-devel
#     apt: $ sudo apt install -y git wget gcc make autoconf libmysqlclient-dev
#
# - If you already have a MySQL server:
#
#   - add these directives to my.cnf (or pass --local-infile=1 to the mysqld command):
#     [server]
#     local-infile = 1
#   - restart your MySQL server
#
# - If you don't have a MySQL server, you can start one with Docker:
#
#   - MySQL 8.0:
#     docker run --name mysql80 -p 3306:3306 -d --rm -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql:8.0 --innodb-buffer-pool-size=16G --local-infile=1
#
#   - MySQL 8.1:
#     docker run --name mysql81 -p 3306:3306 -d --rm -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql:8.1 --innodb-buffer-pool-size=16G --local-infile=1
#
#   Adapt --innodb-buffer-pool-size to your available RAM.
#
# - Finally, if needed, edit this script with your MySQL hostname & credentials.

set -e

host='127.0.0.1'
port='3306'
user='root'
pass=''

dbt2_version='dbt2-0.37.50.16';

mysql_path=$(which mysql)
mysql_dir_path=$(dirname $mysql_path)

log() {
  echo
  echo -e "\e[37;42m$1\e[0m"
}

# Clean up previous builds
rm -rf /tmp/dbt2

# Set up working directories
mkdir -p /tmp/dbt2/data

cp dbt2.patch /tmp/dbt2/data

log 'Downloading...'
wget -q -O - https://downloads.mysql.com/source/$dbt2_version.tar.gz | tar xvz -C /tmp/dbt2

# Go to the directory extracted
cd /tmp/dbt2/$dbt2_version

# Apply patch
git apply /tmp/dbt2/data/dbt2.patch

log 'Configuring...'
./configure --with-mysql

log 'Compiling...'
make

log 'Generating data files...'
src/datagen -w 30 -d /tmp/dbt2/data --mysql

log 'Loading data into the database...'
scripts/mysql/mysql_load_db.sh \
  --path /tmp/dbt2/data \
  --local \
  --mysql-path "$mysql_path" \
  --host "$host" \
  --port "$port" \
  --user "$user" \
  --password "$pass"

log 'Loading stored procedures...'
scripts/mysql/mysql_load_sp.sh \
  --client-path "$mysql_dir_path" \
  --sp-path storedproc/mysql \
  --host "$host" \
  --port "$port" \
  --user "$user" \
  --password "$pass"

log 'Running benchmark...'
scripts/run_mysql.sh \
  --connections 20 \
  --time 300 \
  --warehouses 30 \
  --zero-delay \
  --host "$host" \
  --port "$port" \
  --user "$user" \
  --password "$pass"

# Clean up
rm -rf /tmp/dbt2

@marinsalinas @gowthamksa24 I case you still need this, I just fixed an issue that was probably causing the problem you encountered (dbt2 uses mysql -p instead of --password, I added a patch for this + another issue).