sysbench压力测试工具安装和参数介绍.docx

sysbench压力测试工具安装和参数介绍一、sysbench压力测试工具简介：sysbench是一个开源的、模块化的、跨平台的多线程性能测试工具，可以用来进行CPU、内存、磁盘I/O、线程、数据库的性能测试。目前支持的数据库有MySQL、Oracle和PostgreSQL。以下操作都将以支持MySQL数据库为例进行。1.sysbench工具安装：默认支持MySQL，如果需要测试Oracle/PostgreSQL，则在configure时需要加上with-oracle或者with-pgsql参数.1.1安装环境CentOS release 6.3 (Final)MySQL 5.6.13MySQL_HOME=/usr/local/mysql/Sysbench 下载安装包# wget/projects/sysbench/sysbench-0.4.12.tar.gz1.3编译安装# tar zxvf sysbench-0.4.12.tar.gz进入解压缩包sysbench-0.4.12,并执行脚本autogen.sh# cd sysbench-0.4.12# ./autogen.sh关键的三步：configure & make & make install首先是./configure命令，sysbench默认是支持MySQL的benchmarking的，如果不加任何选项则要求保证MySQL的安装路径都是默认的标准路径，headfile位于/usr/include目录下，libraries位于/usr/lib/目录下。因为我的MySQL是源码编译安装的，安装路径是放在/usr/local/mysql下，所以这里要添加相应的选项命令：# ./configure -prefix=/usr/local/sysbench -with-mysql=/usr/local/mysql -with-mysql-includes=/usr/local/mysql/include/mysql/ -with-mysql-libs=/usr/local/mysql/lib/mysql/注意：这里在编译时要将路径写到最后的include/mysql及lib/mysql，如下所示：with-mysql-includes=/usr/local/mysql/include/mysql/-with-mysql-libs=/usr/local/mysql/lib/mysql/因为网上好多资料都没有提到这一层，在编译时总是编译不过去，这里浪费了好多精力。接下来执行如下命令：# make & make install1.4yum安装如果觉得源码安装麻烦，也可以采用yum安装，操作如下：# yum install -y sysbench2.sysbench支持的功能参数首先，看看sysbench都支持哪些功能参数：rootdb-master sysbench#sysbench -helpUsage: sysbench general-options. -test=test-options. commandGeneral 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 exitCompiled-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 testCommands: prepare run cleanup help versionSee sysbench -test=help for a list of options for each test.Sysbench的测试主要包括以下几个方面：1、磁盘io性能2、cpu性能3、内存分配及传输速度4、POSIX线程性能5、调度程序性能6、数据库性能(OLTP基准测试).2.1测试fileio命令帮助：rootdb-master # sysbench -test=fileio helpsysbench 0.4.12: multi-threaded system evaluation benchmarkfileio 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,fastmmap,slowmmap 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 - dont 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 - dont merge) 0 -file-rw-ratio=N reads/writes ratio for combined test 1.5参数详解：-file-num=N代表生成测试文件的数量，默认为128。-file-block-size=N 测试时所使用文件块的大小，如果想磁盘针对innodb存储引擎进行测试，可以将其设置为16384，即innodb存储引擎页的大小。默认为16384。-file-total-size=SIZE创建测试文件的总大小，默认为2G大小。-file-test-mode=STRING文件测试模式，包含：seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)。-file-io-mode=STRING 文件操作的模式，sync（同步）,async（异步）,fastmmap（快速mmap）,slowmmap（慢速mmap），默认为sync同步模式。-file-async-backlog=N对应每个线程队列的异步操作数，默认为128。-file-extra-flags=STRING打开文件时的选项，这是与API相关的参数。-file-fsync-freq=N执行fsync()函数的频率。fsync主要是同步磁盘文件，因为可能有系统和磁盘缓冲的关系。0代表不使用fsync函数。默认值为100。-file-fsync-all=on|off每执行完一次写操作，就执行一次fsync。默认为off。-file-fsync-end=on|off在测试结束时执行fsync函数。默认为on。-file-fsync-mode=STRING文件同步函数的选择，同样是和API相关的参数，由于多个操作系统对于fdatasync支持不同，因此不建议使用fdatasync。默认为fsync。-file-merged-requests=N大多情况下，合并可能的IO的请求数，默认为0。-file-rw-ratio=N测试时的读写比例，默认时为1.5，即可3：2。2.2测试cpu命令帮助：rootdb-master # sysbench -test=cpu helpsysbench 0.4.12: multi-threaded system evaluation benchmarkcpu options: -cpu-max-prime=N upper limit for primes generator 10000参数详解： -cpu-max-prime=N用来选项指定最大的素数，具体参数可以根据CPU的性能来设置，默认为100002.3测试memory命令帮助：rootdb-master # sysbench -test=memory helpsysbench 0.4.12: multi-threaded system evaluation benchmarkmemory 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参数详解：-memory-block-size=SIZE测试内存块的大小，默认为1K-memory-total-size=SIZE数据传输的总大小，默认为100G-memory-scope=STRING内存访问的范围，包括全局和本地范围，默认为global-memory-hugetlb=on|off 是否从HugeTLB池分配内存的开关，默认为off-memory-oper=STRING内存操作的类型，包括read, write, none，默认为write-memory-access-mode=STRING内存访问模式，包括seq,rnd两种模式，默认为seq2.4测试threads命令帮助：rootdb-master # sysbench -test=threads helpsysbench 0.4.12: multi-threaded system evaluation benchmarkthreads options: -thread-yields=N number of yields to do per request 1000 -thread-locks=N number of locks per thread 8参数详解：-thread-yields=N指定每个请求的压力，默认为1000-thread-locks=N指定每个线程的锁数量，默认为82.5测试mutex命令帮助：rootdb-master # sysbench -test=mutex helpsysbench 0.4.12: multi-threaded system evaluation benchmarkmutex options: -mutex-num=N total size of mutex array 4096 -mutex-locks=N number of mutex locks to do per thread 50000 -mutex-loops=N number of empty loops to do inside mutex lock 10000参数详解：-mutex-num=N数组互斥的总大小。默认是4096-mutex-locks=N每个线程互斥锁的数量。默认是50000-mutex-loops=N内部互斥锁的空循环数量。默认是100002.6测试oltp命令帮助：rootdb-master # sysbench -test=oltp helpsysbench 0.4.12: multi-threaded system evaluation benchmarkoltp 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) 75General 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 autoCompiled-in database drivers: mysql - MySQL driver pgsql - PostgreSQL drivermysql 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 pgsql options: -pgsql-host=STRING PostgreSQL server host localhost -pgsql-port=N PostgreSQL server port 5432 -pgsql-user=STRING PostgreSQL user sbtest -pgsql-password=STRING PostgreSQL password -pgsql-db=STRING PostgreSQL database name sbtest参数详解： -oltp-test-mode=STRING执行模式simple,complex(advanced transactional),nontrx(non-transactional),sp。默认是complex -oltp-reconnect-mode=STRING重新连接模式session(不使用重新连接。每个线程断开只在测试结束),transaction(在每次事务结束后重新连接),query(在每个SQL语句执行完重新连接),random(对于每个事务随机选择以上重新连接模式)。默认是session -oltp-sp-name=STRING存储过程的名称。默认为空 -oltp-read-only=on|off只读模式。Update，delete，insert语句不可执行。默认是off -oltp-skip-trx=on|off省略begin/commit语句。默认是off -oltp-range-size=N查询范围。默认是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查询类型对于非事务执行模式select, update_key, update_nokey, insert, delete select -oltp-auto-inc=on|off AUTO_INCREMENT是否开启。默认是on -oltp-connect-delay=N在多少微秒后连接数据库。默认是10000 -oltp-user-delay-min=N每个请求最短等待时间。单位是ms。默认是0 -oltp-user-delay-max=N每个请求最长等待时间。单位是ms。默认是0 -oltp-table-name=STRING测试时使用到的表名。默认是sbtest -oltp-table-size=N测试表的记录数。默认是10000 -oltp-dist-type=STRING分布的随机数uniform(均匀分布),Gaussian(高斯分布),special(空间分布)。默认是special -oltp-dist-iter=N产生数的迭代次数。默认是12 -oltp-dist-pct=N值的百分比被视为special (for special distribution)。默认是1 -oltp-dist-res=Nspecial的百分比值。默认是753.总结：以上就是sysbench工具的安装配置及相关选项参数的介绍，可以根据自己的需要添加对应的参数进行压力测试即可。在下一篇文档中将会对上面的几种模式进行相应的测试。2.1测试数据库服务器的硬件配置信息如下：CPU: 24核心线程数，Intel(R) Xeon(R) CPU E5-2620 0 2.00GHzMEM: 64G，8*8G=64GDISK: 15000转/秒2.2 对磁盘IO性能的测试：2.2.1创建fileio文件创建初始化fileio文件：rootdb-master sysbench# sysbench -test=fileio -file-num=16 -file-total-size=2G preparesysbench 0.4.12: multi-threaded system evaluation benchmark16 files, 131072Kb each, 2048Mb totalCreating files for the test.2.2.1开始fileio测试接下来开始对这些文件进行测试，使用16个线程随机读进行测试结果如下：rootdb-master sysbench# sysbench -test=fileio -file-total-size=2G -file-test-mode=rndrd -max-time=180 -max-requests=100000000 -num-threads=16 -init-rng=on -file-num=16 -file-extra-flags=direct -file-fsync-freq=0 -file-block-size=16384 run可以看到随机读取的性能为75.988Mb/sec，随机读的IOPS为4863.25 Requests/sec.说明服务器的硬件配置还不错。2.2.3测试完成执行cleanup测试结束后，记得执行cleanup,以确保测试所产生的文件都已删除：rootdb-master sysbench#sysbench -test=fileio -file-num=16 -file-total-size=2G cleanupsysbench 0.4.12: multi-threaded system evaluation benchmarkRemoving test files.如果需要测试seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)等6种模式，并且还可能需要测试不同的线程和不同的文件块下磁盘的性能表现，这时，可以使用如下脚本达到测试目的。测试脚本内容如下：#!/bin/bash#=# FILE: sysbench_auto.sh# USAGE: ./sysbench_auto.sh# DESCRIPTION: This file is sysbench_auto.sh# AUTHOR: Kevin Lu (kevin), # ORGANIZATION: cmcc# CREATED: 02/26/2014 17:35# REVISION: v1.0.1#=for size in 8G,64Gdofor mode in seqwr,seqrewr,seqrd,rndrd,rndwr,rndrwdofor blksize in 4096,16384dosysbench -test=fileio -file-num=64 -file-total-size=$size preparefor threads in 1,4,8,16,32doecho =testing $blksize in $threads threadsecho PARAS $size $mode $threads $blksize sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksizefor i in 1,2,3dosysbench -test=fileio -file-total-size=$size -file-test-mode=$mode -max-time=180 -max-requests=100000 -num-threads=$threads -init-rng=on -file-num=64 -file-extra-flags=direct -file-fsync-freq=0 -file-block-size=$blksize run|tee -a sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize 2&1donedonesysbench -test=fileio -file-total-size=$size cleanupdonedonedone-脚本运行后，在当前目录下会生成如下文件：3.1对mysql事务型OLTP的测试：3.1.1prepare准备阶段对于mysql的OLTP测试，和file一样，同样需要经历prepare,run,cleanup三个阶段。prepare阶段会在数据库中产生一张指定行数的表，默认表在sbtest架构下，表名为sbtest(sysbench默认生成表的存储引擎为innodb),如创建一张8000万条记录的表：rootdb-master sysbench# sysbench -test=oltp -oltp-table-size=80000000 -db-driver=mysql -mysql-socket=/data/mysqlsoft3307/mysql.sock -mysql-user=dba_manager -mysql-password=111111 -mysql-db=test preparesysbench 0.4.12: multi-threaded system evaluation benchmarkCreating table sbtest.Creating 10 records in table sbtest.3.1.2 run运行测试接下来对上面产生的表进行oltp的测试：rootdb-master sysbench# sysbench -test=oltp -oltp-table-size=80000000 -oltp-read-on