首页 > 数据库 >MariaDB 和 GreatSQL 性能差异背后的真相

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

时间:2024-11-09 18:10:00浏览次数:1  
标签:binlog log 真相 GreatSQL bk innodb MariaDB com wAP

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
file

file

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

file

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 不会。在论坛帖子求助也无果

bk.gzysart.com/0wSwf.Wap
bk.51yjjy.com/c4FSw.wAp
bk.vwotech.com/asd7/f8W4d.wAP
bk.huanbao580.com/jasl/0wSwf.Wap
bk.qcbysq.com/asd7/D30eq.wAP
bk.xintiao78.com/D30eq.wAP
bk.sdymsxfh.com/beyn/fX8Xw.WaP
bk.yjh9988.com/cliq/c4FSw.wAp
bk.62nsfs.com/jasl/fX8Xw.WaP
bk.bjyins.com/asd7/c4FSw.wAp
bk.qcbysq.com/meyl/0wSwf.Wap
bk.hndsedu.com/c5W4R.WAp
bk.bjyins.com/cliq/D30eq.wAP
bk.xintiao78.com/deyz/c5W4R.WAp
bk.gzysart.com/beyn/c5W4R.WAp
bk.re-shake.com/cliq/0wSwf.Wap
bk.sdymsxfh.com/fX8Xw.WaP
bk.bjyins.com/deyz/0wSwf.Wap
bk.chinabgroup.com/cliq/fX8Xw.WaP
bk.tanjiuspace.com/c5W4R.WAp
bk.chinabgroup.com/fX8Xw.WaP
bk.xintiao78.com/cliq/c5W4R.WAp
bk.hndsedu.com/dcwl/f8W4d.wAP
bk.huanbao580.com/meyl/c4FSw.wAp
bk.qcbysq.com/dcwl/c4FSw.wAp
bk.jyh01.com/dcwl/D30eq.wAP
bk.51yjjy.com/f8W4d.wAP
bk.qcbysq.com/jasl/0wSwf.Wap
bk.sdymsxfh.com/deyz/f8W4d.wAP
bk.jinduoceramics.com/c5W4R.WAp
bk.jyh01.com/meyl/c4FSw.wAp
bk.62nsfs.com/beyn/0wSwf.Wap
bk.szlcdpq.com/c4FSw.wAp
bk.shuixitech.com/dcwl/f8W4d.wAP
bk.vwotech.com/c5W4R.WAp
bk.zcyxsm.com/jasl/0wSwf.Wap
bk.jinduoceramics.com/cliq/0wSwf.Wap
bk.szlcdpq.com/jasl/D30eq.wAP
bk.xintiao78.com/fX8Xw.WaP
bk.re-shake.com/jasl/c5W4R.WAp
bk.re-shake.com/beyn/c5W4R.WAp
bk.jiaforhui.com/beyn/c4FSw.wAp
bk.huaxinlighting.com/cliq/f8W4d.wAP
bk.shuixitech.com/D30eq.wAP
bk.lovehua99.com/asd7/f8W4d.wAP
bk.lovehua99.com/beyn/c5W4R.WAp
bk.huaxinlighting.com/dcwl/D30eq.wAP
bk.tanjiuspace.com/deyz/c4FSw.wAp
bk.lovehua99.com/meyl/f8W4d.wAP
bk.wekochat.com/meyl/c5W4R.WAp
bk.testoppo.com/meyl/0wSwf.Wap
bk.xintiao78.com/jasl/c5W4R.WAp
bk.vwotech.com/asd7/fX8Xw.WaP
bk.jinduoceramics.com/deyz/D30eq.wAP
bk.jinduoceramics.com/dcwl/c4FSw.wAp
bk.jinduoceramics.com/jasl/c5W4R.WAp
bk.51yjjy.com/cliq/f8W4d.wAP
bk.sdymsxfh.com/beyn/f8W4d.wAP
bk.jinduoceramics.com/cliq/f8W4d.wAP
bk.kfamaw.com/asd7/c5W4R.WAp
bk.qcbysq.com/f8W4d.wAP
bk.zcyxsm.com/c4FSw.wAp
bk.jiaforhui.com/f8W4d.wAP
bk.wekochat.com/f8W4d.wAP
bk.tanjiuspace.com/0wSwf.Wap
bk.zjbhzs.com/meyl/0wSwf.Wap
bk.kfamaw.com/dcwl/D30eq.wAP
bk.vwotech.com/D30eq.wAP
bk.51jyo.com/dcwl/c4FSw.wAp
bk.hndsedu.com/jasl/c5W4R.WAp
bk.zjbhzs.com/0wSwf.Wap
bk.testoppo.com/dcwl/f8W4d.wAP
bk.51yjjy.com/D30eq.wAP
bk.hndsedu.com/D30eq.wAP
bk.51yjjy.com/beyn/0wSwf.Wap
bk.vwotech.com/f8W4d.wAP
bk.xintiao78.com/meyl/f8W4d.wAP
bk.zcyxsm.com/fX8Xw.WaP
bk.zcyxsm.com/dcwl/c4FSw.wAp
bk.tanjiuspace.com/meyl/0wSwf.Wap
bk.zcyxsm.com/D30eq.wAP
bk.xintiao78.com/jasl/0wSwf.Wap
bk.szlcdpq.com/beyn/c4FSw.wAp
bk.huanbao580.com/D30eq.wAP
bk.cdkenxb120.com/fX8Xw.WaP
bk.vwotech.com/jasl/fX8Xw.WaP
bk.wekochat.com/c5W4R.WAp
bk.sdymsxfh.com/deyz/c4FSw.wAp
bk.zjbhzs.com/asd7/f8W4d.wAP
bk.jinduoceramics.com/asd7/D30eq.wAP
bk.xintiao78.com/cliq/c4FSw.wAp
bk.shplcchina.com/beyn/c4FSw.wAp
bk.re-shake.com/asd7/D30eq.wAP
bk.huaxinlighting.com/beyn/fX8Xw.WaP
bk.zcyxsm.com/c5W4R.WAp
bk.xintiao78.com/beyn/c4FSw.wAp
bk.shuixitech.com/0wSwf.Wap
bk.51yjjy.com/deyz/0wSwf.Wap
bk.zcyxsm.com/cliq/fX8Xw.WaP
bk.wekochat.com/f8W4d.wAP

信息收集:
可看原贴

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 的频率等设置,面向测试环境数据是很好看的,但是增加了生产环境的风险和硬件成本。

标签:binlog,log,真相,GreatSQL,bk,innodb,MariaDB,com,wAP
From: https://www.cnblogs.com/XX-SHE/p/18537063

相关文章

  • GreatSQL 在 SQL 中使用 HINT 语法修改会话变量
    GreatSQL在SQL中使用HINT语法修改会话变量在GreatSQL支持一种新的优化Hint,名字叫SET_VAR,这个特性支持用户在查询语句里修改GreatSQL数据库的一些会话变量,当然修改只是对当前查询会话生效,不会影响到其他会话。SET_VAR语法SET_VAR这个hint用于临时设置系统变量的......
  • Oracle 与 GreatSQL 差异:更改唯一索引列
    Oracle与GreatSQL差异:更改唯一索引列问题来源在从Oracle迁移到GreatSQL的应用系统中,一条普通的update语句在GreatSQL中却报错,需要进行SQL语句的改写。把实际问题简化为下面简单情况进行说明。在Oracle下,可以正常执行的update语句。--建表CREATETABLEte......
  • 【GreatSQL 优化器 - 01】const_table
    一、const_table介绍GreatSQL的优化器主要用JOIN类来进行处理SQL语句的,JOIN类有以下四个table数量相关的成员变量。其中const_tables是optimize最开始就检查并且标识的,因为这样可以把记录最少的表放在执行计划的第一步,在后面的执行计划里面这些consttables是不参......
  • 【GreatSQL优化器-01】const_table
    【GreatSQL优化器-01】const_table一、const_table介绍GreatSQL的优化器主要用JOIN类来进行处理SQL语句的,JOIN类有以下四个table数量相关的成员变量。其中const_tables是optimize最开始就检查并且标识的,因为这样可以把记录最少的表放在执行计划的第一步,在后面的执行计划里面这......
  • 揭秘光伏真相:光伏发电有辐射?对人体有害吗?一文带你全面了解!
    光伏有辐射吗?光伏对人体有伤害吗?在探讨光伏是否有辐射,以及是否对人体有伤害的问题时,我们首先需要了解光伏发电的基本原理和工作机制。光伏发电是一种利用太阳能转化为电能的技术,其核心部件是太阳能电池板。太阳能电池板通过光电效应将太阳光中的光子能量转化为直流电能,这一过......
  • Oracle与GreatSQL差异:更改唯一索引列
    Oracle与GreatSQL差异:更改唯一索引列1.问题来源在从Oracle迁移到GreatSQL的应用系统中,一条普通的update语句在GreatSQL中却报错,需要进行SQL语句的改写。把实际问题简化为下面简单情况进行说明。在Oracle下,可以正常执行的update语句。--建表CREATETABLEtest.test1(id......
  • 麒麟系统修改镜像地址,并安装数据库mariadb
    1.跳转到镜像源文件夹cd/etc/yum.repos.d2.备份系统镜像源文件ps:要养成修改系统文件之前做文件备份的好习惯cpkylin_aarch64.repokylin_aarch64.repo.bak3.修改的镜像源vikylin_aarch64.repo[ks10-adv-cdrom]name=KylinLinuxAdvancedServer10-cdrombaseurl......
  • 小红书起号全流程:7天搞定起号,3大真相揭晓!
    在小红书上起号,很多新人总会面临各种问题:“如何快速起号?”“到底该怎么操作才能避开雷区?”今天,我为你整理了一份全面的起号秘笈,帮助你在短短七天内掌握起号技巧,还会揭示三大核心真相,让你少走弯路,稳步起号! 七天起号计划 第1天:探索用户标签起号的第一步是通过浏览、点赞......
  • MariaDB 和 GreatSQL 性能差异背后的真相
    MariaDB和GreatSQL性能差异背后的真相前言最近项目上遇到了两次MariaDB和GreatSQL的对比,GreatSQL受到客户质疑,最后经过排查抓到性能差异背后的真相。基于此做个分享。版本MariaDB版本:10.3.39该版本为麒麟V10yum安装GreatSQL版本:GreatSQL-8-0-32-25问题一:MariaDB......
  • MariaDB 矢量版-专为人工智能设计
       作为一名在关系型数据库系统领域拥有二十多年经验的解决方案架构师,我最近开始探索MariaDB的新矢量版,看看它能否解决我们面临的一些人工智能数据挑战。快速浏览一下似乎很有说服力,尤其是它如何将人工智能的魔力直接带入常规数据库设置中。不过,想通过一个简单的用例对它进......