10 Essential MySQL Configuration Settings for Performance Optimization

The article begins with six precautionary rules for modifying MySQL settings: change only one setting at a time to evaluate its effect; most parameters can be adjusted at runtime with SET GLOBAL for quick rollback, but changes must be written to the configuration file for persistence; verify the correct configuration file and [mysqld] section if a restart does not apply changes; watch for unit mismatches (e.g., innodb_buffer_pool_size in bytes, max_connections as a unitless integer); avoid duplicate entries in the config file and use version control for history; and do not linearly scale all values when hardware resources change.

Basic Settings

innodb_buffer_pool_size : The single most important setting for any InnoDB deployment. The buffer pool caches data and indexes; sizing it as large as possible ensures most reads come from memory rather than disk. Typical values: 5–6 GB for 8 GB RAM, 20–25 GB for 32 GB RAM, 100–120 GB for 128 GB RAM.

innodb_log_file_size : Controls the size of each InnoDB redo log file, directly affecting write performance and crash recovery time. Before MySQL 8.0.30 this required manual configuration; from 8.0.30 onward dynamic redo logs were introduced and the parameter is gradually deprecated.

max_connections : Default is 151. Increase if you encounter "Too many connections" errors, often caused by applications not closing connections properly. Setting it too high (e.g., 1000+) can make the server unresponsive due to too many active transactions. Mitigate with connection pooling at the application layer or thread pooling in MySQL.

InnoDB Settings

Since MySQL 5.5, InnoDB is the default and most-used storage engine, warranting careful configuration.

innodb_file_per_table : When ON (default since MySQL 5.6), each table gets its own .ibd file, allowing space reclamation on DROP, TRUNCATE, or rebuild, and enabling compression. The only downside is very large numbers of tables (10,000+). For older versions, set to ON before data import because it affects only newly created tables.

innodb_flush_log_at_trx_commit : Default 1 provides full ACID compliance; every commit forces a log flush to disk, which can be a bottleneck. Value 2 flushes once per second — acceptable for some primaries and good for replicas. Value 0 is fastest but risks losing up to one second of transactions on crash; suitable only for replicas.

innodb_flush_method : Determines how data and logs are synced to disk. With a battery-backed write-cache RAID controller, O_DIRECT is common; otherwise fdatasync (default) works for most cases. The author recommends using sysbench to decide between the two.

innodb_log_buffer_size : Buffers uncommitted transactions; default 1 MB is usually fine. If transactions contain large BLOB / TEXT fields, the buffer fills quickly and triggers extra I/O. Monitor Innodb_log_waits; if non-zero, increase this setting.

Other Settings

query_cache_size : The query cache is a known bottleneck even at modest concurrency. Best practice is to set it to 0 from day one (default in MySQL 5.6) and accelerate reads via proper indexing, read replicas, or external caches like memcached or Redis. MySQL 8.0.3+ removed the feature entirely.

log_bin : Required for the primary in replication; also enable server_id with a unique value. Useful for point-in-time recovery on a standalone server: restore the latest backup and replay binary logs. Binary logs persist indefinitely; prevent disk exhaustion with PURGE BINARY LOGS or expire_logs_days. Disable on non-primary replicas to avoid overhead.

skip_name_resolve : Disables DNS hostname lookups on client connection. If DNS is slow, connections become slow. Enable this to skip lookups entirely; the trade-off is that GRANT statements must use IP addresses only. Exercise caution when enabling on existing systems.

Conclusion

Many other settings matter depending on workload and hardware (low memory/fast disk, high concurrency, write-heavy loads). The goal of this list is to provide a quick path to a solid baseline MySQL configuration without spending excessive time on non-essential parameters or documentation review.