Sysbench Benchmarks#
In this chapter we assume that you have created a RonDB cluster as documented in the part on creating a RonDB cluster. We also assume that the cluster you have created has at least 2 MySQL VMs and 1 API VM. After doing so, the setup is in place to easily run benchmarks towards RonDB.
The benchmarks are always executed from a terminal window. The first step is to log in to the VM from where you want to execute the benchmarks. If you have used the cloud script to set things up this would be the head VM, in the managed version of RonDB this would be the API node VM. Let’s assume that the IP address of this VM is 3.13.33.53. In this case the following commands should be executed to run a benchmark in RonDB using AWS.
# Log into the AWS API VM
ssh ubuntu@3.13.33.53 # `ssh 3.13.33.53` on Azure and GCP
# Log into the mysql user in this VM
sudo su - mysql
# Execute the Sysbench benchmark against multiple MySQL servers.
# The benchmarking script is in the $PATH.
bench_run.sh --default-directory /home/mysql/benchmarks/sysbench_multi
This will run the default Sysbench benchmark. In the following chapters we will describe how to read the results and how we can modify the benchmarks.
Configuring Sysbench RonDB benchmarks#
The benchmarks executed by RonDB is configured by a file called autobench.conf. The default-directory must contain a file called autobench.conf, since this file describes the benchmark configuration.
Below is the configuration used to run Sysbench Standard OLTP RW benchmark against 2 MySQL servers. This is the configuration file you will find under /home/mysql/benchmarks/sysbench_multi/autobench.conf. It is already setup such that one can start a benchmark out of the box, but it can be changed to suite one’s needs.
#############################
#### Software definition ####
#############################
MYSQL_BIN_INSTALL_DIR="/srv/hops/mysql"
BENCHMARK_TO_RUN="sysbench"
#########################################
#### 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 ####
##############################
SYSBENCH_TEST="oltp_rw"
SYSBENCH_INSTANCES="2"
THREAD_COUNTS_TO_RUN="1;2;4;8;12;16;24;32;48;64;96;112;128"
MAX_TIME="30"
The following explains the parameters set in this file.
- MYSQL_BIN_INSTALL_DIR
This specifies where the RonDB binary installation is placed.
- BENCHMARK_TO_RUN
This specifies the benchmark to run. Currently, we support sysbench, dbt2, dbt3 or flexAsynch.
- SERVER_HOST
This is a list of the IP addresses of the MySQL Servers. This list is filled in automatically by the installation scripts of RonDB using both scripts and the managed RonDB version.
- MYSQL_PASSWORD
This is the generated password that makes it possible to access the MySQL Servers using the default user mysql. It is also filled in by the installation process.
- SYSBENCH_TEST
This is the type of Sysbench benchmark, mainly used are oltp_rw and oltp_ro.
- SYSBENCH_INSTANCES
This is the number of instances of Sysbench running in parallel. Every Sysbench instance will be assigned to a MySQL server in a round robin fashion and each instance will run through the thread counts in THREAD_COUNTS_TO_RUN. It is recommended to set the Sysbench instances to be the same or smaller than the number of MySQL Servers.
To run the Sysbench instances on different API servers, use BENCHMARK_SERVERS. Otherwise, they will default to localhost. Synchronising Sysbench instances across API servers takes slightly more care and is not handled in this documentation.
- THREAD_COUNTS_TO_RUN
This is the number of threads that each Sysbench instance will be runnning in a number of test runs.
Thus in this case we will first run the benchmark with 1 thread, next with 2 threads, 4,... and finally we will run it with 128 threads.
- MAX_TIME
This is the time each benchmark will execute for one set of threads.
Description of Standard OLTP RW#
The Sysbench OLTP RW benchmark consists of 20 SQL queries. There is a transaction, this means that the transaction starts with a BEGIN statement and it ends with a COMMIT statement.
After the BEGIN statement follows 10 SELECT statements that selects one row using the primary key of the table.
Next follows 4 SELECT queries that select 100 rows within a range and either uses SELECT DISTINCT, SELECT … ORDER BY, SELECT or SELECT sum(..).
Finally there is one INSERT, one DELETE and 2 UPDATE queries.
In Pseudo code thus:
BEGIN
Repeat 10 times: SELECT col(s) from TAB where PK=pk
SELECT col(s) from TAB where key >= start AND key < (start + 100)
SELECT DISTINCT col(s) from TAB where key >= start AND key < (start + 100)
SELECT col(s) from TAB where key >= start AND key < (start + 100) ORDER BY key
SELECT SUM(col) from TAB where key >= start AND key < (start + 100)
INSERT INTO TAB values (....)
UPDATE TAB SET col=val WHERE PK=pk
UPDATE TAB SET col=val WHERE key=key
DELETE FROM TAB WHERE PK=pk
COMMIT
This is the standard OLTP RW benchmark executed by the above configuration file.
Standard OLTP RO#
Standard OLTP RO is a read only version of OLTP RW. This means that all queries are the same except that we remove the INSERT, DELETE and UPDATE queries.
To execute this benchmark replace SYSBENCH_TEST with oltp_ro.
OLTP RW using filters#
This benchmark uses the same queries as Standard OLTP RW. It does however add one more part to the WHERE clause that ensures that only 1 row is returned from the query. To execute this benchmark the line below to the autobench.conf file.
Key Lookup#
Another variant that can be achieved with Sysbench is a batch key lookup benchmark. This tests how many key lookups that can be performed per second using SQL statements that fetches many rows. This test executes the Standard OLTP RO benchmark, removes all the range scans and finally changes the primary key lookup queries to use the IN-statement such that the query fetches many rows using the primary key in a single SQL statement.
By adding the below lines to the autobench.conf file we will transform Sysbench into a Key Lookup benchmark.
SYSBENCH_TEST="oltp_ro"
SB_USE_IN_STATEMENT="100"
SB_SIMPLE_RANGES="0"
SB_ORDER_RANGES="0"
SB_DISTINCT_RANGES="0"
SB_SUM_RANGES="0"
Viewing the results#
To view the results after executing the benchmark to completion one can print the result file through the command:
The following code snippet shows an example of the output this can produce. Note that the unit for Mean is Transactions per second.
Final results for this test run
Threads: 1 Mean: 130
Threads: 2 Mean: 265
Threads: 4 Mean: 481
Threads: 8 Mean: 918
Threads: 12 Mean: 1331
Threads: 16 Mean: 1684
Threads: 24 Mean: 2494
Threads: 32 Mean: 3381
Threads: 48 Mean: 4879
Threads: 64 Mean: 6325
Threads: 96 Mean: 7851
Threads: 112 Mean: 8032
Threads: 128 Mean: 8048
It is also possible to view the results live while the benchmark is running. To do this open another terminal window by executing the following commands:
ssh ubuntu@3.13.33.53 # `ssh 3.13.33.53` on Azure and GCP
sudo su - mysql
cd benchmarks/sysbench_multi/sysbench_results
tail -f oltp_rw_0_0.res
The directory sysbench_results is created when the first benchmark is executed using the sysbench_multi directory. The file is called oltp_rw if OLTP RW is executed and oltp_ro if OLTP RO is executed. The first 0 is the number of the sysbench instance. Thus more than one file is written into in parallel if there are more than one sysbench instance.
Here is an example of the output to this file when executing with 12 threads. The intermediate result output have been edited a bit for improved readability here, in reality it is 1 line per 3 seconds.
Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using "BEGIN" for starting transactions
Not using auto_inc on the id column
Using 1 test tables
Threads started!
[ 3s] Intermediate results: 12 threads, tps: 1331.904644,
reads/s: 18646.665011,
writes/s: 6659.523218 response time: 10.268101ms (95%)
[ 6s] Intermediate results: 12 threads, tps: 1304.152396,
reads/s: 18258.133543,
writes/s: 6520.761980 response time: 10.472978ms (95%)
.....
[ 39s] Intermediate results: 12 threads, tps: 1336.415180,
reads/s: 18709.812515,
writes/s: 6682.075898 response time: 9.998158ms (95%)
Time limit exceeded, exiting...
(last message repeated 11 times)
Done.
OLTP test statistics:
queries performed:
read: 745892
write: 266390
other: 106556
total: 1118838
transactions: 53278 (1331.71 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1012282 (25302.54 per sec.)
other operations: 106556 (2663.43 per sec.)
General statistics:
total time: 40.0071s
total number of events: 53278
total time taken by event execution: 479.6287
response time:
min: 5.70ms
avg: 9.00ms
max: 80.64ms
approx. 95 percentile: 10.15ms
Threads fairness:
events (avg/stddev): 4439.8333/206.51
execution time (avg/stddev): 39.9691/0.00
Characteristics of Sysbench#
Sysbench uses Read Committed for all queries. This means that all the SELECT queries in the benchmark has the ability to use both ldm threads and query threads. This means that the performance of Sysbench with RonDB can scale even with a single table even with very large data nodes.
Sysbench is very flexible and easy to modify to test many different capabilities. It can be turned into a benchmark of mostly writes and it can be turned into a read benchmark.
Setting up Sysbench on a benchmark cluster#
The above assumes that you have already a running RonDB cluster, either in the managed RonDB cloud or something you have setup yourself.
It is also possible to use the benchmark scripts to execute the Sysbench benchmarks on a set of machines you have set aside for benchmarking. This is what has been done for many blogs by Mikael Ronström about benchmarks on NDB and RonDB.
In this case we need to add a few more sections to the autobench.conf configuration file.
In the example below we assume that you have access to 2 machines, each equipped with 18 Xeon cores, thus 36 CPUs each. They are equipped with enough memory and enough disk bandwidth for the experiment. In preparation you have downloaded the RonDB binary tarball from repo.hops.works and unpacked it in your home directory. You have also made sure that the bin directory in the unpacked directory is in your PATH. Most importantly, the bench_run.sh script is to be found there. To add the binaries to your path, run the following command:
3 machines is the minimum to execute a highly available configuration, but for this benchmark we focus on the performance aspects and place the management server on one of the 2 machines we have access to. With 2 machines we also place the MySQL Server on the same machines and also the Sysbench program itself.
With this cluster setup it is important to ensure that the nodes are isolated from each other. Thus we use CPU locking to ensure that they are not running on the same CPUs.
We place the RonDB data node on 14 CPUs, the MySQL Server use 18 CPUs and the Sysbench program is provided with 4 CPUs. The SERVER_BIND and other BIND parameters specify the NUMA node to allocate memory from. In this case the RonDB data node need to specify all NUMA nodes, they can be found using the command:
The benchmark definition uses 2 Sysbench instances in this case, one on each server.
#############################
#### Software definition ####
#############################
MYSQL_BIN_INSTALL_DIR="/home/username/rondb-21.04.4-linux-glibc2.17-x86_64"
BENCHMARK_TO_RUN="sysbench"
DATA_DIR_BASE="/home/username/ndb"
#################################
#### MySQL Server definition ####
#################################
TASKSET="numactl"
SERVER_HOST="172.31.23.248;172.31.31.222"
SERVER_PORT="3316"
SERVER_BIND="0"
SERVER_CPUS="9-17,27-35"
NDB_MULTI_CONNECTION="4"
MYSQL_USER="root"
##############################
#### NDB node definitions ####
##############################
USE_SHM="yes"
NDB_MGMD_NODES="172.31.23.248"
NDBD_NODES="172.31.23.248;172.31.31.222"
NDB_TOTAL_MEMORY="64G"
NDBD_BIND="0,1"
NDBD_CPUS="0-6,18-24"
NDB_SPIN_METHOD="DatabaseMachineSpinning"
# USE_SUPERSOCKET="yes"
##############################
#### Benchmark definition ####
##############################
BENCHMARK_TASKSET="numactl"
BENCHMARK_BIND="0"
BENCHMARK_CPUS="8,9,26,27"
SYSBENCH_TEST="oltp_rw"
SYSBENCH_INSTANCES="2"
BENCHMARK_SERVERS="172.31.23.248;172.31.31.222"
THREAD_COUNTS_TO_RUN="1;2;4;8;12;16;24;32;48;64;96;112;128"
MAX_TIME="30"
Additional parameters we specified here is the total memory used by the RonDB data node. 64 GByte is enough by a large margin to execute the Sysbench benchmark. The NDB_MULTI_CONNECTION means that we use 4 cluster connections for each connection from the MySQL Server to the RonDB data nodes. This ensures that the MySQL Server scales to at least 32 CPUs which is a lot more than we have access to here.
The default behaviour in RonDB is to use latency optimised spinning, in this setup we set it up using an even more aggressive spinning. Since we set up the RonDB cluster on a small set of machines with colocated data nodes and MySQL Servers we also use shared memory for communication between nodes in the same server.
In the above example we also show how easy it is to benchmark using Dolphin Supersockets. This is HW that provides an efficient communication between computers providing 50 Gbit/second transfer rates at latency below 1 microsecond. Normal Ethernet provides latency on roundtrips of above 20 microseconds. Thus Dolphin SuperSocket can be very useful in applications with extreme demands on low latency such as financial applications and gaming applications.
Running the benchmark#
In this case we need to use the following commands to run the benchmark. The commands includes start and stop of the cluster. First create a directory sysbench_multi in your home directory. Place the autobench.conf in this directory. Now to start the cluster use the following command.
cd /home/username/sysbench_multi
bench_run.sh --default-directory /home/username/sysbench_multi --generate --start --skip-run --skip-stop
The generate flag generates a set of configuration files and needs to be added the first time one runs the benchmark and also every time some part of the autobench.conf has changed. If a change of the data node or MySQL Server configuration has happened, then a restart of the RonDB cluster is also required.
The below command executes a benchmark.
To stop the RonDB cluster use the following command.
The output from the benchmark is the same as described previously.
A final note on benchmark setups#
Using the benchmark scripts provided in the RonDB binary tarball assumes that all nodes in the configuration can communicate using SSH without a password. This must be setup before executing the benchmark.