Benchmarking MySQL using Sysbench

  • Install Sysbench on CentOS / Redhat 
                   yum install sysbench 

  • Install Sysbench on Debian / Ubuntu 
                  apt-get install sysbench 

Using sysbench to benchmark CPU 

This is a simple CPU benchmarking exercise using sysbench, In this test we are using sysbench to measure total time required to calculate prime 

[root@localhost backup]# sysbench --test=cpu --cpu-max-prime=20000 run

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

Prime numbers limit: 20000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   259.27

General statistics:
    total time:                          10.0032s
    total number of events:              2594

Latency (ms):
         min:                                  3.45
         avg:                                  3.85
         max:                                 20.48
         95th percentile:                      5.67
         sum:                               9998.63

Threads fairness:
    events (avg/stddev):           2594.0000/0.00
    execution time (avg/stddev):   9.9986/0.00

[root@localhost backup]# 

What really useful for us from this exercise is total time, Which is 10.0032s

Using sysbench to benchmark I/O

Now let's see how system behaves / performs under different kinds of I/O loads. The pre-requsite for this test is to prepare files for this benchmark. You must prepare more data than what can fit in the memory, If data fits in the memory then operating system will cache most of it and so you will not have accurate results. We will begin by creating the files:

[root@localhost backup]# sysbench --test=fileio --file-total-size=180G prepare  

The next step is to run the benchmark, There are multiple options to test different kinds of I/O performance 

  • seqwr - sequential write 
  • rndwr - random write 
  • seqrewr - sequential rewrite 
  • seqrd - sequential read 
  • rndrd - random read  
  • rndrw - combined random read / write 
In this example we are running random read/write access file I/O benchmark 

[root@localhost backup]# sysbench --test=fileio --file-total-size=180G --file-test-mode=rndrw --max-time=300 --max-requests=0 run 

File operations:
    reads/s:                      148.60
    writes/s:                     99.06
    fsyncs/s:                     316.59

    read, MiB/s:                  2.32
    written, MiB/s:               1.55

General statistics:
    total time:                          300.0026s
    total number of events:              169280

Latency (ms):
         min:                                  0.00
         avg:                                  1.77
         max:                                148.57
         95th percentile:                      4.10
         sum:                             299124.94

Threads fairness:
    events (avg/stddev):           169280.0000/0.00
    execution time (avg/stddev):   299.1249/0.00

Though we have exhaustive report post benchmarking I/O what really I look up-to is Throughput and total time 

** Please do not forget to cleanup after this benchmarking exercise 

[root@localhost backup]# sysbench --test=fileio --file-total-size=180G cleanup

Removing test files...

OLTP (online transaction processing workload) benchmark using sysbench 

In this exercise we will se how to use sysbench to emulate OLTP workload. I have used here Sysbench 0.5 which is only available from source tree at Launchpad. You can build it straightforward:

bzr branch lp:sysbench sysbench-trunk
cd sysbench-trunk

The Lua scripts can be found in the sysbench source director under sysbench/tests/db. To run the OLTP benchmark you have to give path to the Lua scrip to run

[root@localhost db]# sysbench oltp.lua --mysql-user=root --mysql-password=MyMySQLPassword --time=60 --threads=10 prepare 

sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'

[root@localhost db]# sysbench oltp.lua --mysql-user=root --mysql-password=MyMySQLPassword --time=60 --threads=10 run 

sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 10
Initializing random number generator from current time

Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            232932
        write:                           66537
        other:                           33270
        total:                           332739
    transactions:                        16632  (277.06 per sec.)
    queries:                             332739 (5542.93 per sec.)
    ignored errors:                      6      (0.10 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0268s
    total number of events:              16632

Latency (ms):
         min:                                  4.64
         avg:                                 36.08
         max:                                390.05
         95th percentile:                     56.84
         sum:                             600078.60

Threads fairness:
    events (avg/stddev):           1663.2000/10.06
    execution time (avg/stddev):   60.0079/0.00

  I generally look upto the matrices like transactions "277.06 per sec."  queries "5542.93 per sec." , total time "60.0268s", Latency (ms) "600078.60"      

No comments: