内容简介: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.
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
从莎草纸到互联网:社交媒体2000年
[英]汤姆·斯丹迪奇 / 林华 / 中信出版社 / 2015-12 / 58.00元
【内容简介】 社交媒体其实并不是什么新鲜的东西。从西塞罗和其他古罗马政治家用来交换信息的莎草纸信,到宗教改革、美国革命、法国大革命期间印制的宣传小册子,过去人类跟同伴交流信息的方式依然影响着现代社会。在报纸、广播和电视在散播信息上面统治了几十年后,互联网的出现使社交媒体重新变成人们与朋友分享信息的有力工具,并推动公共讨论走向一个新的模式。 汤姆•斯丹迪奇在书中提醒我们历史上的社交网络其......一起来看看 《从莎草纸到互联网:社交媒体2000年》 这本书的介绍吧!