sysbench的安裝和做效能測試

一、安裝

# 下載原始碼包，按以下步驟安裝
# 下載原始碼包
tar zxf sysbench-0.4.12.tar.gz 
cd sysbench-0.4.12 
# 配置。mysql的安裝路徑 /usr/local/mysql/
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib

# 編譯
make

錯誤1：X--tag=CC: command not found

解決辦法：vim libtool(當前目錄下)

# An echo program that does not interpret backslashes.

#ECHO="echo"

替換為

echo="echo"

          http://blog.itpub.net/26250550/viewspace-1682634/
# 安裝
make install

二、測試
每種測試模式的詳細引數
    mode：fileio，cpu，memory，threads，mutex，oltp
    sysbench --test= help
    --num-threads=N,是每種測試都需要設定的

fileio
    sysbench --num-threads=N --test=fileio --file-test-mode=
        --num-threads=N,根據資料庫的IO執行緒數(讀寫各4個，purge1個，主執行緒)+max_user_connections(不確定是否加這個，理論上應該不加)
        --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
            oltp使用rndrw; olap 使用seqrd; 日誌：使用seqwr;
    sysbench --num-threads=10 --test=fileio --file-test-mode=rndrw prepare
    sysbench --num-threads=10 --test=fileio --file-test-mode=rndrw run
    sysbench --num-threads=10 --test=fileio --file-test-mode=rndrw cleanup
    

點選(此處)摺疊或開啟

1. Operations performed: 6005 Read, 3998 Write, 12807 Other = 22810 Total
   
2. Read 93.828Mb Written 62.469Mb Total transferred 156.3Mb (2.8269Mb/sec)
   
3.   180.92 Requests/sec executed
   
4. 
   
5. Test execution summary:
   
6.     total time: 55.2882s
   
7.     total number of events: 10003
   
8.     total time taken by event execution: 116.1046
   
9.     per-request statistics:
   
10.          min: 0.01ms
    
11.          avg: 11.61ms
    
12.          max: 127.19ms
    
13.          approx. 95 percentile: 45.21ms
    
14. 
    
15. Threads fairness:
    
16.     events (avg/stddev): 1000.3000/50.58
    
17.     execution time (avg/stddev): 11.6105/0.22

cpu
    sysbench --num-threads=500 --test=cpu run
        --num-threads=N,根據max_user_connections

點選(此處)摺疊或開啟

1. Test execution summary:
   
2.     total time: 2.7773s
   
3.     total number of events: 10000
   
4.     total time taken by event execution: 929.5525
   
5.     per-request statistics:
   
6.          min: 1.10ms
   
7.          avg: 92.96ms
   
8.          max: 1990.11ms
   
9.          approx. 95 percentile: 524.92ms
   
10. 
    
11. Threads fairness:
    
12.     events (avg/stddev): 20.0000/15.88
    
13.     execution time (avg/stddev): 1.8591/0.52

memory
    sysbench --num-threads=500 --test=memory --memory-block-size=16K run
        --num-threads=N,根據max_user_connections
        --memory-block-size= 16K, 與頁大小一致
        --memory-oper=write|read，測試不同模式下的速度
    

點選(此處)摺疊或開啟

1. Operations performed: 6553600 (144538.32 ops/sec)
   
2. 
   
3. 102400.00 MB transferred (2258.41 MB/sec)
   
4. 
   
5. 
   
6. Test execution summary:
   
7.     total time: 45.3416s
   
8.     total number of events: 6553600
   
9.     total time taken by event execution: 17959.0829
   
10.     per-request statistics:
    
11.          min: 0.00ms
    
12.          avg: 2.74ms
    
13.          max: 1360.60ms
    
14.          approx. 95 percentile: 0.03ms
    
15. 
    
16. Threads fairness:
    
17.     events (avg/stddev): 13107.2000/394.74
    
18.     execution time (avg/stddev): 35.9182/0.88

mutex
    sysbench --num-threads=500 --test=mutex run
        --num-threads=N,根據max_user_connections    

點選(此處)摺疊或開啟

1. Test execution summary:
   
2.     total time: 4.3611s
   
3.     total number of events: 100
   
4.     total time taken by event execution: 432.1235
   
5.     per-request statistics:
   
6.          min: 4148.02ms
   
7.          avg: 4321.24ms
   
8.          max: 4356.73ms
   
9.          approx. 95 percentile: 4350.67ms
   
10. 
    
11. Threads fairness:
    
12.     events (avg/stddev): 1.0000/0.00
    
13.     execution time (avg/stddev): 4.3212/0.03

threads
    sysbench --num-threads=500 --test=threads run    

點選(此處)摺疊或開啟

1. Test execution summary:
   
2.     total time: 3.3147s
   
3.     total number of events: 10000
   
4.     total time taken by event execution: 329.1616
   
5.     per-request statistics:
   
6.          min: 0.53ms
   
7.          avg: 32.92ms
   
8.          max: 228.56ms
   
9.          approx. 95 percentile: 85.67ms
   
10. 
    
11. Threads fairness:
    
12.     events (avg/stddev): 100.0000/4.70
    
13.     execution time (avg/stddev): 3.2916/0.01

oltp
    1：oltp: 500執行緒，innodb表，最大100W行資料
    sysbench --num-threads=500 --test=oltp --oltp-test-mode=complex --mysql-host=localhost --mysql-user=sbtest --mysql-password=sbtest --mysql-db=test_sb --oltp-table-size=1000000  prepare
        --num-threads=N,根據max_user_connections
        --oltp-test-mode=complex，使用混合測試
    sysbench --num-threads=500 --test=oltp --oltp-test-mode=complex --mysql-host=localhost --mysql-user=sbtest --mysql-password=sbtest --mysql-db=test_sb --oltp-table-size=1000000  run
    sysbench --num-threads=500 --test=oltp --oltp-test-mode=complex --mysql-host=localhost --mysql-user=sbtest --mysql-password=sbtest --mysql-db=test_sb --oltp-table-size=1000000  cleanup
    

點選(此處)摺疊或開啟

1. OLTP test statistics:
2. queries performed:
3. read: 145194
4. write: 50922
5. other: 20374
6. total: 216490
7. transactions: 10003 (372.70 per sec.)
8. deadlocks: 368 (13.71 per sec.)
9. read/write requests: 196116 (7307.05 per sec.)
10. other operations: 20374 (759.11 per sec.)
12. Test execution summary:
13. total time: 26.8393s
14. total number of events: 10003
15. total time taken by event execution: 2679.0895
16. per-request statistics:
17. min: 40.40ms
18. avg: 267.83ms
19. max: 1712.27ms
20. approx. 95 percentile: 566.22ms
22. Threads fairness:
23. events (avg/stddev): 100.0300/4.68
24. execution time (avg/stddev): 26.7909/0.03