How to Benchmark Your System (CPU, File IO, MySQL) with Sysbench

sysbench is a benchmark suite which allows you to quickly get an impression of 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.

1 Installing sysbench

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

sudo apt-get install sysbench

On CentOS and Fedora, it can be installed from EPEL repository.

Add EPEL Repository:

rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY*
yum -y install epel-release
yum -y update

Then install sysbench with yum:

yum install sysbench

Get an overview of sysbench options

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 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword prepare
[email protected]:~# sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --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]:~#

Replace the word yourrootsqlpassword with your MySQL root password in the above command. Do the same in the next commands.

Afterwards, you can run the MySQL benchmark as follows:

sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --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 --db-driver=mysql --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 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=yourrootsqlpassword cleanup
Share this page:

Suggested articles

14 Comment(s)

Add comment

Comments

By: Michael at: 2013-07-21 15:16:36

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

By: Anonymous at: 2013-07-31 02:55:19

Thanks alot, this solved my problem

By: perok at: 2016-03-01 09:51:26

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

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

By: Paul M at: 2016-07-29 10:10:26

yes, please update the link!

By: Jonathan Hartley at: 2016-03-10 21:21:52

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?

By: Jonn Marc Vistal at: 2016-05-21 01:30:58

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

By: Kim Son Vu at: 2016-08-31 06:28:19

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.

By: Vu Kim Son at: 2016-08-31 07:16:47

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

 

By: xtof at: 2016-09-01 17:48:24

Thanks for this article, useful stuff.

By: cesar at: 2016-09-21 12:36:11

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

By: Faizal at: 2016-12-02 18:01:31

Hello everyone,

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

By: Matthias at: 2017-01-28 10:39:34

If sysbench mysql test comes up with message: FATAL: unable to connect to MySQL server, aborting...FATAL: error 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)FATAL: failed to connect to database server!

 

Try to specify the socket file by adding:  --mysql-socket=/var/lib/mysql/mysql.sock (or Path to Socket File)

 

sysbench --test=oltp --mysql-db=test --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password=xxxxxxxx cleanup

 

By: Tyler at: 2017-07-13 14:48:18

Hi, All tests, ecept for MySQL, execute perfectly. I modified my lines to append the output and display while running:MySQL errors:

No DB drivers specified, using mysql

FATAL: unable to connect to MySQL server, aborting...

FATAL: error 1049: Unknown database 'test'

ALERT: Error: failed to determine table 'sbtest' type!

ALERT: MySQL error: Unknown database 'test'

FATAL: failed to get database capabilities!

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Please advise on how to correct this, as MySQL is a big indicator of my web-server performance.Also, where can we find acceptable ranges / charts for these results to interpret?- Tyler

By: Tyler at: 2017-07-13 14:49:33

My apologies, here's the three failure outputs:

FATAL: unable to connect to MySQL server, aborting...

FATAL: error 1049: Unknown database 'database'

FATAL: failed to connect to database server!

sysbench 0.4.12:  multi-threaded system evaluation benchmark

 

No DB drivers specified, using mysql

FATAL: unable to connect to MySQL server, aborting...

FATAL: error 1049: Unknown database 'test'

ALERT: Error: failed to determine table 'sbtest' type!

ALERT: MySQL error: Unknown database 'test'

FATAL: failed to get database capabilities!

sysbench 0.4.12:  multi-threaded system evaluation benchmark

 

No DB drivers specified, using mysql

FATAL: unable to connect to MySQL server, aborting...

FATAL: error 1049: Unknown database 'test'

FATAL: failed to connect to database server!