Optimizing MySQL: Essential Configuration Parameters for Performance and Stability

Connection‑related Parameters

max_connections : Maximum concurrent client connections. Default is 151; commonly set between 500‑2000.

max_connect_errors : If a host exceeds this error count, new connections are blocked until the host cache is cleared with FLUSH HOSTS or mysqladmin flush‑hosts. Default 100, often raised to 100000.

interactive_timeout and wait_timeout : Timeout (seconds) before MySQL closes interactive and non‑interactive connections. Default is 8 hours; recommended not to exceed 24 hours (86400 s).

skip_name_resolve : When ON, MySQL skips DNS lookups and uses only IP addresses; the Host column in grant tables must contain IPs or localhost. Default is OFF.

back_log : Size of the TCP/IP listen queue for incoming connections. Increase when the server receives many short‑lived connection bursts.

File‑related Parameters

sync_binlog : Number of binary‑log groups that must be written before syncing to disk. 0 disables sync; 1 provides the safest durability but reduces performance. Common values: 2, 4, 6, 8, 16.

expire_logs_days : Days after which binary logs are automatically purged. Default 0 (no auto‑purge). Use PURGE BINARY LOGS to delete manually.

max_binlog_size : Maximum size of a single binary‑log file. Minimum 4096 B, default and maximum 1 GB. Often set to 512 M.

local_infile : Enables LOAD DATA INFILE. If OFF, the LOCAL keyword cannot be used.

open_files_limit : Upper bound on file descriptors MySQL may open, derived from OS limits, max_connections, and table_open_cache. Approximate formulas:

10 + max_connections + (table_open_cache * 2)

max_connections * 5

Value specified at MySQL startup

Cache Control Parameters

binlog_cache_size : Per‑transaction binary‑log cache. Increase for large transactions; keep ≤ 64 MB.

max_binlog_cache_size : Upper limit for binlog_cache_size. Recommended ≤ 4 GB; often set to binlog_cache_size * 2.

binlog_stmt_cache_size : Cache for non‑transactional statements. Increase when many large statements are executed.

thread_cache_size : Number of reusable threads. Formula for hit‑rate: (1‑thread_created/connections)*100%. Typical setting 300‑500 for high‑traffic servers.

query_cache_size , query_cache_min_res_unit , query_cache_type : Control the query cache (disabled by default). Size should be a multiple of 1024, ≤ 256 MB; type values 0/OFF, 1/ON, 2/DEMAND.

sort_buffer_size : Memory per session for sorting operations. Recommended 1‑4 MB; increase for large ORDER BY / GROUP BY workloads.

read_buffer_size and read_rnd_buffer_size : Buffers for sequential and random reads of MyISAM tables. Values must be multiples of 4 KB; larger values improve read‑heavy queries.

join_buffer_size : Buffer for joins without indexes. Default 256 KB; raise for complex joins.

net_buffer_length and max_allowed_packet : Network packet buffers. max_allowed_packet defaults to 4 MB, can be set up to 1 GB (must be a multiple of 1024).

bulk_insert_buffer_size : Buffer for bulk inserts into MyISAM tables. Default 8 MB; set to 0 to disable.

MyISAM‑specific Parameters

key_buffer_size : Shared index cache for MyISAM tables. Recommended up to 25 % of system RAM; exceeding ~50 % degrades performance.

key_cache_block_size : Block size for the key cache (default 1024 B).

myisam_sort_buffer_size : Buffer used during REPAIR TABLE, CREATE INDEX, or ALTER TABLE on MyISAM tables.

myisam_max_sort_file_size : Maximum temporary file size for MyISAM sort operations.

myisam_repair_threads : Number of parallel threads for MyISAM repair; default 1.

InnoDB‑specific Parameters

innodb_buffer_pool_size : Memory for InnoDB data and indexes. Default 128 MB; typically set to 80 % of physical RAM for InnoDB‑heavy workloads.

innodb_buffer_pool_instances : Number of buffer‑pool instances (effective when pool ≥ 1 GB). Usually 1 GB per instance.

innodb_flush_method : I/O flushing method. Common values: fsync, O_DSYNC, O_DIRECT, etc., each affecting durability and performance.

innodb_log_file_size and innodb_log_files_in_group : Size and count of redo‑log files. Total log size must not exceed 512 GB.

innodb_flush_log_at_trx_commit : Controls durability vs. speed. 1 = full ACID, 0 = flush once per second, 2 = write to log file then flush once per second.

innodb_flush_log_at_timeout : Flush interval for log buffer when innodb_flush_log_at_trx_commit is 2.

innodb_lock_wait_timeout : Transaction lock wait timeout (default 50 s). Reduce for interactive apps, increase for long‑running data‑warehouse queries.

innodb_fast_shutdown : Shutdown mode (0 = full, 1 = fast, 2 = immediate). Choose based on upgrade or crash‑recovery needs.

Other InnoDB settings such as innodb_file_per_table, innodb_undo_logs, innodb_undo_tablespaces, and innodb_buffer_pool_dump_at_shutdown control file layout, undo‑log capacity, and buffer‑pool persistence.

Additional Notes

Properly tuning these variables requires monitoring status variables like Connections, Threads_created, Key_reads, and Key_read_requests to ensure that cache hit rates are high and that resource limits are not being hit.