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

文摘科技 2024-07-24 08:00 福建

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

引言

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

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

GreatSQL 构建高效 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 架构

环境准备及版本介绍

服务器配置

$ uname -a
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
$ cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)

组件配置

IP	角色	版本	备注
192.168.6.215:3306	Primary 节点	GreatSQL 8.0.32-25
192.168.6.214:3306	Secondary 节点	GreatSQL 8.0.32-25	专属 HTAP 只读节点
192.168.6.54:3306	Secondary 节点	GreatSQL 8.0.32-25	高可用备节点
192.168.6.215:3306	MySQL Router	8.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状态

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

生成测试数据

主库写入数据

-- 创建测试数据库  
CREATE DATABASE IF NOT EXISTS htap_test_db;  
USE htap_test_db;  
  
-- 创建接近生产环境的表  
CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `product_id` int NOT NULL,
  `order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `order_status` char(10) NOT NULL DEFAULT 'pending',
  `quantity` int NOT NULL,
  `order_amount` decimal(10,2) NOT NULL,
  `shipping_address` varchar(255) NOT NULL,
  `billing_address` varchar(255) NOT NULL,
  `order_notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_order_date` (`order_date`),
  KEY `idx_order_status` (`order_status`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

greatsql> SELECT COUNT(*) FROM htap_test_db.orders;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.01 sec)

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

SELECT
 order_id,customer_id,product_id,order_date,order_status,
 quantity,order_amount,shipping_address,billing_address,
 order_notes,
 SUM( order_amount ) OVER ( PARTITION BY customer_id ) AS total_spent_by_customer,
 COUNT( order_id ) OVER ( PARTITION BY customer_id ) AS total_orders_by_customer,
 AVG( order_amount ) OVER ( PARTITION BY customer_id ) AS average_order_amount_per_customer 
FROM
 orders 
WHERE
 order_status IN ( 'completed', 'shipped', 'cancelled' ) 
 AND quantity > 1 
ORDER BY
 order_date DESC,
 order_amount DESC 
 LIMIT 100;

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

# 第一次
100 rows in set (2.90 sec)
# 第二次
100 rows in set (3.14 sec)
# 第三次
100 rows in set (3.23 sec)

构建专属 HTAP 只读节点

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

使用 Rapid 引擎

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

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

为InnoDB表加上Rapid辅助引擎

greatsql> ALTER TABLE htap_test_db.orders SECONDARY_ENGINE = rapid;

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

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

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

greatsql> SHOW TABLE STATUS like 'orders'\G
*************************** 1. row ***************************
           Name: orders
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 93611
 Avg_row_length: 140
    Data_length: 13123584
Max_data_length: 0
   Index_length: 9502720
      Data_free: 4194304
 Auto_increment: 200001
    Create_time: 2024-06-27 11:00:46
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: SECONDARY_ENGINE="rapid" SECONDARY_LOAD="1"
        Comment: 
1 row in set (0.01 sec)

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

greatsql> SET use_secondary_engine = ON;
greatsql> SET secondary_engine_cost_threshold = 0;

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

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

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

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

# 第一次
100 rows in set (0.10 sec)
# 第二次
100 rows in set (0.08 sec)
# 第三次
100 rows in set (0.08 sec)

启动增量导入任务

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

启动增量导入任务

greatsql> SELECT START_SECONDARY_ENGINE_INCREMENT_LOAD_TASK('htap_test_db', 'orders');
+----------------------------------------------------------------------+
| START_SECONDARY_ENGINE_INCREMENT_LOAD_TASK('htap_test_db', 'orders') |
+----------------------------------------------------------------------+
| success                                                              |
+----------------------------------------------------------------------+

查看增量导入任务状态

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

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

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

# Secondary 节点查看数据是 110000 条和 Primary 节点一致
greatsql> SELECT COUNT(*) FROM htap_test_db.orders;
+----------+
| COUNT(*) |
+----------+
|   110000 |
+----------+
1 row in set (0.02 sec)

greatsql> EXPLAIN SELECT COUNT(*) FROM htap_test_db.orders\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 103611
     filtered: 100.00
        Extra: Using secondary engine RAPID
1 row in set, 1 warning (0.00 sec)

此处启用了 Rapid 引擎所以COUNT(*)速度会很快，若没启用 Rapid 引擎则可能耗时较长

查看执行计划，从 rows 列可以看到，扫描的行数增加了，表示新数据已经增量导入到 Rapid 引擎中

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

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

greatsql> SET GLOBAL super_read_only =on;

此方案真正上线后，还需增添额外的高可用切换逻辑处理，例如：

至此，MGR 架构下和构建 HTAP 专属只读节点完成，接下来是使用中间件实现读写分离

实现读写分离

这里使用的是 MySQL Router 中间件实现的读写分离，MySQL Router 对 MGR 兼容度高，契合度好。

使用 MySQL Router 需要用 MySQL Shell 纳管 MGR 集群，否则 MySQL Router 会报错:

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 目录

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

可以做一个环境变量

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

创建一个 MySQL Router 用户

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

初始化 MySQL Router

$ mysqlrouter --bootstrap repl@192.168.6.215:3306 --user=root
# 输出结果如下
...部分省略
After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/mysqlrouter.conf
...部分省略
- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447
- Read/Write Split Connections: localhost:6450

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

可以看到在 6446、6447 端口的基础上有一个 6450 端口，这个端口可以作为读写分离端口

这就初始化完毕了，按照上面的提示，直接启动 mysqlrouter 服务即可，检查下是否正常启动

mysqlrouter -c /usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/mysqlrouter.conf &

$ ps -ef | grep -v grep | grep mysqlrouter
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

$  netstat -lntp | grep mysqlrouter
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      29153/mysqlrouter   
tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      29153/mysqlrouter   
tcp        0      0 0.0.0.0:6448            0.0.0.0:*               LISTEN      29153/mysqlrouter   
tcp        0      0 0.0.0.0:6449            0.0.0.0:*               LISTEN      29153/mysqlrouter   
tcp        0      0 0.0.0.0:6450            0.0.0.0:*               LISTEN      29153/mysqlrouter   
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      29153/mysqlrouter

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

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
# 更改后
destinations=192.168.6.214:3306,192.168.6.215:3306,192.168.6.56:3306
routing_strategy=first-available
# 更改前
#destinations=metadata-cache://mgr/?role=SECONDARY
#routing_strategy=round-robin-with-fallback
protocol=classic

测试读写分离效果

在启动 Router 测试读写分离效果，先测试写节点是否指向 PRIMARY 节点

$ for ((i=0;i<=2;i++));do mysql -h192.168.6.215 -urepl -p'GreatSQL@2024' -P6446 -e"select @@server_id;";done;
+-------------+
| @@server_id |
+-------------+
|        3306 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|        3306 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|        3306 |
+-------------+

在测试读节点是否指向 SECONDARY 节点

$ for ((i=0;i<=2;i++));do mysql -h192.168.6.215 -urepl -p'GreatSQL@2024' -P6447 -e"select @@server_id;";done;
+-------------+
| @@server_id |
+-------------+
|        3307 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|        3307 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|        3307 |
+-------------+

最后测试读写分离端口 6450 是否会将读写操作分别指向 PRIMARY 节点和 SECONDARY 节点

$ for ((i=0;i<=2;i++));do mysql -h192.168.6.215 -urepl -p'GreatSQL@2024' -P6450 -e"select @@server_id;";done;
+-------------+
| @@server_id |
+-------------+
|        3308 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|        3307 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|        3308 |
+-------------+

# 因为其余两个 SECONDARY 节点设置 super_read_only=OFF，若能写入必定指向 PRIMARY 节点
$ mysql -h192.168.6.215 -uroot -p'GreatSQL@2024' -P6450 -e "INSERT INTO htap_test_db.orders (customer_id, product_id, order_status, quantity, order_amount, shipping_address, billing_address, order_notes) VALUES (274, 467, 'processing', 6, 17.70, 'Shipping Address 2', 'Billing Address 2', 'Order note for order 2');"

由于 3308 节点与 3307 节点皆为 SECONDARY 节点，故而在进行读操作时，会对这两个节点轮询分配，因此 6450 读写分离端口无法指定专门使用专属的 HTAP 节点。倘若有需求，将 3308 节点启用 Rapid 引擎即可。
当 PRIMARY 节点发生宕机状况后，若 3308 这个高可用节点成为新的 PRIMARY 节点，那么 3308 节点所设置的 Rapid 引擎并不会对所有的读写操作产生影响。

自此构建高效 HTAP 服务器架构（MGR）完成！

Enjoy GreatSQL :)

<往期推荐>

独家揭秘丨GreatSQL 没开Binlog时多线程插入数据性能劣化之谜

GreatSQL 构建高效 HTAP 服务架构指南（主从复制）

GreatSQL社区月报 | 2024.06

FILE+POS 方式 GreatSQL 主从复制架构给主节点磁盘扩容

展会 | GreatSQL亮相全球数字经济大会开源数据库赋能金融数字化转型

《GreatSQL HTAP》探索之路

<关于 GreatSQL>

GreatSQL数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为MySQL或Percona Server for MySQL的理想可选替换。

💻社区官网: https://greatsql.cn/