最近公司準備去oracle铐望，遷移到mysql集群捌刮。分布式數(shù)據(jù)庫中間件我們技術(shù)選型選擇了mycat。這無疑前期的準備工作需要做好叹卷，在運維同學的幫助下撼港，集群已經(jīng)搭建好，接下來我們?nèi)蝿?wù)就是壓測骤竹。這篇文章簡單記錄我所選擇的壓測工具Sysbench的安裝和使用帝牡。
sysbench 簡介
Sysbench是一個模塊化的、跨平臺蒙揣、多線程基準測試工具靶溜，主要用于評估測試各種不同系統(tǒng)參數(shù)下的數(shù)據(jù)庫負載情況。它主要包括以下幾種方式的測試：cpu性能懒震，磁盤io性能罩息，線程調(diào)度性能，內(nèi)存分配及傳輸速度和數(shù)據(jù)庫性能个扰。由于本人是dba瓷炮，因此重點關(guān)注sysbench測試數(shù)據(jù)庫的場景。目前sysbench支持mysql递宅，postgreSQL崭别，oracle三種數(shù)據(jù)源。
測試環(huán)境
系統(tǒng)：Centos-8
Sysbench：sysbench-0.4.12.10,下載地址：https://github.com/akopytov/sysbench
也可以直接點擊下載: http://downloads.mysql.com/source/sysbench-0.4.12.10.tar.gz
安裝步驟
sysbench的一些依賴安裝
yum -y install make automake libtool pkgconfig libaio-devel vim-common
如果沒有安裝mysql,則需要安裝mysql-devel
yum install mysql-devel
安裝sysbench
- 進入到sysbench源碼目錄
cd /data/sysbench/sysbench-0.4.12
- 執(zhí)行autogen.sh用它來生成configure這個文件
./autogen.sh
- 執(zhí)行configure && make && make install 來完成sysbench的安裝
./configure --prefix=/wls/sysbench/ --build=x86_64
make && make install
注：如果不添加--build=x86_64可能會提示如下錯誤：
checking build system type... Invalid configuration `x86_64-unknown-linux-': machine `x86_64-unknown-linux' not recognized
configure: error: /bin/sh config/config.sub x86_64-unknown-linux- failed
驗證是否安裝成功
[root@05823ea529b0 sysbench-1.0.15]# /wls/sysbench/bin/sysbench --version
sysbench 0.4.12.10
[root@05823ea529b0 sysbench-1.0.15]# ./sysbench --test=cpu run
sysbench 0.4.12.10 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Prime numbers limit: 10000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 1158.09
General statistics:
total time: 10.0005s
total number of events: 11583
Latency (ms):
min: 0.79
avg: 0.86
max: 1.92
95th percentile: 1.01
sum: 9985.69
Threads fairness:
events (avg/stddev): 11583.0000/0.00
execution time (avg/stddev): 9.9857/0.00
sysbench 查看幫助
Usage:
sysbench [general-options]... --test=<test-name> [test-options]... command
General options:
--num-threads=N number of threads to use [1]
--max-requests=N limit for total number of requests [10000]
--max-time=N limit for total execution time in seconds [0]
--forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off]
--thread-stack-size=SIZE size of stack per thread [32K]
--init-rng=[on|off] initialize random number generator [off]
--seed-rng=N seed for random number generator, ignored when 0 [0]
--tx-rate=N target transaction rate (tps) [0]
--tx-jitter=N target transaction variation, in microseconds [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. []
--test=STRING test to run
--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
Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [4]
--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
oltp - OLTP test
Commands: prepare run cleanup help version
See 'sysbench --test=<name> help' for a list of options for each test.
sysbench 對數(shù)據(jù)庫進行壓力測試的過程
- prepare 階段 這個階段是用來做準備的恐锣、比較說建立好測試用的表茅主、并向表中填充數(shù)據(jù);
- run階段 這個階段是才是去跑壓力測試的SQL;
- cleanup 階段 這個階段是去清除數(shù)據(jù)的、也就是prepare階段初始化好的表要都drop掉;
sysbench 中的測試類型大致可以分成內(nèi)置的土榴，lua腳本自定義的測試
- 內(nèi)置: fileio 诀姚、cpu 、memory 玷禽、threads 赫段、 mutex
- lua腳本自定義型：sysbench 自身內(nèi)涵了一些測試腳本放在了安裝目錄下:
查看幫忙命令:
[root@izwz909ewdz83smewux7a7z bin]# ./sysbench --help
Usage:
sysbench [general-options]... --test=<test-name> [test-options]... command
General options:
--num-threads=N number of threads to use [1]
--max-requests=N limit for total number of requests [10000]
--max-time=N limit for total execution time in seconds [0]
--forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off]
--thread-stack-size=SIZE size of stack per thread [32K]
--init-rng=[on|off] initialize random number generator [off]
--seed-rng=N seed for random number generator, ignored when 0 [0]
--tx-rate=N target transaction rate (tps) [0]
--tx-jitter=N target transaction variation, in microseconds [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. []
--test=STRING test to run
--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
Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [4]
--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
oltp - OLTP test
Commands: prepare run cleanup help version
See 'sysbench --test=<name> help' for a list of options for each test.
通過sysbench自帶的lua腳本對mysql進行測試
- 生成表文件
[root@izwz909ewdz83smewux7a7z bin]# ./sysbench --test=oltp --oltp-num-tables=100000 --oltp-num-tables=10 --mysql-db=sysbench --mysql-user=root --mysql-password='mydb' --mysql-host=localhost --mysql-port=3306 --db-driver=mysql --init-rng=on prepare
- 多線程測試
[root@izwz909ewdz83smewux7a7z bin]# ./sysbench --test=oltp --oltp-table-size=100000 --mysql-db=sysbench --mysql-user=root --mysql-password='mydb' --mysql-host=localhost --mysql-port=3306 --db-driver=mysql --oltp-read-only=off --max-time=60 --num-threads=8 --report-interval=10 --oltp-dist-type=uniform --max-requests=0 --percentile=99 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored
Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Using 1 test tables
Threads started!
[ 10s] Intermediate results: 8 threads, tps: 582.994023, reads/s: 8233.315586, writes/s: 2940.469852 response time: 48.261058ms (99%)
[ 20s] Intermediate results: 8 threads, tps: 589.090480, reads/s: 8248.666697, writes/s: 2945.952392 response time: 35.829896ms (99%)
[ 30s] Intermediate results: 8 threads, tps: 487.603128, reads/s: 6829.243810, writes/s: 2439.015647 response time: 65.415122ms (99%)
[ 40s] Intermediate results: 8 threads, tps: 487.700557, reads/s: 6830.607800, writes/s: 2439.502786 response time: 78.027721ms (99%)
[ 50s] Intermediate results: 8 threads, tps: 478.852588, reads/s: 6703.936228, writes/s: 2394.262939 response time: 71.711459ms (99%)
[ 60s] Intermediate results: 8 threads, tps: 510.752034, reads/s: 7150.528473, writes/s: 2553.760169 response time: 43.136643ms (99%)
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.
OLTP test statistics:
queries performed:
read: 440076 讀總數(shù)
write: 157170 寫總數(shù)
other: 62812 他操作總數(shù)(SELECT呀打、INSERT、UPDATE糯笙、DELETE之外的操作贬丛，例如COMMIT等)
total: 660058 總數(shù)
transactions: 31378 (522.91 per sec.) 總事務(wù)數(shù)(每秒事務(wù)數(shù))
deadlocks: 56 (0.93 per sec.)
read/write requests: 597246 (9953.02 per sec.) 讀寫總數(shù)(每秒讀寫次數(shù))
other operations: 62812 (1046.75 per sec.)
General statistics:
total time: 60.0065s 總耗時
total number of events: 31378 共發(fā)生多少事務(wù)數(shù)
total time taken by event execution: 479.8756 所有事務(wù)耗時相加(不考慮并行因素)
response time:
min: 3.77ms 最小耗時
avg: 15.29ms 平均耗時
max: 180.78ms 最長耗時
approx. 99 percentile: 58.12ms 超過99%平均耗時
Threads fairness:
events (avg/stddev): 3922.2500/6.02
execution time (avg/stddev): 59.9845/0.00
- 清理上面生成的測試表
[root@izwz909ewdz83smewux7a7z bin]# ./sysbench --test=oltp --oltp-num-tables=100000 --oltp-num-tables=10 --mysql-db=sysbench --mysql-user=root --mysql-password='mydb' --mysql-host=localhost --mysql-port=3306 --db-driver=mysql --init-rng=on cleanup
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Dropping table 'sbtest'...
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'...
Done.
[root@izwz909ewdz83smewux7a7z bin]#
由于上面的命令都比較簡單,相信大家都能看明白,有什么疑問也歡迎大家留言交流.
可以對數(shù)據(jù)庫進行調(diào)優(yōu)后，再使用sysbench對OLTP進行測試给涕，看看TPS是不是會有所提高豺憔。
注意：sysbench的測試只是基準測試，并不能代表實際企業(yè)環(huán)境下的性能指標够庙。
個人網(wǎng)站: http://coderluo.top
歡迎關(guān)注筆者公眾號: 愛上敲代碼, 會定期分享Java技術(shù)干活,讓枯燥的技術(shù)游起來!
掃碼關(guān)注
【本文最先發(fā)布于此站恭应，轉(zhuǎn)載請注明來源】： http://coderluo.top/2019/10/13/database/shu-ju-ku-ya-ce-gong-ju-sysbench-0.4.12-an-zhuang/
