數(shù)據(jù)庫(kù)壓測(cè)工具Sysbench-0.4.12安裝

最近公司準(zhǔn)備去oracle，遷移到mysql集群。分布式數(shù)據(jù)庫(kù)中間件我們技術(shù)選型選擇了mycat。這無(wú)疑前期的準(zhǔn)備工作需要做好，在運(yùn)維同學(xué)的幫助下，集群已經(jīng)搭建好，接下來(lái)我們?nèi)蝿?wù)就是壓測(cè)。這篇文章簡(jiǎn)單記錄我所選擇的壓測(cè)工具Sysbench的安裝和使用。

sysbench 簡(jiǎn)介

Sysbench是一個(gè)模塊化的、跨平臺(tái)、多線程基準(zhǔn)測(cè)試工具，主要用于評(píng)估測(cè)試各種不同系統(tǒng)參數(shù)下的數(shù)據(jù)庫(kù)負(fù)載情況。它主要包括以下幾種方式的測(cè)試：cpu性能，磁盤(pán)io性能，線程調(diào)度性能，內(nèi)存分配及傳輸速度和數(shù)據(jù)庫(kù)性能。由于本人是dba，因此重點(diǎn)關(guān)注sysbench測(cè)試數(shù)據(jù)庫(kù)的場(chǎng)景。目前sysbench支持mysql，postgreSQL，oracle三種數(shù)據(jù)源。

測(cè)試環(huán)境

系統(tǒng)：Centos-8

Sysbench：sysbench-0.4.12.10,下載地址：https://github.com/akopytov/sysbench

也可以直接點(diǎn)擊下載: http://downloads.mysql.com/source/sysbench-0.4.12.10.tar.gz

安裝步驟

sysbench的一些依賴安裝

yum -y install  make automake libtool pkgconfig libaio-devel vim-common
如果沒(méi)有安裝mysql,則需要安裝mysql-devel
yum install mysql-devel

安裝sysbench

  1. 進(jìn)入到sysbench源碼目錄
cd /data/sysbench/sysbench-0.4.12
  1. 執(zhí)行autogen.sh用它來(lái)生成configure這個(gè)文件
./autogen.sh
  1. 執(zhí)行configure && make && make install 來(lái)完成sysbench的安裝
./configure --prefix=/wls/sysbench/ --build=x86_64 
make && make install

注：如果不添加--build=x86_64可能會(huì)提示如下錯(cuò)誤：

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

驗(yàn)證是否安裝成功

[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 對(duì)數(shù)據(jù)庫(kù)進(jìn)行壓力測(cè)試的過(guò)程

  • prepare 階段 這個(gè)階段是用來(lái)做準(zhǔn)備的、比較說(shuō)建立好測(cè)試用的表、并向表中填充數(shù)據(jù);
  • run階段 這個(gè)階段是才是去跑壓力測(cè)試的SQL;
  • cleanup 階段 這個(gè)階段是去清除數(shù)據(jù)的、也就是prepare階段初始化好的表要都drop掉;

sysbench 中的測(cè)試類型大致可以分成內(nèi)置的，lua腳本自定義的測(cè)試

  1. 內(nèi)置: fileio 、cpu 、memory 、threads 、 mutex
  2. lua腳本自定義型：sysbench 自身內(nèi)涵了一些測(cè)試腳本放在了安裝目錄下:
查看幫忙命令:
[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.

通過(guò)sysbench自帶的lua腳本對(duì)mysql進(jìn)行測(cè)試

  1. 生成表文件
[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
  1. 多線程測(cè)試
[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 寫(xiě)總數(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.)  讀寫(xiě)總數(shù)(每秒讀寫(xiě)次數(shù))
    other operations:                    62812  (1046.75 per sec.)

General statistics:
    total time:                          60.0065s   總耗時(shí)
    total number of events:              31378  共發(fā)生多少事務(wù)數(shù)
    total time taken by event execution: 479.8756   所有事務(wù)耗時(shí)相加(不考慮并行因素)
    response time:
         min:                                  3.77ms   最小耗時(shí)
         avg:                                 15.29ms   平均耗時(shí)
         max:                                180.78ms   最長(zhǎng)耗時(shí)
         approx.  99 percentile:              58.12ms   超過(guò)99%平均耗時(shí)

Threads fairness:
    events (avg/stddev):           3922.2500/6.02
    execution time (avg/stddev):   59.9845/0.00
  1. 清理上面生成的測(cè)試表
[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]#

由于上面的命令都比較簡(jiǎn)單,相信大家都能看明白,有什么疑問(wèn)也歡迎大家留言交流.

可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行調(diào)優(yōu)后，再使用sysbench對(duì)OLTP進(jìn)行測(cè)試，看看TPS是不是會(huì)有所提高。

注意：sysbench的測(cè)試只是基準(zhǔn)測(cè)試，并不能代表實(shí)際企業(yè)環(huán)境下的性能指標(biāo)。

個(gè)人網(wǎng)站: http://coderluo.top

歡迎關(guān)注筆者公眾號(hào): 愛(ài)上敲代碼, 會(huì)定期分享Java技術(shù)干活,讓枯燥的技術(shù)游起來(lái)!

掃碼關(guān)注

【本文最先發(fā)布于此站，轉(zhuǎn)載請(qǐng)注明來(lái)源】： http://coderluo.top/2019/10/13/database/shu-ju-ku-ya-ce-gong-ju-sysbench-0.4.12-an-zhuang/

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容