What happened to my-*.cnf in MySQL 5.6.8?

Question

Up to MySQL 5.6.7, the official RPM was shipping with these files:

/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-small.cnf

In 5.6.8, these files are gone, and the only one left is:

/usr/share/mysql/my-default.cnf

I'm not a MySQL tuning expert, and was used to always start with my-innodb-heavy-4G.cnf. Now I am left with a single configuration option:

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

Is tuning this only variable the magic key to most optimizations, or are there other relevant parameters that I should tune when installing a new server?

In which case, where can I find an equivalent file, that would contain sensible defaults for a InnoDB-only, 4GB RAM dedicated MySQL server?

Answer 1

You know what they say, about "when all else fails..."

This release continues the process begun in MySQL 5.6.6 of making changes to the default values of server parameters. The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually. These changes are subject to revision in future releases as we gain feedback.

This is all explained in the Release Notes for MySQL 5.6.8.

On Unix platforms, mysql_install_db now creates a default option file named my.cnf in the base installation directory. This file is created from a template included in the distribution package named my-default.cnf. You can find the template in or under the base installation directory. When started using mysqld_safe, the server uses my.cnf file by default. If my.cnf already exists, mysql_install_db assumes it to be in use and writes a new file named my-new.cnf instead.

The my-default.cnf template replaces the older sample option files (my-small.cnf, my-medium.cnf, and so forth), which are no longer distributed.

Without question, innodb_buffer_pool_size is the single most important tuning parameter for a MySQL installation.

For "sensible defaults," start with the defaults.

To put it another way...

"Tuning" your server can be a stunning waste of time.

-- Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Location 12182). OReilly Media - A. Kindle Edition.

Answer 2

It is true that innodb_buffer_pool_size is the single most important MySQL with InnoDB tuning parameter. However if you wish to go further, then tools.percona.com has a my.cnf generator that will generate a very reasonable configuration for you.

Disclaimer: Percona is my employer.