Building notes, projects, and occasional rants

MySQL optimization quick tips

I'm not a expert on MySQL, but I spent a better part of this past night optimizing a server, and I've collected some notes. This is mostly targeted at InnoDB-based tables.

To change this settings, you should edit your my.cnf and update them in the mysqld section. See the MySQL System Variables manual page for more information. Please remember to keep a good working backup of your previous my.cnf. Better yet, include your my.cnf in your source control tree. I assume that you already have tested backups of your data...

So, do this steps:

These are the basic settings that I pay attention to. Of course there is much much more settings that you can tune (transaction-isolation and innodb_flush_log_at_trx_commit come to mind), but the things above will cover most of what you need.

After this, you should also use the mysqlreport tool. It requires more investment on your part, understanding the data, but its very thorough.

Finally, install Maatkit and get used to the tools it provides. They are essential if you are using replication.