GreatSQL 構(gòu)建高效 HTAP 服務(wù)架構(gòu)指南

作者：KAiTO 2024-07-12 12:43:51

從 GreatSQL 8.0.32-25 版本開始，新增Rapid存儲引擎，該引擎使得 GreatSQL 能滿足聯(lián)機(jī)分析（OLAP）查詢請求。GreatSQL Rapid引擎性能表現(xiàn)優(yōu)異，在32C64G測試機(jī)環(huán)境下，TPC-H 100G測試中22條SQL總耗時僅需不到80秒。

引言

全文約定：$為命令提示符、greatsql>為 GreatSQL 數(shù)據(jù)庫提示符。在后續(xù)閱讀中，依據(jù)此約定進(jìn)行理解與操作。

Rapid 引擎

從 GreatSQL 8.0.32-25 版本開始，新增Rapid存儲引擎，該引擎使得 GreatSQL 能滿足聯(lián)機(jī)分析（OLAP）查詢請求。

GreatSQL Rapid引擎性能表現(xiàn)優(yōu)異，在32C64G測試機(jī)環(huán)境下，TPC-H 100G測試中22條SQL總耗時僅需不到80秒。

圖片

Rapid 引擎更多介紹可前往查看：

GreatSQL Rapid引擎正式上線！
GreatSQL 官網(wǎng)：https://greatsql.cn/docs/8.0.32-25/5-enhance/5-1-highperf-rapid-engine.html

有了 Rapid 引擎的加持，便可使用 GreatSQL 構(gòu)建一個高效的 HTAP 服務(wù)架構(gòu)，以此來提升 GreatSQL 的查詢效率。

服務(wù)架構(gòu)圖

圖片

本服務(wù)架構(gòu)采用的是 GreatSQL 主從復(fù)制，主節(jié)點采用默認(rèn) InnoDB 引擎，從節(jié)點使用輔助引擎 Rapid 加速查詢構(gòu)建專屬 HTAP 只讀節(jié)點。加上 MySQL Router 等之類的代理/中間件負(fù)責(zé)讀寫分離來完成 HTAP 服務(wù)架構(gòu)。

采用此 HTAP 架構(gòu)可獲得以下收益

高查詢效率：Rapid 引擎的引入使得從節(jié)點能夠加速查詢處理，特別適用于 OLAP（聯(lián)機(jī)分析處理）場景。
高負(fù)載均衡：利用代理/中間件實現(xiàn)讀寫分離，確保主節(jié)點（寫操作）和從節(jié)點（讀操作）負(fù)載均衡。
高并發(fā)性能：主節(jié)點上采用 InnoDB 響應(yīng)高并發(fā)事務(wù)請求，確保業(yè)務(wù)需求寫入性能。
高靈活和擴(kuò)展：GreatSQL 的可插拔存儲引擎架構(gòu)使得系統(tǒng)可以根據(jù)需要選擇適合的存儲引擎。Rapid 引擎作為輔助引擎，可以動態(tài)安裝或卸載，為用戶提供了極大的靈活性和可擴(kuò)展性。

部署主從復(fù)制

環(huán)境準(zhǔn)備及版本介紹

服務(wù)器配置

$ 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	GreatSQL 主庫	GreatSQL 8.0.32-25
192.168.6.214:3306	GreatSQL 從庫	GreatSQL 8.0.32-25	專屬 HTAP 只讀節(jié)點
192.168.6.215:3306	MySQL Router	8.4.0 TLS	代理/中間件。可根據(jù)需求靈活替換

安裝 GreatSQL

GreatSQL 安裝版本為 8.0.32-25 版本，并分別安裝兩個實例 GreatSQL

安裝步驟詳見：https://greatsql.cn/docs/8.0.32-25/4-install-guide/0-install-guide.html

部署主從復(fù)制

主節(jié)點建立賬戶并授權(quán)

# 建立復(fù)制賬戶
greatsql> ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'GreatSQL@2024';
Query OK, 0 rows affected (0.01 sec)
# 授權(quán)
greatsql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
greatsql> FLUSH PRIVILEGES;

然后查看主節(jié)點狀態(tài)，記錄二進(jìn)制文件名 binlog.000002 和位置 2027

greatsql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 2027
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: e766387a-2d3f-11ef-8435-00163e8e122e:1-8
1 row in set (0.00 sec)

從節(jié)點服務(wù)器配置，并開啟從服務(wù)器復(fù)制

greatsql> CHANGE MASTER TO master_host='192.168.6.215',master_port=3306,master_user='slave',master_password='GreatSQL@2024',master_log_file='binlog.000002',master_log_pos=2027;
greatsql> START REPLICA

檢查主從復(fù)制情況

greatsql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.6.215
                  Source_User: slave
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 2027
               Relay_Log_File: gip-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000002
           Replica_IO_Running: Yes  # 為 Yes 即表示構(gòu)建成功
          Replica_SQL_Running: Yes  # 為 Yes 即表示構(gòu)建成功

生成測試數(shù)據(jù)

主庫寫入數(shù)據(jù)

往主庫生成數(shù)據(jù)；

-- 創(chuàng)建測試數(shù)據(jù)庫  
CREATE DATABASE IF NOT EXISTS htap_test_db;  
USE htap_test_db;  
  
-- 創(chuàng)建接近生產(chǎn)環(huán)境的表  
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

往該表插入十萬行數(shù)據(jù)；

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

從庫此時也會復(fù)制主庫的十萬行數(shù)據(jù)；

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

如果在主庫或從庫進(jìn)行一個復(fù)雜 SQL 查詢，需要用時 4~5 秒左右；

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;

在從庫運行三次結(jié)果平均值為 4.91 秒；

# 第一次
100 rows in set (4.99 sec)
# 第二次
100 rows in set (4.59 sec)
# 第三次
100 rows in set (5.15 sec)

構(gòu)建專屬 HTAP 只讀節(jié)點

以下所有操作都在 GreatSQL 從庫中進(jìn)行；

使用 Rapid 引擎

進(jìn)入 GreatSQL 從庫，加載 Rapid 引擎；

greatsql> INSTALL PLUGIN Rapid SONAME 'ha_rapid.so';

為 orders 表加上 Rapid 輔助引擎；

greatsql> ALTER TABLE htap_test_db.orders SECONDARY_ENGINE = rapid;

將表中數(shù)據(jù)一次性全量導(dǎo)入到 Rapid 引擎中；

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

檢查導(dǎo)入情況，注意關(guān)鍵詞 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: 99381
 Avg_row_length: 142
    Data_length: 14172160
Max_data_length: 0
   Index_length: 9502720
      Data_free: 3145728
 Auto_increment: 100001
    Create_time: 2024-06-19 11:11:27
    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.00 sec)

打開 Rapid 引擎的總控制開關(guān)，并把啟用閾值調(diào)小；

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

secondary_engine_cost_threshold 的值可根據(jù)實際情況設(shè)置；

查看該 SQL 的執(zhí)行計劃，注意關(guān)鍵詞 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: 99381
     filtered: 33.33
        Extra: Using where; Using filesort; Using secondary engine RAPID
1 row in set, 2 warnings (0.00 sec)

執(zhí)行三次結(jié)果平均值為 0.12 秒，比之前提升近 41 倍！

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

啟動增量導(dǎo)入任務(wù)

因為在生產(chǎn)環(huán)境中數(shù)據(jù)是無時不刻在產(chǎn)生，所以需要啟用增量導(dǎo)入，才可保證最新數(shù)據(jù)始終在 Rapid 引擎內(nèi)；

啟動增量導(dǎo)入任務(wù)；

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

查看增量導(dǎo)入任務(wù)狀態(tài)；

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-19 14:13:53
        START_GTID: e766387a-2d3f-11ef-8435-00163e8e122e:9-100010:100012,
f4248873-2d46-11ef-90f8-00163e832e1f:1-8
COMMITTED_GTID_SET: e766387a-2d3f-11ef-8435-00163e8e122e:9-100010:100012,
f4248873-2d46-11ef-90f8-00163e832e1f:1-8
         READ_GTID: 
  READ_BINLOG_FILE: /data/GreatSQL/binlog.000003
   READ_BINLOG_POS: 1906
             DELAY: 0
            STATUS: RUNNING
          END_TIME: 
              INFO:

在給主庫插入 1 萬條數(shù)據(jù)，確認(rèn)主從復(fù)制和 Rapid 引擎的增量導(dǎo)入沒有問題，產(chǎn)生的新數(shù)據(jù)也可以使用 Rapid 引擎加速查詢。

請注意，Rapid 引擎在增量導(dǎo)入數(shù)據(jù)時可能存在短暫延遲。大量 Insert、Delete 數(shù)據(jù)，可能無法立即通過 Rapid 引擎查詢到這些最新變動的數(shù)據(jù)。等增量任務(wù)導(dǎo)入完成后 Rapid 引擎才能查詢到最新變動的數(shù)據(jù)。

# 從機(jī)查看數(shù)據(jù)是 110000 條和主庫一致
greatsql> SELECT COUNT(*) FROM htap_test_db.orders;
+----------+
| COUNT(*) |
+----------+
|   110000 |
+----------+
1 row in set (0.01 sec)

此處啟用了 Rapid 引擎所以COUNT(*)速度會很快，若沒啟用 Rapid 引擎則可能耗時較長；

查看執(zhí)行計劃，從 rows 列可以看到，掃描的行數(shù)增加了，表示新數(shù)據(jù)已經(jīng)增量導(dǎo)入到 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: 109381  # 掃描的行數(shù)也增加了
     filtered: 33.33
        Extra: Using where; Using filesort; Using secondary engine RAPID

至此，主從復(fù)制和構(gòu)建 HTAP 專屬只讀節(jié)點完成，接下來是實現(xiàn)讀寫分離，當(dāng)然一主一從的情況下是不太需要讀寫分離中間件的，要中間件的情況是怕 HTAP 專屬服務(wù)器宕機(jī)，這時候主節(jié)點就要負(fù)責(zé)讀寫了。

實現(xiàn)讀寫分離

這里使用的是 MySQL Router 中間件實現(xiàn)的讀寫分離，如果有其它讀寫分離中間件，例如 MySQL Proxy 等也可以替換。

安裝 MySQL Router

下載過程省略，可自行到 MySQL 網(wǎng)站上下載；

這里選擇的是最新的長期支持版 MySQL Router 8.4.0 版本；

解壓安裝包，并進(jìn)入 MySQL Router 的 bin 目錄

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

把 MySQL Router 配置模板拷貝出來放到 /etc/mysqlrouter 目錄下，并改名為 mysqlrouter.conf

$ cp /usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/share/doc/mysqlrouter/sample_mysqlrouter.conf /etc
$ mv /etc/sample_mysqlrouter.conf /etc/mysqlrouter.conf

修改 MySQL Router 配置文件；

$ vim /etc/mysqlrouter.conf
[DEFAULT]
logging_folder = /usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/log/mysql-router
plugin_folder = /usr/local/mysql-router-8.4.0-linux-glibc2.17-x86_64/lib/mysqlrouter/
runtime_folder = /var/run
config_folder = /etc/

[logger]
level = debug

# 主節(jié)點故障轉(zhuǎn)移配置
[routing:basic_failover]
# 寫節(jié)點地址
bind_address=192.168.6.215
# 寫節(jié)點端口
bind_port = 7001
# 模式，讀寫
mode = read-write
destinations = 192.168.6.215:3306
routing_strategy=first-available
# 從節(jié)點負(fù)載均衡配置
[routing:balancing]
# 綁定的IP地址
bind_address=192.168.6.215
# 監(jiān)聽的端口
bind_port = 7002
# 連接超時時間
connect_timeout = 3
# 后端服務(wù)器地址
destinations = 192.168.6.214:3306,192.168.6.215:3306
# 模式:讀還是寫
mode = read-only
routing_strategy=first-available
[keepalive]
interval = 60

這里從節(jié)點負(fù)載均衡配置采用first-available，優(yōu)先使用 HTAP 服務(wù)器。若專屬 HTAP 服務(wù)器宕機(jī)，可自動切換使用主節(jié)點查詢；

啟動 MySQL Router；

$ mysqlrouter --config /etc/mysqlrouter.conf &

查看監(jiān)聽端口是否啟用；

$ netstat -ntlp |grep mysqlrouter
tcp6       0      0 ::1:7001                :::*                    LISTEN      14404/./mysqlrouter 
tcp6       0      0 ::1:7002                :::*                    LISTEN      14404/./mysqlrouter

這里演示的是主從復(fù)制模式，所以有讀寫兩個端口。在新版本的 MySQL Router 中，在原先的6446、6447端口上，新增一個6450端口，支持讀寫分離；

測試只讀端口是否只連接專屬 HTAP 節(jié)點；

$ for ((i=0;i<=3;i++));do mysql -h192.168.6.215 -uroot -p -P7002 -e"select @@server_id;";done;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

自此構(gòu)建高效 HTAP 服務(wù)器架構(gòu)（主從復(fù)制）完成！

責(zé)任編輯：武曉燕來源： GreatSQL社區(qū)