面向金融级应用的GreatSQL正式开源

经过几个月的紧张筹备,GreatSQL宣布正式开源。

GreatSQL是源于Percona Server的分支版本。GreatSQL在Percona Server已有的稳定可靠、高效、管理更方便等优势基础上,进一步提升了MGR(MySQL Group Replication)的性能及可靠性,新增金融级应用场景需求特性并修复数个影响可靠性的严重bug。

GreatSQL可以作为MySQL或Percona Server的可选替代方案,用于线上生产环境。

GreatSQL完全免费并兼容MySQL或Percona Server。

GreatSQL由万里数据库发起、主导、维护,也欢迎广大MySQL使用者、爱好者下载使用,或者提交代码、issue等。

1. 使用MySQL社区版存在什么风险

万里数据库核心研发团队深入研究MGR架构,并在不断的BUG修复实践中总结出了一套完善、流畅的BUG修复流程,将MGR的缺陷分为BUG和性能两类,整理出共16种BUG及性能缺陷问题。

搜索MySQL官方bug站,可以看到MGR分类下未修复的bug数量还是比较多的:

当服务器配置高,网络环境好,业务量小的时候,这些MGR相关的bug可能不容易碰到。如果是网络环境稍微复杂一些,例如同城多数据中心环境,甚至跨交换机,都可能会遇到网络分区条件下的一些bug。或者当业务量较大,负载较高时,可能会产生丢数据、OOM,或事务频繁回滚、死锁等问题。

由于MGR自身的复杂性,以及复现BUG场景也更困难,所以MySQL社区版针对MGR的BUG修复工作通常比较缓慢,堆积较多。这也就造成了不少用户不太敢放心使用MySQL社区版的MGR,担心遇到各种不可控的BUG,甚至较严重的线程、事务hang住等问题,感觉还是不那么可靠。

而GreatSQL已经有效解决了绝大多数较严重的问题,可以更放心地在金融级应用场景使用MGR架构。

2. GreatSQL的优势及展望

在金融级应用场景中,对数据的可靠性和架构的容错性要求都更高,对多数据中心甚至多活都有较高需求。此外,业务系统中经常会有定期跑批计算任务,而MySQL在这方面存在明显的性能瓶颈,很难满足大数据量跑批需求。为此,GreatSQL未来会在以下几方面着重发力。

2.1 增加更多金融级场景需求特性

  • 增加地理标签功能。当在多机房部署MGR时,可以保证每个机房中至少有个节点都参与事务认证,确保该节点总有最新事务,这可用于解决多机房数据同步的问题。
  • 采用全新的流控机制,流控阈值计算更合理、细致,不会出现频繁性能抖动问题。
  • (下个版本计划)增加single-primary模式下快速单主机制,省略事务认证过程,效率更高。
  • (下个版本计划)增加投票节点功能,该节点仅参与MGR投票仲裁,不存放实际数据也无需执行DML操作,在保证MGR可靠性的同时还能降低服务器成本。

2.2 提升同城双机房和跨城架构部署的可靠性

  • 支持AFTER模式下多数派写机制。发生网络分区时,只要多数派节点已经回放完毕,集群就可以继续处理新的事务,依然可以保障集群的高可用性。
  • 解决磁盘空间爆满时导致MGR集群阻塞的问题。当发现某节点磁盘空间满了,就会让这个节点主动退出集群,避免像MySQL社区版那样整个集群被阻塞的问题。
  • 解决多主模式下或切主时可能导致丢数据的问题。调整了事务认证处理流程,改成放到 applier queue 里按照paxos顺序处理,这就解决了在多主模式下或切主时可能导致丢数据的问题。
  • 解决节点异常退出集群时导致性能抖动的问题。优化paxos通信机制,发生异常时只会产生约1~3秒的性能小抖动,最差时TPS可能只损失约20% ~ 30%。而MySQL社区版本可能会造成约20~30秒的性能抖动,最差时TPS可能有好几秒都降为0。下面两个图非常明显体现了GreatSQL针对这种情况所做的优化。

MySQL社区版:耗时约29秒才完全恢复。

GreatSQL版本:耗时约3秒即完全恢复,时效提升约90% – 节点异常状态判断更完善,比MySQL社区版本能更快发现、判断节点异常状态,有效减少切主和异常节点的等待耗时。下面两个图体现了GreatSQL针对节点状态异常做出更快速准确的判断。

MySQL社区版:5秒发现问题,22秒后将其踢出。

GreatSQL版本:2秒发现问题,9秒后将其踢出,时效提升约60%

2.3 MGR性能提升

  • 优化事务认证队列清理算法。MySQL社区版本中,认证数据库采用类似全表扫描的方式,效率极低。优化后,采用基于类似索引机制,有效解决清理效率低、性能抖动大的问题。
  • 提高MGR吞吐量。经过优化,有效提升MGR的吞吐量,并减少网络延迟对访问性能的影响。
  • 提升强一致性读性能,并降低从库只读延迟。

2.4 InnoDB事务锁以及并行查询优化

合并了由华为鲲鹏计算团队贡献的两个Patch,分别针对OLTP和OLAP两种业务场景。 – 优化InnoDB事务锁机制,将原来的红黑树改为无锁哈希结构,在高并发场景中有效提升事务并发性能至少10%以上。

  • 实现对InnoDB底层B+树多个子树的并行扫描机制,极大提升聚合查询效率,TPC-H测试中,最高可提升30倍,平均提升15倍。特别适用于周期性数据汇总报表之类的SAP、财务统计等业务。

2.5 其他更多企业级特性展望

未来我们还计划将一部分企业级特性也开放出来,包括且不仅限于国产化硬件适配、等保合规、安全加密、Oracle兼容等众多特性。

3. GreatSQL VS MySQL社区版

特性 GreatSQL MySQL社区版
地理标签
全新流控算法
InnoDB并行查询优化
InnoDB事务锁优化
网络分区异常应对 ⭐️⭐️⭐️⭐️⭐️ ⭐️
大事务处理 ⭐️⭐️⭐️⭐️⭐️ ⭐️
节点异常退出处理 ⭐️⭐️⭐️⭐️⭐️ ⭐️
一致性读性能 ⭐️⭐️⭐️⭐️⭐️ ⭐️
提升MGR吞吐量 ⭐️⭐️⭐️⭐️⭐️ ⭐️
多写模式下可能丢数据 ⭐️⭐️⭐️⭐️⭐️ /
单主模式下切主丢数据 ⭐️⭐️⭐️⭐️⭐️ /
MGR集群启动效率 ⭐️⭐️⭐️⭐️⭐️ /
集群节点磁盘满处理 ⭐️⭐️⭐️⭐️⭐️ /
TCP self-connect问 题 ⭐️⭐️⭐️⭐️⭐️ /

GreatSQL代码已上传到gitee上,项目地址 https://gitee.com/GreatSQL/GreatSQL,欢迎围观、加星,也欢迎来“找茬”,提patch。

Enjoy GreatSQL :)

Changes in GreatSQL 8.0.25 (2021-8-26)

1.新增特性

1.1 新增节点地理标签

可以对每个节点设置地理标签,主要用于解决多机房数据同步的问题。 新增选项 group_replication_zone_id,用于标记节点地理标签。该选项值支持范围 0 ~ 8,默认值为0。 当集群中各节点该选项值设置为不同的时候,就被认定为设置了不同的地理标签。 在同城多机房部署方案中,同一个机房的节点可以设置相同的数值,另一个机房里的节点设置另一个不同的数值,这样在事务提交时会要求每组 group_replication_zone_id 中至少有个节点确认事务,然后才能继续处理下一个事务。这就可以确保每个机房的某个节点里,总有最新的事务。

System Variable Name group_replication_zone_id
Variable Scope global
Dynamic Variable YES
Permitted Values [0 ~ 8]
Default 0
Description 设置MGR各节点不同的地理标签,主要用于解决多机房数据同步的问题。
修改完该选项值之后,要重启MGR线程才能生效。

1.2 采用全新的流控机制

原生的流控算法有较大缺陷,触发流控阈值后,会有短暂的流控停顿动作,之后继续放行事务,这会造成1秒的性能抖动,且没有真正起到持续流控的作用。

在GreatSQL中,重新设计了流控算法,增加主从延迟时间来计算流控阈值,并且同时考虑了大事务处理和主从节点的同步,流控粒度更细致,不会出现官方社区版本的1秒小抖动问题。

新增选项 group_replication_flow_control_replay_lag_behind 用于控制MGR主从节点复制延迟阈值,当MGR主从节点因为大事务等原因延迟超过阈值时,就会触发流控机制。

System Variable Name group_replication_flow_control_replay_lag_behind
Variable Scope global
Dynamic Variable YES
Permitted Values [0 ~ ULONG_MAX]
Default 60
Description 用于控制MGR主从节点复制延迟阈值,当MGR主从节点因为大事务等原因延迟超过阈值时,就会触发流控机制

该选项默认为60秒,可在线动态修改,例如:

mysql> SET GLOBAL group_replication_flow_control_replay_lag_behind = 60;

正常情况下,该参数无需调整。

2.稳定性提升

2.1 支持AFTER模式下多数派写机制

这样在发生网络分区时,依然可以保障集群的高可用性。

发生网络分区故障时,只要多数派节点已经回放完毕,集群就可以继续处理新的事务。

2.2 解决磁盘空间爆满时导致MGR集群阻塞的问题

在官方社区版本中,一旦某个节点磁盘空间满了,就会导致整个集群被阻塞,这种情况下,节点数量越多,可用性越差。

在GreatSQL版本中,一旦发现某节点磁盘空间满了,就会让这个节点主动退出集群,就可以避免整个集群被阻塞的问题。

2.3 解决多主模式下或切主时可能导致丢数据的问题

官方社区版本中,是提前处理事务认证数据的。

而在GreatSQL版本中,调整了事务认证处理流程,改成放到 applier queue 里按照paxos顺序处理,这就解决了在多主模式下或切主时可能导致丢数据的问题。

2.4 解决节点异常退出集群时导致性能抖动的问题

官方社区版本中,paxos通信机制较为粗糙,当节点异常退出时,会造成较长时间(约20~30秒)的性能抖动,最差时TPS可能有好几秒都降为0。

GreatSQL版本中对此进行优化后,只会产生约1~3秒的性能小抖动,最差时TPS可能只损失约20% ~ 30%。(原来1:修复了节点异常时导致MGR大范围性能抖动问题。原来2:网络分区情况下,优化吞吐量和等待时间)。

2.5 节点异常状态判断更完善

当发生节点异常崩溃或者网络分区时,GreatSQL版本能更快发现这些异常状态。官方需要5秒才能发现,而GreatSQL只需要大概1秒,这就能有效减少切主和异常节点的等待耗时。

2.6 优化日志输出格式

增加更多DEBUG信息,便于排查MGR运行时遇到的问题。

3.性能提升

3.1 重新设计事务认证队列清理算法

官方社区版本中,对事务认证队列清理时采用了类似全表扫描的算法,清理效率较低,性能抖动较大。

在GreatSQL版本中,对事务认证队列增加了类似索引机制,并控制每次清理的时间,可以有效解决清理效率低、性能抖动大的问题。

3.2 提高MGR吞吐量

在类似跨城IDC部署的高延迟场景下,提升应用访问MGR的吞吐量,尽量减少网络延迟对访问性能的影响。

3.3 提升强一致性读性能

提升强一致性读性能,使得从库只读延迟大大降低。

4. 合并华为鲲鹏计算团队贡献的两个Patch

4.1 InnoDB事务对象映射数据结构优化

在官方社区版本实现中,使用了红黑树结构实现了事务ID到事务对象的快速映射关系。但是该数据结构在高并发应用场景中,大量的锁竞争会造成事务处理的瓶颈。

在GreatSQL中采用全新的无锁哈希结构,显著减少了锁的临界区消耗,提升事务处理的能力至少10%以上。

4.2 InnoDB并行查询优化

根据B+树的特点,可以将B+树划分为若干子树,此时多个线程可以并行扫描同一张InnoDB表的不同部分。对执行计划进行多线程改造,每个子线程执行计划与MySQL原始执行计划一致,但每个子线程只需扫描表的部分数据,子线程扫描完成后再进行结果汇总。通过多线程改造,可以充分利用多核资源,提升查询性能。

优化后,在TPC-H测试中表现优异,最高可提升30倍,平均提升15倍。该特性适用于周期性数据汇总报表之类的SAP、财务统计等业务。

使用限制: – 暂不支持子查询,可想办法改造成JOIN。 – 暂时只支持ARM架构平台,X86架构平台优化也会尽快完成。

新增参数选项

System Variable Name force_parallel_execute
Variable Scope global, session
Dynamic Variable YES
Permitted Values ON/OFF
Default OFF
Description 设置并行查询的开关,bool值,on/off。默认off,关闭并行查询特性。
System Variable Name parallel_cost_threshold
Variable Scope global, session
Dynamic Variable YES
Permitted Values [0, ULONG_MAX]
Default 1000
Description 设置SQL语句走并行查询的阈值,只有当查询的估计代价高于这个阈值才会执行并行查询,SQL语句的估计代价低于这个阈值,执行原生的查询过程。

| System Variable Name | parallel_default_dop | | ——————– | —————————————————————————————————————————————– | | Variable Scope | global, session | | Dynamic Variable | YES | | Permitted Values | [0, 1024] | | Default | 4 | | Description | 设置每个SQL语句的并行查询的最大并发度。
SQL语句的查询并发度会根据表的大小来动态调整,如果表的二叉树太小(表的切片划分数小于并行度),则会根据表的切片划分数来设置该查询的并发度。每一个查询的最大并行度都不会超过parallel_default_dop参数设置的值。 |

System Variable Name parallel_max_threads
Variable Scope global, session
Dynamic Variable YES
Permitted Values [0, ULONG_MAX]
Default 64
Description 设置系统中总的并行查询线程数。
System Variable Name parallel_memory_limit
Variable Scope global, session
Dynamic Variable YES
Permitted Values [0, ULONG_MAX]
Default 1073741824(1GB)
Description 并行执行时leader线程和worker线程使用的总内存大小上限。

| System Variable Name | parallel_queue_timeout | | ——————– | ————————————————————————————————————- | | Variable Scope | global, session | | Dynamic Variable | YES | | Permitted Values | [0, ULONG_MAX] | | Default | 0 | | Description | 设置系统中并行查询的等待的超时时间,如果系统的资源不够,例如运行的并行查询线程已达到parallel_max_threads的值,并行查询语句将会等待,如果超时后还未获取资源,将会执行原生的查询过程。
单位:毫秒 |

新增状态变量

  • PQ_threads_running global级别,当前正在运行的并行执行的总线程数。

  • PQ_memory_used global级别,当前并行执行使用的总内存量。

  • PQ_threads_refused global级别,由于总线程数限制,导致未能执行并行执行的查询总数。

  • PQ_memory_refused global级别,由于总内存限制,导致未能执行并行执行的查询总数。

5.bug修复

  • 修复了AFTER模式下的多个bug,提高强一致性写AFTER模式的可靠性。例如有新节点加入时,由于消息处理不当、线程同步等问题容易导致一系列异常退出集群的现象。
  • 修复了多数派节点不同类型异常退出集群导致的视图更新的问题。当节点崩溃和节点退出同时发生的话,可能会导致MGR视图跟实际情况不符,从而出现一系列不异常问题。
  • 修复了MGR部署在单机环境下多节点的TCP self-connect问题。相关bug #98151
  • 修复了recovery过程中长时间等待的问题。同时启动多个节点,可能会导致一直卡在recovering状态,持续时间很久(甚至可能超过数小时)。修复了几处不合理的sleep做法,解决该问题。
  • 修复了传输大数据可能导致逻辑判断死循环问题。
  • 修复若干coredump问题
    • a)执行start group_replication 和查询 replication_group_member_stats 并发操作时,可能导致 start group_replication 启动失败,甚至节点coredump。
    • b)执行 start group_replication 启动过程可能失败,在销毁内部对象过程中,如果同时查询 replication_group_member_stats 状态,可能导致coredump。

6.使用注意事项

  • 当MGR集群任何节点处于recovering状态,并且还有业务流量时,【不要】执行stop group_replicationt停止MGR服务,否则可能会导致GTID紊乱甚至丢数据风险。
  • 选项 slave_parallel_workers 建议设置为逻辑CPU数的2倍,提高从库回放时的并发效率。
  • 设置 group_replication_flow_control_replay_lag_behind 参数后,原生MGR中的流控参数不再起作用,GreatSQL会根据队列长度、大小自动进行流控。
  • 在MGR节点正式拉起之前,务必设置 super_read_only=ON(或者确保此时不会有人修改数据)。
  • 选项 group_replication_unreachable_majority_timeout 建议不要设置,否则网络分区的时候,给用户返回错误,但其它分区多数派已经提交了事务。
  • 出于问题诊断需要,建议设置 log_error_verbosity=3
  • 启用InnoDB并行查询优化特性(force_parallel_execute = ON)时,建议同时调大 parallel_default_dop 选项值,以提高单个SQL查询并行度。
  • 启用InnoDB并行查询优化特性时,建议同时调大 parallel_max_threads 选项值,以提高整个实例的查询并行度。
  • 启用InnoDB并行查询优化特性时,SQL运行时如果需要产生临时表,则可能会报告 table...full 的错误,这是MySQL的BUG#99100,可以通过加大 temptable_max_ram 选项值解决。

《叶问》36期,MySQL最多只能用到128个逻辑CPU,是真的吗

江湖传言MySQL最多只能用到128个逻辑CPU,是真的吗?

同事从客户现场回来,委屈巴巴的说,某PG服务商告诉客户“MySQL最高只能支持128个逻辑CPU,更多就用不上了,还是用PG吧”。

作为从MySQL 3.23时代就开始一路陪跑过来的我,肯定不能忍啊。。。

在早期以MyISAM引擎为主的年代,的确有类似的限制。MyISAM存在众多限制,这个也是众所周知的,不赘述了。

但自从InnoDB成为MySQL默认引擎后,这个情况应该是不复存在了。尤其是自从MySQL引入innodb_autoinc_lock_mode、innodb_io_capacity、innodb_read_io_threads、innodb_write_io_threads等多个可控参数选项后,对于高并发的业务场景,基本上都能把所有逻辑CPU跑满。

口说无凭,直接测试验证下吧。

测试环境:

#查看CPU,共有176个逻辑CPU
$ lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                176
On-line CPU(s) list:   0-175
Thread(s) per core:    2
Core(s) per socket:    22
Socket(s):             4
...

#OS环境
$ cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)
$ uname -a
Linux3.10.0-1127.el7.x86_64 #1 SMP Tue Mar 31 23:36:51 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

下载MySQL 5.5.62版本的二进制包,修改下面几个参数选项:

innodb_io_capacity = 20000
innodb_autoinc_lock_mode = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_thread_concurrency = 0

实例启动后,在另外的客户机上运行sysbench进行压测(sysbench不要和MySQL服务器跑在同一个服务器上):

#128个测试表,每个表10万行记录,并发256个线程
$ sysbench /usr/share/sysbench/oltp_read_write.lua  --mysql-host=172.16.10.10 --mysql-port=3306 --mysql-user=xx --mysql-password=xx --mysql-db=sbtest  --db-driver=mysql  --tables=128  --table_size=100000 --threads=256 --report-interval=1 --db-ps-mode=disable --time=0 run

而后运行htop观察所有CPU的状态,肉眼即可见所有逻辑CPU上都有负载:

P.S,MySQL 5.1.73版本中的并发度确实有限,不建议使用该版本,不过现在用5.1的应该很少了吧。

最后,借用微信朋友圈一位同学的留言:talk is cheap, show your test。

GreatSQL重磅特性,InnoDB并行并行查询优化测试

GreatSQL马上正式开源了,这次又新增了两个重磅特性:InnoDB事务锁优化 以及 InnoDB引擎的并行查询优化,这两个特性是由华为鲲鹏计算团队贡献的Patch合并而来。

InnoDB并行查询优化怎么实现的?

根据B+树的特点,可以将B+树划分为若干子树,此时多个线程可以并行扫描同一张InnoDB表的不同部分。对执行计划进行多线程改造,每个子线程执行计划与MySQL原始执行计划一致,但每个子线程只需扫描表的部分数据,子线程扫描完成后再进行结果汇总。通过多线程改造,可以充分利用多核资源,提升查询性能。

优化后,在TPC-H测试中表现优异,最高可提升30倍,平均提升15倍。

该特性适用于周期性数据汇总报表之类的SAP、财务统计等业务,例如月初、月底跑批业务等。

使用限制:

  • 暂不支持子查询,可想办法改造成JOIN。
  • 暂时只支持ARM架构平台,X86架构平台优化也会尽快完成。

关于该Patch详情见:https://support.huaweicloud.com/fg-kunpengdbs/kunpengdbs_20_0005.html

本文针对 InnoDB引擎的并行查询优化 特性进行对比测试。

1、测试环境

服务器:神州鲲泰R222,华为Hi1616 * 2(主频 2400 MHz 共64个逻辑CPU),256G内存。

操作系统:Docker 20.10.2,Docker容器下的CentOS Linux release 7.9.2009,Linux 4.15.0-29-generic。

本次测试采用TPC-H,dbgen构造测试数据参数 dbgen -vf -s 50,导入后数据库物理大小约70G。
GreatSQL关键配置:

#运行Q10测试时,需要较大临时表
temptable_max_ram = 6G

#使得本测试基于纯内存场景
innodb_buffer_pool_size=96G

#InnoDB并行查询优化
#global级别,设置并行查询的开关,bool值,on/off。默认off,关闭并行查询特性。可在线动态修改。
force_parallel_execute = ON

#global级别,设置系统中总的并行查询线程数。有效值的范围是(0, ULONG_MAX),默认值是64。
parallel_max_threads = 64

#global级别,并行执行时leader线程和worker线程使用的总内存大小上限。有效值的范围是(0, ULONG_MAX),默认值是1G
parallel_memory_limit = 32G

2、测试数据

测试过程中,注意要确保每次查询都是基于纯内存的场景,也就是确保innodb_buffer_pool_size大于数据库物理大小,并确认查询过程中没有额外的物理I/O发生。

个别SQL例如Q10在运行过程中会产生临时表(Using temporary),这时候需要加大 temptable_max_ram 选项值。该选项默认值1G,在上述测试数据量前提下,大概需要加大到4G才能hold住。如果该选项值不够的话,可能运行过程中会提示诸如 The table '/tmp/#sql57_a1_0' is full 这样的错误提示,然后退出查询,这是MySQL的BUG#99100

InnoDB并行查询特性通过HINT语法可以很方便地使用,首先确认启用了该特性(可在线动态打开):

$ mysqladmin var|grep force_parallel_execute
| force_parallel_execute                                   | ON

那么默认所有的SQL只要符合条件,即可自动采用并行查询,通过查看执行计划确认:

mysql> EXPLAIN SELECT ... FROM ... WHERE ...
...
Parallel execute (4 workers)
...

可以看到执行计划输出中包含 Parallel execute (4 workers) 关键字,这就表示最高可并行4个线程查询。

也可以查看树状执行计划:

mysql> EXPLAIN FORMAT=TREE SELECT ... FROM ... WHERE ...
...
| -> Limit: 1 row(s)
    -> Sort: lineitem.l_returnflag, lineitem.l_linestatus, limit input to 1 row(s) per chunk
        -> Table scan on <temporary>
            -> Aggregate using temporary table
                -> Parallel scan on <temporary>
                    -> Sort: lineitem.l_returnflag, lineitem.l_linestatus
                        -> Table scan on <temporary>
                            -> Aggregate using temporary table
                                -> Filter: (lineitem.l_shipdate <= <cache>((DATE'1998-12-01' - interval '88' day)))  (cost=6342898.28 rows=19669815)
                                    -> PQblock scan on lineitem  (cost=6342898.28 rows=59015354)
...

可以看到执行计划中包含 PQblock scan on ... 关键字,并且注意到同一行里提示 cost=6342898.28,这是启用并行查询的条件之一,也就是 cost 超过了 parallel_cost_threshold = 1000 设置的阈值开关。

一条SQL若不想启用并行查询,加上相应的HINT即可:

mysql> SELECT /*+ NO_PQ */ ... FROM ... WHERE ...

也可以动态调整并行线程数为最高64线程:

mysql> SELECT /*+ PQ(64) */ ... FROM ... WHERE ...

好了,直接查看结果对比数据:

TPCH 并行扫描(默认参数)
耗时(秒)
并行扫描(参数优化后)
耗时(秒)
未优化前
耗时(秒)
并行扫描 vs 未优化前的提升 提高查询并行读优化后提升
Q1 611.541496 43.636506 1406.118961 32.223 14.014
Q3 124.013688 16.522413 343.813205 20.809 7.506
Q5 300.617792 33.121942 297.152399 8.971 9.076
Q6 251.192255 20.221361 237.598312 11.750 12.422
Q10 146.754999 42.034981 276.591330 6.580 3.491
Q12 326.641641 24.804828 594.310294 23.959 13.168
Q19 21.286635 2.216351 58.947622 26.597 9.604

从这个测试结果简单概括几条:

  • 1、平均提升约14倍,最高提升约32倍。
  • 2、如果并发量更高,则优化效果更好。

全文完。

Enjoy GreatSQL :)

延伸阅读

在Linux下源码编译安装GreatSQL/MySQL

本次介绍如何利用Docker来将GreatSQL源码编译成二进制文件,以及制作二进制包、RPM包等。

本文介绍的运行环境是CentOS 7.9:

[root@greatsql ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

[root@greatsql ~]# uname -a
Linux greatsql 3.10.0-1160.11.1.el7.x86_64 #1 SMP Fri Dec 18 16:34:56 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

1、准备工作

1.1、配置yum源

开始编译之前,建议先配置好yum源,方便安装一些工具。

以阿里、腾讯两大云主机为例,可以这样配置(两个yum源自行二选一):

[root@greatsql ~]# mv /etc/yum.repos.d/CentOS-Base.repo{,.orig}

#阿里云
[root@greatsql ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

#腾讯云
[root@greatsql ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.cloud.tencent.com/repo/centos7_base.repo

#替换完后,更新缓存
[root@greatsql ~]# yum clean all
[root@greatsql ~]# yum makecache

1.2、安装docker

安装docker,并启动docker进程。

[root@greatsql]# yum install -y docker
[root@greatsql]# systemctl start docker

1.3、提前下载几个必要的安装包

分别下载几个编译过程中需要的依赖包:

  • boost, https://boostorg.jfrog.io/artifactory/main/release/1.73.0/source/boost_1_73_0.tar.gz
  • git, https://github.com/git/git/archive/v2.27.0.tar.gz, 下载后重命名为 git-v2.27.0.tar.gz
  • patchelf, https://github.com/NixOS/patchelf/archive/refs/tags/0.12.tar.gz, 下载后重命名为 patchelf-0.12.tar.gz
  • rpcsvc-proto, https://github.com/thkukuk/rpcsvc-proto/releases/download/v1.4/rpcsvc-proto-1.4.tar.gz

下载GreatSQL源码包:https://gitee.com/GreatSQL/GreatSQL/archive/greatsql-8.0.25-15.tar.gz

1.4、构建docker镜像

用下面这份Dockerfile构建镜像,这里以CentOS 7为例:

FROM centos:7
ENV LANG en_US.utf8

RUN yum install -y epel-release && \
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.cloud.tencent.com/repo/centos7_base.repo && \
yum clean all && \
yum makecache
RUN yum install -y yum-utils && \
yum install -y --skip-broken \
automake bison bison-devel boost-devel bzip2 bzip2-devel ccache centos-release-scl clang \
cmake cmake3 diffutils expat-devel file flex gcc gcc-c++ gflags-devel git jemalloc jemalloc-devel \
gtest-devel libaio-devel libarchive libcurl-devel libevent-devel libffi-devel libicu-devel libssh \
libssl libtirpc libtirpc-devel libtool libxml2-devel libzstd libzstd-devel libzstd-static lz4-devel \
lz4-static make ncurses-devel ncurses-libs net-tools numactl numactl-devel numactl-libs openldap-clients \
openldap-devel openssl openssl-devel pam pam-devel patchelf perl perl-Env perl-JSON perl-Memoize \
perl-Time-HiRes pkg-config psmisc re2-devel python-devel readline-devel redhat-lsb-core rpcgen rpm* \
scl-utils-build snappy-devel tar time unzip valgrind vim wget wget zlib-devel

RUN yum install -y devtoolset-10-gcc*
RUN scl enable devtoolset-10 bash
RUN echo 'scl enable devtoolset-10 bash' >> /root/.bash_profile

#git, https://github.com/git/git/archive/v2.27.0.tar.gz, 下载后重命名为 git-v2.27.0.tar.gz
COPY git-v2.27.0.tar.gz /tmp/
RUN cd /tmp/ && tar -xzvf git-v2.27.0.tar.gz && cd git-2.27.0 && make prefix=/opt/git/ all && make prefix=/opt/git/ install
RUN mv /usr/bin/git /usr/bin/git.bk && ln -s /opt/git/bin/git /usr/bin/git

#patchelf, https://github.com/NixOS/patchelf/archive/refs/tags/0.12.tar.gz, 下载后重命名为 patchelf-0.12.tar.gz
#如果编译过程报错,再手动打补丁 https://github.com/NixOS/patchelf/pull/275/files
COPY patchelf-0.12.tar.gz /tmp/
RUN cd /tmp && tar -xzvf patchelf-0.12.tar.gz && cd patchelf-0.12 && ./bootstrap.sh && ./configure && make && make install

#rpcsvc-proto, https://github.com/thkukuk/rpcsvc-proto/releases/download/v1.4/rpcsvc-proto-1.4.tar.gz
COPY rpcsvc-proto-1.4.tar.gz /tmp/rpcsvc-proto-1.4.tar.gz
RUN tar zxvf /tmp/rpcsvc-proto-1.4.tar.gz -C /tmp && cd /tmp/rpcsvc-proto-1.4/ && ./configure && make && make install

RUN rm -fr /tmp/*

#boost, https://boostorg.jfrog.io/artifactory/main/release/1.73.0/source/boost_1_73_0.tar.gz
COPY boost_1_73_0.tar.gz /opt/

RUN ln -fs /usr/bin/cmake3 /usr/bin/cmake

开始构建docker镜像,成功后再保存到本地并导入本地镜像:

[root@greatsql ~]# docker build -t centos7-greatsql .
... ...
[root@greatsql ~]# docker save -o centos7-greatsql.tar centos7-greatsql
[root@greatsql ~]# docker load -i centos7-greatsql.tar

创建一个docker容器,并将GreatSQL源码包copy进去:

[root@greatsql ~]# docker run -itd --name greatsql --hostname=greatsql centos7-greatsql bash
[root@greatsql ~]# docker cp /opt/greatsql-8.0.25-15.tar.gz greatsql:/opt/
[root@greatsql ~]# docker exec -it greatsql bash
[root@greatsql /]# ls -l /opt/
-rw------- 1 root root 128699082 Jul 27 06:56 boost_1_73_0.tar.gz
drwxr-xr-x 5 root root      4096 Jul 28 06:38 git
-rw------- 1 1000 1000 526639994 Jul 27 05:59 greatsql-8.0.25-15.tar.gz
drwxr-xr-x 3 root root      4096 Jul 28 06:34 rh

2、编译GreatSQL

进入容器后,解压GreatSQL和boost缩源码包:

[root@greatsql /]# tar zxf /opt/greatsql-8.0.25-15.tar.gz -C /opt
[root@greatsql /]# tar zxf /opt/boost_1_73_0.tar.gz -C /opt/greatsql-8.0.25-15/

2.1、只编译二进制文件

如果只是想在本机使用,则可以只编译出二进制文件即可,无需打包或制作RPM包。用下面的命令进行编译:

[root@greatsql /]# cmake3 /opt/greatsql-8.0.25-15 \
-DBOOST_INCLUDE_DIR=../boost_1_73_0 \
-DCMAKE_INSTALL_PREFIX=/usr/local/GreatSQL-8.0.25-15-Linux-glibc2.17 \
-DWITH_ZLIB=bundled \
-DWITH_NUMA=ON \
-DFORCE_INSOURCE_BUILD=1 \
-DCMAKE_EXE_LINKER_FLAGS="-ljemalloc" \
-DCMAKE_BUILD_TYPE=RelWithDebInfo \
-DBUILD_CONFIG=mysql_release \
-DCOMPILATION_COMMENT="GreatSQL (GPL), Release 15, Revision 6d439c6ef3f" \
-DWITH_TOKUDB=OFF \
-DWITH_ROCKSDB=OFF \
-DWITH_COREDUMPER=OFF

cmake过程如果没报错,就会输出类似下面的结果:

... ...
-- Build files have been written to: /opt/greatsql-8.0.25-15

接下来可以开始正式编译了:

[root@greatsql ~]# make -j30 VERBOSE=1 && make install

参数 -j30 设定为并行编译的逻辑CPU数量,可以指定为比逻辑CPU总数少一点,不要把所有CPU都跑满。

编译完成后,就会将二进制文件安装到 /usr/local/GreatSQL-8.0.25-15-Linux.x86_64 目录下。

2.2、编译并打包成二进制文件包或RPM包

如果是想要在编译完后也能拷贝到其他服务器上使用,也可以直接编译生成二进制包或RPM包,可以用下面的命令编译:

[root@greatsql ~]# cd /opt/greatsql-8.0.25-15/build-gs/
[root@greatsql ~]# export your_processors=30   #同上,修改并行CPU数量
[root@greatsql ~]# export TAR_PROCESSORS=-T$your_processors
[root@greatsql ~]# export MAKE_JFLAG=-j$your_processors
[root@greatsql ~]# mkdir -p workdir
[root@greatsql ~]# cp /opt/boost_1_73_0.tar.gz /opt/greatsql-8.0.25-15/build-gs/
[root@greatsql ~]# cp /opt/greatsql-8.0.25-15.tar.gz /opt/greatsql-8.0.25-15/build-gs/workdir/

# 选择1:编译并打包成二进制包
[root@greatsql ~]# bash -xe ./percona-server-8.0_builder.sh --builddir=`pwd`/workdir --get_sources=0 --install_deps=0 --with_ssl=1 --build_tarball=1 --build_src_rpm=0 --build_rpm=0 --no_git_info=1 --local_boost=1

# 选择2:编译并打包成RPM包
[root@greatsql ~]# bash -xe ./percona-server-8.0_builder.sh --builddir=`pwd`/workdir --get_sources=0 --install_deps=0 --with_ssl=1 --build_tarball=0 --build_src_rpm=1 --build_rpm=1 --no_git_info=1 --local_boost=1

编译过程中如果遇到类似下面的patchelf报错:

+ patchelf --replace-needed libreadline.so.7 libreadline.so bin/mysql
patchelf: cannot normalize PT_NOTE segment: non-contiguous SHT_NOTE sections

可以参考这个patch:patchelf: Fix alignment issues with contiguous note sections #275,修改下源码,在容器里重新手动编译patchelf。

编译结束后,就会在 /opt/greatsql-8.0.25-15/build-gs/workdir/ 目录下生成相应的二进制包、RPM包:

[root@greatsql build-gs]# du -sh workdir/TARGET/
40M     workdir/TARGET/GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64-minimal.tar.xz
500M    workdir/TARGET/GreatSQL-8.0.25-15-Linux-glibc2.17-x86_64.tar.xz

[root@greatsql build-gs]# du -sh workdir/rpm/greatsql-*
14M     workdir/rpm/greatsql-client-8.0.25-15.1.el8.x86_64.rpm
30M     workdir/rpm/greatsql-client-debuginfo-8.0.25-15.1.el8.x86_64.rpm
3.4M    workdir/rpm/greatsql-debuginfo-8.0.25-15.1.el8.x86_64.rpm
23M     workdir/rpm/greatsql-debugsource-8.0.25-15.1.el8.x86_64.rpm
2.1M    workdir/rpm/greatsql-devel-8.0.25-15.1.el8.x86_64.rpm
4.6M    workdir/rpm/greatsql-mysql-router-8.0.25-15.1.el8.x86_64.rpm
27M     workdir/rpm/greatsql-mysql-router-debuginfo-8.0.25-15.1.el8.x86_64.rpm
13M     workdir/rpm/greatsql-rocksdb-8.0.25-15.1.el8.x86_64.rpm
204M    workdir/rpm/greatsql-rocksdb-debuginfo-8.0.25-15.1.el8.x86_64.rpm
60M     workdir/rpm/greatsql-server-8.0.25-15.1.el8.x86_64.rpm
343M    workdir/rpm/greatsql-server-debuginfo-8.0.25-15.1.el8.x86_64.rpm
1.4M    workdir/rpm/greatsql-shared-8.0.25-15.1.el8.x86_64.rpm
2.5M    workdir/rpm/greatsql-shared-debuginfo-8.0.25-15.1.el8.x86_64.rpm
440M    workdir/rpm/greatsql-test-8.0.25-15.1.el8.x86_64.rpm
18M     workdir/rpm/greatsql-test-debuginfo-8.0.25-15.1.el8.x86_64.rpm

这就可以用在copy到其他服务器上安装使用了。

3、初始化GreatSQL

本次计划在下面3台服务器上部署MGR集群:

node ip datadir port role
nodeA 172.16.16.10 /data/GreatSQL/ 3306 PRIMARY
nodeB 172.16.16.11 /data/GreatSQL/ 3306 SECONDARY
nodeC 172.16.16.12 /data/GreatSQL/ 3306 SECONDARY

先在nodeA服务器上执行下面的初始化工作,另外两个服务器也照做一遍即可。

首先编辑 /etc/my.cnf 配置文件,可参考采用下面的配置参数:

#my.cnf
[mysqld]
user    = mysql
port    = 3306
#主从复制或MGR集群中,server_id记得要不同
#另外,实例启动时会生成 auto.cnf,里面的 server_uuid 值也要不同
#server_uuid的值还可以自己手动指定,只要符合uuid的格式标准就可以
server_id = 3306
basedir=/usr/local/GreatSQL-8.0.25-15-Linux.x86_64
datadir = /data/GreatSQL
socket  = /data/GreatSQL/mysql.sock
pid-file = mysql.pid
character-set-server = UTF8MB4
skip_name_resolve = 1
#若你的MySQL数据库主要运行在境外,请务必根据实际情况调整本参数
default_time_zone = "+8:00"

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M

#log settings
log_timestamps = SYSTEM
log_error = /data/GreatSQL/error.log
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
slow_query_log_file = /data/GreatSQL/slow.log
long_query_time = 0.1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin = /data/GreatSQL/binlog
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
#MySQL 8.0.22前,想启用MGR的话,需要设置binlog_checksum=NONE才行
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE

#myisam settings
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

#replication settings
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1
slave_parallel_type = LOGICAL_CLOCK
#可以设置为逻辑CPU数量的2倍
slave_parallel_workers = 64
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1
slave_checkpoint_period = 2

#mgr settings
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
#MGR本地节点IP:PORT,请自行替换
loose-group_replication_local_address = "172.16.16.10:33061"
#MGR集群所有节点IP:PORT,请自行替换
loose-group_replication_group_seeds = "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode = ON

#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话,可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs = 1
innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log_error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864

#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
innodb_monitor_enable = "module_adaptive_hash"

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

执行下面的命令进行初始化:

[root@greatsql ~]# /usr/local/GreatSQL-8.0.25-15-Linux.x86_64/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure

初始化时可选项有 --initialize--initialize-insecure 两种,前者会为root账号生成一个随机密码,后者不会。在这里为了省事,选用后者,生产环境里请务必要为root用户设置安全密码

之后就可以启动mysqld进程了:

[root@greatsql ~]# /usr/local/GreatSQL-8.0.25-15-Linux.x86_64/bin/mysqld --defaults-file=/etc/my.cnf &

GreatSQL是基于Percona Server的分支版本,默认情况下需要用到jemalloc这个库,如果启动过程中报告类似下面的错误,只需要再安装jemalloc或者libaio等相关的软件包即可:

/usr/local/GreatSQL-8.0.23-14/bin/mysqld: error while loading shared libraries: libjemalloc.so.1: cannot open shared object file: No such file or directory

补充安装libjemalloc库即可:

[root@greatsql ~]# yum install -y jemalloc jemalloc-devel

如果想要关闭mysqld进程,执行下面的命令即可:

# 假设此时root还是空密码
[root@greatsql ~]# /usr/local/GreatSQL-8.0.25-15-Linux.x86_64/bin/mysql -uroot -S/data/GreatSQL/mysql.sock shutdown

查看版本号:

root@GreatSQL [(none)]> \s
...
Server version:     8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
...

这就启动GreatSQL服务了,接下来同样的方法,完成另外两个服务器上的GreatSQL初始化并启动,然后开始构建MGR集群。

另外,也可以参考这篇指南”将GreatSQL添加到系统systemd服务“,把GreatSQL加入系统systemd服务中。

4、搭建MGR集群

MGR集群的部署可以自己手动一步步操作,也可通过MySQL Shell快速完成,分别参考下面的文档即可:

延伸阅读

全文完。

Enjoy GreatSQL :)