前言：最近新到一批機(jī)器嫂丙，需要對(duì)機(jī)器進(jìn)行壓測(cè)娘赴，主要壓測(cè)范圍包括CPU測(cè)試、磁盤(pán)IO測(cè)試跟啤、線程測(cè)試诽表、OLTP測(cè)試等，那么sysbench就可以滿足我們的壓測(cè)需求隅肥。下面我們簡(jiǎn)單來(lái)看下sysbench的安裝使用以及壓測(cè)結(jié)果的解讀竿奏。
一、sysbench安裝
sysbench安裝其實(shí)非常簡(jiǎn)單腥放，可以參考下git泛啸，其實(shí)MySQL官網(wǎng)也可以下到，不過(guò)版本比較老了秃症，最新的版本是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
其實(shí)可以查看下README.md,上面也寫(xiě)了如何安裝
## Build and Install
shell
./autogen.sh
# Add --with-pgsql to build with PostgreSQL support
./configure
make -j
make install
二匹耕、壓測(cè)過(guò)程及壓測(cè)結(jié)果解讀
1.cpu測(cè)試
[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 time
Prime numbers limit: 2000000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 0.63
Throughput:
events/s (eps): 0.6334
time elapsed: 11.0520s
total number of events: 7
Latency (ms):
min: 1571.86
avg: 1578.84
max: 1620.53
95th percentile: 1618.78
sum: 11051.91
Threads fairness:
events (avg/stddev): 7.0000/0.00
execution time (avg/stddev): 11.0519/0.00
cpu測(cè)試主要是進(jìn)行素?cái)?shù)的加法運(yùn)算，上面我們的例子中荠雕，指定了最大的質(zhì)數(shù)發(fā)生器數(shù)量為 2000000稳其，可以看出服務(wù)器此次測(cè)試 執(zhí)行時(shí)間 大約為11.05秒
2.磁盤(pán)IO測(cè)試
[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 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: 16
Initializing random number generator from current time
Extra 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.57
Latency (ms):
min: 0.00
avg: 0.06
max: 3.61
95th percentile: 0.31
sum: 156128.82
可以看到，磁盤(pán)的讀IOPS可以達(dá)到68491.05,寫(xiě)IOPS可以達(dá)到45660.96炸卑，fsync可以達(dá)到146103.57
3.線程測(cè)試
[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 time
Initializing worker threads...
Threads started!
Throughput:
events/s (eps): 14851.1802
time elapsed: 10.0036s
total number of events: 148565
Latency (ms):
min: 0.02
avg: 4.31
max: 24.49
95th percentile: 10.46
sum: 639889.93
Threads fairness:
events (avg/stddev): 2321.3281/42.94
execution time (avg/stddev): 9.9983/0.00
(發(fā)送64次/個(gè)測(cè)試線程請(qǐng)求既鞠，每次/個(gè)線程請(qǐng)求產(chǎn)生/生成100個(gè)數(shù)量，每個(gè)線程的鎖數(shù)量為2) 盖文，測(cè)試執(zhí)行時(shí)間為10秒
4.內(nèi)存測(cè)試
[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 time
Running memory speed test with the following options:
block size: 8KiB
total size: 40960MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
Total operations: 5242880 (1443403.42 per second)
40960.00 MiB transferred (11276.59 MiB/sec)
Throughput:
events/s (eps): 1443403.4239
time elapsed: 3.6323s
total number of events: 5242880
Latency (ms):
min: 0.00
avg: 0.00
max: 0.02
95th percentile: 0.00
sum: 2926.10
Threads fairness:
events (avg/stddev): 5242880.0000/0.00
execution time (avg/stddev): 2.9261/0.00
上述參數(shù)指定了本次測(cè)試整個(gè)過(guò)程是在內(nèi)存中傳輸 40G 的數(shù)據(jù)量嘱蛋，每個(gè) block 大小為 8K。測(cè)試結(jié)果顯示：
執(zhí)行時(shí)間為3.6323秒椅寺，每秒傳輸速度為11276.59Mb每秒
5.OLTP測(cè)試
(1)100線程
數(shù)據(jù)準(zhǔn)備：
[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'...
開(kāi)始測(cè)試：
[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
測(cè)試過(guò)程中浑槽，可以tail -f /tmp/40_100.log查看輸出
測(cè)試結(jié)果：這里只展示統(tǒng)計(jì)部分
SQL statistics:
queries performed:
read: 143195290
write: 40912940
other: 20456470
total: 204564700
transactions: 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.1536
time elapsed: 3600.0289s
total number of events: 10228235
Latency (ms):
min: 2.49
avg: 35.19
max: 2017.54
95th percentile: 39.65
sum: 359973625.38
Threads fairness:
events (avg/stddev): 102282.3500/5194.20
execution time (avg/stddev): 3599.7363/0.01
(2)200線程
數(shù)據(jù)準(zhǔn)備：
[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
開(kāi)始測(cè)試：
[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
測(cè)試結(jié)果：
SQL statistics:
queries performed:
read: 142876258
write: 40821788
other: 20410894
total: 204108940
transactions: 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.8009
time elapsed: 3600.0577s
total number of events: 10205447
Latency (ms):
min: 2.39
avg: 70.55
max: 2011.65
95th percentile: 80.03
sum: 719978372.22
Threads fairness:
events (avg/stddev): 51027.2350/3840.19
execution time (avg/stddev): 3599.8919/0.02
(3)300線程
數(shù)據(jù)準(zhǔn)備：
[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
開(kāi)始測(cè)試：
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
測(cè)試結(jié)果：
SQL statistics:
queries performed:
read: 144281438
write: 41223268
other: 20611634
total: 206116340
transactions: 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.6561
time elapsed: 3600.0891s
total number of events: 10305817
Latency (ms):
min: 2.64
avg: 104.79
max: 4576.60
95th percentile: 123.28
sum: 1079986824.04
Threads fairness:
events (avg/stddev): 34352.7233/2459.96
execution time (avg/stddev): 3599.9561/0.03
生成壓測(cè)圖
安裝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對(duì)比圖
多線程TPS對(duì)比圖
三蒋失、壓測(cè)注意事項(xiàng)
1.在OLTP測(cè)試過(guò)程中返帕，數(shù)據(jù)庫(kù)初始化后，冷備data目錄篙挽，每次測(cè)試完畢后刪除當(dāng)前數(shù)據(jù)目錄荆萤，拷貝data目錄重新啟動(dòng)，為的是每次開(kāi)始測(cè)試前環(huán)境保持一致铣卡。
2.測(cè)試完成后链韭，需要再系統(tǒng)層做一些清理的工作
shell>sync #刷新臟數(shù)據(jù)到磁盤(pán)
shell>echo 3 >/proc/sys/vm/drop_cache #清除cache
shell>swapoff -a && swapon -a 釋放swap
3.壓測(cè)過(guò)程中需要修改參數(shù)max_prepared_stmt_count默認(rèn)為16382，修改為65536
4.如果是SSD硬盤(pán)的話煮落，innodb_io_capacity和innodb_io_capacity_max兩個(gè)參數(shù)也要相應(yīng)調(diào)大敞峭，建議調(diào)整到50000以上。
5.對(duì)于測(cè)試數(shù)據(jù)的準(zhǔn)備蝉仇，我們通過(guò)show engine innodb status觀察Free buffers,盡量模擬生產(chǎn)環(huán)境旋讹，讓測(cè)試數(shù)據(jù)填滿整個(gè)buffer pool。我這里innodb_buffer_pool_size設(shè)置的40G轿衔，測(cè)試表數(shù)量10張沉迹，每張表數(shù)據(jù)2000萬(wàn)，差不多填滿buffer pool害驹。
下篇會(huì)給大家?guī)?lái)InnoDB引擎和TokuDB引擎的壓力測(cè)試鞭呕。
