Optimizing MySQL: Key InnoDB Settings and How to Tune Them

innodb_buffer_pool_size works like Oracle's SGA; for a MySQL server it is typically set to 60%–80% of the total physical memory.

innodb_buffer_pool_instances splits that memory into N parts. It only takes effect when the pool size exceeds 1 GB and is useful for parallel processing across multiple sessions; a common rule is to keep the instance count less than or equal to the number of CPU cores.

max_connections defines the maximum number of simultaneous MySQL connections. In high‑concurrency environments this value should be tuned to a reasonable level to meet business demand while avoiding connection refusals.

max_user_connections limits the number of connections a single user can open.

innodb_log_buffer_size sets the size of the log buffer. It need not be overly large—large enough to hold roughly one second of write activity is sufficient, as MySQL flushes the log to disk about once per second by default.

innodb_stats_on_metadata controls whether InnoDB gathers statistics dynamically. Enabling it can degrade performance, so it is usually turned off and refreshed manually or on a schedule via a database job.

innodb_lock_wait_timeout specifies the lock wait timeout, defaulting to 50 seconds. Adjust it only if certain business processes legitimately require longer execution times.

innodb_thread_concurrency limits the number of threads that can enter the InnoDB engine simultaneously. A typical recommendation is twice the number of CPU cores, though the default setting is often adequate.

innodb_write_io_threads and innodb_read_io_threads define the number of threads used for writing dirty pages and reading file blocks, respectively, influencing I/O concurrency.

Example configuration for a server with two 8‑core CPUs:

innodb_read_io_threads = 8
innodb_write_io_threads = 8

If read operations dominate write operations, you might adjust the values as follows:

innodb_read_io_threads = 10
innodb_write_io_threads = 6