Optimize MariaDB / MySQL on Debian

The performances of a database server are greatly dependent of its hosting hardware. Disk latency, available memory and processor computing power are the main parameters of the equation. This howto help you adjust a MariaDB (or MySQL) server settings to its hosting hardware.

This howto is tested on:

• Debian 10.0 Buster

Requirements

This howto requires:

• a MySQL or MariaDB server, as described by Install MariaDB (MySQL) on Debian.

This howto recommends:

• A hosting environment with a lot of free RAM. Function of the database server load, the recommended RAM amount for MariaDB vary from 1 GB to 8 GB.
• the regular optimization of the databases’ tables, as described by Analyzing and defragmenting MySQL/MariaDB tables.
• Tuning Primer optimization tool, as described by Install Tuning Primer on Debian.
• MySQLTuner optimization tool, as described by Install MySQLTuner on Debian.
• Adjusting the filesystem mount options, as described by Adjust filesystem options for MariaDB (MySQL).
• Enabling the database server binary log, as described by Enable MariaDB (MySQL) binary log on Debian.

Environment setup

Detect if sudo is available (“command” is used if not):

cmdProxy='command'
command type -f 'sudo' &>'/dev/null' && cmdProxy='sudo'

Foreword

Optimizing a Maria DB database starts by optimizing the database structure and writing efficient SQL queries. Adjusting the Maria DB server configuration to its hardware is the cherry on top.

The following links provides good development practices for MariaDB:

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

Diagnostic

MySQLTuner & Tuning Primer

MySQLTuner and Tuning Primer are optimization tools for MariaDB. MySQLTuner is the best one of the two. Adjust the settings of your database server according to the recommendations proposed by these tools for better performances.

PHPMyAdmin

PHPMyAdmin is a Web application providing a friendly GUI to manage a MariaDB (MySQL) server. It provides valuable database server configuration tips in the “State” tab available on its home page.

Optimization

Initialize the optimizations configuration template:

${cmdProxy} wget 'https://raw.github.com/biapy/howto.biapy.com/master/mysql/tuning.cnf' \
    --no-check-certificate --output-document='/etc/mysql/conf.d/tuning.cnf'

Edit the configuration file and change the settings according to MySQLTuner and Tuning Primer recommendations:

${cmdProxy} vim '/etc/mysql/conf.d/tuning.cnf'

Metadata statistic updates can impact strongly performance of database servers. Disable innodb_stats_on_metadata:

command mysql --user='root' --execute='SET GLOBAL innodb_stats_on_metadata = 0;'

Reload the database server configuration:

${cmdProxy} systemctl 'restart' 'mariadb'

Wait for 48h before checking the new settings with MySQLTuner and Tuning Primer.

Bibliography

• High Performance MySQL (en)
• MySQL High Availability: Tools for Robust Data Centers (en)

Thanks

• Thanks to Ludovic from Serveur Linux (fr) for MySQL 5.0 (fr).