Arce 发表于 2021-7-3 21:50:46

mysql性能测试工具之sysbench

  sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数 下的数据库负载情况,主要测试以下几种:
  (1).cpu性能
  (2).磁盘io性能
  (3).调度程序性能
  (4).内存分配及传输速度
  (5).POSIX线程性能
  (6).数据库性能(OLTP基准测试)
  

  mysql dba技术群 378190849
  武汉-linux运维群 236415619
  1.下载和安装sysbench工具
  # wget http://down1.chinaunix.net/distfiles/sysbench-0.4.10.tar.gz
  # cd sysbench-0.4.10
  # ./configure--help | grep mysql       --自己仔细查看安装帮助
--with-mysql            compile with MySQL support (default is enabled)
--with-mysql-includes   path to MySQL header files
--with-mysql-libs       path to MySQL libraries
Report bugs to alexeyk@mysql.com.
  # ./configure--prefix=/usr/local/sysbench-0.4.10 --with-mysql --with-mysql-includes=/usr/local/mysql-5.7.4-m14-linux/include/ --with-mysql-libs=/usr/local/mysql-5.7.4-m14-linux/lib/
  # make       --这时如果以下错误
  drv_mysql.c: In function 'mysql_drv_store_results':
drv_mysql.c:784: error: 'MYSQL' undeclared (first use in this function)
drv_mysql.c:784: error: 'con' undeclared (first use in this function)
drv_mysql.c:785: error: 'MYSQL_RES' undeclared (first use in this function)
drv_mysql.c:785: error: 'res' undeclared (first use in this function)
drv_mysql.c:786: error: 'MYSQL_ROW' undeclared (first use in this function)
drv_mysql.c:786: error: expected ';' before 'row'
drv_mysql.c:834: error: 'ER_LOCK_DEADLOCK' undeclared (first use in this function)
drv_mysql.c:834: error: 'ER_LOCK_WAIT_TIMEOUT' undeclared (first use in this function)
drv_mysql.c:850: error: 'row' undeclared (first use in this function)
drv_mysql.c: In function 'mysql_drv_free_results':
drv_mysql.c:874: error: 'MYSQL_RES' undeclared (first use in this function)
drv_mysql.c:874: error: expected expression before ')' token
make: *** Error 1
make: Leaving directory `/home/sysbench-0.4.10/sysbench/drivers/mysql'
make: *** Error 1
make: Leaving directory `/home/sysbench-0.4.10/sysbench/drivers'
make: *** Error 1
make: Leaving directory `/home/sysbench-0.4.10/sysbench'
make: *** Error 1
  # yum install mysql-devel    --解决方法,安装mysql-devel开发包
  # rpm -ql mysql-devel | grep include    --查看开发包的路径
  # ./configure--prefix=/usr/local/sysbench-0.4.10 --with-mysql --with-mysql-includes=/usr/include/mysql/   --with-mysql-libs=/usr/local/mysql-5.7.4-m14-linux/lib/mysql
  # make      --这时如果再有以下错误
  ../libtool: line 841: X--tag=CC: command not found
../libtool: line 874: libtool: ignoring unknown tag : command not found
../libtool: line 841: X--mode=link: command not found
../libtool: line 1007: *** Warning: inferring the mode of operation is deprecated.: command not found
../libtool: line 1008: *** Future versions of Libtool will require --mode=MODE be specified.: command not found
../libtool: line 2234: X-g: command not found
../libtool: line 2234: X-O2: command not found
../libtool: line 1954: X-L/usr/local/mysql-5.7.4-m14-linux/lib/mysql: No such file or directory
../libtool: line 2403: Xsysbench: command not found
../libtool: line 2408: X: command not found
../libtool: line 2415: Xsysbench: command not found
../libtool: line 2550: X-lmysqlclient_r: command not found
../libtool: line 2550: X-lrt: command not found
../libtool: line 2550: X-lm: command not found
../libtool: line 2632: X-L/home/sysbench-0.4.10/sysbench: No such file or directory
../libtool: line 2550: X-lmysqlclient_r: command not found
../libtool: line 2550: X-lrt: command not found
../libtool: line 2550: X-lm: command not found
../libtool: line 2632: X-L/home/sysbench-0.4.10/sysbench: No such file or directory
  # vim libtool       --解决方法(将libtool文件中的"ECHO="改为"echo=")
  ECHO="echo"       修改为   echo="echo"
  # make                   --此时不会再有错误了
  # make install
  # echo $?                --检查是否安装成功
0
  # ll /usr/local/sysbench-0.4.10/bin/
total 288
-rwxr-xr-x. 1 root root 293649 Oct8 20:00 sysbench
#
  2.测试CPU的性能
  # ./sysbench--help |more             --命令帮助               
Usage:
sysbench ... --test=<test-name> ... command
  General options:
--num-threads=N            number of threads to use
--max-requests=N         limit for total number of requests
--max-time=N               limit for total execution time in seconds
--thread-stack-size=SIZE   size of stack per thread
--init-rng=      initialize random number generator
--test=STRING            test to run
--debug=         print more debugging info
--validate=      perform validation checks where possible
--help=            print help and exit
--version=         print version and exit
  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--test=cpu --cpu-max-prime=20000 run   --测试CPU的性能
sysbench 0.4.10:multi-threaded system evaluation benchmark
  Running the test with following options:
Number of threads: 1
  Doing CPU performance benchmark
  Threads started!
Done.
  Maximum prime number checked in CPU test: 20000
  
Test execution summary:
    total time:                        23.3571s
    total number of events:            10000
    total time taken by event execution: 23.3527
    per-request statistics:
         min:                                  2.23ms
         avg:                                  2.34ms
         max:                                 11.08ms
         approx.95 percentile:               2.58ms
  Threads fairness:
    events (avg/stddev):         10000.0000/0.00
    execution time (avg/stddev):   23.3527/0.00
  #
  3.线程(threads)测试
  # ./sysbench--test=threads --num-threads=64 run
sysbench 0.4.10:multi-threaded system evaluation benchmark
  Running the test with following options:
Number of threads: 64
  Doing thread subsystem performance test
Thread yields per test: 1000 Locks used: 8
Threads started!
Done.
  
Test execution summary:
    total time:                        4.8920s
    total number of events:            10000
    total time taken by event execution: 311.4399
    per-request statistics:
         min:                                  0.39ms
         avg:                                 31.14ms
         max:                               1167.60ms
         approx.95 percentile:            87.74ms
  Threads fairness:
    events (avg/stddev):         156.2500/9.58
    execution time (avg/stddev):   4.8662/0.02
  #
  4.内存(memory)测试
  # ./sysbench --test=memory --memory-block-size=8K --memory-total-size=2G--num-threads=16 run
sysbench 0.4.10:multi-threaded system evaluation benchmark
  Running the test with following options:
Number of threads: 16
  Doing memory operations speed test
Memory block size: 8K
  Memory transfer size: 2048M
  Memory operations type: write
Memory scope type: global
Threads started!
Done.
  Operations performed: 262144 (735089.70 ops/sec)
  2048.00 MB transferred (5742.89 MB/sec)
  
Test execution summary:
    total time:                        0.3566s
    total number of events:            262144
    total time taken by event execution: 4.9494
    per-request statistics:
         min:                                  0.00ms
         avg:                                  0.02ms
         max:                                 47.02ms
         approx.95 percentile:               0.00ms
  Threads fairness:
    events (avg/stddev):         16384.0000/1285.55
    execution time (avg/stddev):   0.3093/0.01
  #
  5.文件(file)的输入和输出测试
  # ./sysbench --test=fileio --file-total-size=20G prepare    --创建20G的文件大小
sysbench 0.4.10:multi-threaded system evaluation benchmark
  128 files, 163840Kb each, 20480Mb total
Creating files for the test...
  #./sysbench --test=fileio --file-total-size=20G --file-test-mode=rndwr run
  --读和写20G的文件输出IO值
sysbench 0.4.10:multi-threaded system evaluation benchmark
  Running the test with following options:
Number of threads: 1
  Extra file open flags: 0
128 files, 160Mb each
20Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Done.
  Operations performed:0 Read, 10000 Write, 12800 Other = 22800 Total
Read 0bWritten 156.25MbTotal transferred 156.25Mb(1.858Mb/sec)
118.91 Requests/sec executed
  Test execution summary:
    total time:                        84.0955s
    total number of events:            10000
    total time taken by event execution: 0.2631
    per-request statistics:
         min:                                  0.01ms
         avg:                                  0.03ms
         max:                                 11.91ms
         approx.95 percentile:               0.05ms
  Threads fairness:
    events (avg/stddev):         10000.0000/0.00
    execution time (avg/stddev):   0.2631/0.00
  #./sysbench --test=fileio --file-total-size=20G --file-test-mode=rndwr cleanup   --删除刚才创建的文件
  6.数据库mysql的测试
  # ./sysbench--test=oltp help         --查看帮助
sysbench 0.4.10:multi-threaded system evaluation benchmark
  oltp options:
--oltp-test-mode=STRING         test type to use {simple,complex,nontrx,sp}
--oltp-sp-name=STRING         name of store procedure to call in SP test mode []
--oltp-read-only=       generate only 'read' queries (do not modify database)
--oltp-skip-trx=      skip BEGIN/COMMIT statements
--oltp-range-size=N             range size for range queries
--oltp-point-selects=N          number of point selects
--oltp-simple-ranges=N          number of simple ranges
--oltp-sum-ranges=N             number of sum ranges
--oltp-order-ranges=N         number of ordered ranges
--oltp-distinct-ranges=N      number of distinct ranges
--oltp-index-updates=N          number of index update
--oltp-non-index-updates=N      number of non-index updates
--oltp-nontrx-mode=STRING       mode for non-transactional test {select, update_key, update_nokey, insert, delete}
--oltp-auto-inc=      whether AUTO_INCREMENT (or equivalent) should be used on id column
--oltp-connect-delay=N          time in microseconds to sleep after connection to database
--oltp-user-delay-min=N         minimum time in microseconds to sleep after each request
--oltp-user-delay-max=N         maximum time in microseconds to sleep after each request
--oltp-table-name=STRING      name of test table
--oltp-table-size=N             number of records in test table
--oltp-dist-type=STRING         random numbers distribution {uniform,gaussian,special}
--oltp-dist-iter=N            number of iterations used for numbers generation
--oltp-dist-pct=N               percentage of values to be treated as 'special' (for special distribution)
--oltp-dist-res=N               percentage of 'special' values to use (for special distribution)
  General database options:
  --db-driver=STRINGspecifies database driver to use ('help' to get list of available drivers)
--db-ps-mode=STRING prepared statements usage mode {auto, disable}
  
Compiled-in database drivers:
mysql - MySQL driver
  mysql options:
--mysql-host=       MySQL server host
--mysql-port=N                MySQL server port
--mysql-socket=STRING         MySQL socket
--mysql-user=STRING         MySQL user
--mysql-password=STRING       MySQL password []
--mysql-db=STRING             MySQL database name
--mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated}
--mysql-engine-trx=STRING   whether storage engine used is transactional or not {yes,no,auto}
--mysql-ssl=          use SSL connections, if available in the client library
--myisam-max-rows=N         max-rows parameter for MyISAM tables
--mysql-create-options=STRING additional options passed to CREATE TABLE []
  # ./sysbench --test=oltp --mysql-user=root --mysql-password=system --mysql-db=tong --oltp-table-name=sbtest --mysql-table-engine=innodb --oltp-table-size=1000000 prepare          --准备测试的数据
sysbench 0.4.10:multi-threaded system evaluation benchmark
  No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
  # ./sysbench --test=oltp --mysql-port=3306 --mysql-user=root --mysql-password=system --mysql-db=tong --oltp-table-name=sbtest --mysql-table-engine=innodb--oltp-table-size=1000000 --max-requests=1000 --num-threads=100 run    --测试mysql的并发和压力
sysbench 0.4.10:multi-threaded system evaluation benchmark
  No DB drivers specified, using mysql
WARNING: Preparing of "BEGIN" is unsupported, using emulation
(last message repeated 99 times)
Running the test with following options:
Number of threads: 100
  Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 1000
Threads started!
Done.
  OLTP test statistics:
    queries performed:
      read:                            14000
      write:                           5000
      other:                           2000
      total:                           21000
    transactions:                        1000   (143.56 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:               19000(2727.71 per sec.)
    other operations:                  2000   (287.13 per sec.)
  Test execution summary:
    total time:                        6.9655s
    total number of events:            1000
    total time taken by event execution: 683.5913
    per-request statistics:
         min:                                 88.12ms
         avg:                              683.59ms
         max:                               3688.93ms
         approx.95 percentile:            1362.33ms
  Threads fairness:
    events (avg/stddev):         10.0000/1.23
    execution time (avg/stddev):   6.8359/0.10
  #

  
页: [1]
查看完整版本: mysql性能测试工具之sysbench