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:

This howto recommends:

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:

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

Thanks


0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.