Category : How-to
You can spend hours tweaking the settings of a MySQL server instance to get the best possible performance for your hardware and environment. The hardest part is to ensure that the changes made are reflected with increased performance.
To ensure each change results in better performance of the MySQL server we need to measure the performance of the MySQL server before and after the change.
There are a verity of tools to automate MySQL benchmarking, one of which is Sysbench. I will be demonstrating the tests on a Debian 7 system however Sysbench will work on most common Linux distributions. Sysbench can be used to test both InnoDB or MyISAM database types in either a single server environment or a clustered environment with a single instance.
Installing Sysbench will differ on each Linux distribution; it can be downloaded and built from source from Sourceforge or installed with apt-get on Ubuntu or Debian.
apt-get install sysbench
Login to MySQL using the CLI or your favorite GUI tool and create a new database which will be used for the test. If you already have a database you can use for the test then you can skip this step. This example will use a database called dbtest for the tests.
create database dbtest;
The next step is to use the prepare statement with sysbench to generate a table in the specified database which will be used when performing tests.
From the command line, run the below command changing [USER] and [PASSWORD] to your MySQL access credentials.
sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] 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'...
This has created a table called sbtest with 1000000 rows of data which will be used for testing. The below commands show the the created table and do not need to be executed.
mysql> use dbtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_dbtest | +------------------+ | sbtest | +------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM sbtest; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.12 sec)
The next step is to being the performance tests. There are multiple parameters which can be changed to alter the test performed but we will do a simple read write test. Again you will need to change [USER] and [PASSWORD] to your MySQL access credentials.
sysbench --test=oltp --oltp-table-size=1000000 --oltp-test-mode=complex --oltp-read-only=off --num-threads=6 --max-time=60 --max-requests=0 --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] run
To perform a read only test, change the above parameter oltp-read-only=off to oltp-read-only=on.
The results will look similar to the below output. The main statistic to look for is transactions which shows the number of transactions the test managed to complete, and how many per second.
sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Running the test with following options: Number of threads: 6 Doing OLTP test. Running mixed OLTP 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 5 times) Done. OLTP test statistics: queries performed: read: 456680 write: 163100 other: 65240 total: 685020 transactions: 32620 (543.63 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 619780 (10329.05 per sec.) other operations: 65240 (1087.27 per sec.) Test execution summary: total time: 60.0036s total number of events: 32620 total time taken by event execution: 359.8823 per-request statistics: min: 1.66ms avg: 11.03ms max: 981.94ms approx. 95 percentile: 15.13ms Threads fairness: events (avg/stddev): 5436.6667/31.44 execution time (avg/stddev): 59.9804/0.00
Finally, you need to clean up your test area. If you can drop the entire database which was used for testing then login to MySQL and run the below command.
drop database dbtest;
If you are unable to drop the whole database then Sysbench comes with a cleanup command. Again you will need to change [USER] and [PASSWORD] to your MySQL access credentials.
sysbench --test=oltp --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] cleanup