Optimize MySQL on Debian

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).