Optimizing MySQL, Intermediate results

In the past few weeks we’ve been looking into different MySQL configuration options and were running many simple tests. Even though all tests were different and were targeting different configuration options – one thing was common – there were no simultaneous connections and queries to the same tables.

Before we move to next tests, where we are going to run similar queries against one or two tables at the same time, I would like to present test result summary and most important configuration options. These recommendations are good enough for most common situations and servers.

Configuration options, that made huge difference, comparing to the default values

1. Query cache must be enabled and configured

2. Various read and sort buffers must be set

3. Set InnoDB tables to be stored each in separate file

4. Tweak MyISAM table

Configuration options, that help to run more complex queries

1. Let MySQL have larger in-memory temporary tables

2. Tweak InnoDB engine

3. Additional tweaks – open file limit, skip name resolve, maximum size of a single packet, number of opened tables to keep in memory

Combined my.cnf, ready to be copied and deployed