11. March 2007, 20:23, by Silvan Mühlemann

Diät für die Foto-Tabelle

Orange on Diet“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.

Server-Load-Graph

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!

Field Type Key
fid int(5) unsigned PRI
ts timestamp MUL
filename varchar(100) MUL
description text Fulltext
zugriffe int(10) unsigned MUL
rating int(11)  
rating_count int(11)  
directory varchar(100)  
quickaccess int(11) MUL
status int(11)  
gid int(11) MUL
location_bild tinyint(4)  
mid int(11) MUL
mid2 int(11) MUL
hires_filename varchar(50)  
f_bilder_pos tinyint(3) unsigned MUL
rid int(11) MUL
size_x int(10) unsigned  
size_y int(10) unsigned  
file_location_id tinyint(4)  
has_nametag enum(‘no’,’yes’)  
comment_timestamp datetime MUL

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.

5 Comments

  1. Wie ist der Stand?
    Um welchen Faktor ist die Performance gestiegen?

    Grüße,
    Schakko

    Comment by Schakko — 12. March 2007 @ 12:30

  2. “Gleichzeitiges” Einfügen von Datensätzen (sofern möglich) dürfte das ganze auch schneller machen. Also Anstelle von 10 Inserts nur eine Insert mit mehreren Values.

    Comment by http://leo.buettiker.org/ — 12. March 2007 @ 13:48

  3. Danke, Leo. Genau dies haben wir gemacht:

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

    Comment by Silvan Mühlemann — 12. March 2007 @ 13:59

  4. Ein Tipp der Eure Performance für die Applikation transparent steigert: Benutzt InnoDB als Tabellen-Format statt MyISAM. InnoDB verfügt über Row-Level-Locking im Gegensatz zu MyISAM, welches Table-Level-Locking verwendet (siehe http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html ). Bei jedem INSERT wird derzeit die ganze Tabelle gesperrt. Es ist also pro Tabelle nur ein INSERT gleichzeitig möglich. Bei InnoDB wird pro INSERT (oder auch UPDATE) nur diese eine Zeile gesperrt. Somit sind auch mehrere INSERTs oder UPDATEs gleichzeitig möglich.

    Comment by Simon Rupf — 24. March 2007 @ 21:55

  5. Hallo Tillate IT-Team ;)

    Ich hab eine kurze Frage:

    Was für MYSQL Bücher lest Ihr um euch weiterzubilden ich bin derzeit auf der Suche nach einer “Mysql bibel” welche alle Themen behandelt könnt Ihr mir einen Tipp geben ?

    Grüße,
    Mathias

    Comment by Mathias — 27. March 2007 @ 11:59

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

© 2017 tilllate Schweiz AG - Powered by WordPress