We’ve been benchmarking and tuning the MySQL database for a decision support data mart product we’re currently developing and have had some interesting results.

The initial configuration that ships with the MySQL server isn’t necessarily suitable for every requirement(!) and so we’ve been experimenting with our lab server to try to find the optimal set up. (We’re using INNODB tables only so if you’re using MyISAM tables or a mix then there are different configurations that should be tested.)

We’re expecting data volumes to be fairly large for the data mart (i.e. millions of rows) and want to ensure that the it performs well. Our lab server is a lower specification that we’d recommend for the product but we have got some good results by tuning the buffer pool size and the query cache.

MySQL uses the buffer pool to cache both index and row data. In a denormalised data mart this is likely to give the biggest improvement in query times (as there usually is a lot of data in both!), and our lab database was no exception.

We managed to reduce the times of our benchmark queries by an order of magnitude when we set the size of the pool to 25% of avaiable memory. This is conservative given the usual recommendations to set to 80% but our lab machine also runs a web server. The buffer pool is configured through the innodb_buffer_pool_size parameter in the my.ini / my.cnf file.

We also saw a large improvement (several orders of magnitude) by enabling the query cache. The query cache stores queries and their results. It’s important to realise that it will only use a query from the cache if it’s exactly the same. Given that we are using named queries and will be querying data for the same month a lot of the time, it made sense to enable this. The query cache is enabled by setting the query_cache_size to a non-zero value.

We found it made the first run of the query slightly longer (as it puts the query in to the cache) but then subsequent runs very fast (e.g. 1800ms to 78ms). The trick is/will be to size the query cache to minimise the number of queries that are evicted (find this from the Qcache_lowmem_prunes variable).

We also use an application cache on the web server for our reference data so it won’t make sense to cache these queries as they will be run only once each server restart. Exclude queries from the cache by adding the SQL_NO_CACHE clause to the SELECT statement (e.g. SELECT SQL_NO_CACHE name, FROM person) but you will need to make sure that the query_cache_type system variable is set correctly.

As mentioned in the previous post the High Performance MySQL book has been very useful, as has the detailed documentation
on the MySQL site.