使用 sysbench对mysql进行压力测试介绍之一
sysbench是一款开源的多线程性能测试工具,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL。本文只是简单演示一下几种测试的用法,具体的一些参数设置,需要根据不同的测试要求来进行调整。我也是参考了很多网上的文章,本来以为很简单,实际上比较曲折,还好这几天不上班,天气也不好。
1 安装sysbench
到http://downloads.sourceforge.net/project/sysbench下载sysbench-0.4.12。
tar zxvf sysbench-0.4.12.tar.gz
cd sysbench-0.4.12
[root@testdb2 sysbench-0.4.12]# ./autogen.sh
./autogen.sh: line 3: aclocal: command not found
[root@testdb2 sysbench-0.4.12]# cat autogen.sh
#!/bin/sh
./autogen.sh: line 3: aclocal: command not found
[root@testdb2 sysbench-0.4.12]# cat autogen.sh
#!/bin/sh
aclocal && automake -c --foreign --add-missing && autoheader && autoconf
需要安装一个包:yum install automake
sysbench/tests/cpu/Makefile.am:17: The usual way to define `RANLIB' is to add `AC_PROG_RANLIB'
sysbench/tests/cpu/Makefile.am:17: to `configure.ac' and run `autoconf' again.
sysbench/tests/fileio/Makefile.am:17: library used but `RANLIB' is undefined
sysbench/tests/cpu/Makefile.am:17: to `configure.ac' and run `autoconf' again.
sysbench/tests/fileio/Makefile.am:17: library used but `RANLIB' is undefined
需要增加一个参数
vi configure.ac
# Checks for programs.
AC_PROG_CC
#AC_PROG_LIBTOOL
AC_PROG_RANLIB
AX_CHECK_DOCBOOK
AC_PROG_CC
#AC_PROG_LIBTOOL
AC_PROG_RANLIB
AX_CHECK_DOCBOOK
在执行 ./autogen.sh
./configure --prefix=/usr/local/sysbench \
> --with-mysql-includes=/usr/include/mysql \
> --with-mysql-libs=/usr/lib64/mysql
> --with-mysql-includes=/usr/include/mysql \
> --with-mysql-libs=/usr/lib64/mysql
make
make install
[root@testdb2 bin]# ./sysbench
Missing required command argument.
Usage:
sysbench [general-options]... --test= [test-options]... command
Missing required command argument.
Usage:
sysbench [general-options]... --test= [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]
--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
--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]
--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
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= help' for a list of options for each test.
2 mysql oltp的一些测试选项
[root@testdb2 bin]# ./sysbench --test=oltp help
sysbench 0.4.12: multi-threaded system evaluation benchmark
sysbench 0.4.12: multi-threaded system evaluation benchmark
oltp options:
--oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]
--oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]
--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]
--oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]
--oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]
--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]
--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 []
--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 []
3 分别测试mysql 5.1.51 和mysql 5.5.16
前面还是挺顺利的,不过在这个服务器上安装mysql 5.5.16老是启动就crashed。只好在另外的机器上装,晕啊:
前面还是挺顺利的,不过在这个服务器上安装mysql 5.5.16老是启动就crashed。只好在另外的机器上装,晕啊:
my.cnf:
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 2048M
innodb_log_buffer_size = 16M
innodb_log_buffer_size = 16M
innodb_additional_mem_pool_size = 60M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 0
query_cache_size = 0
init_connect='SET autocommit=0'
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 0
query_cache_size = 0
init_connect='SET autocommit=0'
本机mysql 5.1.51 innodb:
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456 --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=15 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 15
Running the test with following options:
Number of threads: 15
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 10000
Threads started!
Done.
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 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140042
write: 50015
other: 20006
total: 210063
transactions: 10003 (1117.67 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190057 (21235.67 per sec.)
other operations: 20006 (2235.33 per sec.)
queries performed:
read: 140042
write: 50015
other: 20006
total: 210063
transactions: 10003 (1117.67 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190057 (21235.67 per sec.)
other operations: 20006 (2235.33 per sec.)
Test execution summary:
total time: 8.9499s
total number of events: 10003
total time taken by event execution: 133.9374
per-request statistics:
min: 5.21ms
avg: 13.39ms
max: 95.67ms
approx. 95 percentile: 31.75ms
total time: 8.9499s
total number of events: 10003
total time taken by event execution: 133.9374
per-request statistics:
min: 5.21ms
avg: 13.39ms
max: 95.67ms
approx. 95 percentile: 31.75ms
Threads fairness:
events (avg/stddev): 666.8667/17.47
execution time (avg/stddev): 8.9292/0.00
events (avg/stddev): 666.8667/17.47
execution time (avg/stddev): 8.9292/0.00
本机mysql 5.1.51 myisam:
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456 --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=20 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 20
Running the test with following options:
Number of threads: 20
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "LOCK TABLES WRITE" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "LOCK TABLES WRITE" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (154.32 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (2931.99 per sec.)
other operations: 20000 (308.63 per sec.)
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (154.32 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (2931.99 per sec.)
other operations: 20000 (308.63 per sec.)
Test execution summary:
total time: 64.8023s
total number of events: 10000
total time taken by event execution: 1294.2359
per-request statistics:
min: 9.19ms
avg: 129.42ms
max: 303.85ms
approx. 95 percentile: 136.45ms
total time: 64.8023s
total number of events: 10000
total time taken by event execution: 1294.2359
per-request statistics:
min: 9.19ms
avg: 129.42ms
max: 303.85ms
approx. 95 percentile: 136.45ms
Threads fairness:
events (avg/stddev): 500.0000/0.00
execution time (avg/stddev): 64.7118/0.04
events (avg/stddev): 500.0000/0.00
execution time (avg/stddev): 64.7118/0.04
其他机的mysql 5.5.16
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=sbtest --mysql-password=123456 --mysql-host=192.1.1.39 --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=20 --max-requests=100000 --oltp-test-mode=complex run
sysbench 0.4.12: multi-threaded system evaluation benchmark
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 20
Running the test with following options:
Number of threads: 20
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.
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: 1402492
write: 500890
other: 200179
total: 2103561
transactions: 100001 (602.62 per sec.)
deadlocks: 177 (1.07 per sec.)
read/write requests: 1903382 (11470.01 per sec.)
other operations: 200179 (1206.30 per sec.)
queries performed:
read: 1402492
write: 500890
other: 200179
total: 2103561
transactions: 100001 (602.62 per sec.)
deadlocks: 177 (1.07 per sec.)
read/write requests: 1903382 (11470.01 per sec.)
other operations: 200179 (1206.30 per sec.)
Test execution summary:
total time: 165.9442s
total number of events: 100001
total time taken by event execution: 3317.4742
per-request statistics:
min: 10.66ms
avg: 33.17ms
max: 302.29ms
approx. 95 percentile: 43.34ms
total time: 165.9442s
total number of events: 100001
total time taken by event execution: 3317.4742
per-request statistics:
min: 10.66ms
avg: 33.17ms
max: 302.29ms
approx. 95 percentile: 43.34ms
Threads fairness:
events (avg/stddev): 5000.0500/15.97
execution time (avg/stddev): 165.8737/0.01
events (avg/stddev): 5000.0500/15.97
execution time (avg/stddev): 165.8737/0.01
其他机的mysql 5.1.51 :
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=sbtest --mysql-password=123456 --mysql-host=192.1.1.39 --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=20 --max-requests=100000 --oltp-test-mode=complex run
sysbench 0.4.12: multi-threaded system evaluation benchmark
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 20
Running the test with following options:
Number of threads: 20
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.
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: 1400000
write: 500000
other: 200000
total: 2100000
transactions: 100000 (542.68 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900000 (10310.86 per sec.)
other operations: 200000 (1085.35 per sec.)
queries performed:
read: 1400000
write: 500000
other: 200000
total: 2100000
transactions: 100000 (542.68 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900000 (10310.86 per sec.)
other operations: 200000 (1085.35 per sec.)
Test execution summary:
total time: 184.2717s
total number of events: 100000
total time taken by event execution: 3684.1488
per-request statistics:
min: 12.50ms
avg: 36.84ms
max: 426.66ms
approx. 95 percentile: 45.09ms
total time: 184.2717s
total number of events: 100000
total time taken by event execution: 3684.1488
per-request statistics:
min: 12.50ms
avg: 36.84ms
max: 426.66ms
approx. 95 percentile: 45.09ms
Threads fairness:
events (avg/stddev): 5000.0000/11.43
execution time (avg/stddev): 184.2074/0.01
events (avg/stddev): 5000.0000/11.43
execution time (avg/stddev): 184.2074/0.01
好像mysl5.5在这种并发下并没用宣传得那么好,还得找时间好好研究一下,再测试一下。