You are here: Home / Debian GNU/Linux / Servers / MySQL / Optimize MySQL on Debian

Optimize MySQL on Debian

by Pierre-Yves Landuré last modified Nov 11, 2017 03:07

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:

The optimization of server configuration does not replace using well conceived SQL queries !

Prerequisites

This howto needs:

This howto recommends:

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:

Thanks