DBT2 Benchmarks#

As with Sysbench it should be fairly straightforward to execute the DBT2 benchmark. The command to execute the benchmark corresponds directly to the execution of the Sysbench benchmark. The only change is that the benchmark directory is dbt2_multi instead of sysbench_multi.

ssh ubuntu@3.13.33.53  # `ssh 3.13.33.53` on Azure and GCP
sudo su - mysql
cd benchmarks
bench_run.sh --default-directory /home/mysql/benchmarks/dbt2_multi

As with Sysbench in the managed RonDB the benchmark is ready to use out of the box.

Configuring the DBT2 benchmark#

Below is the contents of the autobench.conf for the DBT2 benchmark. The DBT2 benchmark is not so flexible, as it is a benchmark that contains 5 different complex transactions and the percentage of each of those transactions is set in the benchmark.

The DBT2 benchmark introduces two new terms: warehouses and terminals. A warehouse can be interpreted as a data chunk and each warehouse will require around 100 MByte in RonDB DataMemory. A terminal on the other hand is implemented as a benchmarking thread, whereby by default each terminal will always use the same warehouse.

In the configuration of DBT2, one can thus mainly configure

the total number of warehouses
the number of MySQL Servers to run against
the number of threads to use against each MySQL Server
the running time of each test in the benchmark

To set the total number of warehouses, change the DBT2_WAREHOUSES parameter below.

#############################
#### Software definition ####
#############################

MYSQL_BIN_INSTALL_DIR="/srv/hops/mysql"
BENCHMARK_TO_RUN="dbt2"

#########################################
#### Storage definition (empty here) ####
#########################################

#################################
#### MySQL Server definition ####
#################################

SERVER_HOST="172.31.23.248;172.31.31.222"
MYSQL_PASSWORD='3*=13*820.*07?=45'

###########################################
#### NDB node definitions (empty here) ####
###########################################

##############################
#### Benchmark definition ####
##############################

DBT2_TIME="30"
DBT2_WAREHOUSES="64"
DBT2_DATA_DIR="/home/mysql/benchmarks/dbt2_data"

DBT2 also uses a second configuration file. This file is called dbt2_run_1.conf. Each line here represents one run of the benchmark, whereby it is here we also specify how many of our configured MySQL servers we run against for each test. The NUM_WAREHOUSES in this file references the number of warehouses per MySQL Server. The total number of warehouses previously defined naturally needs to be greater or equal than the product of the number of MySQL servers and number of warehouses per MySQL Server. The number of terminals can be up to 10. The more terminals are used, the more deadlocks there will be.

One major difference with DBT2 compared to TPC-C is that each terminal starts a new transaction immediately after the previous finished. In TPC-C a terminal is only allowed to generate about 15 transactions per minute.

The following shows an example of a dbt2_run_1.conf file:

# NUM_MYSQL_SERVERS  NUM_WAREHOUSES  NUM_TERMINALS
2                    1               1
2                    2               1
2                    4               1
2                    8               1
2                    12              1
2                    16              1
2                    24              1
2                    32              1

The results of the benchmarks is stored in the benchmark directory in the file final_result.txt. More details can be found in the dbt2_results directory. One can also look at results while the processing is ongoing in the dbt2_output directory. Progress during the load process can be monitored in the dbt2_logs directory.

Altered mode of DBT2#

Since the default mode of running DBT2 is that each terminal always uses the same warehouse, it may happen that if the number of warehouses is very high, we do not use all the data in the database. Thus to allow the use of all data, we have a mode called altered mode in DBT2. In this case each terminal will select a new warehouse at random for each new transaction. This ensures that we make use of all the data in RonDB.

This was an important feature when testing on a Server with Intel Optane memory of 6 TBytes. Since the Intel Optane uses DRAM as a cache it was important to ensure that the benchmark also used data from the Intel Optane and not always find its data in the DRAM cache. It is in generally a good parameter to use when running with large amounts of warehouses.

To activate this feature add the following line in autobench.conf.

DBT2_USE_ALTERED_MODE="yes"

Characteristics of DBT2#

In DBT2 most queries are using locks. Thus DBT2 is more of a classic transaction-oriented workload. Almost 70% of the load generated is on a single table, this means that performance is much better in RonDB 22.01 with improvements of placements of the primary replica and the ability to use query threads for locked reads as well.

DBT2 on a benchmark cluster#

All the settings defined in the section on how to setup a benchmark cluster for Sysbench also applies to executing a DBT2 benchmark with the exception that the benchmark driver and clients are always executed on the machine driving the benchmark. It can however access MySQL Servers on many machines.