How MySQL and MariaDB Perform on NVMe Storage

mysql mariadb nvme

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:

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.

 

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.

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

Comments

Popular posts from this blog