Optimizing MySQL: Key Configuration Parameters and Best Practices

General Parameters

max_connections = 151 Recommended to set max connections to about 80% of the limit.

sort_buffer_size = 2M Buffer size for ORDER BY and GROUP BY; can be increased up to 16M.

query_cache_limit = 1M Only query results smaller than 1 MB are cached to avoid overwriting the cache with large results.

query_cache_size = 16M Size of the cache for SELECT results; can be multiplied as needed.

open_files_limit = 1024 If the number of open files reaches this limit, the server may fail to connect or hang.

MyISAM Parameters

key_buffer_size = 16M Index cache size, typically 30‑40% of physical memory.

read_buffer_size = 128K Read‑operation buffer; recommended 16 M–32 M.

InnoDB Parameters

innodb_buffer_pool_size = 128M Index and data buffer size, usually 60‑70% of physical memory.

innodb_buffer_pool_instances = 1 Number of buffer‑pool instances; 4 or 8 is recommended.

innodb_flush_log_at_trx_commit = 1 Critical setting: 0 writes logs to disk roughly every second, risking up to 1 second of data loss; 1 writes and syncs after each transaction (high I/O cost); 2 writes to OS cache and syncs each second, offering high performance with minimal risk. For low safety requirements, 2 is recommended.

innodb_file_per_table = OFF Default shared tablespace can cause I/O slowdown; enabling per‑table tablespaces isolates each table’s data and indexes, allowing easier migration.

innodb_log_buffer_size = 8M Log buffer size; usually does not need to exceed 16 M because logs are flushed at most once per second.