創(chuàng)新技術(shù)實踐 | MySQL基準(zhǔn)測試實踐

本文首先分析了 MySQL 基準(zhǔn)測試的必要性，隨后簡要介紹了幾種常用的 MySQL 基準(zhǔn)測試工具，接下來針對兩個常用的工具（SysBench 和 Tpcc-MySQL）進(jìn)行了測試案例分享，主要進(jìn)行了性能指標(biāo)的解讀及測試流程的介紹，最后對本文的工作進(jìn)行了總結(jié)。

1、引言

MySQL 作為軟件開發(fā)人員工作中使用最多的數(shù)據(jù)庫之一，了解它的性能情況是十分必要的。作為一種測量和評估軟件性能指標(biāo)的手段，基準(zhǔn)測試可以通過建立在某個時間點的性能基準(zhǔn)，方便了數(shù)據(jù)庫開發(fā)人員在對系統(tǒng)優(yōu)化后重新進(jìn)行基準(zhǔn)測試以評估變化對性能的影響。所以，通過對 MySQL 進(jìn)行基準(zhǔn)測試，建立性能基準(zhǔn)線，可方便數(shù)據(jù)庫開發(fā)人員了解對數(shù)據(jù)庫的性能優(yōu)化是否達(dá)到了預(yù)期的效果。常用的 MySQL 基準(zhǔn)測試工具包括 SysBench、Tpcc-MySQL、YCSB 等。

2、MySQL 基準(zhǔn)測試工具

2.1 SysBench

SysBench 是一個模塊化，跨平臺和多線程的基準(zhǔn)測試工具。它可以執(zhí)行多種類型的基準(zhǔn)測試，不僅可以用來測試數(shù)據(jù)庫的性能，也可以測試運行數(shù)據(jù)庫的服務(wù)器的性能，如測試 CPU/ 內(nèi)存 / 線程 /IO 等方面的性能測試。本文主要介紹用于評估測試各種不同系統(tǒng)參數(shù)下的數(shù)據(jù)庫負(fù)載情況。

SysBench 對 OLTP 基準(zhǔn)測試可以模擬測試多個簡單事務(wù)處理系統(tǒng)的工作負(fù)載，可支持采用 lua 腳本編寫的比較簡單的測試數(shù)據(jù)庫負(fù)載程序來進(jìn)行基準(zhǔn)測試。主要測試業(yè)務(wù)場景包括隨機點查詢（point select），簡單的點更新（update where id=）和范圍查詢等。用戶可以根據(jù)自己的業(yè)務(wù)場景，改寫 sysbench 的 lua 腳本來進(jìn)行測試。此外，通過改寫 SysBench 的方式，還可以支持對 MySQL 集群做分發(fā)讀寫測試。

2.2 Tpcc-MySQL

TPC(Tracsaction Processing Performance Council)是一個針對大型數(shù)據(jù)庫系統(tǒng)軟硬件性能進(jìn)行評測的非盈利的組織，它制定的 TPC-C 可用于規(guī)范對典型的復(fù)雜 OLTP 系統(tǒng)性能測試工作。

Tpcc-MySQL 是基于 TPC-C 衍生出來專用于開展 MySQL 基準(zhǔn)測試的工具。該工具模擬了電商環(huán)境平臺，通過搭建一套可支持下單、支付、查訂單、發(fā)貨、查庫存的運營環(huán)境，模擬各個環(huán)節(jié)，然后獲取數(shù)據(jù)，來評估在當(dāng)前環(huán)境下數(shù)據(jù)庫系統(tǒng)能承擔(dān)的吞吐量。相比于 SysBench 基準(zhǔn)測試，Tpcc-MySQL 更能夠接近業(yè)務(wù)的真實負(fù)載。

2.3 YCSB

YCSB(Yahoo! Cloud Serving Benchmark)是雅虎開源的一款通用的基準(zhǔn)測試工具。它默認(rèn)提供了許多工作負(fù)載，每個工作負(fù)載可提供一些混合讀寫的操作、鍵 - 值存儲、請求分布等。用戶可以根據(jù)不同的工作負(fù)載（如：Insert、Update、Read、Scan 等）多維度的對系統(tǒng)進(jìn)行測試。

3、SysBench 工具使用方法

3.1 SysBench 標(biāo)準(zhǔn) OLTP 實例基準(zhǔn)測試的性能指標(biāo)

（1）TPS（Transactions Per Second），每秒執(zhí)行的事務(wù)數(shù)，以完成 COMMIT 操作的次數(shù)為準(zhǔn)。

（2）QPS（Queries Per Second），每秒執(zhí)行的 SQL 數(shù)，包含 SELECT、INSERT、DETELE、UPDATE、COMMIT 等。

（3）RT（Response Time）,數(shù)據(jù)庫系統(tǒng)對請求做出響應(yīng)的時間(單位：ms)。包括最小響應(yīng)時間、最大響應(yīng)時間、平均響應(yīng)時間等。前 95-99%的最大響應(yīng)時間往往會決定該系統(tǒng)大多數(shù)情況下的短板。

3.2 測試用例介紹

3.2.1 數(shù)據(jù)準(zhǔn)備

（1）客戶端創(chuàng)建空數(shù)據(jù)庫：

create database sbtest;

（2）導(dǎo)入數(shù)據(jù)：

數(shù)據(jù)準(zhǔn)備命令：

sysbench oltp_read_only.lua --db-driver=mysql --mysql-host=xxx --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --table_size=100000 --tables=10 --threads=4 prepare

圖 1、prepare 數(shù)據(jù)準(zhǔn)備圖例

圖 1 所示是執(zhí)行數(shù)據(jù)準(zhǔn)備命令后的結(jié)果，具體實現(xiàn)的作用就是進(jìn)入到指定的數(shù)據(jù)庫服務(wù)器中，在空數(shù)據(jù)庫 sbtest 中創(chuàng)建 10 張表，每張表的大小為 10W，選擇創(chuàng)建數(shù)據(jù)的線程為 4。詳細(xì)的 SysBench 主要參數(shù)介紹如表 1 所示。

表 1、SysBench 主要參數(shù)

3.2.2 MySQL 單機版業(yè)務(wù)測試

SysBench 自帶的常用測試腳本如下：

（1）oltp_point_select.lua：用于測試 OLTP 的定點查詢性能（如根據(jù)主鍵查詢）；

（2）oltp_update_index.lua：用于測試 OLTP 的更新索引字段的性能；

（3）oltp_read_only.lua：用于測試 OLTP 的只讀性能；

（4）oltp_write_only.lua：用于測試 OLTP 的只寫操作性能；

（5）oltp_read_write.lua：用于測試 OLTP 的讀寫操作性能。

測試命令：

sysbench? xxx.lua --db-driver=mysql --mysql-host=xxx --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --time=180 --table_size=100000 --tables=10 --threads=128 run

其中 xxx.lua 為選擇的對應(yīng)測試業(yè)務(wù)所需的 lua 腳本

3.2.3 MySQL 主從模式測試

主從模式測試，使用 SysBench 的改寫版本可以實現(xiàn)在一主多從部署下的只寫、只讀、混合讀寫測試：

（1）只寫測試：從機空載，利用 SysBench 對主機進(jìn)行只寫壓測，測試方法與單機版一致；

（2）只讀測試：改寫的 SysBench 通過測試命令指定將所有讀均勻分?jǐn)偟街付ǖ膹臋C上，每臺從機接收到的讀數(shù)量都是一致的；

（3）混合讀寫測試：改寫的 SysBench 直接緩存與主機和從機的請求，自動將寫全部定向到主機，將所有讀均勻分?jǐn)偟礁鱾€從機。

一主二從模式混合讀寫測試命令示例：

sysbench xxx.lua --db-driver=mysql --mysql-host=xxx --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --time=180 --report-interval=10 --table_size=100000 --tables=10 --threads=128 --slaves=2 --slave-host="host1,host2" --slave-user=xxx --slave-password=xxx --slave-db=sbtest run

改寫 SysBench 補充參數(shù)解讀：

slaves：主從模式的從機數(shù)量

slave-host：從機數(shù)據(jù)庫實例的連接地址

slave-user：從機數(shù)據(jù)庫實例的用戶名

slave-password：從機數(shù)據(jù)庫實例的密碼

slave-db: 從機數(shù)據(jù)庫實例名

其中 xxx.lua 為選擇的對應(yīng)測試業(yè)務(wù)所需的 lua 腳本

3.2.4 測試結(jié)果分析

如圖 2 所示，是跑出的混合讀寫的測試樣例結(jié)果:

圖 2、SysBench 測試結(jié)果圖例

測試參數(shù)選擇：128 線程+10 張表+10w 表大小+60s 測試時長

主要關(guān)注測試結(jié)果為：15652 QPS+313052 TPS+ 11.04ms RT(95%)

3.2.4 清理數(shù)據(jù)

每項測試完成，進(jìn)行清空測試數(shù)據(jù)庫操作

sysbench oltp_read_only.lua --db-driver=mysql --mysql-host=xxx --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --table_size=100000 --tables=10 --threads=64 cleanup

4、Tpcc-MySQL 工具使用方法

4.1 Tpcc-MySQL 的業(yè)務(wù)邏輯及相關(guān)表

（1）業(yè)務(wù)邏輯

New_Order：新訂單，處理一次完整的訂單事務(wù)

Payment：支付，客戶賬戶余額更新，反映其支付情況

Order-Status：訂單狀態(tài)，客戶最新交易狀態(tài)查詢

Delivery：發(fā)貨（模擬批處理交易）

Stock-Level：庫存，倉庫庫存狀況查詢，便于商家能夠及時補貨

（2）相關(guān)表

Customer：客戶表

District：地區(qū)表

Item：商品表

Warehouse：倉庫表

4.2 主要測試性能指標(biāo)

TpmC(transactions per minute): 每分鐘處理訂單交易的能力。即在數(shù)據(jù)庫系統(tǒng)執(zhí)行支付操作、訂單狀態(tài)查詢、發(fā)貨任務(wù)和查詢庫存狀態(tài)這 4 種交易的同時，該指標(biāo)每分鐘能夠處理新訂單交易的個數(shù)。此外，所有交易數(shù)量所占的比例需要滿足 TPC-C 規(guī)范的要求，且各種交易數(shù)量所占的比例也應(yīng)該滿足 TPC-C 規(guī)范的要求。在這種情況下，TpmC 值越大說明系統(tǒng)的 OLTP 能力越高。

4.3 測試用例介紹

4.3.1 數(shù)據(jù)準(zhǔn)備

（1）客戶端創(chuàng)建空數(shù)據(jù)庫：

create database tpcctest;

（2）創(chuàng)建測試表結(jié)構(gòu)：

./bin/mysql -uroot -p tpcctest < create_table.sql

./bin/mysql -uroot -p tpcctest < add_fkey_idx.sql

（3）創(chuàng)建數(shù)據(jù)：

使用命令行工具 tpcc_load 來提供初始化數(shù)據(jù)的功能。

如表 2 所示，介紹了 Tpcc-MySQL 在測試過程中常用的參數(shù)，需要特別介紹的是 warehouses，它代表了電商倉庫的數(shù)量，每個倉庫負(fù)責(zé)一定區(qū)域，每個顧客購買商品會隨機選擇一個倉庫，在這個倉庫中隨機選擇一些物品，倉庫數(shù)量越多意味著測試的數(shù)據(jù)集越大（如果 warehouses 設(shè)置為 2500，會創(chuàng)建 160GB 左右的數(shù)據(jù)，warehouses 設(shè)為 5000 時，數(shù)據(jù)量大小在 280GB 左右）。

表 2、Tpcc-MySQL 測試參數(shù)介紹

數(shù)據(jù)加載命令：

./tpcc_load -h xxx -P 3306 -d tpcctest -u xxx -p xxx? -w 10

圖 3 示例顯示了配置過程，創(chuàng)建了一個小型（十個倉庫）測試數(shù)據(jù)集，數(shù)據(jù)庫名為 tpcctest。

圖 3、Tpcc-MySQL 數(shù)據(jù)創(chuàng)建圖例

4.3.2 業(yè)務(wù)場景測試

使用命令行工具 tpcc_start 來進(jìn)行壓力測試。

./tpcc_start -h xxx -P 3306 -d tpcctest -u root -p '' -w 10 -c 32 -r 180 -l 360 -i 10 -f tpcc-mysql.log -t tpcc_mysql.rtx

圖 4、Tpcc-MySQL 測試圖例

圖 4 為執(zhí)行測試命令后的部分顯示內(nèi)容。在該項測試過程中選擇的倉庫數(shù)為 10，連接數(shù)為 32，熱身時長為 180s，測試時長為 360s，打印報告的間隔時間為 10s，各項操作記錄輸出到 tpcc-mysql.log 文件中，更詳細(xì)的操作信息輸出到 tpcc_mysql.rtx 中。

4.3.2 測試結(jié)果分析

如圖 5 所示，是跑出的 tpcc 業(yè)務(wù)場景測試結(jié)果:

圖 5、Tpcc-MySQL 測試結(jié)果圖例

測試結(jié)果的含義分別如下:

[0]:新訂單業(yè)務(wù)

[1]:支付業(yè)務(wù)

[2]:訂單狀態(tài)查詢業(yè)務(wù)

[3]:物流相關(guān)業(yè)務(wù)

[4]:倉儲相關(guān)業(yè)務(wù)

表示 tpcc 事務(wù)執(zhí)行期間，5 種事務(wù)執(zhí)行情況。其中：

sc: 表示執(zhí)行成功且請求延時在最大閥值之內(nèi)（5ms）的事務(wù)數(shù)

lt: 表示執(zhí)行成功，但請求延時在最大閥值之外（5ms）的事務(wù)數(shù)

rt: 表示通過重試后執(zhí)行成功的事務(wù)數(shù)

fl: 表示執(zhí)行失敗的事務(wù)數(shù)

avg_rt: 表示事務(wù)的平均處理延遲

主要測試結(jié)果：TmpC=96812.164

5、結(jié)語

MySQL 基準(zhǔn)測試是數(shù)據(jù)庫性能優(yōu)化中非常重要的一個環(huán)節(jié)，針對不同的業(yè)務(wù)場景采用不同的基準(zhǔn)測試方案。如果用戶只是想測試簡單的 OLTP 事務(wù)，可以選擇采用 SysBench 來做基準(zhǔn)測試。如果需要模擬一個完整的事務(wù)處理系統(tǒng)，可以采用 Tpcc-MySQL 來進(jìn)行基準(zhǔn)測試。當(dāng)然，在 MySQL 系統(tǒng)上運行基準(zhǔn)測試，就其技術(shù)本身而言在日常業(yè)務(wù)運維也是非常必要的，希望這些測試實例可以對大家開展相關(guān)基準(zhǔn)測試工作能有所幫助。