How MySQL and MariaDB Perform on NVMe Storage

MySQL 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:

System | Supermicro; SYS-F619P2-RTN; v0123456789 (Other)
   Platform | Linux
    Release | Ubuntu 18.04.4 LTS (bionic)
     Kernel | 5.3.0-42-generic
Architecture | CPU = 64-bit, OS = 64-bit
  Threading | NPTL 2.27
    SELinux | No SELinux detected
Virtualized | No virtualization detected
# Processor ##################################################
 Processors | physical = 2, cores = 40, virtual = 80, hyperthreading = yes
     Models | 80xIntel(R) Xeon(R) Gold 6230 CPU @ 2.10GHz
     Caches | 80x28160 KB
# Memory #####################################################
      Total | 187.6G

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:

./tpcc.lua --mysql-host=127.0.0.1 --mysql-user=sbtest --mysql-password=sbtest --mysql-db=sbtest --time=10000 --threads=56 --report-interval=1 --tables=10 --scale=100 --use_fk=0 --mysql_table_options='DEFAULT CHARSET=utf8mb4' prepare

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.

 

 

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:

 

 

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.

Final Thoughts

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

by Vadim Tkachenko via Percona Database Performance Blog