Diät für die Foto-Tabelle

“Silvan, das Hinzufügen von Fotos ist schweinelangsam am Weekend! Die Fotografen sind am jammern. Ich bekomme andauernd Anrufe. Mach was!”. So beklagten sich die Regionalmanager in den letzten Wochen.

Unsere Überwachungstool bestätigen die Situation: Wie aus dem Maschinengewehr wird mein Handy von Nagios mit SMS beschossen. Die graue Kurve, welche im Ganglia die Server-Load beschreibt, ist weit über der roten Linie. Moreti, unser Tool, um die Antwortzeit zu messen meldet mehrere Sekunden, um eine tilllate-Seite zu liefern.

Eine bekannte Situation, die wie in den letzten sechs Jahren unserer Existenz immer wieder antreffen. Es gibt zwei Arten, mit dieser Situation umzugehen: Mehr Hardware oder Software-Optimierung.

Hardware hinzufügen ist eine schnelle und einfache Lösung.

INSERTs müssen beschleunigt werden

In diesem Fall ist die Situation anders: Es sind die Fotografen, welche Fotos zur Website hinzufügenwollen, welche betroffen sind. Und weniger die Besucher, welche nur Fotos abfragen. “Fotos hinzufügen” funktoniert langsam. Es handelt sich also um INSERT-Statements und nicht SELECT-Queries. Es werden neue Fotos hinzugefügt. Also neue Rows der Datenbank
angehängt:

INSERT INTO bilder
 (fid,filename,hires_filename,directory,
   quickaccess,gid,size_x,size_y,status,mid,mid2)
 VALUES (22377523,"DSCx00000.JPG","DSCx00000.JPG",
    "/2007/03/09/20070309_1_Jahr_Pure90/1",
   1,929623,400,285,0,null,null);

Da wir mit Replikation arbeiten müssen diese Rows auf alle Slaves kopiert. Pro Slave ein INSERT. Fügen wir dem Cluster weitere Server hinzu, so wird dies das Problem nicht lösen, da diese Rows auch auf diesen neuen Servern eingefügt werden müssen. Mehr Server reduziert die Anzahl INSERTs auf dem Cluster nicht. In diesem Fall hilft Harware nicht.

Wir suchen weiter: INSERTS machen Probleme. Die INSERTS sind zu langsam. Was macht INSERTs langsam? Was ist bei einem INSERT teuer? Nicht das Hinzufügen der Daten, sondern das schreiben der Indizes. Indizes sind komplexe Datenstrukturen, B-Trees, Binäre Bäume welche bei jedem Einfügen einer Zeile auf deren Gewichtung überprüft werden müssen. Indizes sind teuer.

Im Fall unserer Tabelle fotos machen die Indizes sogar mehr Bytes aus als die Daten (und dies, obwohl unsere Datenstruktur Amateur-Niveau hat und voller Redundanz ist)

rachel tilllate # du -hs bilder.*
794M    bilder.MYD
1.3G    bilder.MYI

Unnötige Spalten und Indizes!

Wir sind also auf die Indizes der Tabelle bilder losgegangen. Mein Gott, was haben wir denn da für Indizes gesetzt in unserem jugendlichen Leichtsinn? Praktisch jede Spalte hat einen Index, obwohl die bei einer Query nie verwendet wird (Test mit EXPLAIN SELECT). Da hat’s noch Spalten drin, welche nie abgefragt werden. Übel!

60’000 Zeilen Code durchkämmen

Also: Stefan und ich machen uns an die Bildertabellen-Diät: Mit grep über den ganzen tilllate-Code überprüfe ich alle Aufrufe auf die Bilder-Tabelle. Welche Indizes können wir löschen? Welche Spalten braucht’s nicht mehr?

Das Ergebnis: Wir kommen auf drei Spalten und fünf Indizes, welche nicht verwendet werden. Ein besonders fetter Volltext-Index können wir durch vertikale Partitionierung in eine separate Tabelle verbannen. Ist das ein Grund zur Freude, da wir so viel Verbesserungspotential gefunden haben? Oder sollen wir uns über unser Puff im System ärgern?

Egal: Am Samstag-Morgen um 01AM führt Stefan die ALTER TABLE-Statements durch. Nach zwei Stunden Query-Dauer ist die Blitzdiät vollendet. Das Ergebnis:

rachel tilllate # du -hs f_bilder.*
619M    f_bilder.MYD
416M    f_bilder.MYI

Reduktion der Datendatei um 22%. Reduktion der Indexdatei um 70%. Das führt zu schnelleren INSERTs, schnelleren Backups und schnellere REPAIR TABLE (welche bei MyISAM-Tabellen oft ein Problem ist).

Ob es für die Fotografen nun einfacher geht, die Fotos hinzuzufügen, wird sich heute Abend zeigen. Ich bin gespannt.