【MySQL】利用sysbench進行基準測試
/*sysbench安裝篇------------------------------------------------------------------------------------------------------*/
⑴安裝epel包後以便安裝bzr客戶端：
rpm -Uvh
⑵獲取sysbench
cd /usr/local/src
bzr branch lp:sysbench
⑶編譯安裝
[root@node1 sysbench]# ./autogen.sh
[root@node1 bin]#./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
[root@node1 bin]#make && make install
編譯出現錯誤
/usr/bin/ld: cannot find -lmysqlclient_r
collect2: ld returned 1 exit status
make[2]: *** [sysbench] Error 1
解決方法：
mv libmysqlclient_r.so.16.0.0 libmysqlclient_r.so
/*sysbench使用篇---------------------------------------------------------------------------------------------------*/
⑴全域性性引數
root@node1 bin]# ./sysbench help
sysbench 0.5: multi-threaded system evaluation benchmark
Usage:
sysbench [general-options]... --test= [test-options]... command
General options:
--num-threads=N number of threads to use [1]//使用的執行緒數量
--max-requests=N limit for total number of requests [10000]//sysbench最大請求數，為0時，表示無限制使用
--max-time=N limit for total execution time in seconds [0]//sysbench執行最長的時間
--forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off]//超過--max-time強制結束sysbench
--thread-stack-size=SIZE size of stack per thread [64K]
--tx-rate=N target transaction rate (tps) [0]
--report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]//每隔多少秒列印統計資料
--report-checkpoints=[LIST,...]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []//dump統計資料並重新復位計數器
--test=STRING test to run//測試的內容項，cpu
--debug=[on|off] print more debugging info [off]
--validate=[on|off] perform validation checks where possible [off]
--help=[on|off] print help and exit
--version=[on|off] print version and exit [off]//是否隨機初始化資料,如果不隨機化那麼初始好的資料每行內容除了主鍵不同外其他完全相同。
--rand-init=[on|off] initialize random number generator [off]//
--rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]//資料分佈-均勻分佈，正態分佈，特殊分佈
--rand-spec-iter=N number of iterations used for numbers generation [12]
--rand-spec-pct=N percentage of values to be treated as 'special' (for special distribution) [1]//應該是百分一的值是特殊值
--rand-spec-res=N percentage of 'special' values to use (for special distribution) [75]//資料中百分之75是特殊值
--rand-seed=N seed for random number generator, ignored when 0 [0]
--rand-pareto-h=N parameter h for pareto distibution [0.2]
Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]
--percentile=N percentile rank of query response times to count [95]
Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test
Commands: prepare run cleanup help version
See 'sysbench --test= help' for a list of options for each test.
⑵CPU引數
[root@node1 bin]# ./sysbench --test=cpu help
sysbench 0.5: multi-threaded system evaluation benchmark
cpu options:
--cpu-max-prime=N upper limit for primes generator [10000]//求一萬以內的素數（預設）
No help available for test 'cpu'.
來一次cpu的測試
[root@node1 bin]#
[root@node1 bin]# ./sysbench --test=cpu --cpu-max-prime=20000 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Primer numbers limit: 20000
Threads started!
General statistics:
total time: 49.3380s
total number of events: 10000
total time taken by event execution: 49.3346s
response time:
min: 4.93ms
avg: 4.93ms
max: 5.05ms
approx. 95 percentile: 4.94ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 49.3346/0.00
⑵IO引數
[root@node1 bin]# ./sysbench --test=fileio help
sysbench 0.5: multi-threaded system evaluation benchmark
fileio options:
--file-num=N number of files to create [128]//檔案個數
--file-block-size=N block size to use in all IO operations [16384]
--file-total-size=SIZE total size of files to create [2G]//檔案總共大小
--file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}//測試模式，順序寫入，順序讀寫，順序讀，隨機讀，隨機寫，隨機讀寫
--file-io-mode=STRING file operations mode {sync,async,mmap} [sync]//是否同步寫磁碟或者直接只對映到記憶體
--file-async-backlog=N number of asynchronous operatons to queue per thread [128]//每個執行緒的佇列多少個非同步操作
--file-extra-flags=STRING additional flags to use on opening files {sync,dsync,direct} []//direct不寫iocache，直接寫磁碟
--file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100]
--file-fsync-all=[on|off] do fsync() after each write operation [off]//執行完一次寫操作，就執行一次fsync。
--file-fsync-end=[on|off] do fsync() at the end of test [on]// 每在測試結束時執行fsync函式
--file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
--file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0]//儘可能的合併io操作
--file-rw-ratio=N reads/writes ratio for combined test [1.5]//讀寫比例1.5
No help available for test 'fileio'.
[root@node1 bin]#
來一次IO測試
準備資料：
[root@node1 test]# /usr/local/sysbench/bin/sysbench --test=fileio --file-num=16 --file-total-size=10G prepare
sysbench 0.5: multi-threaded system evaluation benchmark
16 files, 655360Kb each, 10240Mb total
Creating files for the test...
Extra file open flags: 0
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
10737418240 bytes written in 335.80 seconds (30.49 MB/sec).
看看是否有建立的16個檔案(建立檔案為你的當前目錄)
[root@node1 test]# ls -l
total 10485872
-rw------- 1 root root 671088640 Jun 11 09:43 test_file.0
-rw------- 1 root root 671088640 Jun 11 09:43 test_file.1
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.10
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.11
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.12
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.13
-rw------- 1 root root 671088640 Jun 11 09:48 test_file.14
-rw------- 1 root root 671088640 Jun 11 09:48 test_file.15
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.2
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.3
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.4
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.5
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.6
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.7
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.8
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.9
[root@node1 test]#
測試：
[root@node1 test]# /usr/local/sysbench/bin/sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrd --max-time=180 --max-requests=100000000 --num-threads=16 --init-rng=on --file-num=16 --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored
Extra file open flags: 3
16 files, 640Mb each
10Gb total file size
Block size 16Kb
Number of IO requests: 100000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Operations performed: 21301 reads, 0 writes, 0 Other = 21301 Total
Read 332.83Mb Written 0b Total transferred 332.83Mb (1.8478Mb/sec)--------------從此處可以看出此機器的效能較差,見過一臺機器的75MB/sec,4895Request/sec，應該算效能比較好的
118.26 Requests/sec executed
General statistics:
total time: 180.1207s
total number of events: 21301
total time taken by event execution: 2881.0238s
response time:
min: 0.38ms
avg: 135.25ms
max: 595.49ms
approx. 95 percentile: 248.44ms
Threads fairness:
events (avg/stddev): 1331.3125/190.50
execution time (avg/stddev): 180.0640/0.03
[root@node1 test]#
刪除測試資料
[root@node1 test]# /usr/local/sysbench/bin/sysbench --test=fileio --file-num=16 --file-total-size=10G cleanup
sysbench 0.5: multi-threaded system evaluation benchmark
Removing test files...
[root@node1 test]# ls -l
total 0
[root@node1 test]#
⑶執行緒引數
[root@node1 bin]# ./sysbench --test=threads help
sysbench 0.5: multi-threaded system evaluation benchmark
threads options:
--thread-yields=N number of yields to do per request [1000]//這裡應該是指使用者級執行緒的個數每個請求
--thread-locks=N number of locks per thread [8]//每個執行緒鎖的數量---這個不理解，知識面不夠吧
No help available for test 'threads'.
[root@node1 bin]#
⑷線上事物處理引數oltp
[root@VM-MYSQL-TEST-LiDan ~]# sysbench --test=oltp help
sysbench 0.5: multi-threaded system evaluation benchmark
PANIC: unprotected error in call to Lua API (cannot open oltp: No such file or directory)
sysbench-0.5已將oltp引數去掉,最新版開始採用lua指令碼---當然本人很不習慣，但引數還是跟以前一樣
http://blog.csdn.net/lidan3959/article/details/36876517
--oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]//採用得測試模式
--oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]//重新連線模式{session(不使用重新連線。每個執行緒斷開只在測試結束),transaction(在每次事務結束後重新連線),query(在每個SQL語句執行完重新連線),random(對於每個事務隨機選擇以上重新連線模式)}。預設是session
--oltp-sp-name=STRING name of store procedure to call in SP test mode []//跟上面的測試對應，採用自定義儲存過程
--oltp-read-only=[on|off] generate only 'read' queries (do not modify database) [off]//產生只讀查詢
--oltp-skip-trx=[on|off] skip BEGIN/COMMIT statements [off]//感覺相當於事務沒用
--oltp-range-size=N range size for range queries [100]//查詢的範圍1-100行，可定小於--oltp-table-size
--oltp-point-selects=N number of point selects [10]//一個事物中點（點指的是測試模式語句中的id=N）查詢十次
--oltp-simple-ranges=N number of simple ranges [1]//一個事務中的範圍查詢的次數（範圍指的是between N and M）
--oltp-sum-ranges=N number of sum ranges [1]
--oltp-order-ranges=N number of ordered ranges [1]
--oltp-distinct-ranges=N number of distinct ranges [1]
--oltp-index-updates=N number of index update [1]
--oltp-non-index-updates=N number of non-index updates [1]
--oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]//nontrx模式只用select語句
--oltp-auto-inc=[on|off] whether AUTO_INCREMENT (or equivalent) should be used on id column [on]//AUTO_INCREMENT開啟
--oltp-connect-delay=N time in microseconds to sleep after connection to database [10000]//什麼含義？
--oltp-user-delay-min=N minimum time in microseconds to sleep after each request [0]
--oltp-user-delay-max=N maximum time in microseconds to sleep after each request [0]
--oltp-table-name=STRING name of test table [sbtest]//測試的表，預設sbtest
--oltp-table-size=N number of records in test table [10000]//表的行數
--oltp-dist-type=STRING random numbers distribution {uniform,gaussian,special} [special]----參考全域性引數
--oltp-dist-iter=N number of iterations used for numbers generation [12]
--oltp-dist-pct=N percentage of values to be treated as 'special' (for special distribution) [1]
--oltp-dist-res=N percentage of 'special' values to use (for special distribution) [75]
--oltp-tables-count=N 表示生成幾個測試表
--mysql-table-engine=innodb 指定表型別
關於測試模式
測試模式有三種：
①simple mode，如果指定為此mode,那麼在執行過程中， sysbench的每個thread將只會執行如下的語句: SELECT c FROM sbtest WHERE id=N N值是個隨機值，它的範圍1...
②advanced transactional mode如果啟用此模式，那麼sysbench的每個thread將會在測試表上（sbtest）上執行事務。不過要注意的是，對於表(sbtest使用 的引擎)是否支援事務，其執 行的過程是不一樣的。如果是innodb engine,那麼它將會使用BEGIN/COMMIT 語句來start/stop一個事務；如果是MyISAM engine,sysbench將會使用LOCK TABLES/UNLOCK TABLES語句。 在一個事務中若是有一些記錄被刪除了，不用擔心，在同一個事務中，會有相同的記錄被補回。所以同一張sbtest測試表可以多次用來進 行測試。這個mode中包含執行的語句如下：
Point queries: SELECT c FROM sbtest WHERE id=N
Range queries: SELECT c FROM sbtest WHERE id BETWEEN N AND M
Range SUM() queries: SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M
Range ORDER BY queries: SELECT c FROM sbtest WHERE id between N and M ORDER BY c
Range DISTINCT queries: SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c
UPDATEs on index column: UPDATE sbtest SET k=k+1 WHERE id=N
UPDATEs on non-index column: UPDATE sbtest SET c=N WHERE id=M
DELETE queries:DELETE FROM sbtest WHERE id=N
INSERT queries:INSERT INTO sbtest VALUES (...)
③Non-transcational mode這個mode與simple mode相似，不同的地方是可以自主選擇想要執行的query,這裡要注意的是，它與Advanced transcational mode的區別，它在查詢請求間不會 保護測試表中的資料，所以如果有多次測試的話，需要進行cleanup/prepare操作。它包含執行的語句如下：
Point queries: SELECT pad FROM sbtest WHERE id=N
UPDATEs on index column:UPDATE sbtest SET k=k+1 WHERE id=N
UPDATEs on non-index column:UPDATE sbtest SET c=N WHERE id=M
DELETE queries:DELETE FROM sbtest WHERE id=N
The generated row IDs are unique over each test run, so no row is deleted twice.
INSERT queries:INSERT INTO sbtest (k, c, pad) VALUES(N, M, S)
來一次mysql的事務測試
準備資料：
[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=50 --max-requests=100000000 --report-interval=1 --test=/usr/local/sysbench/oltp/oltp.lua prepare
sysbench 0.5: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 6000000 records into 'sbtest1'
測試資料
[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=50 --max-requests=100000000 --report-interval=1 --max-time=20 --test=/usr/local/sysbench/oltp/oltp.lua run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 50
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored
Threads started!
[ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 2s] threads: 50, tps: 0.00, reads: 235.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 3s] threads: 50, tps: 0.00, reads: 86.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 4s] threads: 50, tps: 0.00, reads: 99.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 5s] threads: 50, tps: 0.00, reads: 107.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 6s] threads: 50, tps: 0.00, reads: 53.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
OLTP test statistics:
queries performed:
read: 246162
write: 70332
other: 35166
total: 351660
transactions: 17583 (17.54 per sec.)
read/write requests: 316494 (315.67 per sec.)
other operations: 35166 (35.07 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1002.6230s
total number of events: 17583
total time taken by event execution: 50069.6673s
response time:
min: 76.80ms
avg: 2847.62ms
max: 11148.33ms
approx. 95 percentile: 5788.65ms
Threads fairness:
events (avg/stddev): 351.6600/9.02
execution time (avg/stddev): 1001.3933/0.75
[root@node1 test]#
[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=8 --max-requests=1024 --report-interval=1 --test=/usr/local/sysbench/oltp/oltp.lua cleanup
sysbench 0.5: multi-threaded system evaluation benchmark
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
[root@node1 test]#
⑴安裝epel包後以便安裝bzr客戶端：
rpm -Uvh
⑵獲取sysbench
cd /usr/local/src
bzr branch lp:sysbench
⑶編譯安裝
[root@node1 sysbench]# ./autogen.sh
[root@node1 bin]#./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
[root@node1 bin]#make && make install
編譯出現錯誤
/usr/bin/ld: cannot find -lmysqlclient_r
collect2: ld returned 1 exit status
make[2]: *** [sysbench] Error 1
解決方法：
mv libmysqlclient_r.so.16.0.0 libmysqlclient_r.so
/*sysbench使用篇---------------------------------------------------------------------------------------------------*/
⑴全域性性引數
root@node1 bin]# ./sysbench help
sysbench 0.5: multi-threaded system evaluation benchmark
Usage:
sysbench [general-options]... --test= [test-options]... command
General options:
--num-threads=N number of threads to use [1]//使用的執行緒數量
--max-requests=N limit for total number of requests [10000]//sysbench最大請求數，為0時，表示無限制使用
--max-time=N limit for total execution time in seconds [0]//sysbench執行最長的時間
--forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off]//超過--max-time強制結束sysbench
--thread-stack-size=SIZE size of stack per thread [64K]
--tx-rate=N target transaction rate (tps) [0]
--report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]//每隔多少秒列印統計資料
--report-checkpoints=[LIST,...]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []//dump統計資料並重新復位計數器
--test=STRING test to run//測試的內容項，cpu
--debug=[on|off] print more debugging info [off]
--validate=[on|off] perform validation checks where possible [off]
--help=[on|off] print help and exit
--version=[on|off] print version and exit [off]//是否隨機初始化資料,如果不隨機化那麼初始好的資料每行內容除了主鍵不同外其他完全相同。
--rand-init=[on|off] initialize random number generator [off]//
--rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]//資料分佈-均勻分佈，正態分佈，特殊分佈
--rand-spec-iter=N number of iterations used for numbers generation [12]
--rand-spec-pct=N percentage of values to be treated as 'special' (for special distribution) [1]//應該是百分一的值是特殊值
--rand-spec-res=N percentage of 'special' values to use (for special distribution) [75]//資料中百分之75是特殊值
--rand-seed=N seed for random number generator, ignored when 0 [0]
--rand-pareto-h=N parameter h for pareto distibution [0.2]
Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]
--percentile=N percentile rank of query response times to count [95]
Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test
Commands: prepare run cleanup help version
See 'sysbench --test= help' for a list of options for each test.
⑵CPU引數
[root@node1 bin]# ./sysbench --test=cpu help
sysbench 0.5: multi-threaded system evaluation benchmark
cpu options:
--cpu-max-prime=N upper limit for primes generator [10000]//求一萬以內的素數（預設）
No help available for test 'cpu'.
來一次cpu的測試
[root@node1 bin]#
[root@node1 bin]# ./sysbench --test=cpu --cpu-max-prime=20000 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Primer numbers limit: 20000
Threads started!
General statistics:
total time: 49.3380s
total number of events: 10000
total time taken by event execution: 49.3346s
response time:
min: 4.93ms
avg: 4.93ms
max: 5.05ms
approx. 95 percentile: 4.94ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 49.3346/0.00
⑵IO引數
[root@node1 bin]# ./sysbench --test=fileio help
sysbench 0.5: multi-threaded system evaluation benchmark
fileio options:
--file-num=N number of files to create [128]//檔案個數
--file-block-size=N block size to use in all IO operations [16384]
--file-total-size=SIZE total size of files to create [2G]//檔案總共大小
--file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}//測試模式，順序寫入，順序讀寫，順序讀，隨機讀，隨機寫，隨機讀寫
--file-io-mode=STRING file operations mode {sync,async,mmap} [sync]//是否同步寫磁碟或者直接只對映到記憶體
--file-async-backlog=N number of asynchronous operatons to queue per thread [128]//每個執行緒的佇列多少個非同步操作
--file-extra-flags=STRING additional flags to use on opening files {sync,dsync,direct} []//direct不寫iocache，直接寫磁碟
--file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100]
--file-fsync-all=[on|off] do fsync() after each write operation [off]//執行完一次寫操作，就執行一次fsync。
--file-fsync-end=[on|off] do fsync() at the end of test [on]// 每在測試結束時執行fsync函式
--file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
--file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0]//儘可能的合併io操作
--file-rw-ratio=N reads/writes ratio for combined test [1.5]//讀寫比例1.5
No help available for test 'fileio'.
[root@node1 bin]#
來一次IO測試
準備資料：
[root@node1 test]# /usr/local/sysbench/bin/sysbench --test=fileio --file-num=16 --file-total-size=10G prepare
sysbench 0.5: multi-threaded system evaluation benchmark
16 files, 655360Kb each, 10240Mb total
Creating files for the test...
Extra file open flags: 0
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
10737418240 bytes written in 335.80 seconds (30.49 MB/sec).
看看是否有建立的16個檔案(建立檔案為你的當前目錄)
[root@node1 test]# ls -l
total 10485872
-rw------- 1 root root 671088640 Jun 11 09:43 test_file.0
-rw------- 1 root root 671088640 Jun 11 09:43 test_file.1
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.10
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.11
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.12
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.13
-rw------- 1 root root 671088640 Jun 11 09:48 test_file.14
-rw------- 1 root root 671088640 Jun 11 09:48 test_file.15
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.2
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.3
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.4
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.5
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.6
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.7
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.8
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.9
[root@node1 test]#
測試：
[root@node1 test]# /usr/local/sysbench/bin/sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrd --max-time=180 --max-requests=100000000 --num-threads=16 --init-rng=on --file-num=16 --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored
Extra file open flags: 3
16 files, 640Mb each
10Gb total file size
Block size 16Kb
Number of IO requests: 100000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Operations performed: 21301 reads, 0 writes, 0 Other = 21301 Total
Read 332.83Mb Written 0b Total transferred 332.83Mb (1.8478Mb/sec)--------------從此處可以看出此機器的效能較差,見過一臺機器的75MB/sec,4895Request/sec，應該算效能比較好的
118.26 Requests/sec executed
General statistics:
total time: 180.1207s
total number of events: 21301
total time taken by event execution: 2881.0238s
response time:
min: 0.38ms
avg: 135.25ms
max: 595.49ms
approx. 95 percentile: 248.44ms
Threads fairness:
events (avg/stddev): 1331.3125/190.50
execution time (avg/stddev): 180.0640/0.03
[root@node1 test]#
刪除測試資料
[root@node1 test]# /usr/local/sysbench/bin/sysbench --test=fileio --file-num=16 --file-total-size=10G cleanup
sysbench 0.5: multi-threaded system evaluation benchmark
Removing test files...
[root@node1 test]# ls -l
total 0
[root@node1 test]#
⑶執行緒引數
[root@node1 bin]# ./sysbench --test=threads help
sysbench 0.5: multi-threaded system evaluation benchmark
threads options:
--thread-yields=N number of yields to do per request [1000]//這裡應該是指使用者級執行緒的個數每個請求
--thread-locks=N number of locks per thread [8]//每個執行緒鎖的數量---這個不理解，知識面不夠吧
No help available for test 'threads'.
[root@node1 bin]#
⑷線上事物處理引數oltp
[root@VM-MYSQL-TEST-LiDan ~]# sysbench --test=oltp help
sysbench 0.5: multi-threaded system evaluation benchmark
PANIC: unprotected error in call to Lua API (cannot open oltp: No such file or directory)
sysbench-0.5已將oltp引數去掉,最新版開始採用lua指令碼---當然本人很不習慣，但引數還是跟以前一樣
http://blog.csdn.net/lidan3959/article/details/36876517
--oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]//採用得測試模式
--oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]//重新連線模式{session(不使用重新連線。每個執行緒斷開只在測試結束),transaction(在每次事務結束後重新連線),query(在每個SQL語句執行完重新連線),random(對於每個事務隨機選擇以上重新連線模式)}。預設是session
--oltp-sp-name=STRING name of store procedure to call in SP test mode []//跟上面的測試對應，採用自定義儲存過程
--oltp-read-only=[on|off] generate only 'read' queries (do not modify database) [off]//產生只讀查詢
--oltp-skip-trx=[on|off] skip BEGIN/COMMIT statements [off]//感覺相當於事務沒用
--oltp-range-size=N range size for range queries [100]//查詢的範圍1-100行，可定小於--oltp-table-size
--oltp-point-selects=N number of point selects [10]//一個事物中點（點指的是測試模式語句中的id=N）查詢十次
--oltp-simple-ranges=N number of simple ranges [1]//一個事務中的範圍查詢的次數（範圍指的是between N and M）
--oltp-sum-ranges=N number of sum ranges [1]
--oltp-order-ranges=N number of ordered ranges [1]
--oltp-distinct-ranges=N number of distinct ranges [1]
--oltp-index-updates=N number of index update [1]
--oltp-non-index-updates=N number of non-index updates [1]
--oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]//nontrx模式只用select語句
--oltp-auto-inc=[on|off] whether AUTO_INCREMENT (or equivalent) should be used on id column [on]//AUTO_INCREMENT開啟
--oltp-connect-delay=N time in microseconds to sleep after connection to database [10000]//什麼含義？
--oltp-user-delay-min=N minimum time in microseconds to sleep after each request [0]
--oltp-user-delay-max=N maximum time in microseconds to sleep after each request [0]
--oltp-table-name=STRING name of test table [sbtest]//測試的表，預設sbtest
--oltp-table-size=N number of records in test table [10000]//表的行數
--oltp-dist-type=STRING random numbers distribution {uniform,gaussian,special} [special]----參考全域性引數
--oltp-dist-iter=N number of iterations used for numbers generation [12]
--oltp-dist-pct=N percentage of values to be treated as 'special' (for special distribution) [1]
--oltp-dist-res=N percentage of 'special' values to use (for special distribution) [75]
--oltp-tables-count=N 表示生成幾個測試表
--mysql-table-engine=innodb 指定表型別
關於測試模式
測試模式有三種：
①simple mode，如果指定為此mode,那麼在執行過程中， sysbench的每個thread將只會執行如下的語句: SELECT c FROM sbtest WHERE id=N N值是個隨機值，它的範圍1...
②advanced transactional mode如果啟用此模式，那麼sysbench的每個thread將會在測試表上（sbtest）上執行事務。不過要注意的是，對於表(sbtest使用 的引擎)是否支援事務，其執 行的過程是不一樣的。如果是innodb engine,那麼它將會使用BEGIN/COMMIT 語句來start/stop一個事務；如果是MyISAM engine,sysbench將會使用LOCK TABLES/UNLOCK TABLES語句。 在一個事務中若是有一些記錄被刪除了，不用擔心，在同一個事務中，會有相同的記錄被補回。所以同一張sbtest測試表可以多次用來進 行測試。這個mode中包含執行的語句如下：
Point queries: SELECT c FROM sbtest WHERE id=N
Range queries: SELECT c FROM sbtest WHERE id BETWEEN N AND M
Range SUM() queries: SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M
Range ORDER BY queries: SELECT c FROM sbtest WHERE id between N and M ORDER BY c
Range DISTINCT queries: SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c
UPDATEs on index column: UPDATE sbtest SET k=k+1 WHERE id=N
UPDATEs on non-index column: UPDATE sbtest SET c=N WHERE id=M
DELETE queries:DELETE FROM sbtest WHERE id=N
INSERT queries:INSERT INTO sbtest VALUES (...)
③Non-transcational mode這個mode與simple mode相似，不同的地方是可以自主選擇想要執行的query,這裡要注意的是，它與Advanced transcational mode的區別，它在查詢請求間不會 保護測試表中的資料，所以如果有多次測試的話，需要進行cleanup/prepare操作。它包含執行的語句如下：
Point queries: SELECT pad FROM sbtest WHERE id=N
UPDATEs on index column:UPDATE sbtest SET k=k+1 WHERE id=N
UPDATEs on non-index column:UPDATE sbtest SET c=N WHERE id=M
DELETE queries:DELETE FROM sbtest WHERE id=N
The generated row IDs are unique over each test run, so no row is deleted twice.
INSERT queries:INSERT INTO sbtest (k, c, pad) VALUES(N, M, S)
來一次mysql的事務測試
準備資料：
[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=50 --max-requests=100000000 --report-interval=1 --test=/usr/local/sysbench/oltp/oltp.lua prepare
sysbench 0.5: multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 6000000 records into 'sbtest1'
測試資料
[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=50 --max-requests=100000000 --report-interval=1 --max-time=20 --test=/usr/local/sysbench/oltp/oltp.lua run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 50
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored
Threads started!
[ 1s] threads: 50, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 2s] threads: 50, tps: 0.00, reads: 235.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 3s] threads: 50, tps: 0.00, reads: 86.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 4s] threads: 50, tps: 0.00, reads: 99.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 5s] threads: 50, tps: 0.00, reads: 107.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
[ 6s] threads: 50, tps: 0.00, reads: 53.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00
OLTP test statistics:
queries performed:
read: 246162
write: 70332
other: 35166
total: 351660
transactions: 17583 (17.54 per sec.)
read/write requests: 316494 (315.67 per sec.)
other operations: 35166 (35.07 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1002.6230s
total number of events: 17583
total time taken by event execution: 50069.6673s
response time:
min: 76.80ms
avg: 2847.62ms
max: 11148.33ms
approx. 95 percentile: 5788.65ms
Threads fairness:
events (avg/stddev): 351.6600/9.02
execution time (avg/stddev): 1001.3933/0.75
[root@node1 test]#
[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=8 --max-requests=1024 --report-interval=1 --test=/usr/local/sysbench/oltp/oltp.lua cleanup
sysbench 0.5: multi-threaded system evaluation benchmark
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
[root@node1 test]#
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30221425/viewspace-1695811/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 利用sysbench進行MySQL OLTP基準測試MySql
- 使用Sysbench對滴滴雲MySQL進行基準測試MySql
- MySQL基準測試工具sysbenchMySql
- 【Mysql】sysbench基準測試工具MySql
- 詳解 MySQL 基準測試和 sysbench 工具MySql
- 【工具】基準測試工具之sysbench
- 如何使用hammerdb進行MySQL基準測試MySql
- 資料庫基準測試工具 sysbench資料庫
- sysbench 測試MySQLMySql
- sysbench測試mysql oltpMySql
- 華納雲：如何使用HammerDB進行MySQL基準測試MySql
- 使用 Sysbench 進行 Linux 效能測試Linux
- MySQL基準測試MySql
- 使用sysbench測試Mysql效能MySql
- Mysql 壓力測試工具sysbenchMySql
- 使用 sysbench 測試 MySQL 的效能MySql
- MySQL學習 - 基準測試MySql
- TPCC-MySQL基準測試MySql
- 【MYSQL 基準測試結果】MySql
- mysql效能測試工具之sysbench薦MySql
- 使用 sysbench 測試 MySQL 的效能(二)MySql
- 公有云RDS-MySQL基準測試MySql
- MySQL基準壓力測試工具MySQLSlapMySql
- 使用sysbench測試mysql及postgresql(完整版)MySql
- mysql ,tidb sysbench 測試結果記錄MySqlTiDB
- 利用jmeter進行資料庫測試JMeter資料庫
- hadoop基準測試_Hadoop TeraSort基準測試Hadoop
- sysbench測試軟體
- [總結] 簡述 MySQL 基準測試工具MySql
- 【總結】簡述 MySQL 基準測試工具MySql
- 【工具】基準測試工具之tpcc-mysqlMySql
- mysql壓力測試在青雲PCIE盤sysbench版本MySql
- 利用HSQLDB 進行Hibernate單元測試SQL
- 利用 Rize 來進行 UI 測試或 E2E 測試UI
- MySQL效能基準測試對比：5.7 VS 8.0MySql
- mysql之 sysbench0.4.12資料庫效能測試MySql資料庫
- MySQL DB Server 上面安裝 sysbench 作壓力測試MySqlServer
- MySQL壓力測試中定製sysbench的Lua模板MySql