Dies ist Teil 2.1 der Artikelserie zum Thema Webseiten-Skalierung. Die anderen Teile:
Teil 1: Allgemeine Überlegungen
Teil 2: Datenbankoptimierung
Teil 2.1: Praxistipps Datenbank
Teil 3: Caching
Teil 4: Zukünftige Optimierungen (folgt)
Eine praxisnahe Zusammenstellung der Massnahmen, die sich bei My-Hammer.de bewährt haben:
InnoDB vs MyISAM
Ich schrieb bereits, dass man InnoDB nicht nur dann in Erwägung ziehen sollte, wenn man Transaktionssicherheit benötigt. Eine Tabelle von MyISAM auf InnoDB umzustellen kann unter Umständen Geschwindigkeitsvorteile bringen, nämlich dann, wenn das zweite wichtige Feature von InnoDB neben der Transaktionssicherheit, das Row Level Locking, effektiv zum Zug kommen kann. Um herauszufinden, ob dies der Fall ist, kann man wie folgt vorgehen:
Mitloggen aller Queries
Wenn man für einen bestimmten Zeitraum (bei einer gut besuchten Seite reichen wenige Minuten) einmal alle Abfragen, die an die Datenbank gestellt werden, mitschreibt, kann man aus diesem Log eine Menge interessanter Informationen ziehen. Um festzustellen, ob eine Tabelle vom Row Level Locking profitieren könnte, muss man die lesenden (SELECT) und schreibenden (INSERT, UPDATE, DELETE etc.) Abfragen gegenüberstellen.
Wird aus einer Tabelle sehr häufig gelesen, die Daten in der Tabelle aber nur sehr selten verändert, dann macht das Table Level Locking von MyISAM in der Regel keine Probleme: Zwar wird bei einem UPDATE, INSERT oder DELETE die gesamte Tabelle für nachfolgende Lesezugriffe gesperrt (d.h. diese müssen warten), bis der Schreibprozess abgeschlossen ist. Aber da dies nur selten geschieht, kommt es auch selten vor, dass ein Leseprozess warten muss, so dass daraus keine spürbare Verzögerung im Gesamtsystem resultiert.
Gleiches gilt im umgekehrten Fall: Wird in eine Tabelle praktisch nur geschrieben, aber selten daraus gelesen (wie es z.B. bei Logtabellen häufig der Fall ist), dann kollidieren auch hier die “Interessen” nur so selten, dass nicht mit Performanceeinbußen zu rechnen ist.
Slow Log
Interessant sind also jene Tabellen, bei denen Schreib- und Lesezugriffe in einem ausgeglicheneren Verhältnis stehen. In welcher Relation die beiden Zugriffsarten dabei mindestens stehen müssen, damit es sich “lohnt” InnoDB einzusetzen, ist schwer zu sagen. Ein Blick ins Slow-Log von MySQL hilft hier weiter: Wenn man immer wieder bei denselben Tabellen auf langsame Queries stösst, die nicht wegen des Queries selbst langsam waren, sondern weil sie auf ein Lock warten mussten, hat man auf jeden Fall aussichtsreiche Kandidaten.
SHOW PROCESSLIST
Eine weitere Methode ist, sich einmal für einige Minuten immer wieder die Liste der laufenden Prozesse in MySQL auflisten zu lassen (SHOW PROCESSLIST). Wenn man dort immer wieder dieselben Queries sieht, deren Status Locked ist, dann weiss man wo das Problem liegt. Diese Methode mag zwar auf den ersten Blick wie ein Glücksspiel wirken, aber gerade weil man immer nur die Prozesse sieht, die zufällig gerade laufen wenn man den Befehl absetzt, fallen die problematischen Prozesse erst recht auf, die immer wiederkehren und oft vielleicht sogar während zwei oder mehr SHOW Aufrufen immer noch laufen. Meiner Meinung nach die schnellste Methode, Flaschenhälse zu finden.
Mehr zum Thema Locking gibt es im Kapitel ‘Internal Locking Methods’ des MySQL Handbuchs.
Nehmen wir also an, man hat einige Tabellen identifiziert, bei denen Queries öfter als gesund ist auf einen Lock warten müssen. Dies könnte beispielsweise eine Sessiontabelle sein (falls man z.B. PHP nutzt und die Sessionfunktionen so angepasst hat, dass diese eine MySQL Datenbank als Storage nutzen, ein ziemlich klassisches Szenario). Diese Tabelle wird bei jedem Seitenaufruf zu Beginn einmal gelesen, um die Session des aufrufenden Benutzers zu laden, und am Ende des Skripts wird der Sessioninhalt dieses Benutzers wieder geschrieben. Also ein sehr ausgewogenes Verhältnis zwischen lesenden und schreibenden Zugriffen - jeder Seitenaufruf, der gerade an dem Punkt angelangt ist, an dem die Session geschrieben wird, würde also die Tabelle sperren für sämtliche anderen Seitenaufrufe, die in diesem Moment aus der Sessiontabelle lesen möchten - das Performanceproblem ist ab einer bestimmten Anzahl von gleichzeitigen Benutzern vorprogrammiert.
Klassischerweise geht man nun so vor, dass man die Tabelle in InnoDB umwandelt und wieder einige Zeit das Slow Log oder die Prozessliste beobachtet - sinkt die Lock_Time der Abfragen deutlich, hat man einen Flaschenhals erfolgreich eliminiert.
Nun, es wäre freilich zu schön, wenn es nicht doch den ein oder anderen Haken bei der Sache gibt; zum Glück lassen sich die meisten aber zumindest einigermassen elegant umschiffen.
Eine Einschränkung von InnoDB ist beispielsweise, dass der FULLTEXT Index nicht unterstützt wird. Dies war bei My-Hammer ein Problem, weil wir eine Tabelle, die ziemlich eindeutiger Kandidat für eine Umstellung von MyISAM auf InnoDB war, in einem Teil unserer Applikation auch durchsuchen mussten, und zwar eben gerade einige TEXT-Felder, was ohne FULLTEXT Index nicht wirklich Spass macht.
Die Lösung war, die Tabelle umzuwandeln und damit in der Tabelle selbst auf die FULLTEXT Indizes zu verzichten, per cronjob aber eine weitere Tabelle regelmässig mit den Daten der Ursprungstabelle zu füllen. Geschrieben wurde in diese Tabelle nur durch besagten Crobjobs, ansonsten fanden ausschliesslich Lesezugriffe statt, womit MyISAM wieder die perfekte Wahl war - und wir hatten unsere FULLTEXTs wieder. Schöner Nebeneffekt: durchsucht werden müssen eh nur eine Untermenge aller Zeilen der Ursprungstabelle, und es müssen auch nicht alle der (recht zahlreichen) Spalten in die Suchtabelle übertragen werden.
Dadurch konnten wir nicht nur das Lockingproblem der ursprünglichen Tabelle lösen, sondern aufgrund der schlankeren Datenbasis in der Suchtabelle die Suche deutlich beschleunigen.
Wichtig ist jedoch: diese Lösung ist nur möglich, weil wir in diesem Fall darauf verzichten können, auf absoluten Livedaten zu suchen.
Filed by Manuel Kiessling at 11:14 am under software, open source, php, mysql, OpenMassMailer
5 Comments. Write a comment
Say you have an installation of apt-proxy running on a Debian Sarge machine to serve files for apt on your other machines, then you will receive errors like this when issuing an apt-get update on an Etch machine: Error reading from server. Remote end closed connection.The reason is that Etch tries to download a file called Packages.diff which Sarge’s apt-proxy doesn’t know how to serve.