How MySQL and MariaDB Perform on NVMe Storage

mysql mariadb nvmeMySQL and MariaDB on NVMe Storage  – The Great Equalizer

Continuing with the checkpointing topic I restarted a month ago with MongoDB, followed with PostgreSQL, and then with MySQL and MariaDB on Enterprise SSD Storage, this time let’s take a look at how MySQL and MariaDB perform on NVMe storage.

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). The storage is capable of 222000 IOPS in random writes and 638000 IOPS in random reads.

A short settings overview:

  • Data will totally fit into memory (The datasize is ~100GB, memory on the server is 188GB, and we allocate 140GB for MySQL and MariaDB innodb_buffer_pool_size.)
  • The workload on storage will be mostly write-intensive (reads will be done from memory), with full ACID-compliant and data safe settings in MySQL and MariaDB.
  • For innodb_io_capacity I will use 15000 and innodb_io_capacity_max = 20000 to utilize more throughput of NVMe storage.

The benchmark command line is:

This means that the benchmark will run for three hours, 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

Let’s see what results I’ve gotten with this setup.

 

MySQL NVMe Storage

 

I have started to appreciate long, three hours runs. In this case, we can see some interesting internal dynamics for MariaDB, with stabilization after 2500 sec. The U-shaped recovery after the warm-up is something I have never seen before.

The MySQL line is not totally straight either, so to see the trend, let’s draw 1-minute moving average lines:

 

MariaDB NVMe Storage

 

On this chart, we can see that MySQL still has periodical dips, although not as significant as in the SATA SSD case. That’s why I see NVMe storage as very forgiving in the sense that is able to accommodate huge IO spikes without major performance impact.

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

Note: Here’s my test of MariaDB 10.5.5.

Final Thoughts

NMVe storage is a great choice if you are looking to improve database performance and if you are able to accommodate it.

Share this post

Comments (13)

  • Wilson Hauck Reply

    With this capability on NVME,
    With the storage on INTEL SSDPE2KE032T8 (Intel® SSD DC P4610 Series, PCIe 3.1 x4, NVMe). The storage is capable of 222000 IOPS in random writes and 638000 IOPS in random reads.
    Why did you choose to use in the configuration,
    For innodb_io_capacity I will use 15000 and innodb_io_capacity_max = 20000 to utilize more throughput of NVMe storage.
    Did you leave 90%+ of the capability on the table – unused?
    Thanks for the details.

    July 30, 2020 at 12:14 pm
    • Vadim Tkachenko Reply

      Wilson,

      I would say innodb_io_capacity is one of the most misleading and confusing variables in InnoDB.
      The way to think about it is not in absolute IOPS, but what is relative performance of your storage comparing to a single hard drive. Even with this, the best way to find appropriate value for this variable is to perform experiments.
      From my experience, for SATA SSD I use 2000 and for NVMe storage I use 15000

      July 30, 2020 at 12:51 pm
  • Vladislav Vaintroub Reply

    Why to spend hours on testing on known slightly unfortunate version, and draw conclusions which could be invalidated by the very next release of MariaDB. I mean you’ve been informed about bug that slipped into 10.5.4 , so maybe it makes sense to git clone https://github.com/mariadb/server, compile, and inform the world that those fixes do not change anything, or surprise yourself first, and the world second, about how much better MariaDB turned out to be.

    July 30, 2020 at 1:46 pm
    • Vadim Tkachenko Reply

      Vladislav,

      I already got all results and now I share them.
      It would be good if you put into the release notes that the GA release should not be used, and then I would not spend time on it.

      July 30, 2020 at 1:49 pm
  • Vladislav Vaintroub Reply

    Why the GA release should not be used? It should be used alright, and a performance bug can slip in, like any other bug, and it is documented, and was timely fixed, even without you filing any bugs. It is not a showstopper. If you asked whether it is good for benchmarking, we’d tell you a specific MDEV, but you did not ask.

    July 30, 2020 at 3:32 pm
    • Vadim Tkachenko Reply

      Vladislav,

      Just to be clear. I’ve sent a request to review the result to MariaDB Engineering Lead a week before publishing the result, and I’ve received no response. The only way to get a feedback from MariaDB is to publish results on our blog.

      July 30, 2020 at 3:45 pm
      • Sergei Golubchik Reply

        Who did the request to? Just curious

        August 2, 2020 at 5:15 pm
  • Vladislav Vaintroub Reply

    You can go official channels and higher-ups, but they (not sure who you meant, Serg, Max, or Rasmus) might be either out sick, be on vacation, or (surprise) busy, might not have overview over all the Innodb bugs, or time to spend on investigation of ad-hoc email requests.

    As for the ways to communicate, Zulip is probably the best one. You’ll find Innodb lead in there, who will be pleased to chat with you on your findings. A topic that can be special interest for you is here https://mariadb.zulipchat.com/#narrow/stream/118759-general/topic/InnoDB.20scalability.
    If you are not up to chatting, we still have a mailing list, and we even have JIRA.

    Nevermind that, so now, with the feedback you got, what will you do?

    July 30, 2020 at 5:01 pm
    • Jamie Donovan Reply

      I can see that things are a bit touchy regarding the issues in MariaDb. I for one am interested to see how the results compare with known bugs fixed. If it’s a design issue, it’s true that one can’t expect to wait. But I’d love to see an update on the benchmark with bugfixes while this is fresh in our memories. Thanks for your hard work!

      July 31, 2020 at 4:07 pm
      • MySQLonARM (@mysqlonarm) Reply

        Jamie,

        You should surely wait for the official result from Vadim (post 10.5.5 release) but since I was trying something similar but with sysbench and then saw this good blog post from Vadim that tempted me to try things (to investigate some other issue) and this is what I see.

        Avg tps:
        10.5.4 (transactions: 122222773 (12222.26 per sec.))
        10.5-trunk ( transactions: 142004485 (14200.42 per sec.))

        Link to graph: https://mariadb.zulipchat.com/#narrow/stream/118759-general/topic/InnoDB.20scalability

        (Seems like I can’t attach image in graph).

        August 3, 2020 at 9:57 am
  • Michael Widenius Reply

    Is the MySQL and MariaDB configurations the same as used in your previous benchmarks that you refer to in this blog post?
    In other words, the same as in pointed to in https://github.com/Percona-Lab-results/2020-07-MySQL-MariaDB/blob/master/notebook/PostgreSQL-MySQL.ipynb ?

    September 23, 2020 at 9:17 am
    • vadimtk Reply

      The configs are the same as in my previous benchmark or had only some small cosmetic changes.

      September 23, 2020 at 9:20 am

Leave a Reply