Große Tabellen

Heutzutage werden die zu verwaltenden Datenmenge unbestritten immer größer. Viele datenverarbeitende Systeme sammeln und sammeln und sammeln… Außerdem müssen mittlerweile sehr oft auch Veränderungen von Daten aufgezeichnet werden. Dadurch wird die Datenmenge noch einmal deutlich größer. D.h. soll beispielsweise die alte Adresse eines Kunden nach einer Adressänderung weiterhin zur Verfügung stehen, muss sie zwangsläufig separat gespeichert werden. Die nachfolgenden Beispiele beziehen sich ausschließlich auf MySQL.

Große Tabellen – Keep it small!

Viele Entwickler vertreten die Einstellung „Speicher ist billig“. Daher scheint es auch nicht weiter schlimm zu sein, Spalten etwas großzügiger auszustatten, als es diese eigentlich benötigen. Immerhin ist Zeit Geld und je länger man sich mit der Suche der richtigen Datentypen beschäftigt, desto mehr Geld verliert man auch. Dazu kommt: Bei großzügiger gewählten Datentypen ist auch die Wahrscheinlichkeit unzureichener Wertgrenzen geringer. Ein INTEGER wird wohl länger halten als ein TINY INT. Diese Sichtweise vertrete ich allerdings nicht. Wer sich nämlich gründliche Gedanken zur entstehenden Struktur macht, wird i.d.R. auch nicht auf das Problem von zu kleinen Datentypen stoßen. Außerdem kann beispielsweise durch die Nutzung von UNSIGNED der Wertebereich ca. verdoppelt werden und dadurch kann ggfs. dann sogar deutlich Speicher eingespart werden.

Je größer die Typen, desto größer die Tabelle und desto länger dauern Tabellenänderungen. Das mag banal klingen, kann aber an folgendem Beispiel verdeutlicht werden:

Tabelle X hat den Primary Key id als INT und created als DATETIME. Bei einem Eintrag werden deshalb 4 Byte für den INTEGER + 8 Byte für das DATETIME-Feld = 12 Byte belegt. Bei 1000 Einträgen 12000 Byte, bei einer Million 12000000 Byte und bei 16 Millionen 12 * 16 * 10^6 Byte = 192 000 000 Byte = 192 MByte (nach SI-Präfixen zur Basis 10) belegt. Nun das gleiche mit einem UNSIGNED MEDIUM INT (3 Byte) und einem TIMESTAMP (4 Byte): 3 + 4 Byte = 7 Byte pro Zeile. D.h. bei 16 Millionen Einträgen 7 * 16 * 10^6 = 112 000 000 Byte = 112 MByte. Daraus ergibt sich eine Ersparnis von 80 MByte bei gerade einmal 2 Feldern. Oftmals sind sehr viel mehr Felder pro Tabelle im Einsatz. Und eine Datenbank hat dann ja auch nicht nur eine Tabelle, sondern viele. So können wir also munter weitermultiplizieren. Zugegebenermaßen kann man nicht immer ein DATETIME-Feld durch ein TIMESTAMP-Feld (zeitzonenunabhägig) ersetzen. Auch hängt die Größe des INT-Felds stark davon ab, wie viele Einträge man erwarten kann.

Dennoch; Die Praxis zeigt sehr oft mehr Felder in Tabellen und damit hohe Einsparpotentiale. Letztere sind gerade bei (VAR-)CHAR-Feldern anzutreffen. Hinzu kommt die Indexgröße. Denn mit steigender Zeilenanzahl wächst auch die Größe der Indizes, die durchaus auf die gleiche Größe wie die Tabelle selbst oder sogar noch höher steigen kann. Dadurch wird ein ALTER TABLE noch einmal spürbar langsamer. Bei Verwendung von MyISAM-Tabellen wird laut der MySQl-Dokumentation sogar ein Großteil der Änderungszeit auf die Neugenerierung der Indizes verwendet. Hier empfiehlt es sich gerade bei zeichenbasierten Feldern auch die Länge der zu indizierenden Zeichen zu überprüfen. In MySQL können beispielsweise auch nur die ersten 3 Byte eines 16 Byte VARCHAR-Feldes indiziert werden. Die Größe einer Tabelle und deren Indizes erhält man übrigens über den Befehl:

SHOW TABLE STATUS LIKE 'tabellenname';

Das Problem mit ALTER TABLE

Leider lassen sich sehr große Tabellen trotz voriger Erläuterungen nur optimieren, aber nicht verhinden. Solange die Tabellen entsprechend klein sind, stellen Strukturänderungen i.d.R. keine Probleme dar. Sobald sie allerdings größer werden, trifft man unausweichlich auf Lesesperren der Tabellen, während ein ALTER TABLE ausgeführt wird. Während der Strukturänderung kann also nichts von der zu ändernden Tabelle gelesen werden. Dabei gilt: Je größer die Tabelle, desto länger dauert die Änderung. Im Produktivbetrieb kann das richtig unangenehm werden, wenn sich beispielsweise keine Benutzer mehr einloggen können. Wenn diese Benutezr während dieser Zeit eigentlich auch noch Geld auf der Platform ausgeben sollen, ist es umso ärgerlicher. Damit aber nicht genug. MySQL kann auch schon einmal die Tabelle sperren, wenn nur ein Index oder sogar nur ein Spaltenkommentar hinzugefügt werden soll. Dies hängt aber auch ein wenig von der jeweiligen Version und Storage-Engine ab, die man so einsetzt. Beispielsweise kann die NDB-Cluster-Engine, die es leider nur in der sehr kostspieligen Version von MySQL gibt, sehr wohl auch Änderungen ohne Read-Locks durchführen: http://dev.mysql.com/doc/refman/5.1/en/alter-table-online-operations.html

Da die meisten Leser dieses Blogs wohl aber die Normalversion einsetzen, schauen wir uns den ALTER-Vorgang einmal genauer an:

  1. Commit der ggfs. geöffneten Transaktion
  2. Setze ein Read-Lock auf die zu ändernde Tabelle
  3. Erzeuge eine temporäre Tabelle mit der neuen Struktur
  4. Kopiere die Inhalte der alten Tabelle in die neue Tabelle
  5. Die Originaltabelle wird umbenannt
  6. Die temporäre Tabelle erhält den Namen der Originaltabelle
  7. Lösche die mittlerweile umbenannte Originaltabelle
  8. Das Read-Lock wird aufgehoben

Leider lässt sich das Read-Lock nicht so einfach umgehen. Im Internet kursieren auch einige manuelle Lösungsansätze, um dem Problem Herr zu werden. Siehe beispielsweise hier: http://www.mysqlperformanceblog.com/2007/10/29/hacking-to-make-alter-table-online-for-certain-changes/

Die Problematik dabei ist aber, dass man auf Dateiebene, sozusagen außerhalb von MySQL „herumpfuscht“. Man ließt daher immer wieder auch von Entwicklern, die sich damit komplette Tabellen zerstört haben. Daher rate ich hiervon ab.

Dennoch sind ein paar findige Entwickler bei Facebook, die mit Sicherheit ganz andere Datenmengen als der Normal-PHP-Programmierer zu verwalten haben, auf die Idee gekommen, genau jenen Prozess nachzubilden. Im Prinzip verwenden diese ein PHP-Skript, dass die nötigen Änderungen in ähnlicher Weise wie ein ALTER-Statement durchführt.  Allerdings mit der Ausnahme des Read-Locks. Dazu kommen noch eine weitere Tabelle und Trigger, die Änderungen an einzelnen Datensätzen mitschreiben, die später wieder in die neu entstandene Tabelle einfließen. Der Name des PHP-Skripts lautet übrigens OSC –  Online Schema Change. Wer sich mit der genauen Vorgehensweise vertraut machen möchte, kann die Website des Tools und den dazugehörigen, sehr ausführlichen, aber auch sehr interessanten Artikel studieren: http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

Fazit

Leider ist die Dokumentation von MySQL in Sachen ALTER TABLE und Table Locks nicht immer sehr gesprächig. Sicherlich sind einige nützliche Hinweise enthalten, die jedoch nicht ausreichen. Ausprobieren hilft hier viel. Anbei daher noch einige Links zur Dokumentation, die ggfs. hilfreich sein können.

Das Facebook-Tool ist eine interessante Alternative, hat jedoch bei genauer Betrachtung auch einige Nachteile. Probleme gibt es hier beispielsweise noch bei Foreign-Keys oder Triggern.

Links:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *