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