Essential MySQL my.cnf Settings for Optimal Server Performance

Optimizing MySQL performance requires looking at both the server hardware and the MySQL configuration file ( my.cnf). The main hardware factors are disk I/O capability, CPU power, and physical memory size.

Hardware considerations

Disk I/O: For high‑traffic sites (e.g., 1.5 million PV/day) a fast SCSI 7200 rpm disk may become a bottleneck; RAID‑0+1 is recommended, while RAID‑5 should be avoided.

CPU: Choose a processor with sufficient cores; the exact recommendation is omitted.

Memory: At least 2 GB, preferably 4 GB or more, is needed for a production MySQL server.

Key my.cnf parameters

# Basic server settings
[mysqld]
port = 3306
server-id = 1
socket = /tmp/mysql.sock
skip-locking

# Avoid external locking and DNS lookups
skip-name-resolve   # 禁止 MySQL 对外部连接进行 DNS 解析

# Connection backlog
back_log = 384     # 短时间内可排队的请求数，Linux 推荐 < 512

# Buffer sizes (adjust according to memory)
key_buffer_size = 256M   # 索引缓冲区大小，4 GB 服务器可设 256‑384M
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M   # 每连接独占，4 GB 服务器建议 6‑8M
read_buffer_size = 4M   # 每连接独占
read_rnd_buffer_size = 16M
join_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_size = 64M   # 根据 Qcache_lowmem_prunes 调整
tmp_table_size = 256M
max_connections = 768
max_connect_errors = 10000000
wait_timeout = 10
thread_concurrency = 8   # 逻辑 CPU 数 * 2
skip-networking          # 关闭 TCP/IP（仅在本地访问时使用）
innodb_additional_mem_pool_size = 4M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 2M
innodb_thread_concurrency = 8

Important notes:

If Key_reads is high, increase key_buffer_size until Key_reads/Key_read_requests is below 1/100.

If Qcache_lowmem_prunes is large, raise query_cache_size.

Parameters such as back_log must not exceed the OS limits; the default is 50, but values up to 384 are common.

For servers with ~4 GB RAM, keep sort_buffer_size around 6‑8 M and read_buffer_size at 4 M to avoid excessive per‑connection memory usage.

These settings provide a baseline; actual values should be tuned based on real‑world monitoring of metrics like Qcache_hits, Qcache_lowmem_prunes, and connection error rates.