ProxySQL - MySQL MGR 讀寫分離架構的 Sysbench 只讀壓測報告

作者 | 雷宏婧

編輯 | 冬梅

前    言

在大量併發讀請求、讀多寫少的業務場景下，本文利用 Sysbench 性能測試工具，調研基於【負載均衡 + ProxySQL Cluster + MySQL MGR 的讀寫分離架構】能否有效利用橫向擴展的 MySQL 實例的讀能力，並最終提高應用系統 QPS。

1. MySQL Group Replication（MGR）於 2016 年 12 月被推出，提供了高可用、高擴展、高可靠的 MySQL 集羣服務。但其僅解決了數據同步問題和集羣內部的自動故障轉移。當 Master 宕機，應用系統可能需要修改數據庫連接地址，才能保證服務的可用性。爲解決上述問題，可在 MRG 上層增加代理層，例如 ProxySQL。

2. ProxySQL 於 2015 年被推出，是一個開源、高性能、高可用性、協議感知的 MySQL 代理。

   1. 可通過每個節點的 read_only 值，自動調整它們是屬於讀組還是寫組；

   2) 可定製基於用戶、基於 schema、基於語句的規則對 SQL 語句進行路由，實現讀寫分離；

   3. 支持搭建 ProxySQL Cluster 來達到高可用，節點之間的配置可自動同步。

3. 負載均衡是將流量分發至多臺節點設備上處理的服務。

   1. 可通過消除單點故障，提升應用系統的可用性；

   2. 可減緩大量的併發訪問，提高應用系統的處理能力。

4. Sysbench 是一個開源的、模塊化的、跨平臺的多線程性能測試工具。

5. 壓測目的

基於 Sysbench 的 oltpreadonly 壓測模式，對比【負載均衡 + ProxySQL Cluster + MGR 的讀寫分離】和【應用直連 MySQL Master】這兩種架構的只讀性能:

1. 建立讀寫分離架構的只讀性能基線數據；

2. 驗證讀寫分離架構在大量併發讀請求場景下的有效性；

3. 分析各模塊和參數對讀寫分離架構性能的影響。

4. 壓測結論

在 Sysbench oltpreadonly 壓測模式下，【4 層負載均衡 +ProxySQL Cluster+MGR 讀寫分離】架構的 QPS 與併發線程數關係如下表所示。

ps：“/” 表示由於 Sysbench 機器 CPU 耗盡，未能完成測試，無實驗結果。

2.2. 只讀場景下讀寫分離架構的有效性

首先簡單瀏覽下實驗對比架構和結果，

ps：“/” 表示由於 Sysbench 機器 CPU 耗盡，未能完成測試，無實驗結果。實驗結果表明：

1. 在不引入負載均衡、ProxySQL Cluster 等中間件的理想情況下，【應用直連 MGR 2 個只讀實例】QPS 最大值能達到 100w，爲【應用直連 MySQL Master】的只讀 QPS 最大值 37w 的 2.7 倍。該結果驗證了 MGR 架構在大量併發讀請求場景下的有效性。

但實際上，如要保證應用系統高可用，則需引入負載均衡、ProxySQL Cluster 等中間件，而這些中間件或多或少會帶來性能損失。實驗發現，【4 層負載均衡 +ProxySQL Cluster+MGR 讀寫分離】架構的只讀 QPS 最大值爲 89w，約爲【應用直連 MySQL Master】的只讀 QPS 最大值 37w 的 2.4 倍，該結果驗證了【4 層負載均衡 +ProxySQL Cluster+MGR 讀寫分離】架構在大量併發讀請求場景下的有效性。

2.3. 各模塊和參數對讀寫分離架構性能的影響

1. 【4 層負載均衡 +ProxySQL Cluster+MGR 讀寫分離】架構的 QPS 最大值約爲【直連 MGR 2 個只讀實例】QPS 最大值 100w 的 89%。其中，ProxySQL Cluster 帶來約 11% 的性能損失，負載均衡幾乎沒有帶來性能損失。但是 ProxySQL 的 CPU 佔用率最高僅 57%，還需後續探索能否進一步有效利用 ProxySQL。

2. 根據 https://github.com/sysown/ProxySQL/issues/1724，參考 CPU 核數增加 ProxySQL 的 mysql-threads 變量值，即增加 ProxySQL 用於處理 MySQL 流量的後臺線程數，能有效提升 QPS（如將線程數從 4 增加至 16，QPS 提升了 3.3 倍），但目前還沒壓測出 ProxySQL 的 CPU 利用率提升到 100% 的場景。

3. 橫向拓展 ProxySQL 實例數目，能有效提升 QPS（實例數從 1 增加至 2，QPS 提升 1 倍）。

4. 將 7 層負載均衡換成 4 層，由在應用層進行流量分發改成在傳輸層，降低網絡性能損耗，在實驗中提升了 1 倍 QPS。

5. 根據 https://ProxySQL.com/blog/benchmarking-ProxySQL-144/，增大 ProxySQL 的 mysql-max_stmts_per_connection 變量值（20 增加至 100），讓單個連接可以處理更多的 prepared 語句，但實驗中未能影響 QPS。

6. 壓測詳情

3.1. 壓測環境

此外，還安裝了 nodeexporter、mysqlexporter、proxysql_exporter 來監控 OS、MySQL 和 ProxySQL，方便定位問題。

• 每秒執行請求數 QPS（Queries Per Second）數據庫每秒執行的 SQL 數，包含 INSERT、SELECT、UPDATE、DETELE、COMMIT 等。QPS 值越高越好。

• 95% Latency (ms) 95% 請求的延遲時間表徵的是全部 query 請求中的 95% 在發出至收到結果的平均往返時間。延遲越小越好。

爲減少誤差，每輪實驗重複 3 次。每次任務執行完之後，等待 300s，讓系統及時處理未完成任務，才進入下一輪壓測。壓測後除了利用 Sysbench 自帶的 cleanup 清理數據，還額外把 binlog 清理乾淨，以防磁盤空間變少而影響下一次壓測。其他模塊設置見下文。

• MGR：單主模式。共 3 個節點，其中 1 個只寫節點，2 個只讀節點。max_connection 設爲 3000。

• Master-Master：主主同步，僅其中 1 個 Master 提供讀寫服務。max_connection 設爲 3000。

• mysql_user 表的 transaction_persistent 字段：設置爲 1，表示在某節點內啓動的事務將保留在該節點內，而與其他轉發規則無關。用於避免以下問題：一個事務有混合的讀操作和寫操作組成，事務未提交前，如果事務中的讀操作和寫操作路由到不同節點，讀取到的結果是髒數據。

• mysql_servers 表的 max_connections：允許連接到該後端實例的最大連接數，不能大於 MySQL 設置的 max_connections，因此設爲 3000。

• mysql_group_replication_hostgroups 表：配置 MGR writerhostgroup、readerhostgroup 等組別對應的 hostgroupid。ProxySQL 會通過視圖來監控 MGR 節點是否正常，是否開啓了只讀、擠壓事務數等來調整單個 MGR 節點所屬的 hostgroupid，具體調整結果可在 runtime_mysql_servers 中查看。    insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind,comment) values(1,2,3,4,1,1,0,100,'mgr-test');

  -- 可以看出有寫組有 1 個節點，讀組有 2 個節點，均在正常工作

• 查詢規則配置：根據 SQL 的正則表達式匹配，讀請求轉發至讀組，寫請求轉發至寫組。

• 全局變量 mysql-threads：是 ProxySQL 用於處理 MySQL 流量的後臺線程數。默認值爲 4，實驗中發現，增加值至 16 可大幅提升 QPS，因此除了該變量的參數調優實驗，其他實驗中該變量值均爲 16。    

• 實驗基於 Sysbench 的 oltpreadonly 只讀模式。該模式下，一個事務包含 14 個讀 SQL（10 條主鍵點查詢、4 條範圍查詢）。

• oltpreadonly 模式的壓測命令

準備數據:

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=dbtest --tables=1 --table-size=10000000 --report-interval=1 --threads=XXX --rand-type=uniform --time=120 --auto-inc=on /usr/local/share/sysbench/oltp_read_only.lua prepare

運行 workload:

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=dbtest --tables=1 --table-size=10000000 --report-interval=1 --threads=XXX --rand-type=uniform --time=120 --auto-inc=on --skip_trx=on /usr/local/share/sysbench/oltp_read_only.lua run

清理數據:

sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=dbtest --tables=1 --table-size=10000000 --report-interval=1 --threads=XXX --rand-type=uniform --time=120 --auto-inc=on /usr/local/share/sysbench/oltp_read_only.lua cleanup

普通變量：

• time：壓測總持續時間（秒），超時後任務未完也會被終止。

• threads：併發壓測的線程數。取值範圍 [16,32,64,128,256,512,1024,1500,2048,2500,3000,3500,4096]。

重點變量：

• skip_trx[=on|off]：默認爲 off，即啓動顯式事務；值爲 on 時，不啓動顯式事務，以 AUTOCOMMIT 模式執行所有查詢。

• 壓測時設置：sysbench --skiptrx=on；ProxySQL 的 mysqluser 表的 transaction_persistent=1。原因如下：

• ProxySQL 的 mysqluser 表的 transactionpersistent 字段設爲 1 時，在某節點內啓動的事務將保留在該節點內，而與其他轉發規則無關。用於避免以下問題：一個事務有混合的讀操作和寫操作組成，事務未提交前，如果事務中的讀操作和寫操作路由到不同節點，讀取到的結果是髒數據。因此，如果不開啓 skip_trx，sysbench 所有請求都會被 ProxySQL 轉發到寫組，如此便測不了讀寫分離的性能。

• sysbench 默認使用 prepared statements，因本實驗需要測試使用 prepared statements 的情況，故在此不作關閉該功能的參數說明。

• 設置變量 --mysql-host=[host1,host2,...,hostN]，即可對多個 MySQL 同時發起讀請求。可用於併發壓測多個 MySQL 實例時的 QPS。

https://github.com/akopytov/sysbench/issues/19

總共設計了 6 個實驗場景（架構圖詳見實驗結果分析），實驗目的如下：

實驗目的：

獲取通過應用（sysbench）直連 MGR 的 2 個只讀實例數所能帶來的 QPS 上限，確認該上限和應用直連 mysql Master-Master 中其中 1 臺的 QPS 差異。

實驗結果：

ps：“/” 表示由於 Sysbench 機器 CPU 耗盡，未能完成測試，無實驗結果。

• MGR QPS 上限約 100w，約爲 Master-Master 的 2.67 倍。

實驗目的：

在儘可能減少應用和 ProxySQL 之間網絡延遲的情況下，確認增加 ProxySQL 中間件會帶來的性能損失

實驗結果：

ps：“/” 表示由於 Sysbench 機器 CPU 耗盡，未能完成測試，無實驗結果。

• single ProxySQL+MGR QPS 最大 48w，約爲 MGR 的 48%。

• single ProxySQL+MGR QPS 最大時，ProxySQL 實例 CPU 佔用率最高爲 57%。

實驗結論：

在該實驗中，ProxySQL Cluster 帶來約 48% 的性能損失，但此時 ProxySQL 的 CPU 佔用率並不算很高，值得後續探索能否進一步有效利用 ProxySQL。

實驗目的：

確認橫向拓展 ProxySQL 實例數目能否進一步提升 QPS

實驗結果：

ps：“/” 表示由於 Sysbench 機器 CPU 耗盡，未能完成測試，無實驗結果。

• ProxySQL Cluster+MGR QPS 上限約 89w，約爲 single ProxySQL+MGR 的 1.85 倍。

• ProxySQL Cluster+MGR QPS 最大時，ProxySQL 實例 CPU 佔用率最高爲 56%。

實驗結論：

橫向拓展 ProxySQL 實例數目可以進一步提升 QPS 至 89w，相對接近 MGR 的上限 100w。

實驗目的：

增加讀寫分離架構中必不可少的負載均衡服務，並確認其帶來的性能損失

實驗結果：

ps：“/” 表示由於 Sysbench 機器 CPU 耗盡，未能完成測試，無實驗結果。

• 增加負載均衡後，QPS 最大值爲 42w，約 MGR 上限的 50%，僅爲 Master-Master 的 1.135 倍。

實驗結論：

增加負載均衡導致性能損失近 50%，可能是因爲網絡、配置問題，需要進一步排查。

實驗目的：

4 層負載均衡工作在 OSI 模型的傳輸層（基於 IP+ 端口），7 層工作在應用層（基於 URL）。

理論上，7 層負載均衡會帶來更多的網絡性能損耗。因此嘗試調整爲 4 層負載均衡，以減少性能損失。

實驗結果：

ps：“/” 表示由於 Sysbench 機器 CPU 耗盡，未能完成測試，無實驗結果。

實驗結論：

將 7 層負載均衡換成 4 層負載均衡後，QPS 最大值爲 89w，負載均衡幾乎沒帶來性能損失。

實驗目的：

根據 https://github.com/sysown/ProxySQL/issues/1724，mysql-threads 變量是 ProxySQL 用於處理 MySQL 流量的後臺線程數，理論上，根據機器 CPU 核數來調整該變量，可提升 ProxySQL 性能。因此嘗試分析該參數對性能的影響。

實驗結****果：

ps：“/” 表示由於 Sysbench 機器 CPU 耗盡，未能完成測試，無實驗結果。

實驗結論：

根據機器 CPU 核數來增加 ProxySQL 的 mysql-threads 變量值，可一定程度上提升 QPS。

4. 總 結

5. 【4 層負載均衡 + ProxySQL Cluster + MGR 讀寫分離】架構適用於在大量併發讀請求場景，只讀 QPS 最大能達到 89w，約爲【應用直連 MySQL Master】的只讀 QPS 最大值 37w 的 2.4 倍。

6. 參考機器的 CPU 核數增加 ProxySQL 的 mysql-threads 變量值，即增加 ProxySQL 用於處理 MySQL 流量的後臺線程數，能有效提升 QPS（如將線程數從 4 增加至 16，QPS 提升了 3.3 倍）。

7. 橫向拓展 ProxySQL 實例數目，能有效提升 QPS（實例數從 1 增加至 2，QPS 提升 1 倍）。

8. 將 7 層負載均衡換成 4 層，由在應用層進行流量分發改成在傳輸層，能降低網絡性能損耗並提升 QPS。

9. 本次實驗中，ProxySQL Cluster 帶來約 11% 的性能損失，負載均衡幾乎沒有帶來性能損失。但是 ProxySQL 的 CPU 佔用率最高僅 57%，還需後續探索能否進一步有效利用 ProxySQL。

參考文獻：

1. https://dev.MySQL.com/doc/refman/5.7/en/group-replication.html

2. https://ProxySQL.com/documentation/ProxySQL-Threads/

3. https://ProxySQL.com/blog/ProxySQL-vs-maxscale-persistent-connection-response-time/

4. https://www.percona.com/blog/2020/08/28/ProxySQL-overhead-explained-and-measured/

5. https://github.com/sysown/ProxySQL/issues/1724

6. https://www.percona.com/blog/2017/04/10/ProxySQL-rules-do-i-have-too-many/

作者簡介：

雷宏婧，網易遊戲 技術部高級數據庫系統工程師。參與海量玩家數據庫生產環境故障排查和優化，熱衷於研究 MySQL 技術原理、災難備份和高可用方案。

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