Performance tuning -- option files from the distribution and a simple benchmark

Hopefully you are caught up on the recommended reading from the last post as today we will look at creating a simple benchmark using a common tool to determine which option file to use. The MySQL server will use default values if it can not find an option file. Under the /usr/local/mysql/support-files for MySQL 5.6 are eight¹
sample configuration files that can be renamed and dropped in place as a staring point. See the Using Option File page for details.

But these files have not kept pace with hardware. For instance, a small system is listed as being under 64m of memory while huge is 1-2G. More modern settings will be covered in a later post but we can use these files in a simple test on a fairly simple test machine².

So how do we know which configuration is the best for the environment? A simple test will be run that accesses the database on the system and the time will be recorded. Usually you want to use a test that as closely mimics your 'normal' load but this is an introductory series and we will grow into more rigorous tests. Testing these various configuration files was done with mysqlslap and the next post will go into details on mysqlslap and how to use it with queries from your logs.

A simple mysqlslap test was run³ and the times recorded. The various option files were copied to /etc/mysql/my.cnf, mysqld_safe --user=mysq& was used to start the server, and then the test was run. We change one variable, the option file, repeat the test, and not the differences.

Config file	Avg Run Time
None	1.362
Small	1.604
Medium	1.373
Large	1.348
Huge	1.337
InnoDB Heavy	1.526

So the system with 1.5G of memory runs best on the option file designed for system with 1-2G of memory! That result will probably not shock too many people. This was an overly simple example and next time we will go a bit deeper on using mysqlslap and then start to look at some other tools. Then we will create a simple web based application for testing.

To be continues

Next time: Using your data with mysqlslap

1. Three files for Windows: config.small.ini, config.medium.ini, and config.huge.ini and five for UNIX-like systems: my-small.cnf, my-medium.cnf, my-large.cnf, my-huge.cnf, and my-innoidb-heavy-4g.cnf
2. The test box is a IBM Thinkpad R50e that I use for testing and runs a recent version of Fedora with 1.5G of memory.
3. mysqlslap --auto-generate-sql --concurrency=100