MYSQL 效能測試方法 - 基準測試（benchmarking）

MYSQL 效能測試方法 - 基準測試（benchmarking）

近期有碰到 MYSQL 的效能測試需求，因此網上收集了 MYSQL 的測試方法，並使用 sysbench 完成了幾次這次，這裡分享下這次的總結（大多是參考資料裡貼來的）。

基準測試

相較於全鏈路測試，基準測試是在特定時間點透過效能測試設定一個效能基準線。當系統軟硬體環境變動後，可重新執行基準測試，以評估這些變化對效能的具體影響。
而針對資料庫的基準測試，可以評估當前配置（含硬體、作業系統、資料庫設定等）下的效能表現，確定效能閾值，並根據實際需求調整配置。這為資料庫系統的硬體採購、架構設計及升級提供了關鍵資料支援。可以理解為針對資料庫基礎效能的一種壓力測試，但這個測試不涉及業務邏輯，測試資料由工具生成，增刪改查是固定指令碼。

衡量指標

效能基準線需要關注的指標：
系統負載
·CPU：%user、%idle、%sys、%iowait # 最通用的幾個指標·IO：tps、await、svctm、%util
·記憶體：free（free、shared、buffers、cached）、used，以及 swap
記憶體的利用率越多越好，所以我們主要關注空閒、使用、還有 swap ，si so
當前是否使用 swap 是否頻繁使用 swap 也需要關注
MYSQL 執行指標
tps、rt、lock、hit ratio、waits
rt = response time

lock = row lock、table lock
hit ratio = cache/buffer hit ratio
waits = Innodb_buffer_pool_wait_free / Innodb_log_waits / Table_locks_waited / Innodb_row_lock_current_waits / Innodb_row_lock_waits

支援資料庫基準測試的工具很多，當前選擇了 sysbench

測試策略

prepare：執行準備工作。例如，在磁碟上建立必要的測試檔案以進行 fileio 測試，或者在測試資料庫上新建 100 萬行資料以執行資料庫基準測試。
run：使用 testname 引數指定的壓測指令碼執行對應的測試。
cleanup：在測試結束後刪除臨時資料或檔案。

sysbench 線上安裝

編譯必需依賴

yum -y install make automake libtool pkgconfig libaio-devel

編譯 MySQL 必需依賴, 在 RHEL/CentoS 5 上替換為 mysql-devel

yum -y install mariadb-devel openssl-devel

編譯 PostgreSQL 必需依賴

yum -y install postgresql-devel

安裝 sysbench

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh  | sudo bash
sudo yum -y install sysbench

sysbench 命令列

sysbench 命令語法如下：

sysbench [options]… [testname] [command]

1. options（引數選項）：用於指定 sysbench 的併發度、壓測時長等引數。
2. testname（測試名稱）：用於指定 sysbench 的基準測試名稱，可選項包括 oltp_read_ write、oltp_read_only、oltp_write_only、oltp_insert、oltp_delete、oltp_update_index、oltp_update_non_index 等
3. command（測試命令）：用於指定 sysbench 執行什麼測試命令，可選項包括 prepare、run、cleanup 等。

options

常規選項

–threads=N：指定執行緒數，預設值為1，相當於sysbendh 0.5及之前版本中的–num-threads=N選項。
–events=N：指定總的請求數，預設值為0，表示不限制請求數，相當於sysbench 0.5及之前版本中的–max-requests選項。
–time=N：指定壓測時長，預設值為10s，相當於sysbench 0.5及之前版本中的–max-time=N選項。
–forced-shutdown=STRING：有效值為off、N、N%（預設值為off）。off表示不啟用強制關機功能；N表示在–time選項指定的時間到期後，再過N秒強制關機；N%表示在–time選項指定的時間到期後，再過–time*N%時間強制關機。
–thread-stack-size=SIZE：指定每個執行緒的堆疊大小，預設值為64KB。
–rate=N：限定事務速率（tps），預設值為0，表示不限制，相當於sysbench 0.5及之前版本中的–tx-rate=N選項。
–report-interval=N：指定中間統計結果報告的間隔時間，預設值為0，表示關閉中間統計結果報告輸出。
–report-checkpoints=[LIST,…]：用逗號分隔的一組列表值，這些值在執行sysbench壓測時被依次讀取，表示執行多少秒就列印一次統計報告（例如–report- checkpoints=10,20,30，表示當執行10s、20s、30s時分別列印一次統計報告。注意，該數值是指從執行sysbench開始到現在的時間），預設值為空，表示在–time選項指定的時間到期後才列印統計報告。
–debug[=on|off]：是否列印除錯資訊，預設值為off。
–help[=on|off]：是否列印幫助資訊，預設值為off。
–version[=on|off]：是否列印版本資訊，預設值為off。

偽隨機數建立選項

–rand-type=STRING：隨機數分佈型別，可選項包括uniform、gaussian、special、pareto，預設值為special。
–rand-spec-iter=N：隨機數生成的迭代次數，預設值為12次。
–rand-spec-pct=N：對特定隨機數分佈來說被視為“特殊”值的百分比，預設值為1。
–rand-spec-res=N：對特定隨機數分佈來說“特殊”值的百分比，預設值為75。
–rand-seed=N：隨機數發生器的種子。當該選項設定為0時，表示使用當前時間作為RNG種子。
–rand-pareto-h=N：用於指定pareto隨機分佈的h引數，預設值為0.2。

日誌選項

–verbosity=N：日誌列印的詳細程度，5表示列印debug級別以上的日誌，0表示只列印critical級別以上的日誌。預設值為3。
–percentile=N：在延遲時間統計中選擇哪個百分位數，可選範圍為(1～100)，預設值為95。如果設定為0，則表示禁用延遲時間統計功能。
–histogram[=on|off]：是否列印延遲時間直方圖報告，預設值為off。

常規資料庫選項

–db-driver=STRING：指定資料庫驅動程式（即指定資料庫型別），當前版本支援MySQL和PostgreSQL。
–db-ps-mode=STRING：prepare命令使用模式，有效值為auto和disable，預設值為auto，在高併發壓力下建議使用disable。
–db-debug[=on|off]：是否列印資料庫的除錯資訊，預設值為off。

MySQL 選項

–mysql-host=MySQL伺服器主機，預設值為localhost。
–mysql-port=MySQL伺服器埠號，預設值為3306。
–mysql-socket= MySQL伺服器Socket檔案目錄。
–mysql-user=連線MySQL伺服器的使用者名稱，預設值為sbtest。
–mysql-password=連線MySQL伺服器的密碼。
–mysql-db=連線MySQL伺服器的資料庫名，預設值為sbtest。
–mysql-ssl[=on|off]：連線MySQL伺服器是否使用SSL，預設值為off。
–mysql-ssl-cipher=連線MySQL伺服器使用SSL時的Cipher。
–mysql-compression[=on|off]：連線MySQL伺服器是否使用壓縮，預設值為off。
–mysql-debug[=on|off]：連線MySQL伺服器是否跟蹤所有的客戶端庫呼叫，預設值為off。
–mysql-ignore-errors=是否忽略MySQL返回的錯誤，預設值為[1213,1020,1205]。
–mysql-dry-run[=on|off]：是否空跑，只是呼叫MySQL客戶端API，但是不真正執行。

pgsql 選項

–pgsql-host= PostgreSQL伺服器主機，預設值為localhost。
–pgsql-port= PostgreSQL伺服器埠，預設值為5432。
–pgsql-user=連線MySQL伺服器的使用者名稱，預設值為sbtest。
–pgsql-password=連線MySQL伺服器的密碼。
–pgsql-db=連線MySQL伺服器的資料庫名，預設值為sbtest。

其他選項
透過使用如下命令來檢視額外的關於測試名稱（事務模型）命令選項，只需要任意指定一個測試名稱即可。

# 指定oltp_read_write測試名稱來檢視額外的幫助選項
[root@localhost ]# sysbench oltp_read_write help  
……
oltp_read_write options:
  –distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1]
  –sum_ranges=N Number of SELECT SUM() queries per transaction [1]
  –skip_trx[=on|off] Don’t start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
……

各選項解釋如下

–distinct_ranges=N：指定在每個事務中SELECT DISTINCT查詢的執行次數，預設值為1。
-sum_ranges=N：指定在每個事務中SELECT SUM()查詢的執行次數，預設值為1。
–skip_trx[=on|off]：指定在AUTOCOMMIT（自動提交）模式下是否需要跳過啟動顯式事務（使用START語句顯式啟動一個事務），預設值為off。
–secondary[=on|off]：指定是否需要使用一個二級索引來代替主鍵索引，預設值為off。
–create_secondary[=on|off]：指定除主鍵之外，是否還需要建立一個二級索引，預設值為on。
–index_updates=N：指定在每個事務中使用索引執行UPDATE語句的次數，預設值為1。
–range_size=N：指定在每個事務中範圍SELECT查詢的條件值，預設值為100。
–auto_inc[=on|off]：指定是否需要使用自增列的自增值作為主鍵值，如果不使用自增值，則使用sysbench自動生成的ID值作為主鍵值，預設值為on。
–delete_inserts=N：指定在每個事務中DELETE/INSERT組合語句的數量，預設值為1。
–tables=N：指定並行壓測的表數量。
–mysql_storage_engine=STRING：指定表的儲存引擎，預設值為InnoDB。
–non_index_updates=N：指定在每個事務中不使用索引執行UPDATE語句的次數，預設值為1。
–table_size=N：指定每個表的資料總量，預設值為10 000。
–pgsql_variant=STRING：當用PostgreSQL驅動程式執行時使用此PostgreSQL變體。目前唯一支援的變體是“redshift”。啟用後，將自動禁用create_secondary，並將–delete_inserts選項設定為0。
–simple_ranges=N：指定在每個事務中簡單範圍SELECT查詢（這裡指的是BETWEEN範圍查詢）的次數，預設值為1。
–order_ranges=N：指定在每個事務中SELECT ORDER BY查詢的次數，預設值為1。
–range_selects[=on|off]：指定是否需要開啟或關閉所有的範圍SELECT查詢，預設值為on。
–point_selects=N：指定在每個事務中單行SELECT查詢的次數，預設值為10。

備註項：

1. 每一種測試名稱對應的 Lua 指令碼中都定義了需要使用的 DML 測試語句型別，每一種 DML 語句型別都可以透過選項單獨指定在每一個事務中需要執行多少次。例如，在 oltp_read_write 測試名稱中，一共有 9 種 DML 語句型別，按照預設的每一種語句的執行次數計算，在每一個事務中一共有 18 條語句，每一種 DML 語句型別的預設執行次數如下：
2. * 簡單等值 SELECT 語句：預設為 10 次。

• 範圍 SELECT（BETWEEN）語句：預設為 1 次。
• SELECT SUM() 語句：預設為 1 次。
• SELECT ORDER BY：預設為 1 次。
• SELECT DISTINCT 語句：預設為 1 次。
• DELETE 和 INSERT 組合語句：預設為 1 次。
• 使用索引的 UPDATE 語句：預設為 1 次。
• 不使用索引的 UPDATE 語句：預設為 1 次。

在執行 oltp_read_write 測試時，從 MySQL 的 general_log 中抓取的每個事務的語句數量也證實了，在預設的配置下一個事務中的語句數量為 18 條

testname
testname 用於指定 sysbench 的基準測試名稱。基準測試包括：

1. oltp _*.lua，資料庫基準測試 Lua 指令碼集合。這是 DBA 日常經常需要用到的測試指令碼。
2. fileio，檔案系統級基準測試。
3. cpu，簡單的 CPU 基準測試。
4. memory，記憶體訪問基準測試。
5. threads，基於執行緒的排程器基準測試。
6. mutex，POSIX 互斥基準測試。

備註：在實際執行時，對於 Lua 新格式指令碼，可以只寫指令碼名稱（不寫.lua 字尾），如 oltp_read_only，不再需要像 sysbench 0.5 及之前版本那樣使用–test 選項來指定。

壓測結果指標

SQL 統計結果

該項輸出結果包括 sysbench 發起的讀/寫/其他/總計 SQL 查詢數量、總計事務數及每秒事務數、總計請求數及每秒請求數、總計錯誤數及每秒錯誤數、總計重連線數及每秒重連線數。
SQL statistics:
queries performed:
read: 49084
write: 9513
other: 11523
total: 70120
transactions: 3506 (350.33 per sec.)
queries: 70120 (7006.63 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

通用統計值

該項輸出結果包括總計執行的時間、所有的事件數量（這裡對應的是發起的 MySQL 事務數）。
General statistics:
total time: 10.0062s
total number of events: 3506

延遲時間統計結果

該項輸出結果包括延遲時間最低值、平均值、最高值、第 95% 位值、總計值。
Latency (ms):
min: 4.56
avg: 11.41
max: 39.24
95th percentile: 19.65
sum: 39997.58

壓測執行緒統計結果

該項輸出結果包括每個壓測執行緒的平均事件數及標準差、每個事務的平均執行時間及標準差。
Threads fairness:
events (avg/stddev): 876.5000/5.22
execution time (avg/stddev): 9.9994/0.00

壓測示例

如下為只讀場景示例，當前支援 mysql 壓測的場景有：

• 只讀-oltp_read_only
• 刪除-oltp_delete
• 索引更新-oltp_update_index
• 非索引更新-oltp_update_non_index
• 插入-oltp_insert
• 混合讀寫-oltp_read_write

場景 - 只讀

造數

sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=admin1 --mysql-password=68667109!@#1 --mysql-db=sbtest --table_size=2500 --tables=25 --time=60  oltp_read_only prepare

引數解釋：

• sysbench：這是命令列工具的名稱，用於啟動 Sysbench 測試。
• --db-driver=mysql：指定資料庫驅動為 MySQL。Sysbench 支援多種資料庫，透過這個引數指定要測試的資料庫型別。
• --mysql-host=localhost：指定 MySQL 資料庫伺服器的主機地址。這裡使用的是一個 IP 地址，表明測試將在遠端伺服器上執行。
• --mysql-port=3306：指定 MySQL 資料庫伺服器的埠號。3306 是 MySQL 的預設埠。
• --mysql-user=admin1：指定用於連線 MySQL 伺服器的使用者名稱。
• --mysql-password=68667109!@#1：指定用於連線 MySQL 伺服器的使用者密碼。在實際使用中，密碼應妥善保管，避免在命令列中明文顯示。
• --mysql-db=sbtest：指定要測試的資料庫名稱。在這個例子中，資料庫名為 sbtest。
• --table_size=2500：指定每個測試表中的資料行數。這裡每個表將包含 2500 行資料。
• --tables=25：指定要建立的測試表的數量。在這個例子中，將建立 25 個表。
• --time=60：指定測試執行的時間（秒）。在這個例子中，測試將執行 60 秒。
• oltp_read_only：指定要執行的測試型別。oltp_read_only 表示執行只讀的事務處理（OLTP）測試，這種測試主要評估資料庫的讀取效能。
• prepare：這個引數指示 Sysbench 執行測試前的準備工作，即建立所需的資料庫和表，並填充資料。這通常是測試過程的第一步。

壓測

sysbench  --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=admin1 --mysql-password=68667109!@#1 --mysql-db=sbtest --percentile=80 --max-requests=0 --threads=11 --time=60 oltp_read_only run

引數說明：

• sysbench：命令列工具的名稱，用於啟動 Sysbench 測試。
• --db-driver=mysql：指定資料庫驅動為 MySQL。
• --mysql-host=localhost：指定 MySQL 資料庫伺服器的主機地址。
• --mysql-port=3306：指定 MySQL 資料庫伺服器的埠號。
• --mysql-user=admin1：指定連線 MySQL 伺服器的使用者名稱。
• --mysql-password=68667109!@#1：指定連線 MySQL 伺服器的使用者密碼。注意，出於安全考慮，通常不建議在命令列中明文顯示密碼。
• --mysql-db=sbtest：指定要測試的資料庫名稱。
• --percentile=80：指定在計算延遲統計時考慮的百分位數。這裡設定為 80%，意味著會報告 80% 的請求在多少時間內完成，這有助於瞭解系統的效能瓶頸。
• --max-requests=0：指定測試期間要執行的最大請求數。設定為 0 表示沒有限制，測試將持續到指定的時間（--time 引數）結束。
• --threads=11：指定用於測試的併發執行緒數。這裡設定為 11，意味著將使用 11 個併發執行緒來模擬使用者活動。
• --time=60：指定測試執行的時間（秒）。
• oltp_read_only：指定要執行的測試型別為只讀事務處理測試。
• run：這個引數指示 Sysbench 執行測試的實際執行階段。在 prepare 階段之後，你需要使用 run 引數來啟動實際的效能測試。

清理

sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=admin1 --mysql-password=68667109!@#1 --mysql-db=sbtest --table_size=2500 --tables=25 --time=60  oltp_read_only cleanup

引數說明

• sysbench：命令列工具的名稱，用於啟動 Sysbench 測試。
• --db-driver=mysql：指定資料庫驅動為 MySQL，表明我們將要對 MySQL 資料庫進行操作。
• --mysql-host=localhost：指定 MySQL 資料庫伺服器的主機地址，即測試所連線的資料庫伺服器的 IP 地址。
• --mysql-port=3306：指定 MySQL 資料庫伺服器的埠號，MySQL 的預設埠是 3306。
• --mysql-user=admin1：指定用於連線 MySQL 伺服器的使用者名稱。
• --mysql-password=68667109!@#1：指定用於連線 MySQL 伺服器的使用者密碼。請注意，出於安全考慮，不建議在命令列中明文顯示密碼。
• --mysql-db=sbtest：指定要操作的資料庫名稱，這裡假設是為測試建立的名為 sbtest 的資料庫。
• --table_size=2500 和 --tables=25：這兩個引數在 cleanup 階段實際上是不需要的，因為清理過程會刪除整個資料庫或指定的表，而不會關心表的大小或數量。但是，它們通常與 prepare 命令一起使用，用於建立和填充測試表。
• --time=60：在 cleanup 階段，這個引數是不需要的，因為清理過程不會持續特定的時間。它通常與 run 命令一起使用，指定測試執行的時間。
• oltp_read_only：指定測試型別為只讀事務處理（OLTP）測試。在 cleanup 階段，這個引數主要用於識別之前為測試建立的特定表或資料庫結構。
• cleanup：這個引數指示 Sysbench 執行清理操作，刪除為測試建立的資料庫或表。

壓測結果模板

透過上圖可以分析得出如下結論：
1) 60 秒內共執行讀請求 28756 次，更新及其他型別的請求 4108 次，QPS：544.42；
2) 60s 內共執行事務操作 2054，TPS:34.03，成功率 100%；
3) 每個事務執行最小時間 272.88ms，最大時間 850.33ms，平均執行時間 322.46ms。
以上測試結果執行環境基本配置資訊：
1) 虛擬機器 1 顆 CPU、2G 記憶體，10G 儲存空間;
2) MySQL 資料庫引數未最佳化、調整。

特殊情況處理

一、會因為 mysql8.0 密碼加密方式導致報錯
FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded
ALTER USER 'admin1'@'%' IDENTIFIED WITH mysql_native_password BY '68667109!@#1'
flush privileges;

參考文件

mysql 效能解析系列教程：https://www.bilibili.com/video/BV1U64y1d7EE/?p=37
透過 sysbench 工具實現 MySQL 資料庫的效能測試：https://blog.csdn.net/hongjian006/article/details/142760189
MySQL 的效能基線收集及壓力測試：https://blog.51cto.com/yijiu/1566615
MySQL 效能測試（完整版）：https://developer.aliyun.com/article/1509755
MySQL 資料庫效能基準測試之 sysbench 之 1—概念與工具分類及 sysbench 介紹：https://zhuanlan.zhihu.com/p/687978862
sysbench 安裝：https://blog.csdn.net/m0_61066945/article/details/138163575
OLTP 負載測試：https://help.aliyun.com/zh/polardb/polardb-for-mysql/oltp-performance-test
lua 指令碼說明：https://blog.csdn.net/zhou920786312/article/details/125095810
github：https://github.com/akopytov/sysbench#sysbench