Using Sysbench to Benchmark MySQL

benchmarking_mysql

During our earlier benchmarking activities, we benchmarked applications using wikibench. Remember that wikibench is a web application on a traditional LAMP stack. While analyzing the results, we found that the performance is improved by tuning the MySQL database. This motivated us to benchmark MySQL.

To carry out this process, we used sysbench to benchmark MySQL. Sysbench is a modular, cross-platform and multi-threaded benchmarking tool. It’s used to benchmark CPU, file I/O, threads (scheduler performance), mutex and OLTP. This tool is especially useful if you are trying to setup and test a server for intense database usage.

You can install this tool on an Ubuntu machine using the following command:

install sysbench

You can get detailed information about available options by typing this command:

man sysbench

Even though sysbench is used for various kinds of benchmarking, in this post, we are focusing on using sysbench to benchmark OLTP on a MySQL server.

So, let’s get started. Here are the details of the machine and the OS:

  • Machine: AWS m3.large, 2 VCPUs, 7GB RAM and 64-bit arch
  • OS: Ubuntu 14.04 LTS (kernel version: 3.13.0-24-generic)

Preparing for the Test

Of course, we must first install MySQL server on the machine. We need to create a test database on which the benchmarks will run. This is easily done using the following command:

preparing for the test

Once the database is created, we prepare the test by creating a test table and populating the table with data. This is done by using the following command:

db populate

By using this command, it creates a basic table. Its default name is ‘sbtest.’ Following is the table command use to create it. (All of these details are available in the sysbench user manual).

CREATE TABLE `sbtest` (
`id` int(10) unsigned NOT NULL auto_increment,
`k` int(10) unsigned NOT NULL default '0',
`c` char(120) NOT NULL default '',
`pad` char(60) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `k` (`k`);
)

The table we created is then filled with a specified number of rows. By default, the storage engine we used is InnoDB. If you want to test it using other storage engine types, you can specify which type by using the optional parameter: ‘--mysql-table-engine=<storage engine type>’.

Running the Test

Next, we test the run using the following command:

running the test

The following is very important. Here is a brief explanation of some of the parameters:

  • --oltp-test-mode: There are three types of test-modes:
  • simple: Each thread runs simple select queries of the form. Consider:

SELECT c from sbtest where id = n

  • complex: Each thread runs advanced transactional queries, including range queries, range SUM, range ORDER by, inserts and updates on index, as well as non-index columns, delete rows.
  • nontrx: Non-transactional; this is similar to simple, but you can choose the query to run. Unlike advanced transactional, however, it does not preserve the test table between requests.
  • --num-threads: Number of worker threads to create.
  • --max-time: Limit for total execution time in seconds.
  • --max-requests : Limit for total number of requests. “0” means unlimited; (default equals 10000).

Clean Up After the Test

Once the tests are completed, sysbench cleans up the table it created during the ‘prepare’ stage. Clean up is done using the following command:

clean up after the test

Sample Output

A sample output of the sysbench run is shown below. A few key things to look for are highlighted in blue:

  • Transactions per second
  • Per request statistics

sample output

test execution summary

There it is. A complete outline of our results using sysbench to benchmark MySQL.

Did you find this useful?  

Interested in getting tips, best practices and commentary delivered regularly? Click the button below to sign up for our blog and set your topic and frequency preferences.

Sign Me Up!

 

July 15, 2014 / Benchmarking

About the Author

Flux7 Labs
Find me on:

Join Us

Join thousands of technology enthusiasts, subscribe and get expert perspective in your inbox.

Connect With Us