MySQL性能测试工具sysbench -

lalanicer

浏览: 30163 次

最近访客更多访客>>

picksun

追寻_

Kanepan

Peyton111

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

MySQL性能测试工具sysbench

博客分类：

mysql

sysbench是一款开源的多线程性能测试工具，可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL。本文只是简单演示一下几种测试的用法，后续准备利用sysbench来对MySQL进行一系列的测试。具体的一些参数设置，需要根据不同的测试要求来进行调整。

下载

编译安装
默认支持MySQL，如果需要测试Oracle/PostgreSQL，则在configure时需要加上–with-oracle或者–with-pgsql参数

1
./configure --prefix=/u01/sysbench \

2

--with-mysql-includes=/opt/mysql/include/mysql \

3

--with-mysql-libs=/opt/mysql/lib/mysql

4
 
5
make && make install

参数

01
NinGoo:/u01/sysbench/bin>$sysbench

02
Missing required command argument.

03
Usage:

04

  sysbench [general-options]... --test= [test-options]... command

05
 
06
General options:

07

  --num-threads=N            number of threads to use [1]

08

  --max-requests=N           limit for total number of requests [10000]

09

  --max-time=N               limit for total execution time in seconds [0]

10

  --forced-shutdown=STRING   amount of time to wait after --max-time before forcing shutdown [off]

11

  --thread-stack-size=SIZE   size of stack per thread [32K]

12

  --init-rng=[on|off]        initialize random number generator [off]

13

  --test=STRING              test to run

14

  --debug=[on|off]           print more debugging info [off]

15

  --validate=[on|off]        perform validation checks where possible [off]

16

  --help=[on|off]            print help and exit

17

  --version=[on|off]         print version and exit

18
 
19

Compiled-in tests:

20

  fileio - File I/O test

21

  cpu - CPU performance test

22

  memory - Memory functions speed test

23

  threads - Threads subsystem performance test

24

  mutex - Mutex performance test

25

  oltp - OLTP test

26
 
27
Commands: prepare run cleanup help version

28

See 'sysbench --test= help' for a list of options for each test.

CPU测试
sysbench采用寻找最大素数的方式来测试CPU的性能

01
NinGoo:/u01/sysbench/bin>$sysbench --test=cpu --cpu-max-prime=2000 run

02
sysbench 0.4.12:  multi-threaded system evaluation benchmark

03
 
04

Running the test with following options:

05
Number of threads: 1

06
 
07
Doing CPU performance benchmark

08
 
09
Threads started!

10
Done.

11
 
12

Maximum prime number checked in CPU test: 2000

13
 
14
Test execution summary:

15

    total time:                          2.3996s

16

    total number of events:              10000

17

    total time taken by event execution: 2.3917

18

    per-request statistics:

19

         min:                                  0.23ms

20

         avg:                                  0.24ms

21

         max:                                 27.44ms

22

         approx.  95 percentile:               0.24ms

23
 
24
Threads fairness:

25

    events (avg/stddev):           10000.0000/0.00

26

    execution time (avg/stddev):   2.3917/0.00

线程测试

01
NinGoo:/u01/sysbench/bin>$sysbench --test=threads --num-threads=64 --thread-yields=100 \

02
--thread-locks=2 run

03
sysbench 0.4.12:  multi-threaded system evaluation benchmark

04
 
05

Running the test with following options:

06
Number of threads: 64

07
 
08
Doing thread subsystem performance test

09
Thread yields per test: 100 Locks used: 2

10
Threads started!

11
Done.

12
 
13
Test execution summary:

14

    total time:                          4.3925s

15

    total number of events:              10000

16

    total time taken by event execution: 280.4418

17

    per-request statistics:

18

         min:                                  0.04ms

19

         avg:                                 28.04ms

20

         max:                                 72.81ms

21

         approx.  95 percentile:              52.29ms

22
 
23
Threads fairness:

24

    events (avg/stddev):           156.2500/1.43

25

    execution time (avg/stddev):   4.3819/0.01

文件IO性能测试
首先生成需要的测试文件，文件总大小300M，16个并发线程，随机读写模式。执行完后会在当前目录下生成一堆小文件。

1
NinGoo:/u01/sysbench/bin>$sysbench --test=fileio --num-threads=16  \

2
--file-total-size=300M --file-test-mode=rndrw prepare

3
sysbench 0.4.12:  multi-threaded system evaluation benchmark

4
 
5
128 files, 2400Kb each, 300Mb total

6

Creating files for the test...

执行测试

01
NinGoo:/u01/sysbench/bin>$sysbench --test=fileio --num-threads=16  \

02
--file-total-size=300M --file-test-mode=rndrw run

03
sysbench 0.4.12:  multi-threaded system evaluation benchmark

04
 
05

Running the test with following options:

06
Number of threads: 16

07
 
08
Extra file open flags: 0

09
128 files, 2.3438Mb each

10
300Mb total file size

11
Block size 16Kb

12

Number of random requests for random IO: 10000

13

Read/Write ratio for combined random IO test: 1.50

14
Periodic FSYNC enabled, calling fsync() each 100 requests.

15
Calling fsync() at the end of test, Enabled.

16
Using synchronous I/O mode

17
Doing random r/w test

18
Threads started!

19
Done.

20
 
21
Operations performed:  5996 Read, 4004 Write, 12800 Other = 22800 Total

22
Read 93.688Mb  Written 62.562Mb  Total transferred 156.25Mb  (26.713Mb/sec)

23

 1709.66 Requests/sec executed

24
 
25
Test execution summary:

26

    total time:                          5.8491s

27

    total number of events:              10000

28

    total time taken by event execution: 12.5045

29

    per-request statistics:

30

         min:                                  0.01ms

31

         avg:                                  1.25ms

32

         max:                                373.28ms

33

         approx.  95 percentile:               0.03ms

34
 
35
Threads fairness:

36

    events (avg/stddev):           625.0000/109.60

37

    execution time (avg/stddev):   0.7815/0.29

清理现场

1
NinGoo:/u01/sysbench/bin>$sysbench --test=fileio --num-threads=16  \

2
--file-total-size=300M --file-test-mode=rndrw cleanup

3
sysbench 0.4.12:  multi-threaded system evaluation benchmark

4
 
5
Removing test files...

Mutex测试

01
NinGoo:/u01/sysbench/bin>$sysbench --test=mutex --num-threads=16 \

02
--mutex-num=1024 --mutex-locks=10000 --mutex-loops=5000 run

03
sysbench 0.4.12:  multi-threaded system evaluation benchmark

04
 
05

Running the test with following options:

06
Number of threads: 16

07
 
08
Doing mutex performance test

09
Threads started!

10
Done.

11
 
12
Test execution summary:

13

    total time:                          1.1561s

14

    total number of events:              16

15

    total time taken by event execution: 18.3831

16

    per-request statistics:

17

         min:                               1084.60ms

18

         avg:                               1148.94ms

19

         max:                               1153.52ms

20

         approx.  95 percentile:         10000000.00ms

21
 
22
Threads fairness:

23

    events (avg/stddev):           1.0000/0.00

24

    execution time (avg/stddev):   1.1489/0.02

内存测试

01
NinGoo:/u01/sysbench/bin>$sysbench --test=memory --num-threads=16 \

02
--memory-block-size=8192 --memory-total-size=1G run

03
sysbench 0.4.12:  multi-threaded system evaluation benchmark

04
 
05

Running the test with following options:

06
Number of threads: 16

07
 
08
Doing memory operations speed test

09
Memory block size: 8K

10
 
11
Memory transfer size: 1024M

12
 
13
Memory operations type: write

14
Memory scope type: global

15
Threads started!

16
WARNING: Operation time (0.000000) is less than minimal counted value, counting as 1.000000

17
WARNING: Percentile statistics will be inaccurate

18
Done.

19
 
20
Operations performed: 131072 (114162.68 ops/sec)

21
 
22
1024.00 MB transferred (891.90 MB/sec)

23
 
24
Test execution summary:

25

    total time:                          1.1481s

26

    total number of events:              131072

27

    total time taken by event execution: 16.0448

28

    per-request statistics:

29

         min:                                  0.00ms

30

         avg:                                  0.12ms

31

         max:                                  3.60ms

32

         approx.  95 percentile:               0.01ms

33
 
34
Threads fairness:

35

    events (avg/stddev):           8192.0000/192.89

36

    execution time (avg/stddev):   1.0028/0.00

MySQL数据库测试
首先需要创建默认的sbtest数据库,或者使用–mysql-db指定一个已经存在的数据库

生成测试数据，引擎为myisam,表大小为1000000条记录

01
NinGoo:/u01/sysbench/bin>$sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 \

02
--mysql-user=root --mysql-socket=/opt/mysql/run/mysql.sock prepare

03
sysbench 0.4.12:  multi-threaded system evaluation benchmark

04
 
05
No DB drivers specified, using mysql

06

Creating table 'sbtest'...

07

Creating 1000000 records in table 'sbtest'...

08
 
09
root@sbtest 11:42:18>desc sbtest.sbtest;

10
+-------+------------------+------+-----+---------+----------------+

11
| Field | Type             | Null | Key | Default | Extra          |

12
+-------+------------------+------+-----+---------+----------------+

13
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

14
| k     | int(10) unsigned | NO   | MUL | 0       |                |

15
| c     | char(120)        | NO   |     |         |                |

16
| pad   | char(60)         | NO   |     |         |                |

17
+-------+------------------+------+-----+---------+----------------+

执行测试

01
NinGoo:/u01/sysbench/bin>$sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 \

02
--mysql-user=root --mysql-socket=/opt/mysql/run/mysql.sock run

03
sysbench 0.4.12:  multi-threaded system evaluation benchmark

04
 
05
No DB drivers specified, using mysql

06

Running the test with following options:

07
Number of threads: 1

08
 
09
Doing OLTP test.

10
Running mixed OLTP test

11

Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)

12

Using "LOCK TABLES WRITE" for starting transactions

13
Using auto_inc on the id column

14

Maximum number of requests for OLTP test is limited to 10000

15
Threads started!

16
Done.

17
 
18
OLTP test statistics:

19

    queries performed:

20

        read:                            140000

21

        write:                           50000

22

        other:                           20000

23

        total:                           210000

24

    transactions:                        10000  (336.64 per sec.)

25

    deadlocks:                           0      (0.00 per sec.)

26

    read/write requests:                 190000 (6396.11 per sec.)

27

    other operations:                    20000  (673.27 per sec.)

28
 
29
Test execution summary:

30

    total time:                          29.7056s

31

    total number of events:              10000

32

    total time taken by event execution: 29.6301

33

    per-request statistics:

34

         min:                                  2.27ms

35

         avg:                                  2.96ms

36

         max:                                 43.88ms

37

         approx.  95 percentile:               3.36ms

38
 
39
Threads fairness:

40

    events (avg/stddev):           10000.0000/0.00

41

    execution time (avg/stddev):   29.6301/0.00

清理现场

1
NinGoo:/u01/sysbench/bin>$sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 \

2
--mysql-user=root --mysql-socket=/opt/mysql/run/mysql.sock cleanup

3
sysbench 0.4.12:  multi-threaded system evaluation benchmark

4
 
5
No DB drivers specified, using mysql

6

Dropping table 'sbtest'...

7
Done.