Watch it on YouTube

Sysbench allows testers and devops engineers to benchmark MySQL databases by either using one of its bundled tests or custom Lua scripts. Also, Sysbench can benchmark systems that do not run any database at all as it is able to:

• Measure I/O and file system performance under stress
• Measure CPU performance under stress
• Measure memory reading and writing performance under stress
• Check whether mutex operations are likely to be the cause of performance issues
• Measure how systems perform with large number of concurrent threads with shared or exclusive locks

Although Sysbench can target a wide range of scenarios, this article only covers the execution of SQL statements to measure MySQL performance using bundled and custom Lua scripts. It should be noted that Sysbench benchmarks are meaningful to any environment, whether this is virtual machines, containers or physical servers based.

Running bundled scripts

Sysbench comes with a bunch of scripts that can be used to benchmark systems and familiarize with the environment. This article does not show the steps required to build and install Sysbench, as they are available on the official website. Once Sysbench is installed, the reader will need to configure MySQL. As Sysbench targets the sbtest database by default, this one will need to be created beforehand: therefore, assuming that the database engine is locally deployed, the following would be enough to prepare the system:

CREATE USER 'sbtest'@'localhost' IDENTIFIED BY 'password';
CREATE SCHEMA sbtest;
-- Grants can be fine tuned
GRANT ALL ON sbtest.* TO 'sbtest'@'localhost';

Once the database is ready, we can execute one of the bundled Lua tests stored into the /usr/share/sysbench directory (location might differ). The following lines create the test tables (prepare) and then execute the test (run):

sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sbtest --mysql-password=password --mysql-db=sbtest --table_size=100000 --tables=10 --threads=10 prepare
sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sbtest --mysql-password=password --mysql-db=sbtest --table_size=100000 --tables=10 --threads=20 --time=180 run

Which on a small virtual machine (1Gb RAM and 1 CPU) produces the following

SQL statistics:
    queries performed:
        read:                            676984
        write:                           0
        other:                           96712
        total:                           773696
    transactions:                        48356  (268.53 per sec.)
    queries:                             773696 (4296.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          180.0739s
    total number of events:              48356

Latency (ms):
         min:                                   57.36
         avg:                                   74.46
         max:                                  304.70
         95th percentile:                       81.48
         sum:                              3600694.69

Threads fairness:
    events (avg/stddev):           2417.8000/9.38
    execution time (avg/stddev):   180.0347/0.02

The sequence of SQL commands USE sbtest; DESC sbtest1; reveal the structure of the ten tables that were created by the prepare statement:

+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int       | NO   | PRI | NULL    | auto_increment |
| k     | int       | NO   | MUL | 0       |                |
| c     | char(120) | NO   |     |         |                |
| pad   | char(60)  | NO   |     |         |                |
+-------+-----------+------+-----+---------+----------------+

All queries are located into the oltp_read_only.lua script for further investigation. Finally, running this workload again would require the commands cleanup and prepare to be executed first.

Running custom scripts

All scripts need to implement at least the prepare, run and cleanup commands as follows:

• The run command requires:
  • thread_init() Lua function to setup and initialize the environment and create the database connection
  • thread_done() Lua function to tear down the database connection and clean up the environment
  • event() Lua function which contains the logic of the test: this is where all SQL test queries are located
• The prepare command requires:
  • A custom Lua function registered into the sysbench.cmdline.commands hash according to the syntax shown later on in the article
• The cleanup command requires:
  • cleanup() Lua function to execute to cleanup the database

Moreover, the following store useful information:

• sysbench.cmdline.options defines the Sysbench command line options that the script supports
• sysbench.opt contains Sysbench command line options values passed by the users
• sysbench.cmdline.command contains the command to execute
• sysbench.tid Sysbench thread id

The following is a template that can be used to implement custom workloads:

-- Validate that a command was provided
if sysbench.cmdline.command == nil then
    -- Built-in function to print error messages
    error("No command provided. Use: prepare, run, cleanup or help")
end

-- Specify the supported options for this test
sysbench.cmdline.options = {
    autocommit = { "Use AUTOCOMMIT, no manual transaction ", false }
}

function thread_init()
    -- Create the connection
    db = sysbench.sql.driver()
    cnx = db:connect()
end

function thread_done()
    -- Close the connection to the database
    cnx:disconnect()
end

function event()    
    -- Begin transaction if autocommit is off
    if not sysbench.opt.autocommit then
        cnx:query("BEGIN")
    end

    -- -- -- -- -- -- -- -- -- -- -- -- --
    -- Custom SQL to run the actual test 
    -- -- -- -- -- -- -- -- -- -- -- -- --

    -- Commit transaction if autocommit is off
    if not sysbench.opt.autocommit then
        cnx:query("COMMIT")
    end
end

function prepare_db()
    -- Setup the environment, create tables, etc...
end

function cleanup()
    -- Drop all tables here
end

-- Specify function used for the preparation command
-- Specify that the prepare stage will run in parallel
sysbench.cmdline.commands = 
    { prepare = { prepare_db, sysbench.cmdline.PARALLEL_COMMAND } }

This custom script is executed like the bundled ones, optionally passing parameters such as --autocommit. Parameters are to be used to increase scripts flexibility as they can drive, for example:

• The number of tables to create
• The number of rows to insert, delete or update
• The program flow by toggling sections of the script on and off

The script oltp_common.lua declares several parameters which can be used as example.

Conclusion

Using Sysbench devops engineers and testers can quickly benchmark the performance of any system via bundled scripts or via complex custom workloads. This is the perfect tool to use to:

• Test changes to database schema (DDL), DQL, DML, DCL and TCL before they are deployed to production
• Benchmark established systems to investigate any type of bottleneck
• Expand CI/CD cycles with automated performance testing
• Identify hardware failures by stressing any component of a system