Optimize MySQL on Debian
The performances of a MySQL server are greatly dependant of the hardware where it is hosted. The hard drive latency, the available memory and the processor computation power are the main parameters of the equation. This howto help you to adjust MySQL server settings to the hardware where it is hosted.
This howto is tested on:
- Debian 6.0 Squeeze
- Debian 7.0 Wheezy
Note
The optimization of a MySQL server for the hardware where it runs is the cherry on the topping of the optimization work. The main optimization work has to be done on database structure and SQL query optimization.
These articles describe good practices for development with MySQL:
- Optimiser MySQL (fr)
- 10 Tips for Optimizing MySQL Queries (That don't suck) (en)
- MySQL Optimization Hints (en)
- The MySQL Query Cache (en)
- MySQL Performance Blog (en)
The optimization of server configuration does not replace using well conceived SQL queries !
Prerequisites
This howto needs:
- a MySQL server, as described by Install and setup MySQL on Debian.
This howto recommends:
- the availability of a great deal of RAM. Function of the MySQL server load, the recommended quantity can vary for 1 GB to 8 GB of RAM assigned to MySQL.
- the regular maintenance of the MySQL tables, as described by Setup a regular analyze and optimization / defragmentation of MySQL tables.
Diagnosis
MySQLTuner
MySQLTuner is a Perl script computing recommendations for MySQL server configuration. It provides some interesting informations, but seems to be less pertinent than Tuning Primer (described in the next section of this article).
Important: Wait at least 48h after the MySQL server restart to use this tool, in order to dispose of mean usage statistic over a relatively long time period.
Install MySQLTuner:
command wget 'https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl' \
--quiet --no-check-certificate --output-document='/usr/local/bin/mysqltuner'
command chmod +x '/usr/local/bin/mysqltuner'
Run MySQLTuner:
command mysqltuner
The tool check MySQL server configuration and list settings that can be enhanced.
Tuning Primer
Tuning Primer is a script establishing a diagnostic of the MySQL server configuration and proposing enhancements adapted to its use.
Important: Wait at least 48h after the MySQL server restart to use this tool, in order to dispose of mean usage statistic over a relatively long time period. You should nonetheless check the server maximum memory consumption after each restart.
Install the tools needed by Tuning Primer:
command apt-get install bc
Install Tuning Primer:
command wget 'http://www.day32.com/MySQL/tuning-primer.sh' \
--output-document='/usr/local/bin/tuning-primer'
command chmod +x '/usr/local/bin/tuning-primer'
Run Tuning Primer :
command tuning-primer
The tool check MySQL server configuration and list settings that can be enhanced.
PHPMyAdmin
PHPMyAdmin is a MySQL server administration web frontend.
You can find precious recommendations on MySQL server settings in the "State" tab available on the tool home page.
Optimizations
Adjustments to the setup
Download the tuning template configuration file (to be edited according to Tuning Primer results) :
command wget 'https://raw.github.com/biapy/howto.biapy.com/master/mysql/tuning.cnf' \
--quiet --no-check-certificate --output-document='/etc/mysql/conf.d/tuning.cnf'
Edit the template file according to recommendations provided by Tuning Primer and MySQL Tuner :
command vim '/etc/mysql/conf.d/tuning.cnf'
Restart MySQL:
/etc/init.d/mysql reload
Wait 48h, and run once again Tuning Primer and MySQLTuner to check that the settings are fine. Check immediately after server restart that the maximum memory usage does not raise over the system limits.
References
These books can help you:
- Understanding MySQL Internals (en)
- MySQL High Availability: Tools for Building Robust Data Centers (en)
- High Performance MySQL: Optimization, Backups, Replication, and More (en)
Thanks
- Thanks to TUNING Primer (en) developers
- Thanks to MySQLTuner (en) developers.
- Thanks to Ludovic from Serveur Linux (fr) for MySQL 5.0 (fr).