Tweaking MySQL settings for better optimization

MySQL, as it comes out of the box on most Linux distributions, is reasonably optimized. But there are a few things you might have to tweak to get the most out of it. Here are my tweaks for Drupal, and most other applications that use MySQL.

Don't edit /etc/mysql/my.cnf

Nowadays, in most cases, you shouldn't be editng the config files that come with the distributions. Most major software comes with a conf.d directory where you can place a new file that will be picked up and appended to the default configuration. So in our case, we create a new file at /etc/mysql/conf.d/custom.conf. Obviously, you have to do this as root or with sudo.

sudo vi /etc/mysql/conf.d/custom.conf

And now put the following into this new file.

[mysqld]
innodb_file_per_table
skip_name_resolve
default_storage_engine=innodb
innodb=FORCE
innodb_flush_method=O_DIRECT
tmp_table_size=32M
max_heap_table_size=32M
key_buffer_size=64M
query_cache_size=64M
query_cache_limit=4M
query_cache_min_res_unit=1K
innodb_buffer_pool_size=2048M
table_open_cache=4096
thread_cache_size=16
max_allowed_packet=16M
open_files_limit=65534

Explanation

1. innodb_file_per_table - By default MySQL stores all data into one giant table. That's unmanageable. This settings tells MySQL to store each table's data into it's own file.
2. skip_name_resolve - With this setting, MySQL doesn't bother to do reverse DNS look for each client machine that connects to it. Therefore, clients connect much faster with this setting.
3. innodb related settings are to ensure that MySQL uses INNODB storage engine instead of the older MyISAM.
4. The rest of the settings are various tweaks. You're encouraged to tweak them to what works for you.

You can find more information on all MySQL varaiables you can tweak here.

Slow Queries?

Still having slow performance in your apps? Try adding the following lines to the file above to find slow queries ...

log_slow_queries = /var/log/mysql/mysql-slow.log #where to log
long_query_time = 2 #what to log

There you have it. Hope the above helps you get best peformance out of MySQL.