Migrate to Percona software for MySQL – an open source, production-ready, and enterprise-grade MySQL alternative.
MySQL and MariaDB on NVMe Storage – The Great Equalizer
Continuing the checkpointing series—previously covering MongoDB, PostgreSQL, and MySQL and MariaDB on Enterprise SSD Storage—this post looks at performance on NVMe storage.
To evaluate MariaDB and MySQL, sysbench-tpcc with 1000 warehouses was used.
|
1 2 3 4 5 6 7 |
System | Supermicro SYS-F619P2-RTN Platform | Linux Release | Ubuntu 18.04.4 LTS Kernel | 5.3.0-42-generic Architecture | 64-bit Processors | physical = 2, cores = 40, virtual = 80 Memory | 187.6G |
Storage: Intel SSD DC P4610 NVMe (up to ~222k write IOPS, ~638k read IOPS).
Benchmark command:
|
1 |
./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 |
The benchmark runs for ~3 hours, reporting every second.
Long-duration runs reveal internal behavior. MariaDB stabilizes after ~2500 seconds, showing a U-shaped recovery pattern after warm-up.
MySQL also shows variability. A 1-minute moving average clarifies trends:
MySQL still shows periodic dips, but far less severe than on SATA SSD. NVMe appears more tolerant of I/O spikes.
Disclaimer: MariaDB 10.5.4 developers indicated upcoming performance fixes in later releases.
See also: MariaDB 10.5.5 performance improvements.
NVMe storage is a strong choice for improving database performance, especially when handling high I/O workloads.
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.
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
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.
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.
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.
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.
Who did the request to? Just curious
Serg,
I sent you a private email.
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?
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!
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 ?
The configs are the same as in my previous benchmark or had only some small cosmetic changes.
Vadim, other than the model of the drives and the total IOPS capacity, you don’t mention the storage config. Is it RAID 0, 1, or what? Independent disks? I repeated your test on my own hardware, but it was very hard to reproduce your results. My server is 48 cores with 1TB RAM and 6 x Enterprise NVME drives. I managed to get 10,000 TPS for a while when I was using just 1 drive, but all of my other tests (with various RAID setups) yielded much less. Also, I never got a steady state. The performance with 1 drive started at about 11,000 TPS but steadily declined with time. By the end of the 3 hours, TPS was down to about 9500. With RAIDZ or LVM RAID5, TPS was around 4-6K.