BenMorel/dbt2.patch

## 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
	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=210241024;"
	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
	#!/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