是時候聊一聊 ProxySQL 功能測試了

作者 | 林雅婷

編輯 | 冬梅

ProxySQL 是 MySQL 的高性能、高可用性、協議感知代理。支持包括讀寫分離、故障轉換、query 的過濾和路由等功能。本文將從 Proxysql 的基本功能測試、異常情況測試來聊聊 ProxySQL 功能。

1. 背景

ProxySQL 能爲數據庫的高可用和拓展提供以下兩點服務：

1. 故障轉移。當主節點發生故障時，MGR 通過選舉提升另一節點爲主節點。亟需前端代理爲客戶端提供統一的入口，避免連接失敗。

2. 讀寫分離。將讀寫請求分流至不同的數據庫後端，靈活應對各種讀寫場景。

本文實驗採用 ProxySQL+MySQL+MGR 的架構。包括兩臺 ProxySQL 服務及三臺 MySQL 服務

1. 操作系統：Debian GNU/Linux 10 (buster)

2. Proxysql 版本：2.1.1

3. MySQL 版本：5.7.29

4. 基本功能

2.1 配置原理

ProxySQL 支持動態配置，因此首先了解一下它的三層配置架構 runtime、memory、disk/config。

• 第一層是 runtime，即運行時配置，用戶無法直接操作更改，必須從 memory 中加載。

• 第二層是 memory，用戶通過此界面查看 / 編輯 ProxySQL 配置表。

• 第三層是 disk/config，用於持久化 memory 中的配置。

2.2 ProxySQL 基本配置

以下表只截取重要的幾個表字段說明，完整的表結構請參照 https://proxysql.com/documentation/main-runtime/

1. 用戶表 mysql_users

   注：用戶表並不實現 host/ip 限制，在規則表中實現

2. 羣組表 mysql_group_replication_hostgroups

3. 服務表 mysql_server 

在編輯配置表後，LOAD XXX TO RUNTIME 來加載到運行時，SAVE XXX TO DISK 來持久化到磁盤

注：Mysql 的組複製搭建及配置此處不再贅述，可參照 https://dev.mysql.com/doc/refman/5.7/en/group-replication.html

2.3 轉發規則

代理轉發是 ProxySQL 重要功能，實現了根據用戶、IP、數據庫、規則轉發功能。

規則表 mysql_query_rules：

命中規則狀態查看錶

下面討論幾種轉發方式（以下 query 都爲自動提交，不顯式開啓事務）：

2.3.1 根據用戶轉發

當不配置任何規則時，根據用戶表的 default_hostgroup，default_schema 配置轉發至對應組和數據庫

2.3.2 根據訪問 ip 轉發

根據訪問 ip 轉發，可實現 ip 白名單限制

1. 設置一條白名單（注意 rule_id 最小，並且 apply 設置爲 0）insert into mysql_query_rules (rule_id,active,client_addr,match_digest,flagOUT,apply)values(1,1,'XX.XXX.XX.3','.',10,0);

2. 設置其他轉發規則，且 flagIN 承接白名單的 flagOUT

   insert into mysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,flagIN,apply) values(100,1,'test','^select',1,10,1);

3. 插入一條禁止其他所有 ip 訪問的黑名單 (rule_id 設置爲 max(rule_id)，並且 apply=1)

   insert into mysql_query_rules (rule_id,active,match_digest,error_msg,apply)values(100000,1,'.','Access banned, maybe your IP is not allowed to do this',1);

   查詢效果

   1、命中白名單

   2. 命中黑名單

缺陷：只支持 ip，不支持域名

2.3.3 根據數據庫轉發

插入兩條規則 (mysql_user 中只設置 default_hostgroup，不設置 default_schema)

1. insert into mysql_query_rules(rule_id,active,username,schemaname,match_digest,destination_hostgroup,flagIN,apply) values(100,1,'test','A',^select',1,10,1);

2. insert into mysql_query_rules(rule_id,active,username,schemaname,match_digest,destination_hostgroup,flagIN,apply) values(100,1,'test','B','^select',3,10,1);

分別測試了 SQL

總結：不符合預期

1. 不利用 use databases 並且不命中其他規則，默認轉發到用戶 default_hostgroup

2. Use database，不論後面跟什麼，都以規則中設置的 destination_hostgroup 爲準

2.3.4 根據規則轉發

有效的規則設置可以幫助實現讀寫分離

說明：在 mysql_query_rules 中的 match_digest/match_pattern 字段設置正則匹配規則，優先匹配 match_pattern

插入兩條匹配規則：

1. insert into mysql_query_rules(rule_id,active,username,match_pattern,destination_hostgroup,flagIN,apply) values(100,1,'test','^insert',1,10,1);

2. insert into mysql_query_rules(rule_id,active,username,match_pattern,destination_hostgroup,flagIN,apply) values(200,1,'test','^select',3,10,1);

sql 執行效果

2.3.5 根據耗時語句重寫規則

proxysql 的語句重寫是規則轉發的一重要特性。proxysql 對 query 進行指紋化處理後，統計查詢耗時。

用戶通過統計信息可以重新分配查詢路由或者重寫 query

1. 查看統計信息

2. 可以看到第二條查詢語句耗時較長，將其特殊轉發至其他組

insert into mysql_query_rules (rule_id,active,username,flagIN,digest,destination_hostgroup,apply) values (100,1,'test',0,'0x681748B5ABB3CFCA',3,1);

3. 再次觀察，此 query 被轉發至羣組 3

2.3.6 查詢緩存

說明：每個查詢緩存記錄的 key 是根據 username + schemaname +SQL 做 hash 運算出來的

這裏的 SQL 是完整的 包含參數 SQL 語句，而 非參數化後的語句，如果 SQL 語句進行了重寫，則使用重寫後的完整的 SQL 語句參與 hash 運算，即相同 digest 的語句只要參數不相同，會分別緩存

1. 根據查詢用戶全部進行緩存

INSERT INTO mysql_query_rules (active,username,cache_ttl) VALUES (1,"test",120000);

只要是 test 用戶的查詢語句都會進入緩存，hostgroup 值爲 -1

2. 根據數據庫進行緩存

只對 A 數據庫的查詢進行緩存

3. 根據查詢規則進行緩存

update mysql_query_rules set match_pattern='^select \* from';

前後對比

select count(*) 始終不緩存

select * from t where id=?根據 id 的值不同，第一次不緩存，第二次緩存

2.4 異常情況

proxysql 的另一個重要功能，即在發生故障轉移時，爲客戶端提供同一的入口。本節討論 mysql 服務節點異常和網絡異常情況下，proxysql 對於讀寫流量的處理結果。

本實驗的 MGR 結構爲單主模式，一臺寫組 + 兩臺讀組

2.4.1 節點異常

寫服務 down

1)）停掉 XX.XXX.XX.3 上的 mysql 進程

MGR 自動推舉出新的 primary server，並將此服務的 read_only 變量設置爲 NO，ProxySQL 通過監控自動監測並更新服務信息

2）把掛掉的機器拉起來，開啓組複製，它作爲讀節點重新加入

• 讀寫請求都正常轉發至唯一寫組

• 一讀一寫服務 down

  剩下的一個讀節點提升爲寫節點，同樣寫讀請求可以正常轉發。

2.4.2 網絡異常

Proxysql 參數說明：

• 監視 mysql 後端狀況

mysql-monitor_connect_interval: 代理的 Monitor 模塊嘗試連接到所有 MySQL 服務器以檢查它們是否可用的時間間隔。默認 600ms.

mysql-monitor_connect_timeout: 連接超時時間，默認 2000ms，

• Proxysql 監視組複製參數：

mysql-monitor_groupreplication_healthcheck_timeout：監控組複製成員是否健康的閾值時間, 默認 800ms

mysql-monitor_groupreplication_healthcheck_interval: 監視組複製狀態的心跳間隔，如果成員狀態不可得，則被暫時置爲 shunned(由 mysql_galera_hostgroups.max_transactions_behind 列控制)，默認 1000ms

mysql-monitor_groupreplication_healthcheck_max_timeout_count:  設置 ProxySQL 在脫機之前在組複製節點上進行超時檢查的最大次數。默認 3 次

Mysql 參數說明

group_replication_unreachable_majority_timeout:

注意：mysql5.7 默認成員被驅逐的時間限制是 5s

Mysql8.0 可以設置 group_replication_member_expel_timeout=N，說明在 5s 沒有響應之後，再等待 Ns 後驅逐

模擬網絡延遲方法

注意不要超過 10s，否則 ssh 也連不上了

sudo tc qdisc add dev eth0 root netem delay 10s
恢復
sudo tc qdisc del dev eth0 root

2.4.1 讀組延遲

• 參數 1: 網絡延遲 0.5s，但是還沒達到被 shunned(ProxySQL) 的時限 (1s)

表現:   可以正常讀寫，但是出現了讀寫都出現了延遲，觀察 max_time(1 秒 =1000 毫秒 =1000000 微秒，輸出結果爲微秒)

原因：此時 mgr 和 proxysql 都認爲機器狀況正常，所以正常地分別轉發到兩個讀組。

• 參數 2: 網絡延遲 1s，達到被 shunned 的時限 (800ms)

查看延遲情況: 可以正常讀寫，但是 insert 語句耗費時間比較長，select 語句時間正常。

原因：因爲 mgr 並沒有斷掉，所有 mgr 機制要求全部的成員都插入了數據，才能夠返回。而 select 語句全部被轉發到不延遲的讀組。

• 參數 3：網絡延遲 6s，節點被驅逐出 mgr

觀察 proxysql 日誌：
MySQL_Monitor.cpp:1543:monitor_group_replication_thread(): [WARNING]XX.XXX.XX.5:3306 : group replication health check timeout count 3. Maxthreshold 3.
MySQL_Monitor.cpp:1561:monitor_group_replication_thread(): [ERROR] ServerXX.XXX.XX.5:3306 missed 3 group replication checks. Number retries 3, Assumingoffline

觀察 mysql 日誌：
2021-05-18T10:55:59.505363+08:00 0 [ERROR] Plugin group_replication reported:'Member was expelled from the group due to network failures, changing memberstatus to ERROR.'

表現：讀寫仍比不延遲時快了一點，因爲只剩一個讀組。

2.4.2 讀組全部延遲

延遲 0.5s 與上一組實驗類似，讀寫延遲

延遲 6s 可以預測只剩下一臺讀組，請求都轉發至此

• 兩臺讀組都延遲 1s

這時，讀請求被卡死。寫請求正常

mysql-utest -ptest -h XX.XXX.XX.3  -P6033 -e"select * from B.t where id=1"

ERROR 9001 (HY000) at line 1: Max connect timeout reached while reachinghostgroup 3 after 10000ms

恢復網絡後，讀請求正常轉發

2.4.3 寫組延遲

延遲 0.5s 與上一組實驗類似，讀寫延遲

• 寫組延遲 1s

表現：寫請求無法寫入，讀請求正常

• 寫組延遲 6s

XX.XXX.XX.5 被提升爲寫組，讀寫請求正常轉發

表現：讀寫請求正常轉發，其中一臺讀組提升爲寫組

總結

1. 網絡延遲 800ms 以內，會造成讀寫請求延遲，但是功能正常

2. 網絡延遲 800ms~5s 內，可能造成讀寫失敗。是 proxysql 和 mgr 判斷 mysql 服務不正常的時長差異造成。

3. 網絡延遲 5s 以上，proxysql 和 mgr 均判定爲不正常狀態。讀寫正常。

方法：可通過 proxysql 參數mysql-monitor_groupreplication_healthcheck_timeout來調整狀態健康監測時長，而 mgr 目前沒有調整爲 5s 內的辦法。

3. 總結

本文通過對 proxysql+mgr 架構的測試，得出能夠基本滿足故障轉移和讀寫分離需求的結論。

規則功能方面：

1. 數據庫轉發功能無法支持 database.table 形式 sql。

2. 規則配置時，需正確理解 digest, match_digest, match_pattern 的含義，推薦首先使用 match_pattern。

異常情況方面：

1. mysql 服務節點宕機，proxysql 和 mgr 能夠實現正常的故障轉移恢復

2. 針對網絡延遲嚴重情況，可能出現無法讀或寫情況。

作者簡介：

林雅婷，網易遊戲技術部高級數據庫系統工程師。平時熱衷研究關於 MySQL 的高可用架構、備份恢復、SQL 調優等技術。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/Z5zEFuHb2f50xWcO_h9erg