What’s required to tune MySQL?Baron Schwartz
I got a serendipitous call (thanks!) yesterday asking what would be needed to tune a database for better performance. It is a question that I hear often, but I never thought about answering it in public. Here’s a consolidated version of what I explained during our conversation.
Have realistic expectations about configuration
The first thing to know is that server configuration itself really isn’t something you should expect to deliver huge wins. If MySQL is actually badly configured, you can hurt its performance significantly. Correcting these mistakes can correspondingly improve performance. But such mistakes are relatively few and/or non-obvious to make. A few of the common ones I see are not configuring the InnoDB buffer pool size or log file size, and not using InnoDB. If your server really hasn’t been configured — that is, it’s running with a default configuration — then it’s quite possible you have one of these scenarios.
Most other serious configuration mistakes will only affect you in edge cases. If you get bitten, the impact can be severe. But the likelihood of that happening is not extremely high in the general population. This blog’s articles often illustrate extreme cases, so keep in mind that the vast majority of MySQL installations don’t suffer from the variety and severity of problems we write about here. Examples include what happens when you have query cache contention, DNS problems, or very slow I/O. In addition to unlikely circumstances, I sometimes see unlikely settings, such as making the sort buffer size much too large (a gigabyte comes to mind).
As a result, although it’s a good thing to have an expert look at your system and let you know if something obscure is wrong with the configuration, it’s not likely to improve the system’s performance dramatically, all things considered.
More than configuration
To do a proper job, the consultant will need more than simply looking at your my.cnf file. It’s sometimes possible to look at the my.cnf file and see something wrong. Usually, though, the consultant needs to look at several important factors, including the following:
- The hardware
- The server configuration
- The workload
- The queries
- The schema design (table design, data types, indexing, etc)
- The data itself
All of these things interact, and it’s important to have an overall view of the server to form a responsible opinion about its configuration. If a particular person has been working with the system for a time and knows it well, it’s good to keep that person involved if possible.
In addition to requiring more than the configuration as input, the consultant is likely to suggest changes to other areas as a deliverable of the process. For example, you may receive suggestions about reindexing, or changing queries, or even changing the way that the application uses the database. These changes can give a much greater improvement in your database’s performance.
Don’t expect huge improvements from a review of your server’s configuration, unless you think that something is seriously wrong. It’s a wise investment of time to take a deeper look at the server, including how the data, schema, queries, and workload interact with the server itself. This review can sometimes deliver large gains that go far beyond configuration alone.
 I prefer to avoid the word “tuning,” because database tuning is an activity that can be done endlessly, with little definition or quantification of results. I prefer to speak specifically of configuring, or of performance optimization, or something more descriptive and concrete instead.