Minimal InnoDB configuration

This is a bare minimum setup for MySQL servers using InnoDB tables. Using InnoDB, query cache is not required. Reclaim disk space when a table or database is DROPed. If you’re using SSDs, flushing is a redundant operation (SDDs are not sequential).

default_storage_engine = InnoDB
query_cache_type = 0
innodb_file_per_table = 1
innodb_flush_neighbors = 0

Concurrency

Make sure we can create more than than the default 4 threads by setting innodb_thread_concurrency to infinity (0); this lets InnoDB decide based on optimal execution.

innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64

Hard drive utilization

Set the capacity (normal load) and capacity_max (absolute maximum) of IOPS for MySQL. The default of 200 is fine for HDDs, but these days, with SSDs capable of thousands of IOPS, you are likely to want to adjust this number. There are many tests you can run to determine IOPS. The values above should be nearly that limit if you are running a dedicated MySQL server. If you are running any other services on the same machine, you should apportion as appropriate.

innodb_io_capacity = 2500
innodb_io_capacity_max = 3000

RAM utilization

Set the RAM available to MySQL. Whilst the rule of thumb is 70-80%, this really depends on whether or not your instance is dedicated to MySQL, and how much RAM is available. Don’t waste RAM (i.e. resources) if you have a lot available.

innodb_buffer_pool_size = 10G