Optimal MySQL-configuration (my.cnf)

Question

The following is my default production MySQL configuration file (my.cnf) for a pure UTF-8 setup with InnoDB as the default storage engine.

[server]
bind-address=127.0.0.1
innodb_file_per_table
default-character-set=utf8
default-storage-engine=innodb

The setup does the following:

1. Binds to localhost:3306 (loopback) instead of the default *:3306 (all interfaces). Done to increase security.
2. Sets up one table space per table. Done to increase maintainability.
3. Sets the default character set to UTF-8. Done to allow for easy internationalization by default.
4. Sets the default storage engine to InnoDB. Done to allow for row-level-locking by default.

Assume that you could further improve the setup by adding a maximum of three (3) configuration parameters. Which would you add and why?

An improvement would in this context mean either a performance improvement, a reliability improvement or ease-of-use/ease-of-maintainability increase. You can assume that the machine running the MySQL instance will have 1000 MB of RAM.

Answer 1

Increase the innodb buffer pool size, as big as you can practically make it:

innodb_buffer_pool_size=768M

You'll also want some key buffer space for temp tables:

key_buffer_size=32M

Others would depend on what you are doing with the database, but table_cache or query_cache_size would be a couple other potentials.

Answer 2

To cache more data:

innodb_buffer_pool_size = 512M

If you write lots of data:

innodb_log_file_size = 128M

, to avoid too much log switching.

There is no third I'd add in any case, all other depend.