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

Allocating more memory than the default of 8M to InnoDB (using innodb_buffer_pool_size) is surely an enhancement. Regarding the value, on a dedicated database server as yours you can set it up to the 80% of your RAM and the higher you set this value, the fewer the interactions with the hard disk will be. Just to give my two cents, I'd like to mention that you can have some performance boost tweaking the value of innodb_flush_log_at_trx_commit, however sacrificing ACID compliance... According to the MySQL manual:

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.

So you might loose some data that were not written properly in the database due to a crash or any malfunction. Again according to the MySQL manual:

However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.

So, I would suggest:

innodb_flush_log_at_trx_commit = 0

Finally if you have a high connection rate (i.e. if you need to configure MySQL to support a web application that accesses the database) then you should consider increasing the maximum number of connections to something like 500. But since this is something more or less trivial and well known, so I'd like to emphasize on the importance of back_log to ensure connectivity.

I hope these information will help you optimize your database server.

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.

Answer 3

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.