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:

• run the tunning-primer.sh script to see how well is your server doing (or not);
• read the MySQL Performance blog articles, specially the article about choosing innodb_buffer_pool_size;
• make sure you don't double-cache with innodb_flush_method=O_DIRECT;
• make sure Linux is not swapping out your memory "just because":
  • echo 0 > /proc/sys/vm/swappiness
  • echo 'vm.swappiness = 0' >> /etc/sysctl.conf

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.