MySQL Performance Tuning: Essential my.cnf Parameters and Configuration Guide

MySQL Performance Optimization Guide: Key my.cnf Parameters Explained

MySQL's performance is heavily influenced by the settings in its configuration file, my.cnf (or my.ini). This guide details core performance parameters and provides tuning recommendations for MySQL 5.7 and later.

Connection and Network Parameters

• max_connections: The maximum number of concurrent client connections. The default is often 151. Increase this if you encounter "Too many connections" errors, but be aware each connection consumes memory. A typical range for a 4GB server is 400-800.
• max_allowed_packet: Maximum size of a single packet for communication between server and client. Default is often 16MB or 64MB. Increase (e.g., to 64M or 128M) if handling large BLOB/TEXT fields or bulk data operations.
• wait_timeout & interactive_timeout: The number of seconds a connection can be idle before the server closes it (non-interactive and interactive sessions, respectively). Default is 28800 seconds (8 hours). For web applications, reducing this (e.g., to 600 seconds) helps free idle connections.

Buffer and Cache Parameters

• innodb_buffer_pool_size (Most Important): The size of the memory area where InnoDB caches table and index data. This is the most critical setting for InnoDB performance. Set to 50%-70% of available system RAM on a dedicated database server.
• key_buffer_size: Size of the buffer used for caching indexes for MyISAM tables. If using primarily InnoDB, set this low (e.g., 16M-64M).
• query_cache_size (Note: Removed in MySQL 8.0): The size of the query result cache. In MySQL 5.7 and earlier, it can help for read-heavy workloads with few writes, but often becomes a bottleneck under high concurrency. It's generally recommended to disable it (set to 0).
• table_open_cache: The number of open tables that can be cached for all threads. Increase if the Opened_tables status variable grows rapidly. A good starting point is a multiple of max_connections.

Query and Sort Optimization Parameters

• sort_buffer_size: The buffer size allocated for sorting operations per connection. Setting it too high (e.g., >2MB) can waste memory. A typical range is 256K to 2MB.
• read_buffer_size & read_rnd_buffer_size: Buffer sizes for sequential and random read operations, respectively. These are also per connection. Start with defaults (often 128K, 256K) and adjust based on monitoring.
• join_buffer_size: The buffer size used for table joins that don't use indexes. Increasing this may help for complex joins. The default is often small (e.g., 256K).

InnoDB-Specific Parameters

• innodb_log_file_size: The size of each InnoDB redo log file. Larger files reduce disk I/O but increase crash recovery time. A recommended range is 256MB to 2GB.
• innodb_flush_log_at_trx_commit: Controls the durability of transactions.
  • 1 (Default): Safest. Flushes log to disk on every commit. Ensures ACID compliance but is slower.
  • 2: Log is flushed to disk once per second. Better performance, but up to 1 second of data can be lost in a crash.
  • 0: Writes and flushes the log once per second (similar to 2 but with different internal timing).

  For applications that can tolerate minimal data loss (e.g., analytics), setting this to 2 can improve performance.

• innodb_file_per_table: Set to ON. This stores each InnoDB table in its own .ibd file, improving manageability and allowing disk space to be reclaimed when tables are dropped.

Configuration Example

Below is a simplified example configuration for a 4GB RAM server using InnoDB:

[mysqld]
# Connection
max_connections = 400
max_allowed_packet = 64M
wait_timeout = 600
interactive_timeout = 600

# Buffers & Cache
innodb_buffer_pool_size = 2G
key_buffer_size = 32M
query_cache_size = 0
query_cache_type = 0
table_open_cache = 2000

# Query Optimization
sort_buffer_size = 1M
read_buffer_size = 256K
read_rnd_buffer_size = 512K
join_buffer_size = 256K

# InnoDB Settings
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = ON
innodb_flush_method = O_DIRECT

Optimization Principles

1. Iterate Gradually: Change only one or two parameters at a time and monitor the impact.
2. Monitor First: Use SHOW GLOBAL STATUS, SHOW ENGINE INNODB STATUS, and the slow query log to identify bottlenecks.
3. Match Hardware: Tuning must align with server hardware (CPU, RAM, disk type—SSD vs. HDD strategies differ significantly).
4. Application First: Optimize application SQL, indexing, and architecture before deep database tuning.

Important: These are general guidelines. Always test changes in a staging environment and back up your configuration before applying to production. MySQL 8.0 introduces new defaults and improvements, and some older parameters (like query cache) have been removed.