GreatSQL 構建高效 HTAP 服務架構指南（MGR）

GreatSQL發表於2024-07-24
SQL架構

GreatSQL 構建高效 HTAP 服務架構指南（MGR）

引言

全文約定：$為命令提示符、greatsql>為 GreatSQL 資料庫提示符。在後續閱讀中，依據此約定進行理解與操作

上一篇已經介紹瞭如何在主從複製架構中，搭建一個專屬 HTAP 服務。本篇將在 MGR 架構中部署一個專屬 HTAP 服務。

整體方案架構圖

file

本服務架構採用 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

測試讀寫分離效果

在啟動 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）完成！

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章