MySQL-Performance-Tuning durch neue Datenbank-Engines
Moderne MySQL-Forks und -Patches
MySQL ist die Standard-Lösung für freie relationale Datenbank-Systeme im Web-Bereich. In den letzten Jahren ist durch neue Forks, weitere Storage-Engines und gepatchte Versionen das Feld deutlich unübersichtlicher geworden. Höchste Zeit, sich einen Überblick über die wichtigsten Angebote zu verschaffen.
Neue Funktionen
Neue Funktionen des MySQL-Kerns bieten dem Administrator zusätzliche Möglichkeiten, den MySQL-Server bequemer und sicherer zu warten. Eine typische Aufgabe ist das Beenden von MySQL-Prozessen per KILL. Unter Last kann aber es passieren, dass SHOW PROCESSLIST einen Prozess als Idle anzeigt, der im Moment des KILL schon wieder mit einer neuen Abfrage beschäftigt ist. Der »Kill if Idle«-Patch erweitert Kill um die Option, den Prozess nur zu beenden, wenn er untätig ist: KILL IF_IDLE Prozess-Id. Das verhindert es, einen Prozess unbeabsichtigt zu beenden, während er gerade eine Anfrage verarbeitet.
LVM- und ZFS-Snapshots gelten als einfachste Methoden für das weitgehend unterbrechungsfreie Sichern einer InnoDb-Datenbank im laufenden Betrieb. Wer auf diese Methode nicht zurückgreifen kann oder möchte und auf das klassische Dumpfile angewiesen ist, muss dafür Sorge tragen, dass sich während des Dumpens die Daten im MySQL-Server nicht ändern. Der klassische Weg dafür war bisher FLUSH TABLES WITH READ LOCK. Dieser reicht jedoch im Fall von InnoDb nicht zwangsläufig aus, da auch Hintergrund-Prozesse in die Datenbank schreiben. Der InnoDb-Freeze-Patch friert nach Ausführen von SET GLOBAL innodb_disallow_writes = 1 alle Prozesse ein, die schreibend auf InnoDb-Daten zugreifen und ermöglicht das anschließende Erstellen eines Backups. Abschließend deaktiviert SET GLOBAL innodb_disallow_writes = 0 das Einfrieren wieder.
Performance-Verbesserungen
InnoDb hat sich durch die Unterstützung von Transaktionen und Zeilen-basiertes Locking zu einer modernen Alternative der mittlerweile betagten MyIsam-Engine entwickelt. Trotz des Performance-Gewinns bei Schreibzugriffen durch Zeilen-basiertes Locking, kostet der Overhead für die Unterstützung von Transaktionen, Foreign Keys und weiteren Funktionen (auch bei Nicht-Nutzung) wertvolle Rechenzyklen und Hardware-I/O-Ressourcen. MySQL-Systeme, die unter hoher Last stehen, sind daher auf eine optimal konfigurierte und leistungsfähige InnoDb-Engine angewiesen.
Für die mit MySQL ausgelieferte Version von InnoDb gibt es bereits eine Vielzahl von Patches zur Performance-Verbesserung, von denen einige in der Ourdelta.org-Version enthalten sind. Nennenswert ist hier zum Beispiel ein überarbeitetes RW-Lock, das insbesondere das Locking-Verhalten auf Mehrprozessor-Systemen verbessert. Eine Beschreibung aller Verbesserungen würde den Rahmen dieses Artikels sprengen. Festzuhalten ist, dass es Patches für InnoDb gibt, die unter Beibehaltung vollständiger Kompatibilität transparente Optimierungen an der Engine vornehmen. Eine objektive Messung des Performance-Gewinns ist in der Regel schwierig, da die Leistung des MySQL-Servers stark von Hardware, Konfiguration und verwendeten Daten abhängt. Die ausführlichste und zuverlässigste Quelle ist das MySQL Performance Blog, das regelmäßig ausführliche Messergebnisse veröffentlicht.
Verbesserungen durch das selektive Einspielen von Patches an der herkömmlichen InnoDb-Engine stellen heute einen eher konservativen Ansatz dar. Mehr verspricht der Einsatz alternativer Datenbank-Engines. Zunehmende Verbreitung finden die InnoDb-kompatiblen Engines InnoDb-Plugin und Percona-XtraDb.
Das InnoDb-Plugin
Beim InnoDb-Plugin aus dem Hause InnoBase handelt es sich um eine weiterentwickelte Version der von MySQL mitgelieferten InnoDb-Engine. Zu den Verbesserungen zählen eine generelle Optimierung von CPU-Last und I/O-Zugriffen, ein schnellerer Locking-Mechanismus, erweiterte Konfigurations- und Reporting-Möglichkeiten, sowie optionales Komprimieren von Tabellen.
Seit MySQL 5.1 ist es möglich, mitgelieferte Engines zu entladen und durch andere Versionen zu ersetzen. Beginnend mit MySQL 5.1.38 liefert MySQL das InnoDb-Plugin zusätzlich aus. Da es von MySQL als »Release Candidate« betrachtet wird, muss der Admin es jedoch manuell aktivieren. Die offizielle MySQL-Dokumentation nennt die dafür notwendigen Schritte. Es ist zu empfehlen, die jeweils aktuellste InnoDb-Plugin-Version von der InnoDb-Webseite einzuspielen - so kann der Anwender von der Kombination aus Distributions-Updates für den MySQL-Kern und den neuesten Funktionen und Optimierungen des InnoDb-Plugins profitieren.
Ubuntu 10.04 liefert den MySQL-Server in der Version 5.1.41 aus. Im Verzeichnis /usr/lib/mysql/plugin befindet sich standardmäßig ein nicht aktiviertes InnoDb-Plugin in Version 1.0.4. Aktuell ist aber zurzeit Version 1.0.6, die der Anwender von der InnoDb-Webseite herunterladen kann. Die Datei ha_innodb.so kopiert er dann in das Verzeichnis /usr/lib/mysql/plugin. Da Ubuntu standardmäßig Server-Dienste per Apparmor schützt, muss man es entweder deaktivieren oder die Konfiguration anpassen, um das Laden von Inhalten aus dem Plugin-Verzeichnis zu erlauben. Den Regelsatz /etc/apparmor.d/usr.sbin.mysqld erweitert man hierfür um folgende Zeilen:
/usr/lib/mysql/plugin/ r, /usr/lib/mysql/plugin/* mr,
Ein Neustart von Apparmor per service apparmor restart aktiviert die neuen Regeln. In der Datei my.cnf aktiviert der Admin die InnoDb-Engine und lädt das InnoDb-Plugin, wie in Listing 2 zu sehen. In der InnoDb-Plugin-Dokumentation sind ausführliche Informationen zu diesem Vorgang zu finden. Das Error-Log von MySQL zeigt nach dem Neustart des Datenbankservers mit dem InnoDb-Plugin 1.0.6 die in Listing 3 gezeigte Meldung.
| Listing 2: Laden des InnoDB Plugin in der my.cnf |
|---|
01 [mysql] |
| Listing 3: MySQL-Server ohne und mit InnoDb Plugin |
|---|
01 MySQL-Server ohne InnoDb Plugin: |
Ausführliche Informationen zu den Optimierungen und neuen Funktionen des InnoDb-Plugins bietet die Dokumentation. Hervorstechend ist das neue Barracuda-Dateiformat. Es speichert im Gegensatz zum Standard-Format »Antelope« die InnoDb-Tabellen komprimiert. Dies kostet zwar zusätzliche CPU-Zyklen, spart dafür aber - je nach Datenstruktur - mitunter enorme I/O-Performance ein, da deutlich weniger Operationen auf der Festplatte/SSD erforderlich sind. Ob sich der Wechsel auf Barracuda lohnt, muss man durch Messungen ermitteln. Insbesondere Tabellen mit großen Text- und Blob-Feldern profitieren von der Komprimierung. Übrigens bietet MyIsam seit langem Tabellen im Compressed-Format. Allerdings lassen sich komprimierte MyIsam-Tabellen nicht im Betrieb verändern.
