MySQL Tuning and Optimisation: Some Consulting Tips

Frequently, MySQL users think about optimisation only when their application is about to go live. And when asking for our consulting services, many think that server tuning is all there is to care about. This is a big mistake! Server tuning is just part of the task of optimising your MySQL installation, with query optimisation and indexing being other main areas of focus.

First, it is correct that you can and should improve your server settings to reflect your application and data needs. Second, don't expect to be fine off once you have done that job. The bulk of what you can do is hidden in the actual coding.

Tuning MySQL is not fundamentally different from tuning any other RDBMS database on the market. A mistake in a SELECT on a simple join with a few small tables might return you billions of records; see the example in the EXPLAIN section of the manual at
http://www.mysql.com/doc/en/EXPLAIN.html (http://www.mysql.com/doc/en/EXPLAIN.html)
There are lots of other tricky situations as well. Doing the MySQL tuning of an application ends up at systematically looking at every query sent out by the application, and then tuning it appropriately.

One of the most usual focus areas is indexing. Many applications lack proper indexes, or have the wrong indexes with respect to the frequent queries. However, this isn't always acknowledged by the database administrators, who oftentimes prefer tuning server parameters to creating indexes or even changing the application.

On one of my consulting jobs, when I pointed out the need for query tuning, I was told by the DBA guys that the developers don't need any consulting. Nevertheless, most of my tuning work ended up as query and index optimisation. The need was exposed through the slow query log, which I advised to turn on with the special qualifier "log-long-format", resulting in a catch of all queries not using an index. Two days after they went online, I got an email: "Please have a look at the slow query log - it has already 400 MB!" Most of them were queries not using an index, which the slow log always exposes even though they might initially have a response time below the preset limit for a slow query.

So don't be afraid to ask someone who might give you a hint in the right direction. Consulting is about having a fresh look at your situation, not about telling you that you did everything wrong. You are an expert and perfectly fit for the job, but you see things by habit and custom and from the inside. We have a different approach. This and our broad experience both as makers of MySQL and consultants gives us an advantage you might want to exploit.