内容简介:To emulate the IO-bound scenario, I will use innodb_buffer_pool_size=25GB for the database in size 100GB, so there will be a competition for buffer_pool space (unlike in my previous post where I used innodb_buffer_pool_size=140GB, so pretty much the whole
Continuing with the same topic and evaluating new versions of MariaDB and MySQL on Enterprise SSD Storage and How MySQL and MariaDB Perform on NVMe Storage , this time let’s take a look at how MySQL 8.0.21 and MariaDB 10.5.4 perform in IO-bound scenarios on both SATA SSD and NVMe storage.
To emulate the IO-bound scenario, I will use innodb_buffer_pool_size=25GB for the database in size 100GB, so there will be a competition for buffer_pool space (unlike in my previous post where I used innodb_buffer_pool_size=140GB, so pretty much the whole database was sitting in memory).
This scenario is quite complicated for databases, as there is a lot of going to serve application queries:
- IO Reads of database pages from the storage into memory
- Evicting buffer pool pages to free up the space to read pages
- Writing dirty pages to the storage that we can evict them
- Still looking to keep checkpoint age in line, however, this is less of the problem in this scenario than I presented in the first post
So this IO-bound scenario is a showcase on how well the database manages the processes described above.
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) and on SATA SSD INTEL SSDSC2KB960G8 (Intel Enterprise-grade SSD D3-S4510).
A short settings overview:
- Data does not fit into memory (The datasize is ~100GB, memory on the server is 188GB, and we allocate 25GB for MySQL and MariaDB innodb_buffer_pool_size using O_DIRECT, so even though there is a lot of memory on the server, it is not used over the specified 25GB).
- The workload on storage will be very read-write-intensive (reads will be done from the storage), with full ACID-compliant and data safe settings in MySQL and MariaDB.
- For NVMe storage innodb_io_capacity I will use 15000 and innodb_io_capacity_max = 20000 to utilize more throughput of NVMe storage.
- For SATA SSD storage innodb_io_capacity I will use 2000 and innodb_io_capacity_max = 4000 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 with 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 on SATA SSD
Let’s see what results I’ve got with this setup on SATA SSD:
I appreciate even more the three hours runs. In this case, we can see some interesting internal dynamics for MariaDB, in that after 2500 sec there is a major drop in throughput.
Results on NVMe
Disclaimer:
MariaDB 10.5.4 developers made acommentthat there are performance fixes are coming in the next release, which may improve MariaDB performance.
The work to obtain the results with a runtime of three hours, to analyze and validate the anomalies as we see in MariaDB, takes weeks to finalize, so it is not a couple of hours to re-run and re-test a fix that might be available in a source code commit.
When the new release is available, I will re-evaluate the MariaDB performance. I prefer to work with the official releases and not compiling from the source code drops.
Final Thoughts
From the results above, MySQL clearly handles IO-bound scenarios better. The anomalies with MariaDB and performance improvements are expected in the next release, but it has to be validated.
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
21天学通C语言
(美国)琼斯(Bradley L.Jones) (美国)埃特肯(Peter Aitken) / 信达工作室 / 人民邮电出版社 / 2012-8 / 69.00元
《21天学通C语言(第6版•修订版)》是初学者学习C语言的经典教程。本版按最新的标准(ISO∕IEC:9899-1999),以循序渐进的方式介绍了C语言编程方面知识,并提供了丰富的实例和大量的练习。通过学习实例,并将所学的知识用于完成练习,读者将逐步了解、熟悉并精通C语言。《21天学通C语言(第6版•修订版)》包括四周的课程。第一周的课程介绍了C语言程序的基本元素,包括变量、常量、语句、表达式、函......一起来看看 《21天学通C语言》 这本书的介绍吧!