MariaDB no longer meeting your needs?
Migrate to Percona software for MySQL – an open source,
production-ready, and enterprise-grade MySQL alternative.
MySQL and MariaDB on NVMe Storage – The Great EqualizerContinuing 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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:
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 |
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.
Note: Here’s my test of MariaDB 10.5.5.
Final Thoughts
NMVe storage is a great choice if you are looking to improve database performance and if you are able to accommodate it.
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!
Jamie,
You should surely wait for the official result from Vadim (post 10.5.5 release) but since I was trying something similar but with sysbench and then saw this good blog post from Vadim that tempted me to try things (to investigate some other issue) and this is what I see.
Avg tps:
10.5.4 (transactions: 122222773 (12222.26 per sec.))
10.5-trunk ( transactions: 142004485 (14200.42 per sec.))
Link to graph: https://mariadb.zulipchat.com/#narrow/stream/118759-general/topic/InnoDB.20scalability
(Seems like I can’t attach image in graph).
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.