Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks, I'll stay with innodb_buffer_pool_size only for now then! –  Benjamin Nov 24 '12 at 19:16
add comment

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.

share|improve this answer
    
This tool looks very interesting! Thanks for sharing it. –  Benjamin Nov 26 '12 at 8:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.