Last active
November 17, 2023 22:00
-
-
Save BenMorel/6131673 to your computer and use it in GitHub Desktop.
Downloads and runs the DBT2 benchmark for MySQL.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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).
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Did you got solution to this?