Skip to content

MySQL Results#

RonDB has a number of different interfaces. One of the prominent ones is the MySQL Server that provides an SQL interface towards RonDB. As we have seen in the benchmarks on the REST API and Rondis we have very good results when RonDB can combine threading and batching.

It is a lot more difficult to achieve batching in the SQL interface. Actually the NDB team spent 10 years figuring out ways to achieve the batching required to get good performance using SQL.

However SQL is more useful for queries that are more complex in nature. RonDB has its greatest performance strength in batching key lookups. However RonDB still has amazingly low latency even through the SQL interface. It has also very decent ability to send very large data sets over the network. Actually the Sysbench OLTP RW sends 1 GByte of data from RonDB data node to the MySQL Server in the results reported below.

Another benefit of RonDB is that scales very well, thus one can simply add more MySQL Servers to achieve higher throughput. In this blog post we discuss the scalability of RonDB using up to 12 MySQL Servers. Similarly in this blog post we discuss how RonDB behaves when running Sysbench.

There is even a a blog post describing how one can do a bit of batching using SQL in Sysbench to achieve 5.5M key lookups per second.

There is a post on the RonDB website providing a detailed benchmark study using YCSB (Yahoo Cloud Serving Benchmark). This post is unique in the sense that it compares a number of different benchmarks with normal operation and compares it to how it behaves when a node fails and how the node recovery affects the throughput and latency. In addition it compares the behaviour of in-memory tables with disk tables.

In order to understand Sysbench OLTP benchmarks one needs to understand the what queries that Sysbench uses. A traditional Sysbench test is executing 20 SQL queries as a single transaction. There are 10 Point queries performing single key lookup queries, next there are 4 write queries (1 Inser, 2 Update and 1 Delete), next there are 4 scan queries that return 100 rows within a range, finally there is a BEGIN and a COMMIT query.

Thus Sysbench is putting the transaction system into action and not only handling single row interactions. However most of the CPU usage in Sysbench is spent on handling the 4 scan queries. These 4 queries return 100 rows each to the MySQL Server and this means around 50-60 kByte of data sent on the network. It also means that the scanning needs to be efficient in quickly moving from row to row. RonDB is slightly faster than InnoDB in scanning rows. Thus RonDB behaves very well also in Sysbench tests.

Sysbench OLTP RW Results#

Executing a Sysbench test is very simple. The following command will execute a Sysbench on a single machine using the MTR framework. This test will run through the test using a number of different number of threads.

./mtr --suite=ndbcrunch sb_oltp_read_write

In our case we made it a little bit more complex by using CPU binding. In our case we used the following settings (CPU 16-31 is the power efficient ones and 0-15 is the performance CPUs).

[cluster_config.ndbd.1.crunch]
cpubind=16-20

[cluster_config.ndbd.2.crunch]
cpubind=21-24

[mysqld.1.crunch]
cpubind=0-7

[mysqld.2.crunch]
cpubind=8-15

[mysqltest]
cpubind=26-31

To run with this one uses the following command from the mysql-test in the binary distribution.

./mtr --suite=ndbcrunch --defaults-extra-file=suite/ndbcrunch/cpubind.cnf sb_oltp_read_write

We also made a minor change in the my.cnf file by adding a more aggressive SpinMethod and removed NumCPUs since locking to CPUs will make the number of CPUs and which CPUs to use automated.

SpinMethod=DatabaseMachineSpinning
#NumCPUs=6

Ok so on to results (TPS = Transactions per second):

Benchmark TPS Average latency 95% latency Threads CPU
OLTP RW 833/s 1.21 ms 1.30 ms 1 41%
OLTP RW 1300/s 1.54 ms 2.03 ms 2 100%
OLTP RW 2434/s 1.64 ms 2.22 ms 4 190%
OLTP RW 4142/s 1.93 ms 2.57 ms 8 350%
OLTP RW 6605/s 2.42 ms 3.07 ms 16 620%
OLTP RW 9678/s 3.33 ms 4.10 ms 32 920%
OLTP RW 12267/s 5.21 ms 6.67 ms 64 1190%
OLTP RW 13041/s 9.81 ms 14.46 ms 128 1370%
OLTP RW 11995/s 21.3 ms 41.85 ms 256 1380%

So from these numbers one case that the latency is very low, less than 65 microseconds per SQL query, and remember that some queries handles 100 rows and some of them are write queries. So latency is very low.

One can also see that the MySQL Server uses a fair amount of CPU, so for this type of applications one needs a considerable amount of CPUs on the MySQL Server and on the application side.

Sysbench OLTP Write Only Results#

In this benchmark we only kept the write queries and BEGIN and COMMIT. One can see that the base latency of write queries is below 100 microseconds even when performing a distributed two-phase commit transaction.

Benchmark TPS Average latency 95% latency Threads CPU
OLTP W 2579/s 0.39 ms 0.43 ms 1 20%
OLTP W 4304/s 0.46 ms 0.57 ms 2 58%
OLTP W 7642/s 0.52 ms 0.68 ms 4 105%
OLTP W 12717/s 0.63 ms 0.84 ms 8 200%
OLTP W 20753/s 0.77 ms 1.04 ms 16 350%
OLTP W 33372/s 0.96 ms 1.27 ms 32 543%
OLTP W 46613/s 1.37 ms 2.03 ms 64 725%
OLTP W 52691/s 2.43 ms 4.82 ms 128 840%
OLTP W 55073/s 4.64 ms 11.24 ms 256 960%

From the numbers we can see that in this test the RonDB data node became the bottleneck, so one could easily increase the number of CPUs used here to get higher numbers.

Sysbench OLTP Read Only Results#

In this we remove the write queries and execute only the read queries. We note that the majority of the latency comes from these queries and we also note that they use considerably more CPU in the MySQL servers. This is mainly due to the scan queries. The 10 Point queries make up very little part of this load.

Benchmark TPS Average latency 95% latency Threads CPU
OLTP R 1251/s 0.80 ms 0.84 ms 1 51%
OLTP R 1882/s 1.06 ms 1.47 ms 2 118%
OLTP R 3540/s 1.13 ms 1.61 ms 4 230%
OLTP R 6110/s 1.31 ms 1.76 ms 8 430%
OLTP R 9848/s 1.62 ms 2.03 ms 16 735%
OLTP R 14429/s 2.22 ms 2.71 ms 32 1120%
OLTP R 16948/s 3.77 ms 4.91 ms 64 1350%
OLTP R 17181/s 7.45 ms 11.04 ms 128 1450%
OLTP R 15639/s 16.36 ms 31.37 ms 256 1500%

Sysbench OLTP Point Select Results#

This benchmark only runs single key lookup using SQL. Thus we can compare the cost of issuing those through MySQL with those using RonDB REST API server.

Benchmark TPS Average latency 95% latency Threads CPU
OLTP P 25568/s 0.04 ms 0.04 ms 1 43%
OLTP P 40913/s 0.05 ms 0.07 ms 2 115%
OLTP P 76666/s 0.05 ms 0.07 ms 4 220%
OLTP P 137118/s 0.06 ms 0.09 ms 8 420%
OLTP P 238947/s 0.07 ms 0.10 ms 16 740%
OLTP P 397695/s 0.08 ms 0.12 ms 32 1160%
OLTP P 505836/s 0.13 ms 0.23 ms 64 1360%
OLTP P 565536/s 0.23 ms 0.44 ms 128 1530%
OLTP P 563731/s 0.45 ms 0.84 ms 256 1730%

As the numbers the MySQL Server scales very nicely all the way up 400k Point queries. After that the MySQL server becomes overloaded and latency grows.

Compared to the REST API server the MySQL server consumes a bit more CPU, the latency is similar. So for key lookups the REST API server is preferrable and even more so when considering batches of key lookups as often happens in AI applications.

We can also see that the 10 Point queries consume about a third of the Read Only queries in the MySQL server compared to the 4 scan queries.