GreatSQL 构建高效 HTAP 服务架构指南（MGR）

幸福指数

GreatSQL 构建高效 HTAP 服务架构指南（MGR）

引言

全文约定：$为命令提示符、greatsql>为 GreatSQL 数据库提示符。在后续阅读中，依据此约定进行理解与操作

上一篇已经介绍了如何在主从复制架构中，搭建一个专属 HTAP 服务。本篇将在 MGR 架构中部署一个专属 HTAP 服务。
整体方案架构图

本服务架构采用 GreatSQL MGR 架构，在 MGR 架构中部署一个专属 HTAP 服务节点。Primary 节点采用默认 InnoDB 引擎，Secondary 节点使用辅助引擎 Rapid 加速查询构建专属 HTAP 只读节点。加上 MySQL Router 等之类的代理/中间件负责读写分离来完成 HTAP 服务架构。

• 高查询效率：
  
  
  
  • Rapid引擎的引入使得从节点能够加速查询处理，特别适用于 OLAP（联机分析处理）场景。
    
  
  
• 读写分离及读负载均衡：
  
  
  
  • 利用代理/中间件实现读写分离，确保主节点（写操作）和从节点（读操作）的读写负载得到有效均衡。
    
  
  
• 高可用：
  
  
  
  • GreatSQL 针对 MGR 做了大量的改进和提升工作，进一步提升 MGR 的高可靠等级，例如：地理标签、读写节点VIP、仲裁节点等。
    
  • 详见：[GreatSQL 高可用] https://greatsql.cn/docs/8.0.32-25/5-enhance/5-2-ha.html
    
  
  
• 高灵活和扩展：
  
  
  
  • GreatSQL 的可插拔存储引擎架构使得系统可以根据需要选择适合的存储引擎。Rapid引擎作为辅助引擎，可以动态安装或卸载，为用户提供了极大的灵活性和可扩展性。
    
  
  

部署 MGR 架构

环境准备及版本介绍

服务器配置

1. $ uname -a
2. Linux gip 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
3. $ cat /etc/centos-release
4. CentOS Linux release 7.6.1810 (Core)

复制代码

组件配置
IP角色版本备注192.168.6.215:3306Primary 节点GreatSQL 8.0.32-25192.168.6.214:3306Secondary 节点GreatSQL 8.0.32-25专属 HTAP 只读节点192.168.6.54:3306Secondary 节点GreatSQL 8.0.32-25高可用备节点192.168.6.215:3306MySQL Router8.4.0 TLS代理/中间件。可根据需求灵活替换安装 GreatSQL

GreatSQL 安装版本为 8.0.32-25 版本，并分别安装三个实例 GreatSQL

安装步骤详见：https://greatsql.cn/docs/8.0.32-25/4-install-guide/0-install-guide.html

部署 MGR 架构

MGR 部署方案在 GreatSQL 用户手册中有详细介绍，可以使用 MySQL Shell for GreatSQL 或手动部署详见：https://greatsql.cn/docs/8.0.32-25/6-mgr/1-deploy-mgr.html 这里就不在过多赘述了。
部署成功后，在MGR架构中，可以查看MGR状态

1. greatsql> SELECT * FROM performance_schema.replication_group_members;
2. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
4. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
5. | group_replication_applier | 4c78e67d-338a-11ef-995c-00163edb666e | 192.168.6.56  |        3306 | ONLINE       | SECONDARY   | 8.0.32         | XCom                       |
6. | group_replication_applier | d7ebbeef-3384-11ef-8022-00163e832e1f | 192.168.6.214 |        3306 | ONLINE       | SECONDARY   | 8.0.32         | XCom                       |
7. | group_replication_applier | e3fb309c-3389-11ef-8b02-00163e8e122e | 192.168.6.215 |        3306 | ONLINE       | PRIMARY     | 8.0.32         | XCom                       |
8. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
9. 3 rows in set (0.00 sec)

复制代码

生成测试数据

主库写入数据

1. -- 创建测试数据库  
2. CREATE DATABASE IF NOT EXISTS htap_test_db;  
3. USE htap_test_db;  
4.   
5. -- 创建接近生产环境的表  
6. CREATE TABLE `orders` (
7.   `order_id` int NOT NULL AUTO_INCREMENT,
8.   `customer_id` int NOT NULL,
9.   `product_id` int NOT NULL,
10.   `order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
11.   `order_status` char(10) NOT NULL DEFAULT 'pending',
12.   `quantity` int NOT NULL,
13.   `order_amount` decimal(10,2) NOT NULL,
14.   `shipping_address` varchar(255) NOT NULL,
15.   `billing_address` varchar(255) NOT NULL,
16.   `order_notes` varchar(255) DEFAULT NULL,
17.   PRIMARY KEY (`order_id`),
18.   KEY `idx_customer_id` (`customer_id`),
19.   KEY `idx_product_id` (`product_id`),
20.   KEY `idx_order_date` (`order_date`),
21.   KEY `idx_order_status` (`order_status`)
22. ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

复制代码

在 Primary 节点往该表插入十万行数据

1. greatsql> SELECT COUNT(*) FROM htap_test_db.orders;
2. +----------+
3. | COUNT(*) |
4. +----------+
5. |   100000 |
6. +----------+
7. 1 row in set (0.01 sec)

复制代码

如果在 Secondary 节点进行一个复杂 SQL 查询，需要用时 2~3 秒左右

1. SELECT
2.         order_id,customer_id,product_id,order_date,order_status,
3.         quantity,order_amount,shipping_address,billing_address,
4.         order_notes,
5.         SUM( order_amount ) OVER ( PARTITION BY customer_id ) AS total_spent_by_customer,
6.         COUNT( order_id ) OVER ( PARTITION BY customer_id ) AS total_orders_by_customer,
7.         AVG( order_amount ) OVER ( PARTITION BY customer_id ) AS average_order_amount_per_customer
8. FROM
9.         orders
10. WHERE
11.         order_status IN ( 'completed', 'shipped', 'cancelled' )
12.         AND quantity > 1
13. ORDER BY
14.         order_date DESC,
15.         order_amount DESC
16.         LIMIT 100;

复制代码

运行三次结果平均值为 3.09 秒

1. # 第一次
2. 100 rows in set (2.90 sec)
3. # 第二次
4. 100 rows in set (3.14 sec)
5. # 第三次
6. 100 rows in set (3.23 sec)

复制代码

构建专属 HTAP 只读节点

以下所有操作都在 GreatSQL 192.168.6.214:3306 Secondary 节点中进行

使用 Rapid 引擎

进入 Secondary 节点，先关闭 super_read_only 并加载 Rapid 引擎

1. greatsql> SET GLOBAL super_read_only =off;
2. greatsql> INSTALL PLUGIN Rapid SONAME 'ha_rapid.so';

复制代码

为InnoDB表加上Rapid辅助引擎

1. greatsql> ALTER TABLE htap_test_db.orders SECONDARY_ENGINE = rapid;

复制代码

将表数据一次性全量导入到 Rapid 引擎中

1. greatsql> ALTER TABLE htap_test_db.orders SECONDARY_LOAD;
2. Query OK, 0 rows affected (1.72 sec)

复制代码

检查导入情况，注意关键词 SECONDARY_ENGINE="rapid" SECONDARY_LOAD="1"

1. greatsql> SHOW TABLE STATUS like 'orders'\G
2. *************************** 1. row ***************************
3.            Name: orders
4.          Engine: InnoDB
5.         Version: 10
6.      Row_format: Dynamic
7.            Rows: 93611
8. Avg_row_length: 140
9.     Data_length: 13123584
10. Max_data_length: 0
11.    Index_length: 9502720
12.       Data_free: 4194304
13. Auto_increment: 200001
14.     Create_time: 2024-06-27 11:00:46
15.     Update_time: NULL
16.      Check_time: NULL
17.       Collation: utf8mb4_0900_ai_ci
18.        Checksum: NULL
19. Create_options: SECONDARY_ENGINE="rapid" SECONDARY_LOAD="1"
20.         Comment:
21. 1 row in set (0.01 sec)

复制代码

打开 Rapid 引擎的总控制开关，并把优化器阈值调小

1. greatsql> SET use_secondary_engine = ON;
2. greatsql> SET secondary_engine_cost_threshold = 0;

复制代码

secondary_engine_cost_threshold 的默认值是100000，可根据实际情况设置

查看该 SQL 的执行计划，注意关键词 Using secondary engine RAPID 表示使用了 Rapid 引擎

1. greatsql> EXPLAIN SELECT ... 省略 ... ORDER BY order_date DESC,order_amount DESC LIMIT 100;
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: orders
6.    partitions: NULL
7.          type: ALL
8. possible_keys: NULL
9.           key: NULL
10.       key_len: NULL
11.           ref: NULL
12.          rows: 93611
13.      filtered: 33.33
14.         Extra: Using where; Using filesort; Using secondary engine RAPID
15. 1 row in set, 2 warnings (0.00 sec)

复制代码

执行三次结果平均值为 0.086 秒，比之前提升近 36 倍！

1. # 第一次
2. 100 rows in set (0.10 sec)
3. # 第二次
4. 100 rows in set (0.08 sec)
5. # 第三次
6. 100 rows in set (0.08 sec)

复制代码

启动增量导入任务

因为在生产环境中数据是无时不刻在产生，所以需要启用增量导入，此时才可保证数据始终导入在 Rapid 引擎内
启动增量导入任务

1. greatsql> SELECT START_SECONDARY_ENGINE_INCREMENT_LOAD_TASK('htap_test_db', 'orders');
2. +----------------------------------------------------------------------+
3. | START_SECONDARY_ENGINE_INCREMENT_LOAD_TASK('htap_test_db', 'orders') |
4. +----------------------------------------------------------------------+
5. | success                                                              |
6. +----------------------------------------------------------------------+

复制代码

查看增量导入任务状态

1. greatsql> SELECT * FROM information_schema.SECONDARY_ENGINE_INCREMENT_LOAD_TASK\G
2. *************************** 1. row ***************************
3.            DB_NAME: htap_test_db
4.         TABLE_NAME: orders
5.         START_TIME: 2024-06-27 11:26:37
6.         START_GTID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-100011,
7. e3fb309c-3389-11ef-8b02-00163e8e122e:1
8. COMMITTED_GTID_SET: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-100011,
9. e3fb309c-3389-11ef-8b02-00163e8e122e:1
10.          READ_GTID:
11.   READ_BINLOG_FILE: ./binlog.000013
12.    READ_BINLOG_POS: 1710
13.              DELAY: 0
14.             STATUS: RUNNING
15.           END_TIME:
16.               INFO:
17. 1 row in set (0.01 sec)

复制代码

在给主库插入 1 万条数据，确认主从复制和 Rapid 引擎的增量导入没有问题，产生的新数据也可以使用 Rapid 引擎加速查询。

请注意，Rapid 引擎在增量导入数据时可能存在短暂延迟。大量 Insert、Delete 数据，可能无法立即通过 Rapid 引擎查询到这些最新变动的数据。等增量任务导入完成后 Rapid 引擎才能查询到最新变动的数据。

1. # Secondary 节点查看数据是 110000 条和 Primary 节点一致
2. greatsql> SELECT COUNT(*) FROM htap_test_db.orders;
3. +----------+
4. | COUNT(*) |
5. +----------+
6. |   110000 |
7. +----------+
8. 1 row in set (0.02 sec)
10. greatsql> EXPLAIN SELECT COUNT(*) FROM htap_test_db.orders\G
11. *************************** 1. row ***************************
12.            id: 1
13.   select_type: SIMPLE
14.         table: orders
15.    partitions: NULL
16.          type: ALL
17. possible_keys: NULL
18.           key: NULL
19.       key_len: NULL
20.           ref: NULL
21.          rows: 103611
22.      filtered: 100.00
23.         Extra: Using secondary engine RAPID
24. 1 row in set, 1 warning (0.00 sec)

复制代码

此处启用了 Rapid 引擎所以COUNT(*)速度会很快，若没启用 Rapid 引擎则可能耗时较长
查看执行计划，从 rows 列可以看到，扫描的行数增加了，表示新数据已经增量导入到 Rapid 引擎中

1. greatsql> EXPLAIN SELECT ... 省略 ... ORDER BY order_date DESC,order_amount DESC LIMIT 100;
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: orders
6.    partitions: NULL
7.          type: ALL
8. possible_keys: NULL
9.           key: NULL
10.       key_len: NULL
11.           ref: NULL
12.          rows: 103611
13.      filtered: 33.33
14.         Extra: Using where; Using filesort; Using secondary engine RAPID
15. 1 row in set, 2 warnings (0.00 sec)

复制代码

操作完成后，记得把 super_read_only 打开，避免误写入数据，打开 super_read_only=ON 后，Rapid 引擎增量任务可正常运行

1. greatsql> SET GLOBAL super_read_only =on;

复制代码

此方案真正上线后，还需增添额外的高可用切换逻辑处理，例如：
至此，MGR架构下和构建 HTAP 专属只读节点完成，接下来是使用中间件实现读写分离
实现读写分离

这里使用的是 MySQL Router 中间件实现的读写分离，MySQL Router 对 MGR 兼容度高，契合度好。
使用 MySQL Router 需要用 MySQL Shell 纳管 MGR 集群，否则 MySQL Router 会报错:

1. Error: Error executing MySQL query "SELECT * FROM mysql_innodb_cluster_metadata.schema_version": SELECT command denied to user 'repl'@'192.168.6.215' for table 'schema_version' (1142)

复制代码

若使用 MySQL Shell 构建的 MGR 集群则不需要再次纳管，若手动构建的 MGR 集群请参阅文章进行纳管

• https://greatsql.cn/thread-503-1-1.html
  

安装 MySQL Router

下载过程省略，可自行到 MySQL 网站上下载

这里选择的是最新的长期支持版 MySQL Router 8.4.0 版本

解压安装包，并进入 MySQL Router 的 bin 目录

1. $ tar -xvJf mysql-router-8.4.0-linux-glibc2.17-x86_64.tar.xz

复制代码

可以做一个环境变量

1. $ echo 'export PATH=/usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/bin:$PATH' >> ~/.bash_profile
2. $ source ~/.bash_profile

复制代码

创建一个 MySQL Router 用户

1. $ /sbin/groupadd mysqlrouter
2. $ /sbin/useradd -g mysqlrouter mysqlrouter -d /dev/null -s /sbin/nologin

复制代码

初始化 MySQL Router

1. $ mysqlrouter --bootstrap repl@192.168.6.215:3306 --user=root
2. # 输出结果如下
3. ...部分省略
4. After this MySQL Router has been started with the generated configuration
6.     $ /etc/init.d/mysqlrouter restart
7. or
8.     $ systemctl start mysqlrouter
9. or
10.     $ mysqlrouter -c /usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/mysqlrouter.conf
11. ...部分省略
12. - Read/Write Connections: localhost:6446
13. - Read/Only Connections:  localhost:6447
14. - Read/Write Split Connections: localhost:6450
16. ## MySQL X protocol
18. - Read/Write Connections: localhost:6448
19. - Read/Only Connections:  localhost:6449

复制代码

可以看到在 6446、6447 端口的基础上有一个 6450 端口，这个端口可以作为读写分离端口
这就初始化完毕了，按照上面的提示，直接启动 mysqlrouter 服务即可，检查下是否正常启动

1. mysqlrouter -c /usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/mysqlrouter.conf &
3. $ ps -ef | grep -v grep | grep mysqlrouter
4. root     29153  4815  1 16:10 pts/0    00:00:03 mysqlrouter -c /usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/mysqlrouter.conf
6. $  netstat -lntp | grep mysqlrouter
7. tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      29153/mysqlrouter   
8. tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      29153/mysqlrouter   
9. tcp        0      0 0.0.0.0:6448            0.0.0.0:*               LISTEN      29153/mysqlrouter   
10. tcp        0      0 0.0.0.0:6449            0.0.0.0:*               LISTEN      29153/mysqlrouter   
11. tcp        0      0 0.0.0.0:6450            0.0.0.0:*               LISTEN      29153/mysqlrouter   
12. tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      29153/mysqlrouter

复制代码

现在需要更改下 MySQL ROUTER 中的 [routing:bootstrap_ro] 配置使其读操作优先在专属 HTAP 节点上读

1. [routing:bootstrap_ro]
2. bind_address=0.0.0.0
3. bind_port=6447
4. # 更改后
5. destinations=192.168.6.214:3306,192.168.6.215:3306,192.168.6.56:3306
6. routing_strategy=first-available
7. # 更改前
8. #destinations=metadata-cache://mgr/?role=SECONDARY
9. #routing_strategy=round-robin-with-fallback
10. protocol=classic

复制代码

测试读写分离效果

在启动 rouyter 测试读写分离效果，先测试写节点是否指向 PRIMARY 节点
[code]$ for ((i=0;i