使用sysbench测试mysql性能
上一篇 / 下一篇 2016-05-26 15:52:57 / 个人分类:Mysql
sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况。
它主要包括以下几种方式的测试:
1、cpu性能
2、磁盘io性能
3、调度程序性能
4、内存分配及传输速度
5、POSIX线程性能
6、数据库性能(OLTP基准测试)
sysbench下载地址:http://downloads.mysql.com/source/sysbench-0.4.12.10.tar.gz
安装过程:
tar xzvf sysbench-0.4.12.10.tar.gz
cd sysbench-0.4.12.10
./configure
make
make install
确认是否安装成功:
# sysbench --version
sysbench 0.4.12.10
sysbench参数:
# sysbench
Missing required command argument.
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.
这里进行oltp测试:
PS:联机事务处理OLTP(on-line transaction processing)主要是执行基本的、日常的事务处理,比如数据库记录的增、删、改、查。
# sysbench --test=oltp help
sysbench 0.4.12.10: 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-avoid-deadlocks=[on|off] generate update keys in increasing order to avoid deadlocks [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-use-in-statement=N Use IN-statement with 10 PK lookups per query [0]
--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-point-select-mysql-handler=[on|off]Use MySQL HANDLER for point select [off]
--oltp-point-select-all-cols=[on|off] select all columns for the point-select query [off]
--oltp-secondary=[on|off] Use a secondary index in place of the PRIMARY index [off]
--oltp-num-partitions=N Number of partitions used for test table [0]
--oltp-num-tables=N Number of test tables [1]
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 []
常用的参数列表
--mysql-host=test.mysql.rds.aliyuncs.com #数据库host
--mysql-port=3306 #数据库端口
--mysql-user=your_username #数据库用户名
--mysql-password=your_password #数据库密码
--mysql-db=your_db_for_test #数据库名
--mysql-table-engine=STRING storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated},默认存储引擎为[innodb]
--oltp-tables-count=10 #模拟的表的个数,规格越高该值越大
--oltp-table-size=6000000 #模拟的每张表的行数,规格越高该值越大
--oltp-table-name=sbtest #测试表名,默认为[sbtest]
--num-threads=50 #模拟的并发数量,规格越高该值越大
--max-requests=100000000 #最大请求次数
--max-time=20 #最大测试时间(与--max-requests只要有一个超过,则退出)
--report-interval=1 #每1秒打印一次当前的QPS等值
--test=oltp #OLTP基准测试
[prepare | run | cleanup] #prepare准备数据,run执行测试,cleanup清理数据
测试样例:
sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=postfix --mysql-password=postfix --mysql-db=test --oltp-table-size=4000000 --oltp-table-name=yuntest --max-requests=100000 --num-threads=300 --mysql-table-engine=myisam prepare
sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=postfix --mysql-password=postfix --mysql-db=test --oltp-table-size=4000000 --oltp-table-name=yuntest --max-requests=100000 --num-threads=300 --mysql-table-engine=myisam run
测试结果:
# time sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=postfix --mysql-password=postfix --mysql-db=test --oltp-table-size=4000000 --oltp-table-name=yuntest --max-requests=100000 --num-threads=300 --mysql-table-engine=myisam run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 300
Random number generator seed is 0 and will be ignored
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 100000
Using 1 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 1400000
write: 500000
other: 200000
total: 2100000
transactions: 100000 (455.68 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900000 (8657.91 per sec.)
other operations: 200000 (911.36 per sec.)
General statistics:
total time: 219.4526s
total number of events: 100000
total time taken by event execution: 65719.8341
response time:
min: 39.30ms
avg: 657.20ms
max: 763.08ms
approx. 95 percentile: 685.38ms
Threads fairness:
events (avg/stddev): 333.3333/0.47
execution time (avg/stddev): 219.0661/0.19
清理数据:
sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=postfix --mysql-password=postfix --mysql-db=test --oltp-table-size=4000000 --oltp-table-name=yuntest --max-requests=100000 --num-threads=300 --mysql-table-engine=myisam cleanup
相关阅读:
- MYSQL的慢查询两个方法 (zaza9084, 2016-4-25)
- MySQL使用XtraBackup的shell脚本介绍 (zaza9084, 2016-4-27)
- 使用hyperpacer实现AWR报告的同步收集 (stacktestor, 2016-5-05)
- MYSQL高可用(HA)随想 (zaza9084, 2016-5-10)
- 【转】web测试方法总结 (luoyangid, 2016-5-16)
- linux-使用parted来进行分区 (fairylly, 2016-5-18)
- loadrunner 测试mysql性能lib驱动实现 (追风少女, 2016-5-18)
- loadrunner测试mysql性能odbc实现 (追风少女, 2016-5-19)
- linux 使用FIO测试磁盘iops (fairylly, 2016-5-20)
TAG: 性能 使用 MySQL mysql sysbench 测试
我的栏目
标题搜索
日历
|
|||||||||
日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
1 | 2 | ||||||||
3 | 4 | 5 | 6 | 7 | 8 | 9 | |||
10 | 11 | 12 | 13 | 14 | 15 | 16 | |||
17 | 18 | 19 | 20 | 21 | 22 | 23 | |||
24 | 25 | 26 | 27 | 28 | 29 | 30 | |||
31 |
我的存档
数据统计
- 访问量: 1090939
- 日志数: 260
- 文件数: 1
- 书签数: 1
- 建立时间: 2009-01-05
- 更新时间: 2017-08-22