Eine kurze Einführung in die Softwareseitige Performance-Optimierung und das Debugging von MySQL-Datenbanken

Philip Bönisch

Im nachfolgenden Text bekommen Sie eine kurze Einführung darin, mit welchen Möglichkeiten sich die Lese- und Schreibgeschwindigkeit auf MySQL-Datenbanken optimieren lassen. Zudem werden Möglichkeiten des Debuggings von MySQL-Datenbanken erläutert.

Hinweis: Generell ist es sinnvoll, so viel wie möglich in den RAM des Systems auszulagern, da dieser wesentlich schneller ist als die Festplatte. Daher ist es sinnvoll, möglichst viel RAM dem System zu geben, damit so viel wie irgendwie möglich dort von MySQL gecacht werden kann. Dies hat wesentliche Auswirkungen auf die Performance mit den unten aufgeführten Einstellungsmöglichkeiten.

Darüber hinaus ist es sinnvoll, zwischen den Optimierungsmöglichkeiten für den lesenden und den schreibenden Zugriff zu differenzieren. Findet keine Replikation statt, so übernimmt die gleiche MySQL-Datenbank sowohl die lesenden wie auch die schreibenden Zugriffe. In diesem Fall muss für beides optimiert und eine entsprechende Balance gefunden werden. Existiert eine Master-Slave Replikation, so kann der Master für den schreibenden Zugriff und der Slave für die Leseoperationen optimiert werden.

In MySQL ist es möglich, Einstellungen für die Datenbank zur Laufzeit zu ändern. Diese Änderungen gehen bei einem Neustart der MySQL-Instanz verloren und müssen beim nächsten Mal wieder neu gesetzt werden.
Um Änderungen permanent zu machen, gibt es die Möglichkeit eine my.cnf (für Unix-Systeme und Windows-Systeme) bzw. eine my.ini (nur Windows-Systeme) anzulegen.
Die my.cnf kann auf Linux Debian basierten Systemen unter /etc/my.cnf angelegt werden. Die my.ini kann beispielsweise unter C:\my.ini angelegt werden. Andere Ablageorte sind ebenfalls möglich. Eine genaue Übersicht kann hier nachgelesen werden.

Nachfolgend einige Beispiele für MySQL-Einstellungen für Entwicklungs- und Produktiv-Systeme.

Einstellungen für Entwicklungs-Systeme

Logging

Logging in MySQL dient unter anderem dazu Entwicklern Aufschlüsse darüber zu geben, welche Operationen durchgeführt werden (z.B.start, stop, insert, update, delete), welche Auswirkungen diese auf die Performance des Systems haben und welche Datenbankabfragen optimiert werden können/müssen.

Um das Logging zu aktivieren, müssen in der my.cnf die folgenden Einträge gemacht werden:

[mysqld]
log-bin --> bin_log wird aktiviert
log --> general_log wird aktiviert
log-error --> error_log wird aktiviert
log-slow-queries --> slow_queries_log wird aktiviert

Nachfolgend werden die oben genannten Loggings erläutert.

   - Der bin_log speichert unter anderem Informationen über Änderungen an der Struktur der Datenbank und Tabellen, sowie die Dauer einer Operation

log_bin=/var/log/mysql/mysql_bin.log → hier wird der bin_log gespeichert

   - Der slow_query_log loggt alle MySQL-Abfragen, die zur Ausführung länger als die in „long_query_time“ benötigte Zeit in Sekunden benötigen. Für die Größe der „long_query_time“ gibt es keine allgemein gültige Regel. Dies hängt davon ab, ob Abfragen in der Datenbank zu lange dauern oder nicht. Aber generell sollte z. B. die Auslieferung einer Webseite nicht länger als eine Sekunde dauern.

log_slow_queries = /var/log/mysql/mysql-slow.log → hier wird der log gespeichert
long_query_time = 2 → legt fest, ab welcher Ausführungsdauer geloggt wird

   - Der error_log speichert Informationen über Fehler, die zur Laufzeit des MySQL-Servers auftreten.

log_error=/var/log/mysql/mysql_error.log → hier wird der
error_log gespeichert

  - Der general_query_log ist z.B. hilfreich, um Anfragen an den Server zu untersuchen oder um festzustellen, welche Nutzer sich mit der Datenbank verbinden.

general_log_file = /var/log/mysql/mysql.log → hier wird der allgemeine log gespeichert

 

Einstellungen für alle Systeme

Caching zur Optimierung der Lesegeschwindigkeit

Um die Lesegeschwindigkeit von MySQL zu erhöhen, können die oben genannten Einstellungen in der my.conf (my.ini) vorgenommen werden. Hierbei ist zu beachten, dass der Query-Cache ab MySQL 5.6 standardmäßig ausgeschaltet ist.

   - Query Cache: Sollte eigentlich "result cache" heißen, weil es die Abfrage Ergebnisse (+ hash + affected tables) speichert. Es steigert die Lese-Geschwindigkeit (select-Operationen). Hierbei werden die Ergebnisse von select-queries gecacht. Funktioniert gut, wenn viele gleiche select-queries angefragt werden. Daher sollte man viele identische Queries schreiben, queries wieder verwenden oder Stored Procedures einsetzen, damit der Cache zum Einsatz kommt. Ist die Abfrage eigentlich identisch, aber z. B. die Reihenfolge in der Abfrage ist unterschiedlich, so ergibt sich ein anderer Hash Wert und der Cache wird nicht verwendet.

Query cache ist nicht wirksam für update-, insert- und delete-Operationen.

query_cache_type = 1 → aktiviert den query cache
query_cache_limit = 1M → maximale Größe eines einzelnen Ergebnisses
query_cache_size = 32M → Gesamtwert aller gecachten Ergebnisse

Ist die Anzahl der Thread größer als 10 wird der query_cache zum Bottleneck. In diesem Fall ist es besser den query_cache zu deaktivieren.

show global status like 'thread%'; → zeigt wie viele Threads gerade laufen.

Empfehlung: Via Monitoring die Anzahl der Threads und das Caching überwachen.

   - Table Definition Cache: Dieser Cache dient dazu, die Abfrage von Tabellen schneller zu machen, indem die Definitionen der Tabellen im Cache gespeichert werden. So muss nicht jedes Mal erneut die Tabellendefinition geladen werden.

table_definition_cache = 400 → sollte größer sein als die Anzahl aller Tabellen in allen Datenbank auf dem Server
 
select count(*) from information_schema.tables; → zeigt die Anzahl der Tabellen an

   - Table Open Cache: Hier wird die Anzahl der Tabellen gespeichert, die MySQL gleichzeitig offen halten kann. Idealerweise ist diese Anzahl so groß wie möglich.

table_open_cache = 400 → sagt aus wieviele Tabellen der MySQL-Server gleichzeitig offen hält.
                         Berechnet sich wie folgt: Anzahl gleichzeitiger Verbindungen * 1 – 20 Tabellen

  - InnoDB buffer pool size: Dieser Cache reduziert die schreibenden und lesenden Zugriffe auf der Festplatte während auf Datenbanktabellen zugegriffen wird. Wenn möglich sollte diesem so viel RAM wie möglich zugewiesen werden, idealerweise etwa 80 % des verfügbaren RAMs bei einem dedizierten MySQL Server.

innodb_buffer_pool_size = 50% – 80% des verfügbaren RAM des Systems

Optimierung der Schreibgeschwindigkeit

   - InnoDB log file size: Dieser Cache beschreibt die Größe der Dateien iblog0 und iblog1. Diese sollten groß genug sein, um 60 Sekunden oder sogar eine Stunde die SQL Statements zu speichern.

Innodb_log_file_size = 1MB – 4GB → Je größer der Wert, desto besser. Allerdings ist eine Wiederherstellung bei großer Größe langsamer

 

Im folgenden eine grobe Faustregel zur Berechnung der InnoDB log file Größe.
innodb_os_log_written / uptime = ?kb per second
kb per second * 60 seconds * 60 minutes = X
consider a possible peak, so also add a high rate, default 5
=> kb per second * 60 seconds * 60 minutes * peak (5) = X
 
Um diese Einstellung auf einem Server zu ändern, sollte folgendermaßen vorgegangen werden:
   1.    service mysql stop
   2.    verschiebe oder Lösche die Dateien iblog0 and iblog1
   3.    ändere die my.cnf Einstellungen
   4.    service mysql start, die log Dateien sollten wieder erstellt werden

 

   - innodb_flush_log_at_trx_commit: Einstellung, wann der log Puffer in die log Datei auf der Festplatte geschrieben wird.

Mögliche Werte:

   0 bedeutet einmal pro Sekunde (gut bei viel Traffic) - der log Puffer wird einmal pro Sekunde ins log file geschrieben und der Puffer wird wieder geleert, außer es handelt sich um eine Transaktion

   1 (default) der log Puffer wird bei jedem Commit einer Transaktion in die log Datei auf der Festplatte geschrieben und der Puffer wird wieder geleert.

   2 bedeutet, dass die Daten gleichzeitig in eine Cache Datei auf der Festplatte geschrieben werden - der log Puffer wird bei jedem commit in die Datei auf der Festplatte geschrieben. Das Leeren des Puffers erfolgt jede Sekunde.

Fazit

Die Standard-Konfiguration von MySQL ist nicht die beste, daher sollten Änderungen an den Einstellungen vorgenommen werden. Zu beachten ist hierbei, dass sich die Einstellungen je nach Entwicklungsumgebung (dev, test, prod) unterscheiden können. Für Produktivsysteme empfiehlt es sich, die Einstellungen auf Lese- / Schreibgeschwindigkeit zu optimieren. Bei Entwicklungssystemen ist dies, je nach Projekt und Größe der Datenbank, nicht ganz so wichtig und Einstellungen zum Debugging eventuell wichtiger. Eine weitere wichtige Rolle spielt auch die Rechenleistung der jeweiligen Systeme oder ob es sich um eine Master/Slave-Replikation handelt. Für Produktivumgebungen empfiehlt sich auch, ein Monitoring der wichtigsten Werte, wie die Anzahl der gleichzeitigen Threads oder die Auslastung der Caches. Auf diese Weise wird relativ schnell ersichtlich, ob es irgendwo einen Engpass gibt.

Unser Experte

Philip Bönisch

Haben Sie Fragen oder Input? Kontaktieren Sie unsere Expert:innen!

E-Mail senden!

Philip Bönisch, ein Eigengewächs, arbeitet seit 2009 für Cocomore als Senior Frontend Developer. Durch seine Vielseitigkeit wird er auch im Backend als Drupal-Developer eingesetzt und entwickelt dort umfangreiche Web-Anwendungen.
Philip in 3 (+1) Worten: Es gibt nur Lösungen.

Related Content