How MySQL 8.0.21 and MariaDB 10.5.4 Perform in IO-Bound Scenarios on SATA SSD and NVMe Storage

MySQL 8.0.21 and MariaDB 10.5.4 Perform in IO-Bound ScenariosContinuing with the same topic and evaluating new versions of MariaDB and MySQL on Enterprise SSD Storage and How MySQL and MariaDB Perform on NVMe Storage, this time let’s take a look at how MySQL 8.0.21 and MariaDB 10.5.4 perform in IO-bound scenarios on both SATA SSD and NVMe storage.

To emulate the IO-bound scenario, I will use innodb_buffer_pool_size=25GB for the database in size 100GB, so there will be a competition for buffer_pool space (unlike in my previous post where I used innodb_buffer_pool_size=140GB, so pretty much the whole database was sitting in memory).

This scenario is quite complicated for databases, as there is a lot of going to serve application queries:

  • IO Reads of database pages from the storage into memory
  • Evicting buffer pool pages to free up the space to read pages
  • Writing dirty pages to the storage that we can evict them
  • Still looking to keep checkpoint age in line, however, this is less of the problem in this scenario than I presented in the first post

So this IO-bound scenario is a showcase on how well the database manages the processes described above.

Benchmark

To evaluate MariaDB and MySQL I will use sysbench-tpcc with 1000 Warehouses. The hardware I use is:

With the storage on INTEL SSDPE2KE032T8 (Intel® SSD DC P4610 Series, PCIe 3.1 x4, NVMe) and on SATA SSD INTEL SSDSC2KB960G8 (Intel Enterprise-grade SSD D3-S4510).

A short settings overview:

  • Data does not fit into memory (The datasize is ~100GB, memory on the server is 188GB, and we allocate 25GB for MySQL and MariaDB innodb_buffer_pool_size using O_DIRECT, so even though there is a lot of memory on the server, it is not used over the specified 25GB).
  • The workload on storage will be very read-write-intensive (reads will be done from the storage), with full ACID-compliant and data safe settings in MySQL and MariaDB.
  • For NVMe storage  innodb_io_capacity I will use 15000 and innodb_io_capacity_max = 20000 to utilize more throughput of NVMe storage.
  • For SATA SSD storage  innodb_io_capacity I will use 2000 and innodb_io_capacity_max = 4000 to utilize more throughput of NVMe storage.

The benchmark command line is:

This means that the benchmark will run for three hours with reporting throughput every 1 sec. I will use a three-hour time frame for a reason which will be apparent later from the results.

Results on SATA SSD

Let’s see what results I’ve got with this setup on SATA SSD:

MariaDB 10.5.4 Perform in IO-Bound Scenarios

I  appreciate even more the three hours runs. In this case, we can see some interesting internal dynamics for MariaDB, in that after 2500 sec there is a major drop in throughput.

Results on NVMe

 

MySQL 8.0.21 Perform in IO-Bound Scenarios

Disclaimer:

MariaDB 10.5.4 developers made a comment that there are performance fixes are coming in the next release, which may improve MariaDB performance.

The work to obtain the results with a runtime of three hours, to analyze and validate the anomalies as we see in MariaDB, takes weeks to finalize, so it is not a couple of hours to re-run and re-test a fix that might be available in a source code commit.

When the new release is available, I will re-evaluate the MariaDB performance. I prefer to work with the official releases and not compiling from the source code drops.

Final Thoughts

From the results above, MySQL clearly handles IO-bound scenarios better. The anomalies with MariaDB and performance improvements are expected in the next release, but it has to be validated.

Share this post

Comments (3)

  • Frederick Álvarez Reply

    can you run the same test against Oracle 19?

    August 2, 2020 at 10:08 am
  • Marko Mäkelä Reply

    Vadim, unfortunately, my effort to make the InnoDB buf_pool simpler and more performant in the MariaDB 10.5.4 release caused a couple of performance regressions whose fixes will be part of the upcoming 10.5.5 release: https://jira.mariadb.org/browse/MDEV-23017 and https://jira.mariadb.org/browse/MDEV-23369.

    The MariaDB performance drop is an interesting anomaly. Could you please share some more details about it, such as some “perf report” output for a “perf record” that covers a badly performing part of the run? Does it occur with the 10.5 branch as of today? If you are not willing to spend time on a development snapshot, I would greatly appreciate it if you could repeat the run on the MariaDB 10.5.5 release and then share more details.

    August 3, 2020 at 7:24 am
  • Marko Mäkelä Reply

    We believe that we have repeated the problem that affects I/O bound workloads when the buffer pool consists almost entirely of dirty pages. We have filed https://jira.mariadb.org/browse/MDEV-23399 to track it.

    August 4, 2020 at 9:01 am

Leave a Reply