前言：最近新到一批機器，需要對機器進行壓測，主要壓測范圍包括CPU測試、磁盤IO測試、線程測試、OLTP測試等，那么sysbench就可以滿足我們的壓測需求。下面我們簡單來看下sysbench的安裝使用以及壓測結(jié)果的解讀。

一、sysbench安裝

sysbench安裝其實非常簡單，可以參考下git，其實MySQL官網(wǎng)也可以下到，不過版本比較老了，最新的版本是sysbench1.1.0，這里我們講下源碼編譯安裝。
上傳sysbench到服務(wù)器、解壓、安裝：

?

[root@localhost tmp]# unzip sysbench-master.zip [root@localhost tmp]# cd sysbench-master [root@localhost sysbench-master]# ./autogen.sh [root@localhost sysbench-master]# ./configure [root@localhost sysbench-master]# make -j [root@localhost sysbench-master]# make install

其實可以查看下README.md,上面也寫了如何安裝

?

## Build and Installshell./autogen.sh# Add --with-pgsql to build with PostgreSQL support./configuremake -jmake install

二、壓測過程及壓測結(jié)果解讀

1.cpu測試

?

[root@localhost storage]# sysbench --test=cpu --cpu-max-prime=2000000 run WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options. sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options: Number of threads: 1 Initializing random number generator from current timePrime numbers limit: 2000000Initializing worker threads...Threads started!CPU speed:events per second: 0.63Throughput:events/s (eps): 0.6334time elapsed: 11.0520stotal number of events: 7Latency (ms):min: 1571.86avg: 1578.84max: 1620.5395th percentile: 1618.78sum: 11051.91Threads fairness:events (avg/stddev): 7.0000/0.00execution time (avg/stddev): 11.0519/0.00

cpu測試主要是進行素數(shù)的加法運算，上面我們的例子中，指定了最大的質(zhì)數(shù)發(fā)生器數(shù)量為 2000000，可以看出服務(wù)器此次測試 執(zhí)行時間 大約為11.05秒
2.磁盤IO測試

?

[root@localhost storage]# sysbench --test=fileio --num-threads=16 --file-total-size=30G --file-test-mode=rndrw prepare WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options. WARNING: --num-threads is deprecated, use --threads instead sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)128 files, 245760Kb each, 30720Mb total Creating files for the test... Extra file open flags: (none) Creating file test_file.0 Creating file test_file.1 Creating file test_file.2 Creating file test_file.3 Creating file test_file.4 Creating file test_file.5 Creating file test_file.6 Creating file test_file.7 Creating file test_file.8 Creating file test_file.9 Creating file test_file.10 Creating file test_file.11 Creating file test_file.12 Creating file test_file.13 Creating file test_file.14 Creating file test_file.15 Creating file test_file.16 Creating file test_file.17 Creating file test_file.18 Creating file test_file.19 Creating file test_file.20 Creating file test_file.21 Creating file test_file.22 Creating file test_file.23 Creating file test_file.24 Creating file test_file.25 Creating file test_file.26 Creating file test_file.27 Creating file test_file.28 Creating file test_file.29 Creating file test_file.30 Creating file test_file.31 Creating file test_file.32 Creating file test_file.33 Creating file test_file.34 Creating file test_file.35 Creating file test_file.36 Creating file test_file.37 Creating file test_file.38 Creating file test_file.39 Creating file test_file.40 Creating file test_file.41 Creating file test_file.42 Creating file test_file.43 Creating file test_file.44 Creating file test_file.45 Creating file test_file.46 Creating file test_file.47 Creating file test_file.48 Creating file test_file.49 Creating file test_file.50 Creating file test_file.51 Creating file test_file.52 Creating file test_file.53 Creating file test_file.54 Creating file test_file.55 Creating file test_file.56 Creating file test_file.57 Creating file test_file.58 Creating file test_file.59 Creating file test_file.60 Creating file test_file.61 Creating file test_file.62 Creating file test_file.63 Creating file test_file.64 Creating file test_file.65 Creating file test_file.66 Creating file test_file.67 Creating file test_file.68 Creating file test_file.69 Creating file test_file.70 Creating file test_file.71 Creating file test_file.72 Creating file test_file.73 Creating file test_file.74 Creating file test_file.75 Creating file test_file.76 Creating file test_file.77 Creating file test_file.78 Creating file test_file.79 Creating file test_file.80 Creating file test_file.81 Creating file test_file.82 Creating file test_file.83 Creating file test_file.84 Creating file test_file.85 Creating file test_file.86 Creating file test_file.87 Creating file test_file.88 Creating file test_file.89 Creating file test_file.90 Creating file test_file.91 Creating file test_file.92 Creating file test_file.93 Creating file test_file.94 Creating file test_file.95 Creating file test_file.96 Creating file test_file.97 Creating file test_file.98 Creating file test_file.99 Creating file test_file.100 Creating file test_file.101 Creating file test_file.102 Creating file test_file.103 Creating file test_file.104 Creating file test_file.105 Creating file test_file.106 Creating file test_file.107 Creating file test_file.108 Creating file test_file.109 Creating file test_file.110 Creating file test_file.111 Creating file test_file.112 Creating file test_file.113 Creating file test_file.114 Creating file test_file.115 Creating file test_file.116 Creating file test_file.117 Creating file test_file.118 Creating file test_file.119 Creating file test_file.120 Creating file test_file.121 Creating file test_file.122 Creating file test_file.123 Creating file test_file.124 Creating file test_file.125 Creating file test_file.126 Creating file test_file.127 32212254720 bytes written in 55.25 seconds (556.00 MiB/sec).[root@localhost storage]# sysbench --test=fileio --num-threads=16 --file-total-size=30G --file-test-mode=rndrw runWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options. WARNING: --num-threads is deprecated, use --threads instead sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options: Number of threads: 16 Initializing random number generator from current timeExtra file open flags: (none) 128 files, 240MiB each 30GiB total file size Block size 16KiB Number of IO requests: 0 Read/Write ratio for combined random IO test: 1.50 Periodic FSYNC enabled, calling fsync() each 100 requests. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random r/w test Initializing worker threads...Threads started!Throughput:read: IOPS=68491.05 1070.17 MiB/s (1122.16 MB/s)write: IOPS=45660.96 713.45 MiB/s (748.11 MB/s)fsync: IOPS=146103.57Latency (ms):min: 0.00avg: 0.06max: 3.6195th percentile: 0.31sum: 156128.82

可以看到，磁盤的讀IOPS可以達到68491.05,寫IOPS可以達到45660.96，fsync可以達到146103.57
3.線程測試

?

[root@localhost storage]# sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options. WARNING: --num-threads is deprecated, use --threads instead sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options: Number of threads: 64 Initializing random number generator from current timeInitializing worker threads...Threads started!Throughput:events/s (eps): 14851.1802time elapsed: 10.0036stotal number of events: 148565Latency (ms):min: 0.02avg: 4.31max: 24.4995th percentile: 10.46sum: 639889.93Threads fairness:events (avg/stddev): 2321.3281/42.94execution time (avg/stddev): 9.9983/0.00

(發(fā)送64次/個測試線程請求，每次/個線程請求產(chǎn)生/生成100個數(shù)量，每個線程的鎖數(shù)量為2) ，測試執(zhí)行時間為10秒
4.內(nèi)存測試

?

[root@localhost storage]# sysbench --test=memory --memory-block-size=8k --memory-total-size=40G run WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options. sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options: Number of threads: 1 Initializing random number generator from current timeRunning memory speed test with the following options:block size: 8KiBtotal size: 40960MiBoperation: writescope: globalInitializing worker threads...Threads started!Total operations: 5242880 (1443403.42 per second)40960.00 MiB transferred (11276.59 MiB/sec)Throughput:events/s (eps): 1443403.4239time elapsed: 3.6323stotal number of events: 5242880Latency (ms):min: 0.00avg: 0.00max: 0.0295th percentile: 0.00sum: 2926.10Threads fairness:events (avg/stddev): 5242880.0000/0.00execution time (avg/stddev): 2.9261/0.00

上述參數(shù)指定了本次測試整個過程是在內(nèi)存中傳輸 40G 的數(shù)據(jù)量，每個 block 大小為 8K。測試結(jié)果顯示：
執(zhí)行時間為3.6323秒，每秒傳輸速度為11276.59Mb每秒
5.OLTP測試
(1)100線程
數(shù)據(jù)準備：

?

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_common.lua \ > --mysql-user=tpcc --mysql-password=tpcc --mysql-socket=/tmp/mysql3306.sock \ > --tables=10 --table_size=20000000 --threads=100 --max-requests=0 prepare sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Initializing worker threads...Creating table 'sbtest1'... Creating table 'sbtest4'... Creating table 'sbtest2'... Creating table 'sbtest9'... Creating table 'sbtest6'... Creating table 'sbtest8'... Creating table 'sbtest7'... Creating table 'sbtest5'... Creating table 'sbtest10'... Creating table 'sbtest3'... Inserting 20000000 records into 'sbtest1' Inserting 20000000 records into 'sbtest7' Inserting 20000000 records into 'sbtest4' Inserting 20000000 records into 'sbtest9' Inserting 20000000 records into 'sbtest6' Inserting 20000000 records into 'sbtest8' Inserting 20000000 records into 'sbtest2' Inserting 20000000 records into 'sbtest5' Inserting 20000000 records into 'sbtest3' Inserting 20000000 records into 'sbtest10' Creating a secondary index on 'sbtest4'... Creating a secondary index on 'sbtest1'... Creating a secondary index on 'sbtest10'... Creating a secondary index on 'sbtest3'... Creating a secondary index on 'sbtest6'... Creating a secondary index on 'sbtest8'... Creating a secondary index on 'sbtest7'... Creating a secondary index on 'sbtest9'... Creating a secondary index on 'sbtest5'... Creating a secondary index on 'sbtest2'...

開始測試：

?

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=tpcc --mysql-password=tpcc --table_size=20000000 --tables=10 --threads=100 --time=3600 --report-interval=10 --mysql-socket=/tmp/mysql3306.sock run > /tmp/40_100.log

測試過程中，可以tail -f /tmp/40_100.log查看輸出
測試結(jié)果：這里只展示統(tǒng)計部分

?

SQL statistics:queries performed:read: 143195290write: 40912940other: 20456470total: 204564700transactions: 10228235 (2841.15 per sec.)queries: 204564700 (56823.07 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)Throughput:events/s (eps): 2841.1536time elapsed: 3600.0289stotal number of events: 10228235Latency (ms):min: 2.49avg: 35.19max: 2017.5495th percentile: 39.65sum: 359973625.38Threads fairness:events (avg/stddev): 102282.3500/5194.20execution time (avg/stddev): 3599.7363/0.01

(2)200線程
數(shù)據(jù)準備：

?

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_common.lua \ > --mysql-user=tpcc --mysql-password=tpcc --mysql-socket=/tmp/mysql3306.sock \ > --tables=10 --table_size=20000000 --threads=200 --max-requests=0 prepare

開始測試：

?

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_read_write.lua \ --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=tpcc \ --mysql-password=tpcc --table_size=20000000 --tables=10 --threads=200 --time=3600 \ --report-interval=10 --mysql-socket=/tmp/mysql3306.sock run > /tmp/40_200.log

測試結(jié)果：

?

SQL statistics:queries performed:read: 142876258write: 40821788other: 20410894total: 204108940transactions: 10205447 (2834.80 per sec.)queries: 204108940 (56696.02 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)Throughput:events/s (eps): 2834.8009time elapsed: 3600.0577stotal number of events: 10205447Latency (ms):min: 2.39avg: 70.55max: 2011.6595th percentile: 80.03sum: 719978372.22Threads fairness:events (avg/stddev): 51027.2350/3840.19execution time (avg/stddev): 3599.8919/0.02

(3)300線程
數(shù)據(jù)準備：

?

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_common.lua \ > --mysql-user=tpcc --mysql-password=tpcc --mysql-socket=/tmp/mysql3306.sock \ > --tables=10 --table_size=20000000 --threads=300 --max-requests=0 prepare

開始測試：

?

sysbench /usr/local/share/sysbench/oltp_read_write.lua \ --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest \ --mysql-user=tpcc --mysql-password=tpcc --table_size=20000000 --tables=10 --threads=300 \ --time=3600 --report-interval=10 --mysql-socket=/tmp/mysql3306.sock run > /tmp/40_300.log

測試結(jié)果：

?

SQL statistics:queries performed:read: 144281438write: 41223268other: 20611634total: 206116340transactions: 10305817 (2862.66 per sec.)queries: 206116340 (57253.12 per sec.)ignored errors: 0 (0.00 per sec.)reconnects: 0 (0.00 per sec.)Throughput:events/s (eps): 2862.6561time elapsed: 3600.0891stotal number of events: 10305817Latency (ms):min: 2.64avg: 104.79max: 4576.6095th percentile: 123.28sum: 1079986824.04Threads fairness:events (avg/stddev): 34352.7233/2459.96execution time (avg/stddev): 3599.9561/0.03

生成壓測圖
安裝gnuplot

?

plot 'D:\40_100.log' using 9 with linepoint title '100 threads','D:\40_200.log' using 9 with linepoint title '200 threads','D:\40_300.log' using 9 with linepoint title '300 threads';

多線程QPS對比圖

多線程TPS對比圖

三、壓測注意事項

1.在OLTP測試過程中，數(shù)據(jù)庫初始化后，冷備data目錄，每次測試完畢后刪除當前數(shù)據(jù)目錄，拷貝data目錄重新啟動，為的是每次開始測試前環(huán)境保持一致。
2.測試完成后，需要再系統(tǒng)層做一些清理的工作
shell>sync #刷新臟數(shù)據(jù)到磁盤
shell>echo 3 >/proc/sys/vm/drop_cache #清除cache
shell>swapoff -a && swapon -a 釋放swap
3.壓測過程中需要修改參數(shù)max_prepared_stmt_count默認為16382，修改為65536
4.如果是SSD硬盤的話，innodb_io_capacity和innodb_io_capacity_max兩個參數(shù)也要相應(yīng)調(diào)大，建議調(diào)整到50000以上。
5.對于測試數(shù)據(jù)的準備，我們通過show engine innodb status觀察Free buffers,盡量模擬生產(chǎn)環(huán)境，讓測試數(shù)據(jù)填滿整個buffer pool。我這里innodb_buffer_pool_size設(shè)置的40G，測試表數(shù)量10張，每張表數(shù)據(jù)2000萬，差不多填滿buffer pool。

下篇會給大家?guī)鞩nnoDB引擎和TokuDB引擎的壓力測試。

作者：拾荒者charley
鏈接：https://www.jianshu.com/p/9823b4aa445a
來源：簡書
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。

總結(jié)

以上是生活随笔為你收集整理的【MySQL】sysbench压测服务器及结果解读的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。