Practical Server Hardware, Linux Kernel, and MySQL Optimization Tips

Server hardware optimization:

1. Choose the Performance Per Watt Optimized (DAPC) mode to unleash the CPU's maximum performance.

2. Set the memory frequency to "Maximum Performance" for the best speed.

3. Enable Node Interleaving in the memory settings menu to avoid NUMA-related issues.

4. Prefer RAID10 for a balance of read/write performance and data safety (requires at least four disks), rather than the commonly used RAID5, which offers only moderate performance and is not recommended for database workloads.

Linux system kernel optimization:

1. Reduce swap usage by lowering vm.swappiness from the default 60% to around 10% (on RHEL7/CentOS7+ be cautious about setting it to 0, as it may cause OOM).

2. Adjust vm.dirty_background_ratio to 10% and vm.dirty_ratio to 5% so that dirty pages are flushed to disk promptly, preventing sudden I/O stalls.

3. Set net.ipv4.tcp_tw_recycle and net.ipv4.tcp_tw_reuse to 1 to reduce TIME_WAIT sockets and improve TCP efficiency.

4. Increase the system's maximum number of connections via /etc/security/limits.conf .

MySQL parameter optimization:

1. Use default-storage-engine=InnoDB and avoid the MyISAM engine.

2. Allocate innodb_buffer_pool_size to about 60% of physical memory when InnoDB is the primary engine, ensuring most reads are served from memory.

3. Enable innodb_file_per_table=1 to store each table in its own .ibd file, allowing easier space reclamation when tables are dropped or rebuilt.

4. Configure innodb_data_file_path=ibdata1:1G:autoextend instead of the default 10 MB size for better performance under high concurrency.

5. Set innodb_log_file_size and innodb_log_files_in_group to 2 for improved write throughput.

6. Regularly analyze the general query log and the slow‑query log; this step is crucial for identifying bottlenecks and further tuning.

--- end ---