Tweaking MySQL Settings for Better Optimization

MySQL configuration recommendations for Drupal and other applications including InnoDB settings and query cache.

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.

Create /etc/mysql/conf.d/custom.conf:

[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

Key settings:

innodb_file_per_table - Store each table’s data into its own file
skip_name_resolve - Skip reverse DNS lookups for faster connections
InnoDB settings ensure MySQL uses INNODB storage engine

Slow Queries?

Add to find slow queries:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2