使用sysbench测试MySQL5.7.14的oltp性能

目的：使用sysbench测试MySQL的oltp性能，将结果用gnuplot绘制成性能图。

环境：redhat7，mysql5.7.14，sysbench0.5 (均在vmvare中安装)

配置：

物理cpu个数:4
核心数量:1
线程数量:4

mysql配置：默认

1.使用sysbench测量cpu

参数：

sysbench --test=cpu \
--num-threads=`grep "processor" /proc/cpuinfo | wc -l` \
--cpu-max-prime=20000 run

 

结果：

[root@localhost mysql]# ./cpu_test.sh 
sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored

Prime numbers limit: 20000

Initializing worker threads...

Threads started!

General statistics:
total time: 7.2456s
total number of events: 10000
total time taken by event execution: 28.9647s
response time:
min: 2.54ms
avg: 2.90ms
max: 13.15ms
approx. 95 percentile: 3.90ms

Threads fairness:
events (avg/stddev): 2500.0000/1.22
execution time (avg/stddev): 7.2412/0.00

 

2.使用sysbench测量内存

参数：

sysbench --test=memory --memory-block-size=8K --memory-total-size=2G  --num-threads=16 run

结果：

[root@localhost mysql]# ./memory_test.sh 
sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

Operations performed: 262144 (2015598.14 ops/sec)

2048.00 MB transferred (15746.86 MB/sec)

General statistics:
total time: 0.1301s
total number of events: 262144
total time taken by event execution: 0.3529s
response time:
min: 0.00ms
avg: 0.00ms
max: 1.01ms
approx. 95 percentile: 0.00ms

Threads fairness:
events (avg/stddev): 16384.0000/2206.91
execution time (avg/stddev): 0.0221/0.00

 

3.使用sysbench测量IO

参数：

#prepare 10G data

sysbench --test=fileio --file-total-size=10G prepare
#test
sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrw\
--init-rng=on --max-time=300 --max-requests=0 run
#clean temp data
sysbench --test=fileio --file-total-size=10G cleanup

结果：

====初始化测试文件

Creating file test_file.1
Creating file test_file.2
...

Creating file test_file.126
Creating file test_file.127
10737418240 bytes written in 40.59 seconds (252.30 MB/sec).
sysbench 0.5: multi-threaded system evaluation benchmark

====开始测试

Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored

Extra file open flags: 0
128 files, 80Mb each
10Gb total file size
Block size 16Kb
Number of IO requests: 0
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
Initializing worker threads...

Threads started!

Operations performed: 907422 reads, 604948 writes, 1935744 Other = 3448114 Total
Read 13.846Gb Written 9.2308Gb Total transferred 23.077Gb (78.74Mb/sec)
5039.38 Requests/sec executed

General statistics:
total time: 300.1101s
total number of events: 1512370
total time taken by event execution: 238.0086s
response time:
min: 0.00ms
avg: 0.16ms
max: 163.65ms
approx. 95 percentile: 0.18ms

Threads fairness:
events (avg/stddev): 1512370.0000/0.00
execution time (avg/stddev): 238.0086/0.00

sysbench 0.5: multi-threaded system evaluation benchmark

 

4.使用sysbench测量互斥锁

参数：

sysbench --test=mutex --num-threads=16 --mutex-num=2048 \
--mutex-locks=1000000 --mutex-loops=5000 run

结果：

[root@localhost mysql]# ./mutex_test.sh 
sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

General statistics:
total time: 2.3676s
total number of events: 16
total time taken by event execution: 36.2816s
response time:
min: 2042.78ms
avg: 2267.60ms
max: 2367.17ms
approx. 95 percentile: 2366.65ms

Threads fairness:
events (avg/stddev): 1.0000/0.00
execution time (avg/stddev): 2.2676/0.10

 

5.使用sysbench测量线程

参数：

sysbench --test=threads --num-threads=64 run

结果：

[root@localhost mysql]# ./thread_test.sh 
sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64
Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

General statistics:
total time: 2.5541s
total number of events: 10000
total time taken by event execution: 163.0034s
response time:
min: 0.15ms
avg: 16.30ms
max: 246.45ms
approx. 95 percentile: 55.25ms

Threads fairness:
events (avg/stddev): 156.2500/12.16
execution time (avg/stddev): 2.5469/0.00

6.使用sysbench测量mysql oltp

参数：

records=100000
thds=10
type=innodb
host=localhost
port=3306
sysbench --test=/home/ljp/sysbench-0.5/sysbench/tests/db/oltp.lua \
--oltp_tables_count=10 --oltp-table-size=$records --rand-init=on \
--num-threads=$thds --oltp-read-only=off --report-interval=1 \
--rand-type=special --rand-spec-pct=5 --mysql-table-engine=$type \
--max-time=60 --mysql-host=$host --mysql-port=$port --mysql-user=root \
--mysql-password=root --mysql-db=test --max-requests=1000000 $1

 

结果：

[ 1s] threads: 10, tps: 31.01, reads: 398.08, writes: 121.03, response time: 1247.93ms (95%), errors: 0.00, reconnects: 0.00
[ 2s] threads: 10, tps: 71.00, reads: 1024.04, writes: 287.01, response time: 535.40ms (95%), errors: 0.00, reconnects: 0.00

...
[ 59s] threads: 10, tps: 113.04, reads: 1538.59, writes: 423.16, response time: 594.54ms (95%), errors: 0.00, reconnects: 0.00
[ 60s] threads: 10, tps: 7.00, reads: 142.92, writes: 63.97, response time: 1053.44ms (95%), errors: 0.00, reconnects: 0.00
OLTP test statistics:
queries performed:
read: 64554
write: 18444
other: 9222
total: 92220
transactions: 4611 (76.67 per sec.)
read/write requests: 82998 (1380.10 per sec.)
other operations: 9222 (153.34 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 60.1390s
total number of events: 4611
total time taken by event execution: 601.1634s
response time:
min: 2.12ms
avg: 130.38ms
max: 2378.80ms
approx. 95 percentile: 656.66ms

Threads fairness:
events (avg/stddev): 461.1000/24.47
execution time (avg/stddev): 60.1163/0.03

图像：

进行60秒查询，tps

进行60秒查询，读写次数

进行60秒查询，响应时间（ms）

 

转载于:https://my.oschina.net/jumpLee/blog/740589