Optimizing MySQL Performance Using MySQLTuner/Primer Script
MySQLTuner and Primer is a script that evaluates your MySQL database performance and provides recommendations for improvements. Follow this guide to analyze and optimize your MySQL configuration.
1. Check System Resource Usage
Before tuning MySQL, check how system resources (RAM and CPU) are being utilized:
echo [PID] [MEM] [PATH] && ps aux | awk '{print $2, $4, $11}' | sort -k2rn | head -n 20
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -20
2. Install and Run MySQLTuner
🔹 Download MySQLTuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
🔹 Make MySQLTuner Executable
chmod +x mysqltuner.pl
🔹 Run MySQLTuner
Execute the script and enter your MySQL root credentials when prompted:
./mysqltuner.pl
🔹 Sample Output
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.5.41
[OK] Operating on 64-bit architecture
[!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
[!!] Total fragmented tables: 11
[OK] Slow queries: 0% (0/113)
[OK] InnoDB buffer pool / data size: 128.0M/1.2M——– Recommendations —————————————————–
General recommendations:
– Run OPTIMIZE TABLE to defragment tables for better performance
– Enable the slow query log to troubleshoot bad queries
Variables to adjust:
– query_cache_limit (> 1M, or use smaller result sets)
MySQLTuner will provide performance suggestions based on your current configuration.
3. Alternative: Tuning Primer Script
If MySQLTuner does not run properly, use the Tuning Primer script as an alternative.
🔹 Download Tuning Primer
wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
🔹 Make Tuning Primer Executable
chmod +x tuning-primer.sh
🔹 Run Tuning Primer
./tuning-primer.sh
This script will analyze your MySQL performance and suggest changes.
4. Tuning MySQL Configuration
The MySQL configuration file is located at:
/etc/mysql/my.cnf
🔹 Backup the MySQL Configuration
Before making changes, create a backup:
cp /etc/mysql/my.cnf ~/my.cnf.backup
🔹 Apply Changes
Edit `/etc/mysql/my.cnf` and modify recommended variables. Make sure lines are not commented out with `#`.
🔹 Restart MySQL After Changes
For systemd-based systems:
systemctl restart mysqld
For other init-based systems:
service mysql restart
🔹 Monitor Performance
After making changes, rerun MySQLTuner or Tuning Primer to check if further optimizations are needed.
Conclusion
Using MySQLTuner and Tuning Primer , you can optimize MySQL performance safely. Always make incremental changes and monitor performance after each modification..