Mysql 压力测试工具sysbench

Mysql 压力测试工具sysbench

---

下载：sysbench-0.4.10.tar.gz
http://pan.baidu.com/s/1kTzVkVH

安装步骤：

[root@mysqlsource]# tar -xvf sysbench-0.4.10.tar.gz
[root@mysqlsource]# cd sysbench-0.4.10
[root@mysqlsource sysbench-0.4.10]# mkdir ddsysbench

拷贝系统的libtool到sysbench的source code目录下：
[root@mysqlsource sysbench-0.4.10]# cp /usr/bin/libtool /tools/sysbench-0.4.12

[root@mysqlsource sysbench-0.4.10]# ./configure --prefix=/root/sysbench-0.4.10/ddsysbench/ --with-mysql-includes=/mysql5.6.22/include/mysql --with-mysql-libs=/mysql5.6.22/lib64/mysql

./configure --prefix=/mysql/tools/sysbench  \ --指定安装目录
--with-mysql-includes=/mysql/include \ --指定mysql数据库的include目录
--with-mysql-libs=/mysql/lib --指定mysql数据库的lib目录

[root@mysqlsource sysbench-0.4.10]# make && make install

报错：
[root@mysqlsource ddsysbench]# ./bin/sysbench --help
./bin/sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
解决：
[root@mysqlsource sysbench-0.4.10]# export LD_LIBRARY_PATH=/mysql5.6.22/lib64/mysql/
[root@mysqlsource sysbench-0.4.10]# cd ddsysbench/
[root@mysqlsource ddsysbench]# ./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]
  --thread-stack-size=SIZE   size of stack per thread [32K]
  --init-rng=[on|off]        initialize random number generator [off]
  --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


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.

[root@mysqlsource ddsysbench]#

开始测试：
建表：
bin/sysbench --test=oltp \           --测试类型数据库oltp
--mysql-host=127.0.0.1 \             --mysql主机
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陆用户
--mysql-password=$password \         --mysql密码
--mysql-db=test \                    --mysql测试数据库名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=test \             --mysql测试表名
--mysql-table-engine=innodb \        --mysql表使用存储引擎
--oltp-table-size=1000000 prepare    --表数据量


[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock  --mysql-table-engine=innodb --oltp-table-name=test --oltp-table-size=1000000 prepare
sysbench 0.4.10:  multi-threaded system evaluation benchmark


No DB drivers specified, using mysql
Creating table 'test'...
Creating 1000000 records in table 'test'...
[root@mysqlsource ddsysbench]#


模拟访问：
bin/sysbench --test=oltp \
--mysql-host=127.0.0.1 \             --mysql主机
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陆用户
--mysql-password=$password \         --mysql密码
--mysql-db=test \                    --mysql测试数据库名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=test \             --mysql测试表名
--mysql-table-engine=innodb \        --mysql表使用存储引擎
--max-requests=100000            \   --测试请求次数
--max-time=600                 \     --测试最长时间
--num-threads=100 run                --并发线程数


[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock  --mysql-table-engine=innodb --oltp-table-name=test --max-requests=100000  --max-time=600 --num-threads=100 run
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 100000
Threads started!
Done.


OLTP test statistics:
    queries performed:
        read:                            1647128
        write:                           542106
        other:                           217655
        total:                           2406889
    transactions:                        100003 (374.65 per sec.)
    deadlocks:                           17649  (66.12 per sec.)
    read/write requests:                 2189234 (8201.73 per sec.)
    other operations:                    217655 (815.42 per sec.)


Test execution summary:
    total time:                          266.9234s
    total number of events:              100003
    total time taken by event execution: 26677.3311
    per-request statistics:
         min:                                  4.92ms
         avg:                                266.77ms
         max:                               5116.57ms
         approx.  95 percentile:             774.87ms


Threads fairness:
    events (avg/stddev):           1000.0300/31.11
    execution time (avg/stddev):   266.7733/0.06


清理数据：
[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock  --mysql-table-engine=innodb --oltp-table-name=test cleanup
sysbench 0.4.10:  multi-threaded system evaluation benchmark


No DB drivers specified, using mysql
Dropping table 'test'...
Done.
[root@mysqlsource ddsysbench]# 




测试实例：
表属性compress对insert速度的影响。
第一步准备：
创建表结构
bin/sysbench --test=oltp \           --测试类型数据库oltp
--mysql-host=127.0.0.1 \             --mysql主机
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陆用户
--mysql-password=$password \         --mysql密码
--mysql-db=test \                    --mysql测试数据库名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=sbtest \             --mysql测试表名
--mysql-table-engine=innodb \        --mysql表使用存储引擎
--oltp-table-size=1 prepare    --表数据量
将表名修改为uncompressed
rename table sbtest to uncompressed;
再次创建表sbtest，并将表名修改为compressed
rename table sbtest to compressed;
将表改为compressed
alter table compressed ROW_FORMAT=compressed;
第二步：向表uncompressed表中添加记录
bin/sysbench --test=oltp           \
--mysql-host=127.0.0.1 \             --mysql主机
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陆用户
--mysql-password=$password \         --mysql密码
--mysql-db=test \                    --mysql测试数据库名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=uncompressed \             --mysql测试表名
--mysql-table-engine=innodb \        --mysql表使用存储引擎
--oltp-nontrx-mode=insert      \
--oltp-test-mode=nontrx        \
--max-requests=1000000            \
--max-time=600                 \
--num-threads=100 run


测试结果：
[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123ql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock  --mysql-name=test --oltp-table-size=1 pr-table-engine=innodb --oltp-table-name=uncompressed --oltp-nontrx-mode=insert --oltp-test-mode=nontrx --max-requests=1000000 --max-time=600 --num-threads=100 run
sysbench 0.4.10:  multi-threaded system evaluation benchmark


No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 100


Doing OLTP test.
Running non-transactional 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 1000000
Threads started!
Done.


OLTP test statistics:
    queries performed:
        read:                            0
        write:                           1000279
        other:                           0
        total:                           1000279
    transactions:                        1000279 (9492.38 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000279 (9492.38 per sec.)
    other operations:                    0      (0.00 per sec.)


Test execution summary:
    total time:                          105.3771s
    total number of events:              1000279
    total time taken by event execution: 10534.3501
    per-request statistics:
         min:                                  1.28ms
         avg:                                 10.53ms
         max:                               1001.98ms
         approx.  95 percentile:              22.96ms


Threads fairness:
    events (avg/stddev):           10002.7900/49.66
    execution time (avg/stddev):   105.3435/0.01


[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock  --mysql-table-engine=innodb --oltp-table-name=compressed --oltp-nontrx-mode=insert --oltp-test-mode=nontrx --max-requests=1000000 --max-time=600 --num-threads=100 run
sysbench 0.4.10:  multi-threaded system evaluation benchmark


No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 100


Doing OLTP test.
Running non-transactional 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 1000000
Threads started!
Time limit exceeded, exiting...
(last message repeated 99 times)
Done.


OLTP test statistics:
    queries performed:
        read:                            0
        write:                           986965
        other:                           0
        total:                           986965
    transactions:                        986965 (1643.82 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 986965 (1643.82 per sec.)
    other operations:                    0      (0.00 per sec.)


Test execution summary:
    total time:                          600.4096s
    total number of events:              986965
    total time taken by event execution: 60028.4337
    per-request statistics:
         min:                                  1.05ms
         avg:                                 60.82ms
         max:                              17930.03ms
         approx.  95 percentile:             223.06ms


Threads fairness:
    events (avg/stddev):           9869.6500/52.19
    execution time (avg/stddev):   600.2843/0.07








安装mysql：


[root@mysqlsource mysql-5.6.22]# cmake . 
-DCMAKE_INSTALL_PREFIX=/mysql5.6.22/ 
-DINSTALL_SBINDIR=/mysql5.6.22/sbin 
-DMYSQL_DATADIR=/mysql5.6.22/data/ 
-DSYSCONFDIR=/mysql5.6.22/etc/ 
-DINSTALL_PLUGINDIR=lib64/mysql/plugin 
-DINSTALL_MANDIR=share/man 
-DINSTALL_SHAREDIR=share 
-DINSTALL_LIBDIR=lib64/mysql 
-DINSTALL_INCLUDEDIR=include/mysql 
-DINSTALL_INFODIR=share/info 
-DWITH_INNOBASE_STORAGE_ENGINE=1  
-DWITH_ARCHIVE_STORAGE_ENGINE=1 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 
-DWITH_READLINE=1 
-DWITH_SSL=system 
-DWITH_ZLIB=system 
-DWITH_LIBWRAP=0 
-DMYSQL_UNIX_ADDR=/mysql5.6.22/tmp/mysqld.sock 
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_EMBEDDED_SERVER=1 
-DMYSQL_TCP_PORT=3306


均要在basedir下运行脚本，不然会提示找不到文件。
[root@mysqlsource mysql5.6.22]# ./scripts/mysql_install_db --user=mysql --basedir=/mysql5.6.22/ --datadir=/mysql5.6.22/data/ --defaults-file=/mysql5.6.22/etc/my.cnf --user=mysql 


[root@mysqlsource mysql5.6.22]# ./bin/mysqld_safe --defaults-file=/mysql5.6.22/etc/my.cnf &
[root@mysqlsource bin]# ./mysqld_safe --defaults-file=/mysql5.6.22/etc/my.cnf &（会有报错）
[1] 3301
[root@mysqlsource bin]# 150909 09:57:25 mysqld_safe Logging to '/mysql5.6.22/mysql-error.log'.
150909 09:57:25 mysqld_safe The file /mysql5.6.22//mysql5.6.22/sbin/mysqld
does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows:
./bin/mysqld_safe&
See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information
^C


[root@mysqlsource bin]# ./mysqladmin shutdown -u root -p --socket=/mysql5.6.22/tmp/mysql.sock




工具使用：
[root@mysqlsource ddsysbench]# ./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]
  --thread-stack-size=SIZE   size of stack per thread [32K]
  --init-rng=[on|off]        initialize random number generator [off]
  --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


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.


[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --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]
  --thread-stack-size=SIZE   size of stack per thread [32K]
  --init-rng=[on|off]        initialize random number generator [off]
  --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


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.


[root@mysqlsource ddsysbench]# ./bin/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} [complex]
  --oltp-sp-name=STRING           name of store procedure to call in SP test mode []
  --oltp-read-only=[on|off]       generate only 'read' queries (do not modify database) [off]
  --oltp-skip-trx=[on|off]        skip BEGIN/COMMIT statements [off]
  --oltp-range-size=N             range size for range queries [100]
  --oltp-point-selects=N          number of point selects [10]
  --oltp-simple-ranges=N          number of simple ranges [1]
  --oltp-sum-ranges=N             number of sum ranges [1]
  --oltp-order-ranges=N           number of ordered ranges [1]
  --oltp-distinct-ranges=N        number of distinct ranges [1]
  --oltp-index-updates=N          number of index update [1]
  --oltp-non-index-updates=N      number of non-index updates [1]
  --oltp-nontrx-mode=STRING       mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select] //查询插入等等操作
  --oltp-auto-inc=[on|off]        whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
  --oltp-connect-delay=N          time in microseconds to sleep after connection to database [10000]
  --oltp-user-delay-min=N         minimum time in microseconds to sleep after each request [0]
  --oltp-user-delay-max=N         maximum time in microseconds to sleep after each request [0]
  --oltp-table-name=STRING        name of test table [sbtest]
  --oltp-table-size=N             number of records in test table [10000]
  --oltp-dist-type=STRING         random numbers distribution {uniform,gaussian,special} [special]
  --oltp-dist-iter=N              number of iterations used for numbers generation [12]
  --oltp-dist-pct=N               percentage of values to be treated as 'special' (for special distribution) [1]
  --oltp-dist-res=N               percentage of 'special' values to use (for special distribution) [75]


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]




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=STRING         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]
  --myisam-max-rows=N           max-rows parameter for MyISAM tables [1000000]
  --mysql-create-options=STRING additional options passed to CREATE TABLE []


[root@mysqlsource ddsysbench]#

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1799120/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29500582/viewspace-1799120/