• MySQL / mysqldump

MySQL with NVMe SSD is slower than expected

We have evaluated new server hardware and started some benchmarks of mysqldump import. This test compared two platforms with different components from a performance point of view. Surprisingly we saw a deviation of up to 450%.


Hard- and software used for the test

A MySQL dump with about 15 million records should be imported, using these components:


Software

  • CentOS 8.3, 64 Bit
  • Percona Server 8.0.22
  • MySQL dump with about 15 million records

Server1

  • AMD Ryzen 7 3700X 8-Core Processor, 3.6 GHZ
  • 64 GB ECC RAM
  • SAMSUNG MZVLB1T0HBLR, 1TB, sequential write 3000MB/s, random IOPS write 500000
    Samsung MZVLB1T0HBLR

Server2

  • Intel(R) Xeon(R) E-2288G CPU, 3.70GHz
  • 64 GB ECC RAM
  • SAMSUNG MZQLB960HAJR, 960GB, sequential write 1050MB/s, random IOPS write 40000
    Samsung MZQLB960HAJR
    The official samsung.com link did not provide any data at the time of writing, so we used another commercial source: Samsung MZQLB960HAJR - alternative

Results

Server 1 (AMD, MZVLB1T0HBLR)


[root@server1 ~]# date; zcat test.sql.gz | mysql -psecret test; date
Thu Mar 25 19:45:14 CET 2021
Thu Mar 25 20:08:33 CET 2021
Delta:     23:19 minutes

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
0.80    0.00    0.27    4.74    0.00   94.19

Device            r/s     w/s     rkB/s     wkB/s   rrqm/s   wrqm/s  %rrqm  %wrqm r_await w_await aqu-sz rareq-sz wareq-sz  svctm  %util
nvme0n1          0.00 1431.60      0.00  31421.85     0.00   290.07   0.00  16.85    0.00    0.40   0.26     0.00    21.95   0.50  71.40
nvme1n1          0.00 1431.57      0.00  31421.72     0.00   290.07   0.00  16.85    0.00    1.00   1.15     0.00    21.95   0.70  99.68

Duration: 23:19 minutes

Server 2 (Intel, MZQLB960HAJR)

[root@server2 ~]# date; zcat test.sql.gz | mysql -psecret test; date
Thu Mar 25 19:00:01 UTC 2021
Thu Mar 25 19:04:44 UTC 2021
Delta:     04:43 minutes

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
6.32    0.00    0.87    0.75    0.00   92.05

Device            r/s     w/s     rkB/s     wkB/s   rrqm/s   wrqm/s  %rrqm  %wrqm r_await w_await aqu-sz rareq-sz wareq-sz  svctm  %util
nvme0n1          0.00 4319.53      0.00 140428.80     0.00   485.03   0.00  10.10    0.00    0.08   0.02     0.00    32.51   0.02  10.28
nvme1n1          0.00 4319.53      0.00 140428.80     0.00   485.03   0.00  10.10    0.00    0.08   0.02     0.00    32.51   0.02  10.29

Duration: 04:43 minutes

Evaluation and tuning

We have to note that this test is a special case: a MySQL dump import is single-threaded and cannot use current multicore-cpus or NVMe disks to full capacity. However we still can draw conclusions as the deviations must be hardware-based due to identical software and datasets.

According to specifications, Samsungs MZVLB1T0HBLR NVMe in Server 1 should be a lot faster with random write IO than the MZQLB960HAJR in Server 2. Surprisingly we saw a 4.5x increase in processing time.

We did some other tests like checking smart values, starting self-tests and benchmarking to eliminate any faulty disks, but those were all working fine and also delivered more than two gigabytes per second sequential write. With these results we changed MySQL configuration parameteres and raised innodb_log_file_size from 128MB to 5GB (with a group of two logs, 10GB in total). Then we started the test again:

[root@server1 ~]# date; zcat test.sql.gz | mysql -psecret test; date
Fri Mar 26 16:57:12 CET 2021
Fri Mar 26 17:03:45 CET 2021
Delta:     06:33

Duration: 06:33 minutes

Summary

All tests suggest that those NVMe SSDs have been developed for different purposes: Server 1's NVMe MZVLB1T0HBLR for clients/desktops and Server 2's MZQLB960HAJR for datacenters. The controller or firmware of the latter seems to cope better with the requirements of database recovery.

There is a related bugreport concerning MySQL 8.0 and a Samsung 970 Pro NVMe that you might find interesting:
Bug 93734 - MySQL 8.0 is 36 times slower than MySQL 5.7

Data protection info | Help us to optimize this site and allow cookies please - insights will not be passed on to third parties.
Decline Accept