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