GreatSQL 構(gòu)建高效 HTAP 服務(wù)架構(gòu)指南
引言
全文約定：$為命令提示符、greatsql>為 GreatSQL 數(shù)據(jù)庫提示符。在后續(xù)閱讀中，依據(jù)此約定進(jìn)行理解與操作。
Rapid 引擎
從 GreatSQL 8.0.32-25 版本開始，新增Rapid存儲(chǔ)引擎，該引擎使得 GreatSQL 能滿足聯(lián)機(jī)分析（OLAP）查詢請(qǐng)求。
GreatSQL Rapid引擎性能表現(xiàn)優(yōu)異，在32C64G測(cè)試機(jī)環(huán)境下，TPC-H 100G測(cè)試中22條SQL總耗時(shí)僅需不到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)建一個(gè)高效的 HTAP 服務(wù)架構(gòu)，以此來提升 GreatSQL 的查詢效率。
服務(wù)架構(gòu)圖
圖片
本服務(wù)架構(gòu)采用的是 GreatSQL 主從復(fù)制，主節(jié)點(diǎn)采用默認(rèn) InnoDB 引擎，從節(jié)點(diǎn)使用輔助引擎 Rapid 加速查詢構(gòu)建專屬 HTAP 只讀節(jié)點(diǎn)。加上 MySQL Router 等之類的代理/中間件負(fù)責(zé)讀寫分離來完成 HTAP 服務(wù)架構(gòu)。
采用此 HTAP 架構(gòu)可獲得以下收益
- 高查詢效率：Rapid 引擎的引入使得從節(jié)點(diǎn)能夠加速查詢處理，特別適用于 OLAP（聯(lián)機(jī)分析處理）場(chǎng)景。
- 高負(fù)載均衡：利用代理/中間件實(shí)現(xiàn)讀寫分離，確保主節(jié)點(diǎn)（寫操作）和從節(jié)點(diǎn)（讀操作）負(fù)載均衡。
- 高并發(fā)性能：主節(jié)點(diǎn)上采用 InnoDB 響應(yīng)高并發(fā)事務(wù)請(qǐng)求，確保業(yè)務(wù)需求寫入性能。
- 高靈活和擴(kuò)展：GreatSQL 的可插拔存儲(chǔ)引擎架構(gòu)使得系統(tǒng)可以根據(jù)需要選擇適合的存儲(chǔ)引擎。Rapid 引擎作為輔助引擎，可以動(dòng)態(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é)點(diǎn) |
192.168.6.215:3306 | MySQL Router | 8.4.0 TLS | 代理/中間件?？筛鶕?jù)需求靈活替換 |
安裝 GreatSQL
GreatSQL 安裝版本為 8.0.32-25 版本，并分別安裝兩個(gè)實(shí)例 GreatSQL
安裝步驟詳見：https://greatsql.cn/docs/8.0.32-25/4-install-guide/0-install-guide.html
部署主從復(fù)制
主節(jié)點(diǎn)建立賬戶并授權(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é)點(diǎn)狀態(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é)點(diǎn)服務(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)建成功生成測(cè)試數(shù)據(jù)
主庫寫入數(shù)據(jù)
往主庫生成數(shù)據(jù)；
-- 創(chuàng)建測(cè)試數(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)從庫此時(shí)也會(huì)復(fù)制主庫的十萬行數(shù)據(jù)；
# 從庫
greatsql> SELECT COUNT(*) FROM htap_test_db.orders;
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.01 sec)如果在主庫或從庫進(jìn)行一個(gè)復(fù)雜 SQL 查詢，需要用時(shí) 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;在從庫運(yùn)行三次結(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é)點(diǎn)
以下所有操作都在 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í)際情況設(shè)置；
查看該 SQL 的執(zhí)行計(jì)劃，注意關(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òng)增量導(dǎo)入任務(wù)
因?yàn)樵谏a(chǎn)環(huán)境中數(shù)據(jù)是無時(shí)不刻在產(chǎn)生，所以需要啟用增量導(dǎo)入，才可保證最新數(shù)據(jù)始終在 Rapid 引擎內(nèi)；
啟動(dòng)增量導(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 引擎加速查詢。
請(qǐng)注意，Rapid 引擎在增量導(dǎo)入數(shù)據(jù)時(shí)可能存在短暫延遲。大量 Insert、Delete 數(shù)據(jù)，可能無法立即通過 Rapid 引擎查詢到這些最新變動(dòng)的數(shù)據(jù)。等增量任務(wù)導(dǎo)入完成后 Rapid 引擎才能查詢到最新變動(dòng)的數(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(*)速度會(huì)很快，若沒啟用 Rapid 引擎則可能耗時(shí)較長(zhǎng)；
查看執(zhí)行計(jì)劃，從 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é)點(diǎn)完成，接下來是實(shí)現(xiàn)讀寫分離，當(dāng)然一主一從的情況下是不太需要讀寫分離中間件的，要中間件的情況是怕 HTAP 專屬服務(wù)器宕機(jī)，這時(shí)候主節(jié)點(diǎn)就要負(fù)責(zé)讀寫了。
實(shí)現(xiàn)讀寫分離
這里使用的是 MySQL Router 中間件實(shí)現(xiàn)的讀寫分離，如果有其它讀寫分離中間件，例如 MySQL Proxy 等也可以替換。
安裝 MySQL Router
下載過程省略，可自行到 MySQL 網(wǎng)站上下載；
這里選擇的是最新的長(zhǎ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é)點(diǎn)故障轉(zhuǎn)移配置
[routing:basic_failover]
# 寫節(jié)點(diǎn)地址
bind_address=192.168.6.215
# 寫節(jié)點(diǎn)端口
bind_port = 7001
# 模式，讀寫
mode = read-write
destinations = 192.168.6.215:3306
routing_strategy=first-available
# 從節(jié)點(diǎn)負(fù)載均衡配置
[routing:balancing]
# 綁定的IP地址
bind_address=192.168.6.215
# 監(jiān)聽的端口
bind_port = 7002
# 連接超時(shí)時(shí)間
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é)點(diǎn)負(fù)載均衡配置采用first-available，優(yōu)先使用 HTAP 服務(wù)器。若專屬 HTAP 服務(wù)器宕機(jī)，可自動(dòng)切換使用主節(jié)點(diǎn)查詢；
啟動(dòng) 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ù)制模式，所以有讀寫兩個(gè)端口。在新版本的 MySQL Router 中，在原先的6446、6447端口上，新增一個(gè)6450端口，支持讀寫分離；
測(cè)試只讀端口是否只連接專屬 HTAP 節(jié)點(diǎn)；
$ 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ù)制）完成！
