Benchmark RonDB cluster

Benchmarking RonDB

In each binary release of RonDB we also include a number of benchmark programs. This includes both scripts to execute the benchmarks as well as the required binaries to execute those benchmarks.

Currently this is only included in the Linux/x86 releases of RonDB, even though this is in the process of changing.

The benchmarks we focus on are the following

• Sysbench

  This is a simple benchmark that uses the MySQL C client to send simple SQL statements to RonDB. It is quite flexible and can be adapted to test many different things in RonDB.

• YCSB (Yahoo Cloud Serving Benchmark)

  This is a benchmark that can use both the MySQL JDBC driver and it can also use ClusterJ. It contains six different workloads, mostly the operations are simple read or write operations on often a bit bigger rows. It is thus mainly a benchmark of key-value operations. We use it to show the LATS characteristics of RonDB (low Latency, high Availability, high Throughput and scalable Storage). It gives us the possibility to benchmark throughput while showing latency at the same time. We can show throughput and latency while restarting nodes. This gives a good view on our availablilty. Finally we can show performance with in-memory payload and we can show scalable Storage using disk columns for the payload data. This shows our throughput and latency also for disk-based data.

• DBT2

  This is an open source variant of the TPC-C benchmark. It represents a slightly more complex application. It also uses the MySQL C client to issue SQL statements to RonDB. The SQL statements are using stored procedures.

• DBT3

  This is an open source variant of the TPC-H benchmark. This benchmark executes complex queries with a lot of data in the database. It stresses the query execution engine in RonDB. RonDB supports pushing some complex join queries into the RonDB data nodes, thus achieving parallel query capabilities of RonDB. The main performance booster here is that the RonDB data nodes can perform parallel filtering of rows.

• flexAsynch

  This is a simple benchmark focusing on testing RonDB as a key-value store. It uses the C++ NDB API to perform massive amounts of parallel read, insert, update and delete operations. It has been used to show how RonDB can handle massive amounts of key lookups.

• ClusterJ benchmark

  ClusterJ is the native Java RonDB API. The benchmark focuses on performing simple key lookup operations towards RonDB. Makes it possible to show how batching can improve performance using ClusterJ and the C++ NDB API.

We will also discuss in this document how to mix various benchmarks. Given that RonDB is a distributed DBMS it is quite easy to execute more than one application towards it concurrently.

Currently we set up 4 different preconfigured RonDB benchmarks as part of managed RonDB. These are:

# Multiple MySQL Servers running Sysbench in parallel
/home/mysql/benchmarks/sysbench_multi
# Single MySQL Server running Sysbench
/home/mysql/benchmarks/sysbench_single
# Multiple MySQL Servers running DBT2 in parallel
/home/mysql/benchmarks/dbt2_multi
# Single MySQL Server running DBT2
/home/mysql/benchmarks/dbt2_single

The multi benchmarks use all MySQL Servers in the RonDB setup, single uses a single MySQL Server.

The description in this chapter assumes that you are using a cluster that has been started using managed RonDB. See the chapter on managed RonDB to see how this is done. Alternatively, you may have created a RonDB cluster locally with the logicalclocks/rondb-docker repository, which uses the same benchmarking setup.

Quickstart to execute a Sysbench RonDB benchmark

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.

SB_USE_FILTER="yes"

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:

cat /home/mysql/benchmarks/sysbench_multi/final_result.txt

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:

export PATH=/home/username/rondb-21.04.9-linux-glibc2.17-x86_64/bin:$PATH

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:

numactl --show

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.

bench_run.sh --default-directory /home/username/sysbench_multi

To stop the RonDB cluster use the following command.

bench_run.sh --default-directory /home/username/sysbench_multi --stop

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.

Executing the DBT2 benchmark in RonDB

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.

YCSB, Yahoo Cloud Serving Benchmarks

YCSB is widely used for the comparison of cloud data serving systems. You can run YCSB in two configurations

• Using JDBC API that connects to RonDB via MySQL Server(s)

• Using native ClusterJ API that connects directly to RonDB data nodes

Uisng ClusterJ saves computer resources needed for MySQL servers. ClusterJ also brings a bit better performance and latency on key-value lookups and writes. On the other hand, the MySQL Server is a bit more efficient in performing complex range scan operations as tested in Workload E.

Setting up an YCSB benchmark environment

The source code for YCSB is available at github.com/logicalclocks/YCSB So first clone this tree. To build the YCSB tree it is necessary to install Java and Maven.

git clone http://github.com/logicalclocks/YCSB.git
cd YCSB

Build rondb-binding to run the benchmark using the native ClusterJ API.

mvn -pl site.ycsb:rondb-binding -am clean package

Otherwise, build jdbc-binding to run the benchmark using JDBC driver.

mvn -pl site.ycsb:jdbc-binding -am clean package

Extra actions before using ClusterJ

The rondb-binding uses the latest stable ClusterJ version. To use a different version of the ClusterJ library edit the pom.xml file at the top level in the YCSB tree before compiling. There set the rondb.clusterj.version to the version you want to use.

After compiling the YCSB tree it is necessary to point the NDB API library that ClusterJ uses. In the managed RonDB installation this is found in the directory /srv/hops/mysql/lib. Also make sure that the Java engine gets enough memory to execute the YCSB benchmark by setting the JAVA_OPTS variable.

export JAVA_OPTS="-Xmx4g"
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/srv/hops/mysql/lib

Create YCSB table

Each YCSB client uses a single table, but it is possible to use different tables from different clients. One table has limited scaling for writes since the number of partitions per table is 2 * number of data nodes. Thus in a 2-node standard setup we will have 4 partitions per table.

When we create the table we decide how many columns of payload data to use, we decide the size of the payload columns. In addition we can put columns in-memory and we can put them on disk columns.

The default name for the table used by YCSB is usertable. It is possible to run multiple simultaneous instances of YCSB clients operating on different tables. When you create a table with a different name then you can pass the table name to YCSB using the table parameter using -p command parameter or by adding the file describing the workload as described more below.

The example below create a YCSB table with a single payload column which is a 4 kB binary data column.

CREATE TABLE usertable (YCSB_KEY VARCHAR(255) PRIMARY KEY, FIELD0 varbinary(4096))
ENGINE = NDB;

Below is the standard YCSB table using 10 payload columns of VARCHAR data type storing up to 100 characters. The default character set in RonDB is UTF8MB4. This means that each character can take up to 4 bytes of space.

CREATE TABLE usertable (YCSB_KEY VARCHAR(255) PRIMARY KEY,
                        field0 varchar(100),
                        field1 varchar(100),
                        field2 varchar(100),
                        field3 varchar(100),
                        field4 varchar(100),
                        field5 varchar(100),
                        field6 varchar(100),
                        field7 varchar(100),
                        field8 varchar(100),
                        field9 varchar(100))
ENGINE = NDB;

Creating a table with payload columns stored in disk columns requires a bit more effort. First it is necessary to create a logfile group that stores the UNDO log for disk parts. An UNDO log file of 64 Gbyte is sufficiently large to handle all YCSB workloads.

Next one need to create a tablespace and map a data file to this tablespace. Here is an important consideration to make. If one uses a set of NVMe drives, then it is better to have one file per NVMe drive rather than setting up the NVMe drives using a RAID setup.

The blog post at http://mikaelronstrom.blogspot.com/2020/08/setting-up-nvme-drives-on-oracle-cloud.html gives a lot of details of how one can set up an optimal disk setup for use by RonDB except that as mentioned above it is sufficient to have 64 GByte of UNDO log.

When creating the tablespace we need to connect it to the logfile group and set the size of the initial data file. Using the command ALTER TABLESPACE one can then add more data files as required.

Finally when creating the table one connects it to the tablespace and sets the non-indexed columns to use disk storage. Thus in this case the primary key will still use in-memory storage, but the payload column will be stored on disk.

CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 64G
ENGINE=NDB;
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 256G
ENGINE=NDB;
ALTER TABLESPACE ts1
ADD DATAFILE 'datafile2.dat'
INITIAL_SIZE 256G
ENGINE=NDB;
CREATE TABLE usertable (YCSB_KEY VARCHAR(255) PRIMARY KEY, field0 varbinary(4096))
TABLESPACE ts1 STORAGE DISK ENGINE = ndb;

The final example show how you can create a table with a specified number of table partitions. The default in RonDB is that we create 2 * number of data nodes. In managed RonDB we will always have 3 nodes setup for each node group. Thus if one has setup a benchmark in the managed RonDB and created 2 replicas and 2 data nodes, then actually we will create 2 * 3 table partitions.

In most applications with a fair amount of reads and many tables this will work perfectly fine. However in benchmarks like YCSB that hits a single and does a lot of write operations, the performance can be limited by that writes in one partition can only be handled by 1 ldm thread. So to create more write bandwidth one can create the table with more table partitions. In the example below we create a table with 16 partitions. This would be a good setting if we execute towards data nodes with 64 VCPUs and thus 16 ldm threads per data node.

Another manner to handle this is by using different tables for different YCSB clients.

CREATE TABLE usertable (YCSB_KEY VARCHAR(255) PRIMARY KEY, field0 varbinary(4096))
ENGINE = NDB PARTITIONS BY KEY() PARTITIONS 16;

Executing a YCSB benchmark using ClusterJ

Before executing the benchmarks it is necessary to edit the workload file, if one is executing Workload A then the file is found at workloads/workloada in the YCSB tree.

To use the ClusterJ client one need to set the following parameters:

rondb.connection.string=MGM_SERVER_IP:1186
rondb.schema=ycsb
rondb.fieldcount=1
rondb.fieldlength=4096
fieldnameprefix=field
threadcount=32

The first parameter sets the RonDB connection string that needs to point the RonDB management server in the cluster setup. Next one sets the database to be used by the client. We set the number of fields stored in the table and the size of the fields. The field names are named FIELD0, FIELD1 and so forth. It is possible to use a different field name base by setting fieldnameprefix.

We probably want to change the recordcount and operationcount as well. The recordcount sets the number of records to insert and to choose from when executing an operation. operationcount sets the number of operations to execute in one benchmark execution.

The threadcount is a parameter that specifies how many threads that YCSB should use in executing the benchmark. This is likely a parameter to add as command parameter since it will change in different runs.

Now to execute load phase where we fill the usertable with rows is performed using the following command:

./bin/ycsb load rondb -s -P workloads/workloada

Next we execute the actual benchmark command:

./bin/ycsb run rondb -s -P workloads/workloada

It is possible to set parameters using the -p command option when starting the ycsb binary instead as well. The -s parameter says that we want to have status updates during the execution of the benchmark.

Executing a YCSB benchmark using JDBC

There is a set of parameters to set in the workloads file before executing the benchmark. The reasoning above on threadcount holds true here as well that it is probably best to use it as a command parameter.

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://MYSQL_IP:3306/ycsb
db.user=root
db.passwd=passwd
threadcount=32
db.batchsize=1000
jdbc.fetchsize=10
jdbc.autocommit=true
jdbc.batchupdateapi=false

db.driver specifies using the MySQL JDBC driver.

db.url specifies where we find the MySQL Server, the hostname and its port (defaults to 3306). Also the database to connect is provided here. The MySQL JDBC supports load balancing, thus it is possible to specify a list of MySQL Servers in the URL. See MySQL documentation of JDBC driver for more details on this.

db.user and db.passwd is used to log in to the MySQL server.

Next is a set of parameters that can be set up for how JDBC is to perform batching and when to commit.

Finally when executing the benchmark we need to ensure that we have access to the JAR file of the MySQL JDBC driver. This can be downloaded from MySQL.

./bin/ycsb load rondb -s -P workloads/workloada -cp /path/to/mysql-connector.jar

Next we execute the actual benchmark command:

./bin/ycsb run rondb -s -P workloads/workloada -cp /path/to/mysql-connector.jar

DBT3

To setup DBT3 for execution requires a few preparatory steps.

• Create table data files

• Create tables and load data

• Run benchmark

Create DBT3 data files

The benchmark needs to load the DBT3 database, this consists of 9 tables that needs to be filled with data. When loading the data one can choose which scale factor to use. The command below creates table data files for the 9 tables with scale factor 10 (requires about 24 GByte of DataMemory). A scale factor of 10 adds about 10 GByte of user data, but there is also overhead of quite a lot of indexes on in particular the lineitem table.

The -v means that you get verbose output, -f means that you will overwrite any already existing table data files and finally -s 10 provides the scale factor use.

mkdir /home/mysql/benchmarks/dbt3_data/10
cd /home/mysql/benchmarks/dbt3_data/10
cp /srv/hops/mysql/bin/dbt3/dists.dss .
/srv/hops/mysql/bin/dbt3/dbgen -vf -s 10

The dbgen program requires a file called dists.dss located in the directory it runs in. It contains some commands to describe generation of column data. This file is included in the RonDB 21.04.3 releases and newer RonDB releases. Thus to use this description you need to have at least RonDB 21.04.3 installed. If you have an older version of RonDB this file is found in the dbt3-1.10 tarball in the src/dbgen directory.

Create and load DBT3 tables

Now to load the database we will use the benchmark scripts in a similar fashion as for Sysbench and DBT2. In order to do this we need to create a new benchmark directory for DBT3 benchmark. Thus we perform the below commands to create the directory dbt3_multi.

mkdir /home/mysql/benchmarks/dbt3_multi

Now we need to create a autobench.conf file. The below file contains the important information, you need to copy the SERVER_HOST information from e.g. the sysbench_multi benchmark configuration file. Similarly with the MYSQL_PASSWORD.

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

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

#########################################
#### 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 ####
##############################

DBT3_DATA_PATH="/home/mysql/benchmarks/dbt3_data/10"

Now after placing the above content into the file /home/mysql/benchmarks/dbt3_multi/autobench.conf we are ready to execute the command that will create the DBT3 tables and load them with the data.

cd /home/mysql/benchmarks
bench_run.sh --default-directory /home/mysql/benchmarks/dbt3_multi

Run DBT3 benchmark

Now we are ready to run the benchmark, it is possible to execute this benchmark in an automated manner. Personally I more or less consider the 22 queries in DBT3 as each a separate benchmark. Thus I often run only 1 query at a time using the MySQL client. The benchmark tarball contains a directory queries/mysql where all 22 queries have a separate file. Actually each query has 2 files, one for the automated query execution using qgen which is called e.g. 11.sql. The other file is called e.g. 11_2.sql and contains an example of the Q11 that can be immediately executed by using cut and paste into a MySQL client.

As an example we find the file 11.sql as:

-- @(#)11.sql   2.1.8.1
-- TPC-H/TPC-R Important Stock Identification Query (Q11)
-- Functional Query Definition
-- Approved February 1998
:b
:x
:o
select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
from
        partsupp,
        supplier,
        nation
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = ':1'
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * :2
                        from
                                partsupp,
                                supplier,
                                nation
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and n_name = ':1'
                )
order by
        value desc;
:e

In order to run this manually one need to replace the :1 and the :2 with e.g. GERMANY and 0.0001. Thus we get the query found in the file 11_2.sql:

select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
from
        partsupp,
        supplier,
        nation
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'GERMANY'
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * 0.0001
                        from
                                partsupp,
                                supplier,
                                nation
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and n_name = 'GERMANY'
                )
order by
        value desc;

This query can easily be cut and paste into the MySQL client. The MySQL client will report the time it takes to execute the query which is what one wants to know here.

By using EXPLAIN on the query one can quickly check if the queries use a query plan that is reasonable.

Using qgen to generate queries

It is possible the qgen program to automatically generate queries. So far we have been satisfied with the manual execution of DBT3.

Mixing benchmarks

One of the requirements on RonDB is to be able to combine massive amounts of OLTP transactions with a set of complex query executions. Thus to execute a combination of Sysbench, YCSB, DBT2 and DBT3 is an interesting manner to see how RonDB can handle mixed workloads. In particular combining complex queries in DBT3 with other OLTP benchmarks is interesting.