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

栏目: IT技术 · 发布时间: 2天前

来源: www.percona.com

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

本文转载自:https://www.percona.com/blog/2020/07/31/how-mysql-8-0-21-and-mariadb-10-5-4-perform-in-io-bound-scenarios-on-sata-ssd-and-nvme-storage/,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有。

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.


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

关注码农网公众号

关注我们,获取更多IT资讯^_^


为你推荐:

相关软件推荐:

查看所有标签

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

AI Algorithms, Data Structures, and Idioms in Prolog, Lisp, and

AI Algorithms, Data Structures, and Idioms in Prolog, Lisp, and

George F. Luger、William A Stubblefield / Addison Wesley / 2008-09-04 / USD 22.20

This book is designed for three primary purposes. The first is as a programming language component of a general class in Artificial Intelligence. From this viewpoint, the authors see as essential that......一起来看看 《AI Algorithms, Data Structures, and Idioms in Prolog, Lisp, and 》 这本书的介绍吧!

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

URL 编码/解码

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具