A Detailed Guide to MySQL Performance Tuning with my.cnf

Introduction to MySQL Performance Tuning via my.cnf

MySQL performance is heavily influenced by the parameters set in its configuration file, my.cnf (or my.ini on Windows). The following guide provides detailed explanations for common settings. Always adjust these values based on your specific server hardware and workload.

Client and Server Basic Configuration

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1

• server-id: Uniquely identifies the server in a replication setup. Typically set to 1 for a standalone or primary server.

Connection and Network Settings

skip-name-resolve
# skip-networking
back_log = 600
max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535

• skip-name-resolve: Disables DNS hostname lookups for connections, improving speed. Requires using IP addresses in grant statements.
• back_log: The number of outstanding connection requests the queue can hold. Increase for high-concurrency scenarios.
• max_connections: Maximum number of simultaneous client connections. Setting too high can exhaust memory.
• max_connect_errors: Limits consecutive failed connection attempts from a host before it is blocked.
• open_files_limit: Number of file descriptors MySQL can open. Must be greater than table_open_cache.

Cache and Buffer Configuration

table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 8M
query_cache_limit = 2M
key_buffer_size = 4M

• table_open_cache: Number of table descriptors that can be cached, affecting how quickly tables can be reopened.
• tmp_table_size / max_heap_table_size: Limits for in-memory temporary tables. Queries exceeding this limit use on-disk tables.
• thread_cache_size: Caches threads to reduce overhead from frequent creation/destruction.
• query_cache_size: Size of the query result cache. Note: Deprecated in MySQL 5.7.20 and removed in 8.0. For older versions, enable only if query repetition is high.
• key_buffer_size: Index buffer for MyISAM tables. Can be set low if only InnoDB is used.

InnoDB Storage Engine Configuration

default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

• innodb_buffer_pool_size: Crucial parameter. Set to ~70-80% of available physical memory for a dedicated database server.
• innodb_flush_log_at_trx_commit: Controls durability vs. performance.
  • 0: Logs are flushed to disk once per second. Best performance, up to 1 second of data loss on crash.
  • 1: Logs are flushed on every transaction commit. Safest, but slowest performance.
  • 2: Logs are written to the OS cache on commit, flushed to disk once per second. A common balance between safety and performance.
• innodb_log_file_size: Size of each redo log file. Larger sizes improve write performance but increase recovery time.

Logging and Transaction Configuration

transaction_isolation = REPEATABLE-READ
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30
log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
# lower_case_table_names = 1
skip-external-locking

• transaction_isolation: Default transaction isolation level (REPEATABLE-READ).
• binlog_format: Binary logging format. mixed uses a combination of STATEMENT and ROW formats.
• slow_query_log: Enables logging of slow queries for performance analysis.
• lower_case_table_names: Controls case-sensitivity of table names. Critical for cross-platform compatibility.

Other Configuration Parameters

ft_min_word_len = 4
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800

• bulk_insert_buffer_size: Cache used for bulk inserts into MyISAM tables.
• interactive_timeout / wait_timeout: Time in seconds before an idle connection is closed. Prevents resource exhaustion.

Tool-Specific Configuration Sections

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

These sections apply only when the respective tools (mysqldump, myisamchk) are run and do not affect the MySQL server itself.

General Tuning Advice and Precautions

1. Iterate Gradually: Change only a few parameters at a time and monitor the impact.
2. Monitor First: Use commands like SHOW GLOBAL STATUS and SHOW ENGINE INNODB STATUS before and after changes.
3. Match Hardware: Key parameters like innodb_buffer_pool_size and max_connections must align with available server memory.
4. Check Version: Default values and valid parameters differ between MySQL versions (e.g., 5.7 vs. 8.0). Always consult the official documentation.
5. Test Thoroughly: Validate all configuration changes in a staging environment before applying them to production.