How MySQL 8.0.21 and MariaDB 10.5.4 Perform in IO-Bound Scenarios on SATA SSD and NVMe Storage

栏目: IT技术 · 发布时间: 4年前

内容简介: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

How MySQL 8.0.21 and MariaDB 10.5.4 Perform in IO-Bound Scenarios on SATA SSD and NVMe Storage 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:

How MySQL 8.0.21 and MariaDB 10.5.4 Perform in IO-Bound Scenarios on SATA SSD and NVMe Storage

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

How MySQL 8.0.21 and MariaDB 10.5.4 Perform in IO-Bound Scenarios on SATA SSD and NVMe Storage

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语言

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语言》 这本书的介绍吧!

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

URL 编码/解码
URL 编码/解码

URL 编码/解码