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:
- Binds to localhost:3306 (loopback) instead of the default *:3306 (all interfaces). Done to increase security.
- Sets up one table space per table. Done to increase maintainability.
- Sets the default character set to UTF-8. Done to allow for easy internationalization by default.
- 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.
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: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:
So, I would suggest:
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.
To cache more data:
If you write lots of data:
, to avoid too much log switching.
There is no third I'd add in any case, all other depend.
Increase the innodb buffer pool size, as big as you can practically make it:
You'll also want some key buffer space for temp tables:
Others would depend on what you are doing with the database, but table_cache or query_cache_size would be a couple other potentials.