Benchmark RonDB cluster#
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.
The benchmarks we focus on are the following
Sysbench 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.
DBT2 is an open source variant of the TPC-C benchmark. It represents a bit more complex application. It also uses the MySQL C client to issue SQL statements to RonDB. The SQL statements are using stored procedures.
DBT3 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.
flexAsynch 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 is the native Java RonDB API. The benchmark focuses on performing simple key lookup operations towards RonDB.
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 RonDB concurrently.
Currently we set up 4 different preconfigured RonDB benchmarks. 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
Executing a RonDB benchmark#
In this chapter we assume that you have created a RonDB cluster as documented in the part on creating a RonDB cluster. After installing a RonDB cluster the set up 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 220.127.116.11. In this case the following commands should be executed to run a benchmark in RonDB using AWS.
ssh firstname.lastname@example.org sudo su - mysql cd benchmarks bench_run.sh --default-directory /home/mysql/benchmarks/sysbench_multi
If you have set up the cluster using the cloud script on Azure or GCP then replace the ssh command with:
The first command logs in to the VM. The second command logs in to the mysql user in this VM. The third command moves the benchmark directory. The fourth command executes the Sysbench benchmark in multiple instances.
This is it, in the below chapters we will describe how to see the results of the benchmarks and what changes we can do to the benchmarks.
Configuring 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, this file describes the benchmark configuration.
Below is the configuration used to run Sysbench Standard OLTP RW benchmark. This is the configuration file you will find under /home/mysql/benchmarks/sysbench_multi/autobench.conf.
The MYSQL_BIN_INSTALL_DIR parameter specifies where the RonDB binary installation is placed.
BENCHMARK_TO_RUN specifies the bencmark to run, can be sysbench, dbt2, dbt3 or flexAsynch currently.
SERVER_HOST 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 is the generated password that makes it possible to access the MySQL Servers using the user mysql using this password. It is also filled in by the installation process.
SYSBENCH_TEST is the type of Sysbench benchmark, mainly used are oltp_rw and oltp_ro.
SYSBENCH_INSTANCES is the number of instances of Sysbench running. It should be the same or smaller than the number of MySQL Servers.
THREAD_COUNTS_TO_RUN is the number of threads to use 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 is the time each benchmark will execute for one set of threads.
# # 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"
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.
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:
Here is an example of the output this can produce:
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 email@example.com #ssh 18.104.22.168 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 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
One of the requirements on RonDB is to be able to combine massive amounts of OLTP transactions with a set of complex query executions.