MySQLintermediate

MySQL Production Configuration

The my.cnf settings that matter for a production InnoDB workload, and the reasoning behind each value.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

/etc/mysql/my.cnfHigh-load productionAssumes dedicated host, 16 vCPU, 64 GB RAM, NVMe storage, OLTP workload

[mysqld]
# --- Storage engine ----------------------------------------------------
default_storage_engine = InnoDB
innodb_buffer_pool_size = 48G           # ~75% of RAM on a dedicated host
innodb_buffer_pool_instances = 8
innodb_redo_log_capacity = 8G           # 8.0.30+; larger = fewer write stalls, longer recovery
innodb_flush_method = O_DIRECT          # avoid double caching in the OS page cache
innodb_flush_neighbors = 0              # correct for SSD/NVMe; 1 suits spinning disks
innodb_io_capacity = 4000               # sustained IOPS the storage can deliver
innodb_io_capacity_max = 10000
 
# --- Durability --------------------------------------------------------
innodb_flush_log_at_trx_commit = 1      # 1 = a commit survives a crash
sync_binlog = 1                         # 1 = the binary log survives a crash
 
# --- Connections -------------------------------------------------------
max_connections = 500
thread_cache_size = 100
wait_timeout = 600
interactive_timeout = 600
 
# --- Replication -------------------------------------------------------
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = FULL                 # required by most CDC consumers
binlog_expire_logs_seconds = 604800     # 7 days
gtid_mode = ON
enforce_gtid_consistency = ON
 
# --- Character set -----------------------------------------------------
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
 
# --- Observability -----------------------------------------------------
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0       # noisy; enable only while investigating
performance_schema = ON

Why these values

innodb_buffer_pool_size — InnoDB does its own caching and uses O_DIRECT to bypass the OS page cache, so on a dedicated host most of RAM should go here. Leave room for per-connection buffers, the redo log buffer and the OS. In a container, size well below the cgroup limit.

innodb_io_capacity — tells InnoDB how much background flushing the storage can absorb. The default of 200 describes a single spinning disk and throttles flushing on NVMe, which causes the redo log to fill and produce stalls. Set it from a measured device benchmark, not from a guess.

innodb_flush_neighbors = 0 — flushing adjacent pages together helps rotational media and wastes I/O on SSDs.

max_connections — MySQL uses threads rather than processes, so connections are cheaper than in PostgreSQL, but each still allocates per-thread buffers. Set it above the sum of application pool sizes, and use ProxySQL if the number becomes large.

binlog_expire_logs_seconds — retain long enough that a replica can be rebuilt or a delayed replica can catch up after a weekend outage. Too short and a lagging replica breaks irrecoverably; too long and the disk fills.

Per-session buffers

sort_buffer_size, join_buffer_size and read_rnd_buffer_size are allocated per connection and per operation. Raising them globally multiplies across every connection, which is the usual cause of unexpected memory exhaustion. Leave the defaults and raise them per session where a specific job needs it.

Verifying a change

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT * FROM performance_schema.global_variables WHERE variable_name = 'innodb_io_capacity';

Many InnoDB settings are dynamic — innodb_buffer_pool_size can be resized online in MySQL 8.0, though the resize itself takes time and consumes I/O. Check whether a change needs a restart before scheduling a maintenance window for it.