Master MySQL Performance: Essential Configuration Parameters Explained

Connection‑related parameters

max_connections : Maximum concurrent client connections; default 151, typically set between 500‑2000.

max_connect_errors : Server stops accepting new connections after this many connection errors; default 100, often increased to 100000. Reset with FLUSH HOSTS or mysqladmin flush‑hosts.

interactive_timeout and wait_timeout : Timeout before MySQL closes interactive and non‑interactive connections, respectively; defaults are 8 hours, recommended not to exceed 24 hours (86400 seconds).

skip_name_resolve : When ON, MySQL uses IP addresses only, avoiding DNS lookups; requires Host fields in privilege tables to be IPs or localhost. Default is OFF.

back_log : Size of the TCP/IP listen queue for incoming connections; increase when many connections arrive simultaneously.

File‑related parameters

sync_binlog : Number of binary‑log groups flushed to disk per transaction. 0 disables syncing; 1 provides maximum safety. Values like 2, 4, 6, 8, 16 balance safety and performance.

expire_logs_days : Automatic removal interval for binary logs; 0 disables auto‑deletion.

max_binlog_size : Maximum size of a single binary‑log file; minimum 4096 bytes, default and maximum 1 GB. Recommended 512M.

local_infile : Enables LOAD DATA INFILE usage; must be ON for local data loading.

open_files_limit : OS limit for open files by MySQL. Approximate calculation:

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 size; avoid exceeding 64 MB.

max_binlog_cache_size : Upper limit for binlog cache; recommended max 4 GB, often set to binlog_cache_size * 2.

binlog_stmt_cache_size : Cache for non‑transactional statements.

thread_cache_size : Number of reusable threads. Recommended formula: 8 + (max_connections / 100), typically set between 300‑500. Adjust based on Connections and Threads_created metrics.

query_cache_size and related settings ( query_cache_min_res_unit, query_cache_type): Usually disabled; if enabled, keep size ≤ 256 MB and a multiple of 1024.

sort_buffer_size : Memory per session for sorting; 1‑4 MB typical.

read_buffer_size : Buffer for sequential reads of MyISAM tables; must be a multiple of 4 KB.

Used when ORDER BY writes to temporary files

Used for bulk inserts into partitioned tables

Used for caching nested‑query results

read_rnd_buffer_size : Buffer for random reads of MyISAM tables; increase for large ORDER BY operations, preferably set per‑session.

join_buffer_size : Buffer for joins without indexes; increase when necessary, default 256 KB.

net_buffer_length : Initial network buffer size; grows up to max_allowed_packet. Max 1 MB.

max_allowed_packet : Maximum packet size; default 4 MB, can be raised up to 1 GB in 1024‑byte increments.

bulk_insert_buffer_size : MyISAM bulk‑insert cache; default 8 MB.

MyISAM parameters

key_buffer_size : Shared index cache for MyISAM tables; up to 25 % of system memory is typical.

key_cache_block_size : Block size for key cache; default 1024 bytes.

myisam_sort_buffer_size : Buffer used during REPAIR, CREATE INDEX, or ALTER operations.

myisam_max_sort_file_size : Maximum temporary file size for MyISAM sort operations.

myisam_repair_threads : Parallelism for MyISAM repair; default 1.

InnoDB parameters

innodb_buffer_pool_size : Memory for InnoDB data and indexes; commonly set to 80 % of physical RAM.

innodb_buffer_pool_instances : Number of buffer‑pool partitions; useful for pools > 1 GB, typically 1 GB per instance.

innodb_max_dirty_pages_pct : Percentage of dirty pages before flushing; default 75 %.

innodb_thread_concurrency : Max concurrent InnoDB threads; 0 means unlimited. Adjust based on workload (e.g., 0 for < 64 threads, 128‑64‑… for heavy contention).

innodb_flush_method : I/O flushing method (e.g., fsync, O_DIRECT, etc.). Choice affects performance on different OS and storage setups.

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

innodb_log_buffer_size : Log buffer size; 4‑8 MB typical.

innodb_flush_log_at_trx_commit : Controls durability vs. performance (0, 1, 2). Default 1 for full ACID compliance.

innodb_flush_log_at_timeout : Flush interval when innodb_flush_log_at_trx_commit is 0; default 1 second.

innodb_lock_wait_timeout : InnoDB row‑lock wait time; default 50 seconds. Reduce for OLTP, increase for long‑running warehouse queries.

innodb_fast_shutdown : Shutdown mode (0‑slow, 1‑default, 2‑fast). Choose based on need for clean shutdown vs. speed.

innodb_file_per_table : Stores each table's data and indexes in its own .ibd file; improves space reclamation.

innodb_undo_logs and innodb_undo_tablespaces : Number and size of undo log spaces; set > 32 for heavy write workloads.

innodb_buffer_pool_dump_at_shutdown and related parameters: Control dumping and loading of buffer‑pool pages during shutdown/startup to reduce warm‑up time.

By adjusting these settings according to workload characteristics and hardware resources, administrators can significantly improve MySQL’s throughput, latency, and stability.