sysbench安装
./configure --prefix=/sysbench/sysbench/ --with-mysql-includes=/mysql/mysql/include/ --with-mysql-libs=/mysql/mysql/lib/ --with-mysql
make
make install
安装过程报错处理
checking build system type... Invalid configuration `x86_64-unknown-linux-': machine `x86_64-unknown-linux' not recognized
configure: error: /bin/sh config/config.sub x86_64-unknown-linux- failed
解决方法：安装相关依赖包
yum install libtool
报错信息：
/usr/bin/ld: cannot find -lmysqlclient_r
collect2: ld returned 1 exit status
make[2]: *** [sysbench] Error 1
make[2]: Leaving directory `/sysbench/sysbench/sysbench'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/sysbench/sysbench/sysbench'
make: *** [all-recursive] Error 1
解决方法：
ln -s libmysqlclient.so.20.3.4 libmysqlclient_r.so
##sysbench测试，主要从OLTP、CPU，IO，Memory四个方面测试
1、OLTP测试
sysbench --test=oltp help
--oltp-test-mode=STRING 测试类型：simple(简单select测试),complex(事务测试),nontrx(非事务测试),sp(存储过程) ；默认complex
--oltp-reconnect-mode=STRING 连接类型：session(每个线程到测试结束不重新连接),transaction(执行每个事务重新连接),query(每一个查询重新连接),random(随机)；默认 [session]
--oltp-sp-name=STRING 指定执行测试的存储过程名
--oltp-read-only=[on|off] 仅执行select测试，默认关闭
--oltp-avoid-deadlocks=[on|off] 更新过程中忽略死锁，默认[off]
--oltp-skip-trx=[on|off] 语句以bigin/commit开始结尾，默认[off]
--oltp-range-size=N 范围查询的范围大小，默认 [100]，例如begin 100 and 200
--oltp-point-selects=N 单个事务中select查询的数量，默认 [10]
--oltp-use-in-statement=N 每个查询中主键查找(in 10个值)的数量，默认 [0]
--oltp-simple-ranges=N 单个事务中执行范围查询的数量(SELECT c FROM sbtest WHERE id BETWEEN N AND M)，默认[1]
--oltp-sum-ranges=N 单个事务中执行范围sum查询的数量，默认 [1]
--oltp-order-ranges=N 单个事务中执行范围order by查询的数量，默认[1]
--oltp-distinct-ranges=N 单个事务中执行范围distinct查询的数量，默认[1]
--oltp-index-updates=N 单个事务中执行索引更新的操作的数量，默认[1]
--oltp-non-index-updates=N 单个事务中执行非索引更新操作的数量，默认[1]
--oltp-nontrx-mode=STRING 指定单独非事务测试类型进行测试，默认select {select, update_key, update_nokey, insert, delete} [select]
--oltp-auto-inc=[on|off] id列默认自增，默认[on]
--oltp-connect-delay=N 指定每一次重新连接延时的时长，默认1秒 [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 指定测试的表名，默认[sbtest]
--oltp-table-size=N 指定表的记录大小，默认[10000]
--oltp-dist-type=STRING 随机数分布状态。uniform(均匀分布)、gauss(高斯分布)、special(特殊分布)，默认 [special]
--oltp-dist-iter=N number of iterations used for numbers generation [12]
--oltp-dist-pct=N 启用百分比特殊分布，默认 [1]
--oltp-dist-res=N special 百分比[75]
--oltp-point-select-mysql-handler=[on|off] Use MySQL HANDLER for point select [off]
--oltp-point-select-all-cols=[on|off] select查询测试时select所有列，默认[off]
--oltp-secondary=[on|off] 索引不是主键索引而是二级索引，默认[off]
--oltp-num-partitions=N 指定表分区的数量，默认 [0]
--oltp-num-tables=N 指定测试表的数量，默认[1]
General database options:
--db-driver=STRING 指定测试数据库类型，默认mysql
--db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
prepare --生成需要的测试表
sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=bhs --oltp-table-size=100000 --db-driver=mysql --num-threads=2 --mysql-socket=/mysql/data/mysql.sock --mysql-user=root --mysql-host=localhost --mysql-password=root prepare
run --性能测试阶段
sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=bhs --oltp-table-size=100000 --db-driver=mysql --num-threads=2 --mysql-socket=/mysql/data/mysql.sock --mysql-user=root --mysql-host=localhost --mysql-password=root run
cleanup --清理测试时的测试表
[root@slv2 ~]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=bhs --oltp-table-size=100000 --db-driver=mysql --num-threads=2 --mysql-socket=/mysql/data/mysql.sock --mysql-user=root --mysql-host=localhost --mysql-password=root cleanup
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.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (141.69 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (2692.10 per sec.)
other operations: 20000 (283.38 per sec.)
Test execution summary:
total time: 70.5769s
total number of events: 10000
total time taken by event execution: 141.0690
per-request statistics:
min: 0.0063s
avg: 0.0141s
max: 0.0886s
approx. 95 percentile: 0.0224s
Threads fairness:
events (avg/stddev): 5000.0000/69.00
execution time (avg/stddev): 70.5345/0.00
结果：测试执行时间：70.5769秒，TPS：141.69/s RQ:2692.10/s 95%的请求花费了0.0224s
2、cpu测试
sysbench --test=cpu --num-threads=12 --max-requests=10000 --cpu-max-prime=20000 run
Doing CPU performance benchmark
Threads started!
Done.
Maximum prime number checked in CPU test: 20000
Test execution summary:
total time: 31.1477s
total number of events: 10000
total time taken by event execution: 373.0990
per-request statistics:
min: 0.0024s
avg: 0.0373s
max: 0.1288s
approx. 95 percentile: 0.0532s
Threads fairness:
events (avg/stddev): 833.3333/2.75
execution time (avg/stddev): 31.0916/0.03
3、文件IO测试
/sysbench --test=fileio help
--file-num=N 创建测试文件的数量,默认128个
--file-block-size=N block size大小,默认16K
--file-total-size=SIZE 所有文件的总大小，默认2G
--file-test-mode=STRING 测试类型 {seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)}
--file-io-mode=STRING I/O模式,需要系统支持默认sync[sync(同步IO),async(异步IO),mmap()]
--file-async-backlog=N 每个线程的异步操作队列数，默认128个，需要--file-io-mode=async;
--file-extra-flags=STRING additional flags to use on opening files {sync,dsync,direct} []
--file-fsync-freq=N 当请求数达到多少时执行fsync()刷新,默认100,0代表过程中不执行fsync()
--file-fsync-all=[on|off] 执行每一个写操作后执行fsync()刷新操作,默认关闭off
--file-fsync-end=[on|off] 测试结束执行fsync()操作,默认开启on
--file-fsync-mode=STRING 同步刷新方法,默认fsync {fsync, fdatasync}
--file-merged-requests=N 合并指定数量的IO请求,0代表不合并,默认0
--file-rw-ratio=N 读写比例，默认1.5/1
sysbench --test=fileio --num-threads=12 --max-requests=10000 --file-total-size=3G --file-test-mode=rndrw prepare
128 files, 24576Kb each, 3072Mb total
Creating files for the test...
sysbench --test=fileio --num-threads=12 --max-requests=10000 --file-total-size=3G --file-test-mode=rndrw run
Running the test with following options:
Number of threads: 12
Extra file open flags: 0
128 files, 24Mb each
3Gb 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 r/w test
Threads started!
Done.
Operations performed: 6004 Read, 3996 Write, 12800 Other = 22800 Total
Read 93.812Mb Written 62.438Mb Total transferred 156.25Mb (16.923Mb/sec)
1083.05 Requests/sec executed
Test execution summary:
total time: 9.2332s
total number of events: 10000
total time taken by event execution: 14.3464
per-request statistics:
min: 0.0000s
avg: 0.0014s
max: 0.6224s
approx. 95 percentile: 0.0063s
Threads fairness:
events (avg/stddev): 833.3333/67.54
execution time (avg/stddev): 1.1955/0.13
sysbench --test=fileio --num-threads=12 --max-requests=10000 --file-total-size=3G --file-test-mode=rndrw cleanup
Removing test files...
thread测试 --用于高负载下的线程性能测试
thread-yields 每个请求执行“lock/yield/unlock" 循环次数 默认1000
thread-locks 每个线程的互斥锁 默认8个
sysbench --test=threads --num-threads=12 --max-requests=10000 --thread-yields=100 --thread-locks=2 run
Running the test with following options:
Number of threads: 12
Doing thread subsystem performance test
Thread yields per test: 100 Locks used: 2
Threads started!
Done.
Test execution summary:
total time: 1.0595s
total number of events: 10000
total time taken by event execution: 12.6745
per-request statistics:
min: 0.0000s
avg: 0.0013s
max: 0.0374s
approx. 95 percentile: 0.0086s
Threads fairness:
events (avg/stddev): 833.3333/36.08
execution time (avg/stddev): 1.0562/0.00
4、memory测试 --主要是针对不同的块大小进行内存的连续读写或者随机读写测试
--memory-access-mode=seq --顺序分配
sysbench --test=memory --num-threads=12 --max-requests=10000 --memory-block-size=8K --memory-total-size=1G --memory-access-mode=seq run
Running the test with following options:
Number of threads: 12
Doing memory operations speed test
Memory block size: 8K
Memory transfer size: 1024M
Memory operations type: write
Memory scope type: global
Threads started!
Done.
Operations performed: 131072 (624394.96 ops/sec)
1024.00 MB transferred (4878.09 MB/sec)
Test execution summary:
total time: 0.2099s
total number of events: 131072
total time taken by event execution: 2.1804
per-request statistics:
min: 0.0000s
avg: 0.0000s
max: 0.0336s
approx. 95 percentile: 0.0000s
Threads fairness:
events (avg/stddev): 10922.6667/1094.34
execution time (avg/stddev): 0.1817/0.02
--memory-access-mode=rnd --随机分配
sysbench --test=memory --num-threads=12 --max-requests=10000 --memory-block-size=8K --memory-total-size=1G --memory-access-mode=rnd run
Operations performed: 131072 (961669.38 ops/sec)
1024.00 MB transferred (7513.04 MB/sec)
Test execution summary:
total time: 0.1363s
total number of events: 131072
total time taken by event execution: 1.0696
per-request statistics:
min: 0.0000s
avg: 0.0000s
max: 0.0310s
approx. 95 percentile: 0.0000s
Threads fairness:
events (avg/stddev): 10922.6667/2127.00
execution time (avg/stddev): 0.0891/0.01
sysbench --test=memory --num-threads=12 --max-requests=10000 --memory-block-size=16K --memory-total-size=1G --memory-access-mode=rnd run
Doing memory operations speed test
Memory block size: 16K
Memory transfer size: 1024M
Memory operations type: write
Memory scope type: global
Threads started!
Done.
Operations performed: 65536 (973518.40 ops/sec)
1024.00 MB transferred (15211.23 MB/sec)
Test execution summary:
total time: 0.0673s
total number of events: 65536
total time taken by event execution: 0.4436
per-request statistics:
min: 0.0000s
avg: 0.0000s
max: 0.0272s
approx. 95 percentile: 0.0000s
Threads fairness:
events (avg/stddev): 5461.3333/1120.48
execution time (avg/stddev): 0.0370/0.01
sysbench --test=memory --num-threads=12 --max-requests=10000 --memory-block-size=16K --memory-total-size=1G --memory-access-mode=seq run
Doing memory operations speed test
Memory block size: 16K
Memory transfer size: 1024M
Memory operations type: write
Memory scope type: global
Threads started!
Done.
Operations performed: 65536 (284231.81 ops/sec)
1024.00 MB transferred (4441.12 MB/sec)
Test execution summary:
total time: 0.2306s
total number of events: 65536
total time taken by event execution: 2.4985
per-request statistics:
min: 0.0000s
avg: 0.0000s
max: 0.0504s
approx. 95 percentile: 0.0000s
Threads fairness:
events (avg/stddev): 5461.3333/462.35
execution time (avg/stddev): 0.2082/0.01
测试结果：分配同样大小的内存 块越大分配速率越快，随机分配比顺序分配的速度快
