November 28, 2014

What’s required to tune MySQL?

I got a serendipitous call (thanks!) yesterday asking what would be needed to tune[1] 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.

Conclusion

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.

[1] 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.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. Raj Satya says:

    HI Baron,
    In addition, OS tuning and leveraging network bound load like multiple network card also plays a role. Could you please share some of your experience on those lines,

    Rajs

  2. I’ll try to write about that sometime.

  3. I must 100% agree to Baron, few months ago I did “tuning” and bench-marked results.
    Configuring my.conf for InnoDB 70% read workload gave me only 5-7% improvements of query response times.

  4. One of our MySQL 5.5 installation runs on a single PC. There are no space constraints and all the tables are using InnoDB engine. How much innodb_buffer_pool_size and log_size should I keep? What other parameters to fine tune?

    Is there any MySQL database profiling tool through which it is possible to monitor the performance?

Speak Your Mind

*