MySQL性能测试工具sysbench-CSDN博客

本文链接：https://blog.csdn.net/lalanicer/article/details/84539970

本文详细介绍了sysbench性能测试工具的下载、编译、安装及多种测试场景的使用方法，包括CPU测试、线程测试、文件IO性能测试、Mutex测试、内存测试和MySQL数据库测试，提供了一个全面的性能评估方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 

5make && make install

参数

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

02Missing required command argument.

03Usage:

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

05 

06General 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 

27Commands: prepare run cleanup help version

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

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

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

02sysbench 0.4.12:  multi-threaded system evaluation benchmark

03 

04
Running the test with following options:

05Number of threads: 1

06 

07Doing CPU performance benchmark

08 

09Threads started!

10Done.

11 

12
Maximum prime number checked in CPU test: 2000

13 

14Test 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 

24Threads fairness:

25
    events (avg/stddev):           10000.0000/0.00

26
    execution time (avg/stddev):   2.3917/0.00

线程测试

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

02--thread-locks=2 run

03sysbench 0.4.12:  multi-threaded system evaluation benchmark

04 

05
Running the test with following options:

06Number of threads: 64

07 

08Doing thread subsystem performance test

09Thread yields per test: 100 Locks used: 2

10Threads started!

11Done.

12 

13Test 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 

23Threads fairness:

24
    events (avg/stddev):           156.2500/1.43

25
    execution time (avg/stddev):   4.3819/0.01

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

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

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

3sysbench 0.4.12:  multi-threaded system evaluation benchmark

4 

5128 files, 2400Kb each, 300Mb total

6
Creating files for the test...

执行测试

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

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

03sysbench 0.4.12:  multi-threaded system evaluation benchmark

04 

05
Running the test with following options:

06Number of threads: 16

07 

08Extra file open flags: 0

09128 files, 2.3438Mb each

10300Mb total file size

11Block size 16Kb

12
Number of random requests for random IO: 10000

13
Read/Write ratio for combined random IO test: 1.50

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

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

16Using synchronous I/O mode

17Doing random r/w test

18Threads started!

19Done.

20 

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

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

23
 1709.66 Requests/sec executed

24 

25Test 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 

35Threads fairness:

36
    events (avg/stddev):           625.0000/109.60

37
    execution time (avg/stddev):   0.7815/0.29

清理现场

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

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

3sysbench 0.4.12:  multi-threaded system evaluation benchmark

4 

5Removing test files...

Mutex测试

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

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

03sysbench 0.4.12:  multi-threaded system evaluation benchmark

04 

05
Running the test with following options:

06Number of threads: 16

07 

08Doing mutex performance test

09Threads started!

10Done.

11 

12Test 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 

22Threads fairness:

23
    events (avg/stddev):           1.0000/0.00

24
    execution time (avg/stddev):   1.1489/0.02

内存测试

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

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

03sysbench 0.4.12:  multi-threaded system evaluation benchmark

04 

05
Running the test with following options:

06Number of threads: 16

07 

08Doing memory operations speed test

09Memory block size: 8K

10 

11Memory transfer size: 1024M

12 

13Memory operations type: write

14Memory scope type: global

15Threads started!

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

17WARNING: Percentile statistics will be inaccurate

18Done.

19 

20Operations performed: 131072 (114162.68 ops/sec)

21 

221024.00 MB transferred (891.90 MB/sec)

23 

24Test 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 

34Threads fairness:

35
    events (avg/stddev):           8192.0000/192.89

36
    execution time (avg/stddev):   1.0028/0.00

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

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

01NinGoo:/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

03sysbench 0.4.12:  multi-threaded system evaluation benchmark

04 

05No DB drivers specified, using mysql

06
Creating table 'sbtest'...

07
Creating 1000000 records in table 'sbtest'...

08 

09root@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+-------+------------------+------+-----+---------+----------------+

执行测试

01NinGoo:/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

03sysbench 0.4.12:  multi-threaded system evaluation benchmark

04 

05No DB drivers specified, using mysql

06
Running the test with following options:

07Number of threads: 1

08 

09Doing OLTP test.

10Running 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

13Using auto_inc on the id column

14
Maximum number of requests for OLTP test is limited to 10000

15Threads started!

16Done.

17 

18OLTP 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 

29Test 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 

39Threads fairness:

40
    events (avg/stddev):           10000.0000/0.00

41
    execution time (avg/stddev):   29.6301/0.00

清理现场

1NinGoo:/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

3sysbench 0.4.12:  multi-threaded system evaluation benchmark

4 

5No DB drivers specified, using mysql

6
Dropping table 'sbtest'...

7Done.