MariaDB 和 GreatSQL 性能差异背后的真相

MariaDB 和 GreatSQL 性能差异背后的真相

前言

最近项目上遇到了两次 MariaDB 和 GreatSQL 的对比,GreatSQL受到客户质疑,最后经过排查抓到性能差异背后的真相。基于此做个分享。

版本

MariaDB版本:10.3.39 该版本为麒麟V10 yum安装

GreatSQL版本:GreatSQL-8-0-32-25

问题一:MariaDB和GreatSQL使用sysbench压测性能相差100倍

某天某客户反馈他们的sysbench压测结果,MariaDB 和 GreatSQL 压测性能相差100倍。

信息收集

架构:均为单机

版本如上文版本所示

配置文件:MariaDB yum安装后/etc/my.cnf 未曾更改

GreatSQL配置文件为GreatSQL用户手册中的配置文件链接如下:

https://GreatSQL.cn/docs/8.0.32-25/3-quick-start/3-4-quick-start-with-cnf.html

两者在同一台机器上轮流运行(即开启MariaDB时关闭GreatSQL,开启GreatSQL时关闭MariaDB)

ps:没收集硬件信息是因为都是运行在麒麟V10上,相同规格硬件运行,这部分已经排除影响。

思考

遇到这么夸张的性能差距,第一反应是innodb_buffer_pool,binlog开关,sync_binlog 和 innodb_flush_log_at_trx_commit这几个和性能关系较大的参数

复现测试

MariaDB 10.3.39 安装

$ yum -y install MariaDB
$ yum -y install MariaDB.server
 #启动MariaDB
$ systemctl start MariaDB
 #查看MariaDB的状态
$ systemctl status MariaDB

查看相关参数

# 登录,初始化首次登录MariaDB的密码为空
$ mysql -uroot -p -S /var/lib/mysql/mysql.sock

#查看binlog
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.003 sec)

# 查看sync_binlog 
MariaDB [(none)]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.002 sec)

# 查看innodb_flush_log_at_trx_commit
MariaDB [(none)]> show variables like '%trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.001 sec)

查到这里其实问题已经明朗。GreatSQL用户手册中配置为binlog开启,sync_binlog 和 innodb_flush_log_at_trx_commit均设置为1。

GreatSQL 安装

参考用户手册进行安装

https://GreatSQL.cn/docs/8.0.32-25/3-quick-start/3-2-quick-start-with-tarball.html

sysbench测试相关说明

针对这个情况使用sysbench在相同情况下做了对比测试:

innodb_buffer_pool大小设置一致,测试组分别为:

MariaDB 和 GreatSQL 均关闭binlog,sync_binlog 设置为0;

MariaDB 和 GreatSQL 均开启binlog,sync_binlog 设置为1。

MariaDB开启binlog,并设置sync_binlog=1(反向操作注释掉相应内容即可)

配置文件加入

[mysqld]
log_bin=on
binlog_format=ROW
sync_binlog=1 
GreatSQL关闭binlog,并设置为sync_binlog=0

在配置文件中加入,并注释掉相关binlog参数

[mysqld]
skip-log-bin
sync_binlog=0

测试结果

由于文章篇幅原因这里附上测试结果,sysbench安装测试请参考:

https://greatsql.cn/docs/8.0.32-26/10-optimze/3-1-benchmark-sysbench.html

10张表每张表1千万数据,8线程,测试结果均取第四次的结果。测试命令样例:

$ sysbench  ./oltp_read_only.lua --mysql-db=database --mysql-host=ip --mysql-port=port --mysql-user=root --mysql-password=password --tables=10 --table_size=1000000 --report-interval=2 --threads=8 --db-driver=mysql --skip-trx=off --db-ps-mode=disable --create-secondary=off --time=60 --simple-ranges=0 --sum-ranges=0 --order-ranges=0 --distinct-ranges=0 --mysql-ignore-errors=9001,9002,9000,1062 run

MariaDB和GreatSQL均关闭binlog,sync_binlog 设置为0

MariaDB和GreatSQL均开启binlog,sync_binlog 设置为1

ps:在MariaDB和GreatSQL均开启binlog,sync_binlog 设置为1的测试场景下,只读还存在少量的性能差异。因为测试机器为虚拟机性能有限,磁盘I/O等并不稳定,无法排除硬件的影响,有兴趣的朋友可以自行测试得到更为精确的性能报告

问题一小结

实际生产环境出于对数据安全的考虑,建议开启binlog,sync_binlog设置为1。

开启binlog和sync_binlog=1的情况下,MariaDB和GreatSQL大多数场景下性能相当,只有在只读场景下MariaDB 略优于GreatSQL

关闭binlog和sync_binlog=0的情况下,在update_index和delete场景下GreatSQL略优于MariaDB,而只读场景下MariaDB略优于GreatSQL,其余场景下二者性能相当

问题二:GreatSQL插入大事务hang住,MariaDB不会

某天某客户反馈GreatSQL插入大事务会hang住,而MariaDB不会。在论坛帖子求助也无果

原贴链接https://GreatSQL.cn/forum.php?mod=viewthread&tid=827&extra=&highlight=MariaDB&page=2

信息收集:

可看原贴

show processlsit显示

update的线程State整齐卡在waiting for handler commit

hang住的时候,错误日志打印Warning信息:

[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.

问题定位及分析:

1.其配置文件中

"innodb_io_capacity"设置为200过小,可以调整为2000或根据物理I/O适当调整。写负载越高的系统建议设置更高值,但不建议使用高于20000的值。

"innodb_redo_log_capacity"设置为100M过小,可调整到1G~8G之间,在写负载高场景中,可以调整的更大。

2.GreatSQL中的宽表插入大量二进制数据(疑似图片或文件附件二进制内容),易造成更多的page页分裂,造成redo膨胀,redo的写入加剧,消耗更多innodb的I/O,redo的总容量过小,InnoDB在受到重做日志容量压力的情况下被迫把脏页刷新到磁盘,同样需要更多innodb的I/O,innodb的I/O瓶颈导致正常提交的事务hang住。

参数详解:

innodb_io_capacity:

控制innodb可用的总I/O容量

The innodb_io_capacity variable defines the overall I/O capacity available to InnoDB. It should be set to approximately the number of I/O operations that the system can perform per second (IOPS). When innodb_io_capacity is set, InnoDB estimates the I/O bandwidth available for background tasks based on the set value.

You can set innodb_io_capacity to a value of 100 or greater. The default value is 200.

译文

innodb_io_cacapacity变量定义了innodb可用的总I/O容量。它应该设置为大约系统每秒可以执行的I/O操作数(IOPS)。当设置innodb_io_capacity时,innodb会根据设置值估计后台任务可用的I/O带宽。

您可以将innodb_io_capacity设置为100或更大的值。默认值为200。

innodb_redo_log_capacity:

redo日志文件的总容量。

The Innodb_redo_log_capacity_resized server status variable indicates the total redo log capacity for all redo log files.

If innodb_redo_log_capacity is not defined, and if innodb_log_file_size and/or innodb_log_files_in_group is defined, then the InnoDB redo log capacity is calculated as (innodb_log_files_in_group * innodb_log_file_size). This calculation does not modify the unused innodb_redo_log_capacity setting's value.

译文

Innodb_redo_log_capacity_resized服务器状态变量表示所有重做日志文件的总重做日志容量。

如果未定义innodb_redo_log_capacity,并且定义了innodb_log_file_size和/或innodb_log.files_in_group,则innodb重做日志容量计算为(innodb_log-files_in_group*innodb_log_file_size)。此计算不会修改未使用的innodb_redo_log_capacity设置的值。

验证

客户把对应的参数调大,hang住的情况消失,业务能正常使用,达到预期。

问题二小结

该次问题貌似是简单的参数调整,其实其中关联到了以下内容,有兴趣的可以去了解

1.开发规范,是否允许往库宽表插入大量二进制数据,查询怎么办?

2.了解redo是什么,innodb master线程有什么作用(脏页回收线程也合并到master中了)

3.innodb的IO由哪些参数控制,调整为多大是合理值?

4.redo的容量规格由哪些参数控制,调整为多大是合理值?

全文总结

无论是问题一中的100倍性能背后还是插入hang住,其实背后都是对产品的不了解,MariaDB 10.3.39其实对应的是5.7的产品,而且其本身的默认参数调整有作弊嫌疑,默认关闭binlog ,sync_binlog设置为0,checkpoint阈值降低,提高checkpoint的频率等设置,面向测试环境数据是很好看的,但是增加了生产环境的风险和硬件成本。

应知其然,知其不然。

诸君共勉


Enjoy GreatSQL 😃

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。

相关链接: GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区:

社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html

技术交流群:

微信:扫码添加GreatSQL社区助手微信好友,发送验证信息加群