Jede Datenbank, die untertags durch viele INSERT, DELETE oder UPDATE-Statements drangsaliert wird, sollte in regelmäßigen Abstanden optimiert werden. Dies kann beispielsweise mittels PHPmyAdmin per unter dem Punkt „Operatoren“ (vorher eine Tabelle auswählen), „Optimiere Tabelle“ geschehen, oder per mysql-Konsole selbst.
Warum überhaupt eine Datenbanktabelle optimieren?
Doch zuerst müssen wir uns kurz mit der Frage befassen, warum eine MySQL-Datenbanktabelle überhaupt optimiert werden muss. Nun, dies ist keine Eigenschaft, die nur MySQL betrifft. Auch Datenbanken unter Microsoft SQL Server benötigen von Zeit zu Zeit den Optimierungsbefehl. Wobei „benötigen“ eigentlich nicht richtig ist. Man muss den Befehl nicht ausführen, wenn man in Kauf nehmen kann, dass die Serverlast von Tag zu Tag steigt und die Datenbank immer langsamer wird. Der Grund liegt in der Defragmentation der Daten. Nehmen wir mal folgendes Beispiel an. Wir haben ein Feld X vom Wert tinyint. Dies ist anfänglich 0; wir updaten es auf 1. Wird das Dateisystem defragmentiert? Nein, denn ob 0 oder 1 – die Speicherplatzgröße für beide Werte ist gleich. Nehmen wir jetzt aber ein Feld „Name“ vom Typ varchar(x). Das Feld hat den Wert „Müller“. Nun machen wir ein Update auf „Müller-Lüdenscheid“. Hätten wir das Feld per Char(max) festgelegt, hätte das Datenbanksystem die maximale Länge des Feldes blockiert. Der nun längere Name, der aber unter der Maximum-Länge des Feldes liegt, hätte immer noch in den vorbereiteten Platz gepasst. Der Nachteil ist gravierender: Die Datenbank wird mit jedem Datensatz so groß wie die Definition der maximalen Datensatzlänge angibt. Auch wenn nahezu leere Felder eingefügt werden, steigt der Datenplatz unaufhaltsam an. Ein Grund, warum varchar-Felder ein Segen sind, auch wenn sie andere Probleme mit sich bringen. Weiteres über Optimize Table kann direkt in der MySQL-Doku nachgelesen werden.
Ausführung von Optimize Table
Die Ausführung des Befehls ist einfach. In einer MySQL-Shell muss lediglich
OPTIMIZE TABLE Tabellenname;
Ausgeführt werden. Zu beachten ist jedoch, dass MySQL die Tabelle während der Laufzeit von OPTIMIZE TABLE sperrt!
Während man bei einem Microsoft SQL Server – zumindest ab der Standard-Version – Wartungspläne definieren kann, die automatisch solche „Wartungen“ wie ein Optimize Table vornehmen, muss man bei MySQL auf die Möglichkeiten des Host-Systemes zurückgreifen.
Tabelle optimieren oder reparieren mittels mysqlcheck
Die meisten MySQL-Installationen bringen bereits in einer Standardinstallation das Hilfsprogramm „mysqlcheck“ mit. Der Vorteil dieses Programmes ist, dass wir damit auch auf einen laufenden SQL-Server zugreifen und wir eben das Programm per Cronjob aufrufen können. Wir ersparen uns dadurch das Schreiben von Skripts.
Einrichten eines Cronjobs für das Optimieren einer MySQL-Tabelle
- Wir loggen uns per SSH (putty) auf unserem Server ein
- Je nach Linux-Distribution warden wir mit “su” zum Root.
- Editieren der Crontab ( /etc/crontab) mit dem Befehl crontab -e
- Wir fügen folgende neue Zeile per Editor in unserer Crontab ein:
0 1 * * * mysqlcheck -Aao –auto-repair -u root -p[password] > /dev/null - Editor mit ESC, “:wq!” verlassen.
Beschreibung des Befehls:
„0 1 * * *“: Hiermit weisen wir den Cron an, jeden Tag um 1 Uhr und 0 Minuten den Befehl auszuführen. Nähere Informationen zur Steuerung eines Crons erhalten Sie im Artikel „Linux-Cronjob einrichten“
mysqlcheck -Aao –auto-repair: Wir rufen damit das Programm “mysqlcheck” auf. Die Parameter können direkt in der MySQL-Doku nachgelesen werden. Die Parameter hier bewirken:
-A: Alle Datenbanken (all-databases) werden überprüft/optimiert
-a: Analysiert die Tabellen (Analyse)
-o: Optimierung (optimize) der Tabellen
-auto-repair: Wenn eine Tabelle beschädigt ist, wird sie automatisch repariert.
Mit den Parametern „-Aao –auto-repair“ erledigen wir also mehr, als ein reines „Optimize“.
Parameter „-u root -p[password]”: Hier geben wir den Datenbankbenutzer (root) und sein Passwort an
Parameter „> /dev/null”: Wir schreiben die Ausgabe des Befehls ins digitale Nirwana. Hier könnte auch eine Ausgabe in ein Logfile oder eine Mail erfolgen.
UPDATE: mysqlcheck doesn’t support multiple contradicting commands
Diese Fehlermeldung tritt bei neueren Versionen von mysqlcheck auf. Hier ist es nicht mehr möglich, mehrere Kommandos aneinanderzuhängen. Um den Fehler zu umgehen, genügt der nachfolgende Aufruf:
mysqlcheck -u root -p [PASSWORT] --auto-repair --optimize --all-databases