MySQL原理簡介—3.生產環境的部署壓測

大綱

1.普通的Java應用系統能抗多少請求

2.高併發下的資料庫用什麼樣的機器

3.部署完資料庫之後需要先進行壓測

4.QPS和TPS之間的區別

5.IO相關的壓測效能指標

6.壓測時需要關注的其他效能指標

7.一臺機器每秒可以抗多少併發的影響因素

8.sysbench是非常好用的資料庫壓測工具

9.壓測中除了QPS和TPS外還要觀察機器效能

10.壓測時如何觀察機器的CPU負載情況

11.壓測時如何觀察機器的記憶體負載情況

12.壓測時如何觀察機器的磁碟IO情況

13.壓測時觀察網路卡的流量情況

14.使用sysbench壓測資料庫總結

1.普通的Java應用系統能抗多少QPS

部署Java應用系統時，常選用的機器配置是2核4G和4核8G；部署資料庫時，常選用的機器配置最低是8核16G，正常16核32G。

一般Java應用系統部署在4核8G的機器上時，每秒能抗500左右的請求。一臺機器每秒能抗多少請求，往往跟每個請求處理耗費多長時間有關。假設每個請求1s可以處理完，則一臺機器每秒可以處理100個請求；假設每個請求100ms可以處理完，則一臺機器每秒可以處理幾百個請求。

4核8G的機器部署普通的Java系統，每秒能抗下幾百的請求。從每秒一兩百請求到每秒七八百請求都有可能，關鍵還是看每個請求處理要耗費多長時間。

2.高併發下的資料庫用什麼樣的機器

Java應用系統處理請求的耗費時間主要用在與資料庫進行互動。若Java系統僅僅只在內部執行一些普通的業務邏輯，效能是很高的。當Java系統和資料庫產生互動時，耗時最多的便是：將網路請求傳送到資料庫 + 等待資料庫執行SQL語句返回結果。

常說Java系統壓力很大負載很高，主要是指依賴的資料庫壓力很大。當資料庫需要執行大量增刪改查的SQL語句時，需要對記憶體和磁碟檔案進行大量IO操作，所以資料庫的負載會很高。

一般8核16G的機器部署的MySQL資料庫，每秒能抗一兩千請求。對於16核32G的機器部署的MySQL資料庫，每秒能抗兩到四千請求。此外，資料庫所在機器最好採用SSD固態硬碟而不是普通機械硬碟。

3.部署完資料庫之後需要先進行壓測

部署完資料庫後，需要先對該資料庫進行基準壓測。即先基於一些工具模擬一個系統每秒發出1000個請求到資料庫上去，觀察它的CPU負載、磁碟IO負載、網路IO負載、記憶體負載。然後看資料庫能否處理掉這1000個請求，還是隻能處理500個請求。

資料庫的壓測和Java系統的壓測是兩回事。因為對於Java系統的壓測，首先得知道資料庫最大能抗多大壓力。然後再根據資料庫的最大壓力，去看Java系統能抗多大壓力。比如可能Java系統每秒抗2000請求，但資料庫每秒只能抗500請求。

4.QPS和TPS之間的區別

QPS，指的是每秒可以處理多少個請求，可以理解為一個請求就是一條SQL語句。所以QPS指資料庫每秒可以處理多少個SQL語句。

TPS，指的是每秒可以處理的事務量，所以TPS指資料庫每秒會處理多少次事務提交或者回滾。一個事務會包含多個SQL語句，這些SQL語句要麼一起成功，要麼一起回滾。

5.IO相關的壓測效能指標

(1)IOPS

(2)吞吐量

(3)latency

壓測時需要關注如下的IO相關的效能指標：

(1)IOPS

IOPS指機器的隨機IO併發處理能力。比如機器可以達200的IOPS，表示每秒可以執行200個隨機IO讀寫請求。

IOPS這個指標很關鍵。因為InnoDB更新記憶體中的髒資料時，最後都會由後臺IO執行緒在不確定的時間刷回到磁碟裡，這就涉及隨機IO。如果IOPS指標太低，那麼會導致記憶體裡的髒資料刷回磁碟的效率不高。

(2)吞吐量

吞吐量指的是機器的磁碟儲存每秒可以讀寫多少位元組的資料量。

吞吐量這個指標也很關鍵。因為MySQL在執行各種SQL語句、提交事務時，都會大量寫redo日誌、binlog日誌，這些日誌都會寫到磁碟檔案。所以一臺機器的磁碟儲存每秒可以讀寫多少位元組的資料量，就決定了它每秒可以把多少日誌寫入到磁碟裡。

一般寫redo之類的日誌，都是對磁碟檔案進行順序寫入的，也就是一行接著一行的寫，而不會進行隨機讀寫。普通磁碟的順序寫入的吞吐量每秒都可以達到200MB左右。通常而言，機器的磁碟吞吐量都是足夠承載高併發請求的。

(3)latency

latency指的是往磁碟裡寫入一條資料的延遲。

latency這個指標同樣很重要。因為MySQL在執行一條SQL語句和提交事務時，都需要順序寫一條redo日誌和一條binlog日誌到磁碟檔案。所以此時寫一條日誌到磁碟檔案裡去，到底是延遲1ms還是延遲100us，很影響資料庫SQL語句的執行效能。一般來說，磁碟讀寫延遲越低，資料庫的效能就越高，執行每個SQL語句和事務的時候速度就會越快。

6.壓測時需要關注的其他效能指標

(1)CPU負載

(2)網路負載

(3)記憶體負載

除了QPS、TPS、IOPS、吞吐量、latency這些指標外，在壓測時還需要關注機器的一些其他效能指標：

(1)CPU負載

CPU負載是一個很重要的效能指標。假設資料庫壓測到了每秒處理3000請求，可能其他的效能指標還都正常，但CPU負載特別高，這也說明資料庫不能繼續往下壓測更高的QPS了。

(2)網路負載

這個要看機器頻寬在壓測到一定的QPS和TPS時，每秒鐘機器的網路卡會輸入和輸出多少MB資料。因為有可能機器的網路頻寬最多每秒傳輸100MB的資料，如果當QPS為1000時網路卡就打滿了，那麼即使其他指標正常，此時也不能繼續壓測了。

(3)記憶體負載

就是看在壓測到一定情況下，機器記憶體耗費了多少。如果機器記憶體耗費過高，說明也不能繼續壓測了。

7.一臺機器每秒可以抗多少併發的影響因素

影響因素：執行緒數、執行緒處理請求時間、CPU、記憶體、磁碟IO、網路頻寬

(1)4核8G機器上的Java系統處理每個請求只需0.01ms每秒能抗多少請求

理論上透過增加執行緒數、記憶體讀寫，抗幾千併發甚至上萬併發都是可以的。但執行緒數越來越多後，切換上下文很費CPU、導致CPU負載也會變高。因此當請求源源不斷進來時，可能會導致擠壓從而拖垮系統。

一臺機器每秒可以處理的請求數 = 可用執行緒數 * 每個執行緒每秒處理請求數；但即使該Java程式處理一個請求需要0.01ms，能扛多少請求還需考慮：記憶體能抗多少、CPU能支撐多少執行緒、網路卡流量多大的負載能打滿等，綜合這些因素才能得出一臺機器究竟能扛多少請求。

一般的機器配置，如果Java程式不依賴資料庫只是請求處理，那麼每個請求處理只需要0.01ms，此時每秒還是可以扛上萬請求的。

總之，一臺機器到底可以每秒抗下多少併發請求，跟CPU、記憶體、磁碟IO、網路頻寬都有關係。

(2)如何區分QPS和TPS

一個交易系統會拆分很多服務。對於單個服務來說，每秒處理請求數量就是QPS。對於整個交易系統來說一次交易請求需要呼叫多個服務，那麼其每秒處理完這些服務的的交易筆數則是TPS。

8.sysbench是非常好用的資料庫壓測工具

(1)sysbench的用途

(2)在Linux安裝sysbench工具

(3)資料庫壓測的測試用例

(4)基於sysbench構造測試表和測試資料

(5)對資料庫進行360度全方位測試

(6)壓測結果分析

(1)sysbench的用途

可以在資料庫裡自動構造出大量的資料，可以模擬幾千個執行緒併發訪問資料庫，可以模擬使用各種SQL語句來訪問資料庫，可以模擬出各種事務提交到資料庫裡，可以模擬出幾十萬的TPS去壓測資料庫。

(2)在Linux安裝sysbench工具

先設定yum repo倉庫，接著基於yum來安裝sysbench即可；

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

(3)資料庫壓測的測試用例

接著需要在資料庫裡建立好一個測試庫，可以取名為test_db。同時建立好對應的測試賬號，可以叫test_user。然後要基於sysbench構建20個測試表，每個表裡有100萬條資料。接著使用10個併發執行緒去對這個資料庫發起訪問，連續訪問5分鐘。

(4)基於sysbench構造測試表和測試資料

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

上面構造了一個sysbench命令，加入了很多引數，下面是這些引數解釋：

1.--db-driver=mysql：意思是基於mysql的驅動去連線mysql資料庫，這裡可以選oracle、sqlserver等
2.--time=300：意思是連續訪問300秒
3.--threads=10：意思是用10個執行緒模擬併發訪問
4.--report-interval=1：意思是每隔1秒輸出一下壓測情況
5.--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user：意思是連線到哪臺機器的哪個埠上的MySQL庫
6.--mysql-db=test_db --tables=20 --table_size=1000000：意思是在test_db這個庫裡，構造20個測試表，每個測試表裡構造100萬條測試資料，測試表的名字將類似於sbtest1、sbtest2
7.oltp_read_write：意思是執行oltp資料庫的讀寫測試
8.--db-ps-mode=disable：意思是禁止ps模式
9.prepare：意思是參照這個命令的設定去構造出我們需要的資料庫裡的資料，它會自動建立20個測試表，每個表裡建立100萬條測試資料

(5)對資料庫進行360度全方位測試

a.測試資料庫的綜合讀寫TPS，使用oltp_read_write模式。命令中最後不是prepare而是run，代表的就是執行壓測：

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_read_write --db-ps-mode=disable run

b.測試資料庫的只讀效能，使用oltp_read_only模式。命令中的oltp_read_write已經變成oltp_read_only了：

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_read_only --db-ps-mode=disable run

c.測試資料庫的刪除效能，使用oltp_delete模式：

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_delete --db-ps-mode=disable run

d.測試資料庫的更新索引欄位的效能，使用oltp_update_index模式：

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_update_index --db-ps-mode=disable run

e.測試資料庫更新非索引欄位的效能，使用oltp_update_non_index模式：

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_update_non_index --db-ps-mode=disable run

f.測試資料庫的插入效能，使用oltp_insert模式：

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_insert --db-ps-mode=disable run

g.測試資料庫的寫入效能，使用的是oltp_write_only模式：

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_write_only --db-ps-mode=disable run

使用上面的命令，sysbench工具會構造出各種各樣的SQL語句，然後根據這些SQL語句去更新或者查詢20張測試表裡的資料，同時檢測出資料庫的壓測效能指標，最後完成壓測後可以執行下面的cleanup命令進行清理資料：

$ sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 \
--table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

(6)壓測結果分析

按照上面的命令，讓sysbench每隔1秒輸出一次壓測報告，此時每秒輸出類似如下的資料：

[22s] thds:10 tps:380.99 qps:7312.66 (r/w/o:5132.99/1155.86/1321.55) lat(ms,95%):21.33 err/s:0.00 reconn/s:0.00

該輸出的意思是：這是第22s輸出的一段壓測統計報告，後面是具體的統計欄位：

1.thds: 10，意思是有10個執行緒在壓測
2.tps:380.99，意思是每秒執行了380.99個事務
3.qps:7312.66，意思是每秒可以執行7612.66個請求
4.(r/w/o:5132.99/1155.86/1321.55)，意思是每秒7612.66個請求中，有5132.99個請求是讀請求，1155.86個請求是寫請求，1321.55個請求是其他請求
5.lat(ms,95%):21.33，意思是95%的請求的延遲都在21.33毫秒以下
6.err/s:0.00 reconn/s:0.00，意思是每秒有0個請求是失敗的，每秒發生了0次網路重連

完成壓測後，最後會顯示一個總的壓測報告，如下：

SQL statistics:
    queries performed:
        read:1480084 //意思是在300s的壓測期間執行了148萬多次的讀請求
        write:298457 //意思是在壓測期間執行了29萬多次的寫請求
        other:325436 //意思是壓測期間執行了32萬多次的其他請求
        total:2103977 //意思是一共執行了210萬多次的請求
    transactions:105180 (350.6 per sec.) //意思是一共執行了10萬多個事務，每秒執行350多個事務
    queries:2103977 (7013.26 per sec.) //意思是一共執行了210萬多次的請求，每秒執行7000+請求
    ignored errors: 0 (0.00 per sec.)
    reconnects: 0 (0.00 per sec.)
//下面的意思是一共執行了300s壓測，執行了10萬+的事務
General saticstics:
    total time:300.0052s
    total number of events:105180
Latency(ms):
    min:4.32 //請求中延遲最小的是4.32ms
    avg:13.42 //所有請求平均延遲是13.42ms
    max:45.66 //延遲最大的請求是45.56ms
    95th percentile:21.33 //95%的請求延遲都在21.33ms以內

9.壓測中除了QPS和TPS外還要觀察機器效能

上面例子使用了10個執行緒去壓測資料庫。如果機器的效能很高，10個執行緒是沒法壓測出資料庫的最高負載能力的。可以在sysbench中不停的增加執行緒的數量，比如使用20個執行緒，甚至100個執行緒去併發的訪問資料庫，直到發現資料庫的QPS和TPS上不去。

當然，不停地提高執行緒數，讓資料庫不停地承載更高的QPS的過程，還需要配合對機器效能表現的觀察來做，不能盲目增加執行緒。

為什麼在不停增加執行緒數量的時候，要密切關注機器效能？首先假設資料庫當前抗下了每秒2000的QPS，同時這時CPU負載、記憶體負載、網路負載、磁碟IO負載，都在正常範圍。負載相對較高一些，但是還沒有達到這些硬體的極限，那麼可認為這臺資料庫在高峰期能抗每秒2000QPS。

但如果一直不停的在壓測過程中增加sysbench的執行緒數量，然後資料庫此時勉強抗到了每秒5000的QPS了。但這時發現機器CPU已滿負荷執行、記憶體使用率特別高、記憶體快耗盡，然後網路頻寬幾乎被打滿、磁碟IO的等待時間特別長，那麼這時就說明機器已到了極致了。所以這時壓測出的5000QPS是沒什麼代表性的，因為在生產環境根本不可能讓資料庫抗下這麼高的QPS，因為到這麼高的QPS就說明資料庫幾乎已經快掛掉了，這是不現實的。

所以在壓測的過程中：必須是不停增加sysbench的執行緒數量，持續讓資料庫承載更高的QPS，同時密切關注CPU、記憶體、磁碟和網路的負載。在硬體負載情況比較正常的範圍內，哪怕負載相對較高一些，也還是可以繼續增加執行緒數量和提高資料庫的QPS的。

然後當不停增加執行緒數量，發現資料庫可以抗下下一個QPS的數值時，機器的CPU、記憶體、網路和磁碟的負載已經比較高了，機器已經到了一個有一定風險的臨界值了，那麼此時就不能繼續增加執行緒數量和提高資料庫壓測的QPS了。

10.壓測時如何觀察機器的CPU負載情況

先來看一個最常用的監測Linux機器效能的命令，就是top命令。在linux命令列輸入top命令就會看到如下一行資訊：

top - 15:52:00 up 42:35, 1 user, load average: 0.15, 0.05, 0.01

這行資訊是最直觀可以看到機器的CPU負載情況的：首先15:52:00指的是當前時間；up 42:35指的是機器已經執行了多長時間；1 user就是說當前機器有1個使用者在使用；最重要的是load average: 0.15, 0.05, 0.01這行資訊，這說的是CPU在1分鐘、5分鐘、15分鐘內的負載情況。

CPU負載指(假設在4核CPU機器執行top命令)：

一.如果CPU負載是0.15，則說明4核CPU中連一個核都沒用滿，4核CPU基本都很空閒沒怎麼使用；

二.如果CPU負載是1，則說明4核CPU中有一個核已被使用得比較繁忙，另外3個核還是比較空閒的；

三.如果CPU負載是1.5，說明有一個核被使用繁忙，另外一個核也在使用，但是沒那麼繁忙，此時還有2個核可能是空閒的；

四.如果CPU負載是4，那說明4核CPU都被跑滿了；

五.如果CPU負載是6，那說明4核CPU被繁忙的使用還不夠處理當前的任務，很多程序可能一直在等待CPU去執行自己的任務。

所以上面看到的load average實際上就是：CPU在最近1分鐘5分鐘15分鐘內的平均負載數值。

上面看到的負載是0.15，說明CPU根本沒怎麼用。如果壓測過程中發現4核CPU的load average已經達到3.5或者4了，則說明CPU基本都跑滿了，已經在滿負荷運轉了。此時就不要再繼續提高執行緒的數量和增加資料庫的QPS了。

11.壓測時如何觀察機器的記憶體負載情況

在執行top命令後，中間跳過幾行內容，可以看到如下一行內容：

Mem: 33554432k total, 20971520k used, 12268339 free, 307200k buffers

這說的就是當前機器的記憶體使用情況：總記憶體有32G，已使用20G記憶體，還有10多G記憶體是空閒的，有大概300MB左右的記憶體用作OS核心的緩衝區。

對於記憶體而言，同樣要在壓測的過程中緊密觀察。如果記憶體的使用率在80%以內，基本都還能接受，在正常範圍內。但是如果機器的記憶體使用率到了70%~80%了，就說明有點危險了。此時就不要繼續增加壓測的執行緒數量和QPS了，差不多就可以了。

12.壓測時如何觀察機器的磁碟IO情況

可用dstat命令檢視儲存的IO吞吐量、IOPS等資訊。

一.使用命令：dstat -d，會看到如下的資訊：

-dsk/total -
read writ
103k 211k
  0   11k

在上面可以清晰看到：儲存的IO吞吐量是每秒鐘讀取103KB的資料，每秒寫入211KB的資料。像這個儲存IO吞吐量基本上都不算多的，因為普通的機械硬碟都可以做到每秒鐘上百MB的讀寫資料量。

二.使用命令：dstat -r，可以看到如下的資訊：

--io/total-
read writ
0.25 31.9
  0   253
  0   39.0

這個意思就是讀IOPS和寫IOPS分別是多少。也就是說隨機磁碟讀取每秒鐘多少次，隨機磁碟寫入每秒鐘執行多少次。

一般來說，隨機磁碟讀寫每秒在兩三百次都是可以承受的。所以在這裡就需要在壓測的時候密切觀察機器的磁碟IO情況：如果磁碟IO吞吐量已經太高了，都達到極限的每秒上百MB的磁碟IO了，或者隨機磁碟讀寫每秒都到極限的兩三百次IOPS了，此時就不要繼續增加執行緒數量了，否則磁碟IO負載就太高了；

13.壓測時觀察網路卡的流量情況

可以使用命令：dstat -n，檢視網路卡流量資訊：

-net/total-
recv send
16k  17k

這個說的就是：每秒鐘網路卡接收到的流量有多少KB；每秒鐘透過網路卡傳送出去的流量有多少KB。

如果機器使用的是千兆網路卡，那麼每秒網路卡的總流量也就在100MB左右，在壓測時也要觀察網路卡的流量。如果網路卡傳輸流量已到極限值，那麼此時再怎麼提高sysbench執行緒數量，資料庫的QPS也上不去，因為這臺機器每秒已無法透過網路卡傳輸更多的資料。

14.使用sysbench壓測資料庫總結

在資料庫壓測的過程中，必須不停的增加sysbench的執行緒數量，測試資料庫能抗下的QPS。同時透過各種命令觀察機器的CPU、記憶體、磁碟和網路的負載情況。如果發現某個硬體負載已經很高，此時就可以不再提高資料庫的QPS。

在硬體的合理負載範圍內，把資料庫的QPS提高到最大。這就是資料庫壓測時最合理的一個極限QPS值。而不是不管機器的各個硬體的負載，盲目增加sysbench的執行緒數量。

CPU負載：top

記憶體負載：top下的Mem

磁碟IO吞吐量：dstat -d (每秒上百MB)

磁碟IOPS：dstat -r (每秒兩三百)

網路卡流量：dstat -n (千兆網路卡每秒100MB左右)