How To Benchmark Your System (CPU, File IO, MySQL) With sysbench

Version 1.0
Author: Falko Timme
Follow me on Twitter

sysbench is a benchmark suite which allows you to quickly get an impression about system performance which is important if you plan to run a database under intensive load. This article explains how to benchmark your CPU, file IO, and MySQL performance with sysbench.

I do not issue any guarantee that this will work for you!

 

1 Installing sysbench

On Debian/Ubuntu, sysbench can be installed as follows:

apt-get install sysbench

Take a look at

man sysbench

to learn more about its parameters.

I will now perform simple three basic tests which tell you more about your CPU performance, file IO performance, and MySQL performance.

 

2 CPU Benchmark

You can benchmark your CPU performance as follows:

sysbench --test=cpu --cpu-max-prime=20000 run

[email protected]:~# sysbench --test=cpu --cpu-max-prime=20000 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing CPU performance benchmark

Threads started!
Done.

Maximum prime number checked in CPU test: 20000


Test execution summary:
    total time:                          23.8724s
    total number of events:              10000
    total time taken by event execution: 23.8716
    per-request statistics:
         min:                                  2.31ms
         avg:                                  2.39ms
         max:                                  6.39ms
         approx.  95 percentile:               2.44ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   23.8716/0.00

[email protected]:~#

You see a lot of numbers, the most important of it is the total time:

    total time:                          23.8724s

Of course, you must compare benchmarks across multiple systems to know what these numbers are worth.

 

3 File IO Benchmark

To measure file IO performance, we first need to create a test file that is much bigger than your RAM (because otherwise the system will use RAM for caching which tampers with the benchmark results) - 150GB is a good value:

sysbench --test=fileio --file-total-size=150G prepare

Afterwards, we can run the benchmark:

sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run

[email protected]:~# sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run
sysbench: /usr/lib/libmysqlclient.so.18: no version information available (required by sysbench)
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1
Initializing random number generator from timer.


Extra file open flags: 0
128 files, 1.1719Gb each
150Gb total file size
Block size 16Kb
Number of random requests for random IO: 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
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed:  600 Read, 400 Write, 1186 Other = 2186 Total
Read 9.375Mb  Written 6.25Mb  Total transferred 15.625Mb  (53.316Kb/sec)
    3.33 Requests/sec executed

Test execution summary:
    total time:                          300.0975s
    total number of events:              1000
    total time taken by event execution: 158.7611
    per-request statistics:
         min:                                  0.01ms
         avg:                                158.76ms
         max:                               2596.96ms
         approx.  95 percentile:             482.29ms

Threads fairness:
    events (avg/stddev):           1000.0000/0.00
    execution time (avg/stddev):   158.7611/0.00

[email protected]:~#

The important number is the Kb/sec value:

Read 9.375Mb  Written 6.25Mb  Total transferred 15.625Mb  (53.316Kb/sec)

After the benchmark, you can delete the 150GB test file from the system:

sysbench --test=fileio --file-total-size=150G cleanup

 

4 MySQL Benchmark

To measure MySQL performance, we first create a test table in the database test with 1,000,000 rows of data:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword prepare

[email protected]:~# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword prepare
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
[email protected]:~#

Afterwards, you can run the MySQL benchmark as follows:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

[email protected]:~# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only 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
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            2253860
        write:                           0
        other:                           321980
        total:                           2575840
    transactions:                        160990 (2683.06 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2253860 (37562.81 per sec.)
    other operations:                    321980 (5366.12 per sec.)

Test execution summary:
    total time:                          60.0024s
    total number of events:              160990
    total time taken by event execution: 479.3419
    per-request statistics:
         min:                                  0.81ms
         avg:                                  2.98ms
         max:                               3283.40ms
         approx.  95 percentile:               4.62ms

Threads fairness:
    events (avg/stddev):           20123.7500/63.52
    execution time (avg/stddev):   59.9177/0.00

[email protected]:~#

The important number is the transactions per second value:

    transactions:                        160990 (2683.06 per sec.)

To clean up the system afterwards (i.e., remove the test table), run:

sysbench --test=oltp --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword cleanup

 

Share this page:

11 Comment(s)

Add comment

Comments

From: Michael

For the three MySQL tests, I needed to add --db-driver=mysql

From: Anonymous

Thanks alot, this solved my problem

From: perok

Dead link: http://sysbench.sourceforge.net/

New link: https://github.com/akopytov/sysbench/

From: Paul M

yes, please update the link!

From: Jonathan Hartley

On your file io benchmark, you specify "--max-time=300", and the test output includes:

    Time limit exceeded, exiting...        total time:                          300.0975s

Doesn't this mean that you didn't measure the actual IO speed, but instead the test just timed out, so the total time measured is almost meaningless?

From: Jonn Marc Vistal

sysbench 0.4.12 is what you got by default from ubuntu/fedora repo. You might need to check this article https://www.percona.com/blog/2014/09/02/using-sysbench-0-5-benchmark-mysql-whats-changed-latest-release/ the author here emphasize the change made to oltp test option and the new feature to use lua scripting to write your own load tests.

The latest sysbench version is 5.0, we could get if from lefred or https://github.com/akopytov/sysbench

From: Kim Son Vu

MySQL database name. Note SysBench will not automatically create this database. You should create it manually and grant the appropriate privileges to a user which will be used to access the test table.

From: Vu Kim Son

You should change this to:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=database --mysql-user=database_user --mysql-password=password --db-driver=mysql prepare

 

From: xtof

Thanks for this article, useful stuff.

From: cesar

Hello. Can someone explain me about the parameter --cpu-max-prime ???? ([email protected])

From: Faizal

Hello everyone,

Anyone can explain me, why the result of sysbench always different among several trial/test?