MariaDB Performance Optimierung

Andreas Preuss
10-06-2018 23:11

Heute sind MySQL und MariaDB eines der am häufigsten verwendeten RDBMS für Webanwendungen wie WordPress, Joomla, Magento und andere. Diese Beschreibung zeigt einige grundlegende, aber nützliche Tipps, wie man die Leistung von MySQL/MariaDB optimieren könnte. Wir gehen hierbei davon aus, dass bereits MySQL oder MariaDB installiert wurde.
Jedes MySQL-Setup ist einzigartig und erfordert zusätzliche Überlegungen, bevor Änderungen vorgenommen werden. Die MySQL/MariaDB-Konfigurationsdatei befindet sich in /etc/my.cnf. Jedes Mal, wenn diese Datei geändert wird, muss auch der MySQL-Dienst neu gestartet werden, damit die neuen Änderungen wirksam werden.

# So kann man den MySQL Server neu starten:
 /etc/init.d/mysql restart

# So kann man den Server anhalten:
/etc/init.d/mysql stop

# So kann man den MySQL Server starten nachdem er gestoppt wurde 
/etc/init.d/mysql start

Swappiness in MySQL vermeiden
Swapping ist ein Prozess, der auftritt, wenn das System einen Teil des Speichers auf einen speziellen Speicherplatz namens "swap" verschiebt. Das Ereignis wird normalerweise angezeigt, wenn der physische Speicher Ihres Systems erschöpft ist und das System die Informationen auf die Festplatte verschoben hat, anstatt etwas RAM freizugeben. Die Festplatte ist viel langsamer als das RAM.
Standardmässig ist die Option aktiviert:

# sysctl vm.swappiness 
vm.swappiness = 60

# Um Swappiness zu deaktivieren, ändert man den Eintrag auf:
sysctl -w vm.swappiness=0

MySQL Max-Verbindungen einstellen
Die Direktive max_connections sagt dem Server, wie viele gleichzeitige Verbindungen erlaubt sind. Der MySQL/MariaDB-Server erlaubt den in max_connections + 1 angegebenen Wert für Benutzer mit SUPER-Privilegien. Die Verbindung wird nur für die Zeit geöffnet, in der die MySQL-Abfrage ausgeführt wird - danach wird sie geschlossen und eine neue Verbindung kann an ihre Stelle treten. Zu viele Verbindungen können eine hohe RAM-Auslastung verursachen und den MySQL-Server sperren. Normalerweise benötigen man für kleine Websites zwischen 100-200 Verbindungen, während größere Websites 500-800 oder mehr benötigen. Der Wert, den man hier verwendet, hängt stark von der speziellen MySQL/MariaDB-Nutzung ab.
So kann man den Wert von max_connections dynamisch ändern, ohne den MySQL-Dienst neu starten zu müssen:

# mysql -u root -p
mysql> set global max_connections := 300;

MySQL thread_cache_size konfigurieren
Die Direktive thread_cache_size legt die Anzahl der Threads fest, die der Server zwischenspeichern soll. Wenn der Client die Verbindung trennt, werden seine Threads in den Cache gestellt, wenn sie kleiner als die thread_cache_size sind. Weitere Anfragen werden durch die Verwendung der im Cache gespeicherten Threads abgeschlossen. Um Ihre Performance zu verbessern, kann man die thread_cache_size auf eine relativ hohe Zahl setzen. Um die Thread-Cache-Trefferrate zu ermitteln, kann man die folgende Technik verwenden:

mysql> show status like 'Threads_created';    # Threads_erzeugt
mysql> show status like 'Connections';        # Verbindungen

Folgende Formel kann nun angewendet werden, um den Prozentsatz der Thread-Cache-Trefferrate zu berechnen:

100 - ((Threads_erzeugt / Verbindungen) * 100)

Wenn man eine niedrige Zahl erhält, bedeutet das, dass die meisten der neuen mysql-Verbindungen einen neuen Thread starten, anstatt aus dem Cache zu laden. In solchen Fällen würde man sicherlich die thread_cache_size erhöhen wollen. Das Gute dabei ist, dass die thread_cache_size dynamisch geändert werden kann, ohne dass der MySQL-Dienst neu gestartet werden muss. Dies kann man so erreichen:

mysql> set global thread_cache_size = 16;

Deaktivieren des MySQL Reverse DNS-Lookups
Standardmäßig führt MySQL/MariaDB einen DNS-Lookup der IP-Adresse/Hostname des Benutzers durch, von dem die Verbindung kommt. Für jede Client-Verbindung wird die IP-Adresse überprüft, indem sie in einen Hostnamen aufgelöst wird. Danach wird der Hostname wieder in eine IP aufgelöst, um sicherzustellen, dass beide übereinstimmen. Dies kann leider zu Verzögerungen bei schlecht konfiguriertem DNS oder Problemen mit dem DNS-Server führen. Aus diesem Grund könnte man den Reverse-DNS-Lookup deaktivieren, indem man folgendes in der Konfigurationsdatei hinzufügt:

[mysqld]
# Skip reverse DNS lookup of clients
skip-name-resolve

Nach dieser Änderung nicht vergessen den MySQL Dienst neu zu starten.

MySQL query_cache_size konfigurieren
Wenn man viele sich wiederholende Abfragen hat und sich die Daten nicht oft ändern, könnte man den Query-Cache anpassen. Oft wird das Konzept hinter der query_cache_size nicht verstanden und man setzt diesen Wert auf Gigabyte, was zu einer Verschlechterung der Performance führt. Der Grund dafür ist die Tatsache, dass Threads den Cache während der Updates sperren müssen. Normalerweise sollte ein Wert von 200-300 MB mehr als ausreichend sein. Wenn die Website relativ klein ist, kann man versuchen, den Wert von 64M zu erhöhen und die Zeit etwas verlängern. Folgenden Einstellungen können in der MySQL-Konfigurationsdatei vorgenommen werden:

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

tmp_table_size und max_heap_table_size konfigurieren
(Nur bei Verwendung von MyISAM) Beide Direktiven sollten die gleiche Größe haben und das Schreiben auf Datenträgern möglichst verhindern. Die tmp_table_size ist die maximale Größe der internen In-Memory-Tabellen. Bei Überschreitung des betreffenden Limits wird die Tabelle in die MyISAM-Tabelle auf der Festplatte konvertiert. Dies wirkt sich auf die Performance der Datenbank aus. Empfehlung in der Regel die Angabe von 64M für beide Werte für jedes GB RAM auf dem Server.

[mysqld]
tmp_table_size= 64M
max_heap_table_size= 64M

Aktivieren von MySQL Slow Query Logs
Das Protokollieren langsamer Abfragen kann helfen, Probleme mit der Datenbank zu ermitteln und diese zu beheben. Dies kann einfach durch Hinzufügen der folgenden Werte in der MySQL-Konfigurationsdatei aktiviert werden:

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

Die erste Direktive ermöglicht die Protokollierung von langsamen Abfragen, während die zweite Direktive MySQL sagt, wo die eigentliche Protokolldatei gespeichert werden soll. Verwendet man long_query_time, um die Zeitspanne zu definieren, die als lang angesehen wird, bis die MySQL-Abfrage abgeschlossen ist.

Überprüfen auf ungenutzte MySQL-Verbindungen (Idle Connections)
Leere Verbindungen verbrauchen Ressourcen und sollten nach Möglichkeit unterbrochen oder aufgefrischt werden. Solche Verbindungen befinden sich im "Schlaf"-Zustand und bleiben in der Regel über einen längeren Zeitraum erhalten. Um nach leeren Verbindungen zu suchen, kann man den folgenden Befehl ausführen:

# mysqladmin processlist -u root -p | grep "Sleep"

Dies gibt eine Liste der Prozesse, die sich im Ruhezustand befinden aus. Das Ereignis erscheint, wenn der Code eine persistente Verbindung zur Datenbank verwendet. Bei Verwendung von PHP kann dieses Ereignis bei Verwendung von mysql_pconnect auftreten, das die Verbindung öffnet, danach Abfragen ausführt, die Authentifizierung entfernt und die Verbindung offen lässt. Dies führt dazu, dass alle Puffer pro Thread im Speicher gehalten werden, bis der Thread stirbt. Das erste, was Sie hier tun wäre, ist, den Code zu überprüfen und zu reparieren. Wenn man keinen Zugriff auf den ausgeführten Code hat, sollte man die wait_timeout-Direktive ändern. Der Standardwert ist 28800 Sekunden. Man kann ihn sicher auf etwa 60 Sekunden reduzieren:

wait_timeout=60

MySQL max_allowed_packet setzen
MySQL teilt Daten in Pakete auf. Normalerweise wird ein einzelnes Paket als eine Zeile betrachtet, die an einen Client gesendet wird. Die Direktive max_allowed_packet definiert die maximale Paketgröße, die gesendet werden kann. Wenn man diesen Wert zu niedrig setzt, kann eine Abfrage zum Stillstand kommen und man erhält einen Fehler im MySQL-Fehlerprotokoll. Es wird empfohlen, den Wert auf die Größe des größten Pakets zu setzen. Änderung in der Datei my.ini oder ~/.my.cnf, indem man die einzelne Zeile unter [mysqld] die Datei aufnimmt:

nano /etc/mysql/my.cnf
max_allowed_packet=500M # (natürlich individuell, je nachdem was man braucht) unter der Sektion [MYSQLD]!  Nur am unteren Rand der Datei zu platzieren, funktioniert nicht!
# So kann die Einstellung sofort für alle geändert werden, bis der Server neu startet:
SET GLOBAL max_allowed_packet=1073741824;

MySQL-Leistungstuning prüfen
Die Messung der MySQL/MariaDB-Performance sollte man regelmäßig durchführen. Dies zeigt immer auf, ob sich etwas in der Ressourcennutzung ändert oder verbessert werden muss. Für das Benchmarking stehen viele Tools zur Verfügung, aber ich möchte Ihnen eines vorschlagen, das einfach und leicht zu bedienen ist. Das Tool heißt mysqltuner. Um es herunterzuladen und auszuführen, verwendet man den folgenden Befehlssatz: Quelle: https://github.com/major/MySQLTuner-perl/

# wget http://mysqltuner.pl/ -O mysqltuner.pl
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
# perl mysqltuner.pl
# oder
# wget https://github.com/major/MySQLTuner-perl/tarball/master
# tar xf master
# cd MySQLTuner-perl/
# ./mysqltuner.pl 

Man erhält einen ausführlichen Bericht über den MySQL-Service und Empfehlungstipps.

Optimieren und Reparieren von MySQL-Datenbanken
Manchmal stürzen MySQL/MariaDB-Datenbanktabellen ziemlich leicht ab, besonders wenn der Server unerwartet heruntergefahren wird, plötzlich das Dateisystem beschädigt wird oder während des Kopiervorgangs, wenn noch auf die Datenbank zugegriffen wird. Überraschenderweise gibt es ein kostenloses Open-Source-Tool namens'mysqlcheck', das Datenbanken aller Tabellen unter Linux automatisch überprüft, repariert und optimiert.

# mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
# mysqlcheck -u root -p --auto-repair --check --optimize databasename
# oder
# mysqlcheck -u root -p --auto-repair -c -o your_database
# mysqlcheck -u root -p --auto-repair -c -o --all-databases

Wenn man nun wissen will, was der Befehl während der Prüfung macht, fügt man einfach dem Parameter --debug-info wie unten gezeigt hinzu. Dies ist hilfreich, wenn man z.B. eine große Tabelle überprüft.

# mysqlcheck --debug-info -u root -p --auto-repair -c -o your_database your_table
Enter password:
# Ausgabe z.B:
your_database.your_table  - Table is already up to date

User time 0.00, System time 0.00
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 344, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 12, Involuntary context switches 9

 

Sämtliche Änderungen sollten in einer separaten Datei, die von der /etc/mysql/my.cnf includiert wird eingetragen werden, damit bei eventuellen Software Upgrades keine Einstellungen überschrieben werden können.
Ausgehend von meiner bisherigen MariaDB Erfahrung habe ich für meine Anforderungen in meine mariadb.conf Datei folgendes ergänzt:

# /etc/mysql/conf.d/mariadb.cnf
# MariaDB-specific config file.
# Read by /etc/mysql/my.cnf
#############################################
# Bessere CPU-Auslastung mittels pool-of-threads statt one-thread-per-connection
#  -> https://mariadb.com/kb/en/threadpool-in-55/
thread_handling         = pool-of-threads
 
# Die soll MariaDB selbstständig auf die Anzahl der Cores einstellen
#thread_pool_size       = 12
 
# Nach wie viel ms soll ein neuer Thread in einem Pool generiert werden, wenn der aktive Thread nicht fertig wird
thread_pool_stall_limit = 20
 
# Wie viele Thread sind pro Pool sind möglich
thread_pool_max_threads = 300
 
# Das ist noch unklar, doch trotz pool-of-threads limitiert weiterhin max_connections, daher hier pool_size * max_threads
max_connections         = 3600
interactive_timeout     = 1080
wait_timeout            = 360
 
# Mit diesen Port kann trotzdem zugegriffen werden, wenn alle Threads ausgeschöpft sind. Gut zur Administration
extra_port             = 3307
extra-max-connections  = 48
Tags: Linux, MariaDB, mySQL Server
Durchschnittliche Bewertung: 4 (1 Abstimmung)

Es ist möglich, diese FAQ zu kommentieren.