Checkpointing in MySQL and MariaDB

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

内容简介:It was actually some time when I last looked into MySQL in this regard, so I am curious how new MySQL versions (8.0.21 in this case) would perform. And while we’re at it, why don’t we compare this with the new MariaDB 10.5.4, which comes with interesting c

Checkpointing in MySQL and MariaDB Continuing with the checkpointing topic I restarted a month agowith MongoDB and followedwith PostgreSQL, I wanted to check how MySQL and MariaDB perform.

It was actually some time when I last looked into MySQL in this regard, so I am curious how new MySQL versions (8.0.21 in this case) would perform. And while we’re at it, why don’t we compare this with the new MariaDB 10.5.4, which comes with interesting changes in InnoDB subsystems.

Benchmark

To evaluate MariaDB and MySQL,  I will use an identical setup to when I evaluated PostgreSQL: using 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 SATA SSD INTEL SSDSC2KB960G8 (Intel Enterprise-grade SSD D3-S4510).

The 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.

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 one 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 got with this setup:

Checkpointing in MySQL and MariaDB

Wow.

I really did not expect such wave patterns for MySQL, and that’s why I extended the run time from 1 hour to 3 hours to see if MySQL ever stabilizes from wave pattern to a more stable line. And actually, it did, by the end of the 3rd hour, which is quite too long in my opinion.

MariaDB shows stabilization much sooner, but the variation in the throughput is higher.

The averaged results for MariaDB by the end of the 3rd hour are 4900 tps, and for MySQL is 7500 tps, so there, MySQL 8.0.21 outperforms MariaDB by some margin (about 1.5x times).

If we draw the boxplots for the last 1000 sec of the runs, it is:

Checkpointing in MySQL and MariaDB

Disclaimer:

MariaDB 10.5.4 comes with a lot of changes in the InnoDB subsystem and I did not investigate what might be a cause of such difference. I’ve asked MariaDB for the comments.

Final Thoughts

Checkpointing (in providing minimal performance variation) continues to be a hard problem for databases. MariaDB is more stable, but the average performance is not great. For MySQL, I would like to see a way to stabilize the wave line faster than within 3 hours.

If you are interested in the raw results and notebooks, it is available here:

https://github.com/Percona-Lab-results/2020-07-MySQL-MariaDB


以上所述就是小编给大家介绍的《Checkpointing in MySQL and MariaDB》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

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

如何变得有思想

如何变得有思想

阮一峰 / 人民邮电出版社 / 2014-12-2 / 49.00元

本书为阮一峰博客选集,囊括了作者对各种问题的思考,围绕的主题是试图理解这个世界。本书内容非常广泛,涉及观点、文学、历史、科技、影视等方面。作者在书中对具有深刻意义的文字进行摘录,并且在思索后提出自己独特的观点。书后附有阮一峰诗集。 本书适合喜欢独立思考、热爱读书的读者,对于广大读者具有一定的启发作用。一起来看看 《如何变得有思想》 这本书的介绍吧!

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

在线进制转换器
在线进制转换器

各进制数互转换器

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具