GreatSQL 构建高效 HTAP 服务架构指南（MGR）-每日运维

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

引言

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

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

整体方案架构图

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

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

高查询效率：
读写分离及读负载均衡：
高可用：
高灵活和扩展：

部署 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状态

生成测试数据

主库写入数据

-- 创建测试数据库 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

测试读写分离效果

在启动 rouyter 测试读写分离效果，先测试写节点是否指向 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）完成！

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