使用sysbench壓測主機和資料庫

lazydba發表於2016-07-05

交付伺服器或資料庫的時候,我們需要對伺服器和資料庫的效能有一定的瞭解。可以使用sysbench對系統做一些壓測。

sysbench

sysbench下載

https://github.com/akopytov/sysbench

可以使用0.5版本,支援lua外掛壓測資料庫

sysbench 資料庫壓測lua指令碼例子

https://github.com/percona/sysbench-scripts

Usage:
  sysbench --test=<test-name> [options]... <command>

Commands: prepare run cleanup help version

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 [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. []
  --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 [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]
  --rand-seed=N               seed for random number generator, ignored when 0 [0]
  --rand-pareto-h=N           parameter h for pareto distibution [0.2]
  --config-file=FILENAME      File containing command line options

Log options:
  --verbosity=N      verbosity level {5 - debug, 0 - only critical messages} [3]

  --percentile=N      percentile rank of query response times to count [95]

General database options:

  --db-driver=STRING  specifies database driver to use (`help` to get list of available drivers)
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  --db-debug=[on|off] print database-specific debug information [off]


Compiled-in database drivers:
  mysql - MySQL driver

mysql options:
  --mysql-host=[LIST,...]      MySQL server host [localhost]
  --mysql-port=N               MySQL server port [3306]
  --mysql-socket=[LIST,...]    MySQL socket
  --mysql-user=STRING          MySQL user [sbtest]
  --mysql-password=STRING      MySQL password []
  --mysql-db=STRING            MySQL database name [sbtest]
  --mysql-table-engine=STRING  storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
  --mysql-engine-trx=STRING    whether storage engine used is transactional or not {yes,no,auto} [auto]
  --mysql-ssl=[on|off]         use SSL connections, if available in the client library [off]
  --mysql-compression=[on|off] use compression, if available in the client library [off]
  --myisam-max-rows=N          max-rows parameter for MyISAM tables [1000000]
  --mysql-debug=[on|off]       dump all client library calls [off]
  --mysql-ignore-errors=[LIST,...]list of errors to ignore, or "all" [1213,1020,1205]
  --mysql-dry-run=[on|off]     Dry run, pretent that all MySQL client API calls are successful without executing them [off]

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

See `sysbench --test=<name> help` for a list of options for each test.

cpu

sysbench --test=cpu run

指定多個執行緒:
sysbench --test=cpu --num-threads=30 run

指定測試時間:
sysbench --test=cpu --max-time=10 run

指定測試請求數
sysbench --test=cpu --max-requests=100000 --num-threads=100 run

io

$ 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} []
  --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]
  --file-fsync-end=[on|off]     do fsync() at the end of test [on]
  --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]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]

io測試中比較重要的幾個引數:

file-test-mode: 讀還是寫?順序訪問還是隨機訪問? 對於資料庫,需要測試重點關注隨機IO的效能。

file-fsync-freq: 測試fsync的效能。

file-rw-ratio:讀寫比例

io 測試

準備測試檔案

mkdir test
cd test
sysbench --test=fileio --file-test-mode=rndrw prepare


dtstack@test01:~/test$  ls -lh
total 2.0G
-rw------- 1 dtstack dtstack 16M Jun 28 23:00 test_file.0
-rw------- 1 dtstack dtstack 16M Jun 28 23:00 test_file.1
-rw------- 1 dtstack dtstack 16M Jun 28 23:00 test_file.10
-rw------- 1 dtstack dtstack 16M Jun 28 23:00 test_file.100
-rw------- 1 dtstack dtstack 16M Jun 28 23:00 test_file.101


執行壓測, 隨機讀寫
sysbench --test=fileio --file-test-mode=rndrw --max-requests=100000 --num-threads=16 run
壓測執行過程中,注意觀察io效能。比如我們的這個測試機器,寫入iops 500左右,寫資料吞度量8M/s, 響應時間近30ms,效能比較一般。

iostat -kx 3

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.33   115.00    5.33  488.00   114.67  7934.67    32.63    13.67   27.76   14.00   27.91   2.03 100.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.62    0.17    3.12   54.37    0.00   32.73

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   121.00    5.00  474.00   128.00  7732.00    32.82    14.15   29.00    6.40   29.24   2.09 100.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8.98    0.17    2.12   62.49    0.00   26.25

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               2.33   118.00    3.67  488.67   106.67  7909.33    32.56    14.56   30.03   17.09   30.12   2.03 100.00

測試記憶體分配和訪問

dtstack@test01:~/test$ sysbench --test=memory help
sysbench 0.5:  multi-threaded system evaluation benchmark

memory options:
  --memory-block-size=SIZE    size of memory block for test [1K]
  --memory-total-size=SIZE    total size of data to transfer [100G]
  --memory-scope=STRING       memory access scope {global,local} [global]
  --memory-hugetlb=[on|off]   allocate memory from HugeTLB pool [off]
  --memory-oper=STRING        type of memory operations {read, write, none} [write]
  --memory-access-mode=STRING memory access mode {seq,rnd} [seq]

dtstack@test01:~/test$ sysbench --test=memory --num-threads=64 run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64
Random number generator seed is 0 and will be ignored


Initializing worker threads...

Threads started!

Operations performed: 104857600 (890624.94 ops/sec)

102400.00 MB transferred (869.75 MB/sec)

MySQL壓測

sysbench 0.5 使用lua外掛的形式壓測資料庫。

–test 引數指定lua指令碼,lua指令碼的內容參考下面的例子,

或者參考

https://github.com/percona/sysbench-scripts

dtstack@test01:~/junda/sysbench-scripts/timeseries$ sysbench --test=insertv1.lua --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=xx --max-requests=1000 prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

Creating table `sensordata` ...
dtstack@test01:~/junda/sysbench-scripts/timeseries$ sysbench --test=insertv1.lua --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=xx --max-requests=1000 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


Initializing worker threads...

最簡單的lua指令碼,往一個表插入資料

$ cat test.lua

function event(thread_id)
    db_query("insert into t1(a ) values(100)")
end
$ cat create.lua

function event(thread_id)
    for i = 1,10 do
      db_query("create table t" .. i .. "(a int)")
    end
end

$ cat drop.lua

function event(thread_id)
    for i = 1,10 do
      db_query("drop table if exists t" .. i  )
    end
end


相關文章