Wer seine Serverressourcen gut ausnutzen und seine installierten Applikationen bestmögliche Performance entlocken will, der kommt um das Thema MySQL-tuning nicht herum.Vergleichbar mit Tuning am Auto, hier gibt es viele Parameter, an denen man schrauben kann, damit sich die Leistung verbessert. Mehr Motorleistung (im übertragenen Sinne mehr RAM) ist lange nicht die Lösung für alles, wenn Getriebe und Reifen nicht dazu passen, wird das Auto seine Leistung nicht vernünftig auf die Straße bringen. An der Aerodynamik kann man schrauben, am Fahrzeuggewicht … die Liste ist lang.

Ähnlich bei Datenbanken, hier gibt es viele Parameter, an denen man optimieren kann. Nur wer alles richtig aufeinander abstimmt, kann eine vernünftige Leistung erzielen. Manch einer bezeichnet MySQL-tuning als „Black Magic“ und ehrlich gesagt – ich finde den Begriff durchaus zutreffend, denn gewisse Prozesse sind ab einer gewissen Größe von Systemen nicht immer durchschaubar. In der Standardinstallation sind alle Datenbankkonfigurationen meist konservativ und für eine breite Zahl an Nutzungsszenarien so konfiguriert, dass der Datenbankserver nicht gleich abstürzt. Anders gesagt: Wer sein Gaspedal nie ganz durchtritt und das Auto bis ins letzte ausreizt, wird gewiss auch ans Ziel kommen – es gibt gewiss aber schnellere und/oder auch effizientere/ökologischere Wege. Genug der Vergleiche, nun zu den Fakten:

Es hier kleine Helferlein, die einem die Bestandsaufnahme und Optimierungsschritte einfach machen. Unter Linux-Systemen sei hier das Tool „mysqltuner“ genannt. Dieses Skript repariert keine kaputte Konfiguration, noch macht es aus einem Trabbi ein Ferrari, es ist nur eine Art Diagnosegerät, das basiert auf der aktuellen Konfiguration Empfehlungen gibt.

Unter Debian-basierten Systemen installiert man es einfach mit:

sudo apt install mysqltuner

Die Nutzung ist denkbar einfach, mit derm Kommando

sudo mysqltuner

lässt man das Skript die Datenbankaktivitäten seit dem letzten MySQL-restart analysieren und mysqltuner fasst die Analyse zusammen und stellt am Ende einige Empfehlungen aus. Diesen Empfehlungen ist nur zu trauen, wenn der mysql-Server mindestens 48 h gelaufen ist! Ich empfehle noch längere Laufzeiten für vernünftige Optimierungen, meist merkt man erst z.B. nach einer Woche, dass gewisse Parameter nochmal angepasst werden müssen.

WICHTIG:

Änderungen an den Konfigurationen können im worst-case zu einem nicht mehr startendem Datenbankserver fühern. Man sollte also im besten Falle verstehen, was man dort verändert und welche Auswirkungen es haben kann und noch optimaler natürlich die ganze Sache in einer geschützten Testumgebung ausprobieren.

Hier die Empfehlungen aus einem nicht-optimierten MySQL-Server auf einem frisch installierten Ubuntu 18.04 Server:

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (5000) variable 
should be greater than table_open_cache (2000)
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
table_open_cache (> 2000)
innodb_buffer_pool_size (>= 375M) if possible.
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.

Was sagt uns dies nun? Ubuntu 18.04 nutzt MySQL 5.7, query_cache ist in dieser Version veraltet und wird in Version 8 entfernt werden. Mit query_cache_size = 0 und query_cache_type = 0 wird der query_cache komplett deaktiviert.

Um die Änderungen durchzuführen, kopieren wir zunächst die originale mysql-Konfiguration, um es für eventuell später auftretende Fragen griffbereit zu habhen:

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

Danach editieren wir die Konfigurationsdatei:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Und fügen folgende Zeilen hinzu bzw. ändern die vorhandenen Werte:

[mysqld]
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 2M


innodb_buffer_pool_size = 1000M
innodb_log_file_size = 125M
innodb_log_files_in_group = 2

Nachdem die Datei abgespeichert ist, wird der Datenbankserver neu gestartet:

sudo service mysql restart

Nun heißt es direkt wieder mit mysqltuner checken, ob wir versehentlich nichts komplett falsch konfiguriert haben, in diesem Fall würde uns das Skript direkt wieder neue Warnungen und Empfehlungen aussprechen. In diesem Fall bleiben die Empfehlungen übersichtlich und keine Parameter Anpassungen werden empfohlen:

General recommendations:
Control warning line(s) into /var/log/mysql/error.log file
Control error line(s) into /var/log/mysql/error.log file
MySQL started within last 24 hours - recommendations may be inaccurate

Nun heißt es abwarten und danach erneut prüfen und ggf. weitere Parameter anpassen.