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
- 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.
- 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.
- innodb related settings are to ensure that MySQL uses INNODB storage engine instead of the older MyISAM.
- 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.
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.