Verzögertes Schreiben schneller als direktes Schreiben

Ja, ich weiß, dass der Titel 1a gelungen ist 😉
Dieser Beitrag soll erläutern, wie Leseoperationen in Web-Anwendungen möglichst hoch priorisiert werden können, um die Latenzzeit beim Seitenaufbau nicht unnötig zu erhöhen. Genauer geht es darum, wie man mit “unwichtigen” Update-Querys umgeht, damit diese die für den Seitenaufbau wichtigeren SELECT-Anfragen nicht stören.

Ausgangspunkt ist eine Anwendung mit MyISAM-Tabellen mit jeder Menge SELECT-Anweisungen aber auch einigen UPDATE-, INSERT- und DELETE-Querys. Die 3 letztgenannten möchte ich als Update-Operationen im Folgenden bezeichnen.
MyISAM (als MySQL-Standard-Tabellenformat) hat die Eigenschaft, dass während einer Update-Operation auf eine bestimmte Tabelle diese gelockt wird. Das bedeutet: So lange die Operation läuft, keine andere Operation an der Tabelle möglich.

Das wird erst problematisch, wenn es um Webseiten geht, wo die Besucher einen schnellen Seitenaufbau erwarten. Denn dann blockieren mitunter unwichtige Update-Operationen den Seitenaufbau an anderer Stelle.

1. Fall: Zuerst möchte ich den Fall betrachten, dass mitgeloggt wird, welche Seiten der Benutzer besucht hat, damit ihm diese Information an anderer Stelle unter “Bisher angesehene Seiten” wieder präsentiert werden kann. Das geschieht ganz einfach beim Aufruf einer bestimmten Seite durch eine INSERT-Anweisung, die die URLund den zugehörigen Usernamen in eine Tabelle schreibt.

2. Fall: Ein anderer Fall ist z.B. das Inkrementieren eines Counters, um später anzeigen zu können “Die Seite wurde bisher xxx mal aufgerufen”. Dies wird über ein einfaches UPDATE-Statement bewerkstelligt, indem man den Wert des entsprechenden Datensatzes um 1 erhöht.

Und nun kommt der Aspekt ins Spiel, dass die Besucher möglichst kurze Warte- bzw. Ladezeiten sehen wollen.
Obige beiden Update-Operationen sind also umringt von jeder Menge SELECT-Anweisungen. In einem Online-Shop müsste beispielsweise der Beschreibugstext geladen werden, eventuell wie viele Artikel im Bestand sind, einige User-Rezensionen und noch einige Produktbilder.
Das Ziel muss es ja sein, all diese SELECTs in möglichst kurzer Zeit durchzuprügeln.

Und plötzlich kommt da das in Fall 2 beschriebene UPDATE in den Weg. Da Update-Operationen standardmäßig höher priorisiert sind als Select-Anweisungen, hat das zur Folge, dass sämtliche andere SELECTs auf diese Tabelle erstmal gesperrt werden bis das UPDATE ausgeführt wurde. Das ist auch sinnvoll (die MySQL-Entwickler haben sich ja was dabei gedacht), allerdings ist es hier fraglich, was wichtiger ist: dass der User (und andere User, die Scripte aufrufen, die auf die gesperrte Tabelle zugreifen) die Seite möglichst schnell angezeigt bekommt oder doch eher dass der Besuchercounter inkrementiert wird. Ich denke ersteres.

Ganz ähnlich verhält es sich im 1. oben beschriebenen Fall. Das INSERT behindert dort genauso sämtliche anderen SELECTs auf diese Tabelle (und naturgemäß sind Update-Operationen meist langsamer als SELECTs, weil neben den Daten ja noch ggf. Indizes bearbeitet werden müssen). Und auch hier die Frage nach der Priorität: schneller Seite ausliefern oder lieber zuerst eine Information in die DB einfügen, die man erst später benötigt (wenn der User danach überhaupt auf eine Seite geht, auf der die bisher besuchten Webseiten angezeigt werden – eventuell war das INSERT auch ganz umsonst). Auch hier ist erstgenanntes wohl wichtiger.

Doch wie ist es möglich unwichtige Update-Operationen auch als solche zu kennzeichnen, damit MySQL weiß, dass deren Ausführung auch noch etwas Zeit hat? Mein erster Gedanke ging in Richtung des Schlüsselworts LOW_PRIORITY. Dadurch blockiert die betreffende Anfrage keine anderen SELECT-Anfragen mehr, die auf die gleiche Tabelle gehen, sondern wartet bis keine höher priorisierten Statements mehr vorliegen. Das Problem dabei ist, dass es dafür die Abarbeitung des aktuellen Scripts seitens PHP behindert, da mysql_query() ja einen Rückgabewert liefert, ob der SQL-Befehl erfolgreich ausgeführt werden konnte. Und dieser kann erst geliefert werden, sobald die Query ausgeführt wurde. So kann man leicht das ganze System ausbremsen, denn jeder, der auf Script abc.php zugreift, worin viele SELECTs gemacht werden und nur wenige “UPDATE LOW_PRIORITY”-Statements, muss warten bis die SELECTs des jeweils anderen ausgeführt worden sind.

Schlauer ist demzufolge die Höher-Priorisierung von SELECT-Statements per HIGH_PRIORITY. Dadurch bewirkt man, dass alle SELECT-Abfragen vor den Update-Operationen ausgeführt werden.

Bei INSERT-Statements gibts neben LOW_PRIORITY noch eine niedrigere Priorität namens DELAYED. Das ist eigentlich noch besser, denn die Operation wird einfach in den MySQL-Puffer geschrieben und später ausgeführt, wenn keine anderen Operationen vorliegen. Der Rückgabewert von mysql_query() ist dadurch aber nicht mehr aussagefähig (immer true). Dadurch wird der Seitenaufbau nicht behindert. Wenn das INSERT ausgeführt wird und es kommt zwischenzeitlich eine andere Operation für diese Tabelle, dann wird es abgebrochen und zuerst das SELECT (oder eine andere Query) ausgeführt. Auch hier wird deutlich, dass unter bestimmten Umständen das INSERT seeeehr lang warten muss. Man muss also entscheiden, wie unwichtig das INSERT wirklich ist bzw. mal in die Statistiken schauen, wie häufig es vorkommt, dass auf eine Tabelle zugegriffen wird. Gibt es nämlich ständig Anfragen in der Warteschlange für diese Tabelle, wird das INSERT-DELAYED erstmal gar nicht ausgeführt.

Schön auch, dass INSERT-DELAYED-Statements der gleichen Tabelle zusammengefasst werden zu einer INSERT-Query, das beschleunigt die Sache zusätzlich.

So, jetzt bitte noch nicht losstürmen und die Schlüsselwörter einfügen, denn wie immer gibts einen Haken: In einem Benchmark von gleichen INSERTs – einmal mit DELAYED und einmal ohne – wird stets die Variante ohne schneller sein, denn bei DELAYED muss MySQL ja stets prüfen, ob nicht doch noch eine andere Operation reingekommen ist und das kostet natürlich Zeit.
Folge: DELAYED sollte nur angewendet werden, wenn der Durchsatz irrelevant und die Wertänderung wirklich vorerst unwichtig ist (wie z.B. bei Einzeloperationen wie in Fall 1 oben beschrieben).
Außerdem wird die zu tätigende Operation wirklich nur in den Arbeitsspeicher geschrieben, nicht auf die Festplatte. Das ist dann problematisch, wenn MySQL oder der Server an sich abstürzt. Also besser nur für unwichtige Daten verwenden (wie eben einen Counter, wo es nicht darauf ankommt, ob der nun 1 mehr oder weniger anzeigt).

Bei SELECT HIGH_PRIORITY gibt es solche Bedenken nicht. Sobald die Tabelle frei ist, wird diese Anfrage ausgeführt, da sie höher priorisiert ist als alle anderen Abfragen.

Prüft also immer (sowohl für HIGH_PRIORITY und noch mehr für DELAYED): Ist es wichtig, dass eine bestimmte Wertänderung sofort durchgeführt wird oder reicht es auch in ein paar Sekunden noch?!
Wenn ihr feststellt, dass die Update-Operation eher unwichtig ist für den weiteren Seitenaufbau oder auch die Informationsgewinnung an anderer Stelle, dann kann durch die beiden Schlüsselwörter der Seitenaufbau beschleunigt werden (um wie viel, hängt natürlich vom Aufwand für die Update-Operation ab).

Also probierts einfach mal aus und wie immer sind Kommentare sehr willkommen.

Jan hat 152 Beiträge geschrieben

23 Kommentare zu “Verzögertes Schreiben schneller als direktes Schreiben

  1. MKay sagt:

    Das klingt sehr interessant.
    Auf meiner Webseite werden pro Minute mehrere Hundert Datensätze eingefügt (im Hintergrund via Script), während durch die User Select-Anfragen an die selbe Tabelle gesendet werden. (habe eine Art Suchmaschine)

    Den Tipp werde ich auf jeden Fall mal ausprobieren, danke 🙂

  2. Florian sagt:

    Also mit SELECT HIGH_PRIORITY und INSERT DELAYED ist gut erklärt. Aber wie schaut das nun mit den UPDATE-Anweisungen aus? An dieser Stelle soll LOW_PRIORITY verwendet werden?

    Eventuell kann man dies noch im Artikel deutlicher machen. Ansonsten wieder ein sehr interessanter Artikel!

    Weiter so!

  3. tcomic sagt:

    Wirklich interessanter Artikel!
    Leider gibt es halt auch die Scripts, in welchen SELECTs auf Datensätze zugreifen müssen, welche in dem selben Scriptablauf erst eingefügt wurden (ev. wenn eine Zwischentabelle geupdated wird und dann per Join das ganze Paket gelsen werden soll). In diesem Fall würde die Priorisierung ev. sogar zu Fehlern im Scriptablauf führen.
    Aber falls das nicht der Fall sein sollte, ist eine Priorisierung der SELECTs vorzuziehen…

  4. Jan sagt:

    @Florian:
    LOW_PRIORITY wollte ich eher nicht empfehlen (deshalb bin ich im Beitrag dann auf HIGH_PRIORITY umgeschwänkt). Der Grund:
    – Benutzer A ruft ein Script mit einem UPDATE LOW_PRIORITY tabelle1 auf.
    – gleichzeitig erfolgen jede Menge Zugriffe auf tabelle1 von anderen Scripten.
    – nun muss Benutzer A so lange warten bis keinerlei anderer Zugriff mehr auf die Tabelle erfolgt. Das wird er natürlich nicht tun, sondern lieber zur Konkurrenz gehen, weils ihm zu lange dauert.

    @tcomic:
    Korrekt, deshalb hab ich im Text immer von “unwichtigen” Update-Operationen gesprochen. Wenn das Schreiben möglichst schnell gehen soll, sollte man weiterhin ein normales INSERT verwenden.

  5. Florian sagt:

    Ich habe mal eine Frage zu einem praktischen Beispiel:

    Ich habe eine Tabelle, in der ich die Besucher inklusive User-Agent etc. logge. Auf diese führe ich also bei jedem Seitenaufruf ein SELECT aus, um eben zu gucken, ob der Besucher bereits als Datensatz existiert. Ist der Besucher neu, wird zunächst ein INSERT ausgeführt und kurz darauf – sofern JS aktiv ist – auch noch ein UPDATE. Wie wäre es in diesem Fall am Sinnvollsten, mit DELAY, LOW_PRIORITY und HIGH_PRIORITY zu operieren?

    Habe auch noch beispielsweise eine Tabelle namens counter_betriebssysteme, die sechs Datensätze oder so besitzt (halt Windows XP, Vista etc.) und den User-Agent – wenn neuer Besucher – überprüft, ob er mit einer bestimmten Zeichenkette übereinstimmt. Kann er zugeordnet werden, wird ein UPDATE ausgeführt, einfach eine Zahl, die in der Betriebssystem-Tabelle steckt, um den Wert 1 erhöht. Was wäre an dieser Stelle sinnvoll? Den SELECT oder UPDATE modifizieren?

    Würde mich über Antworten sehr freuen!

  6. Mirco sagt:

    So ganz klar kommt der Anwendungsvorschlag nicht heraus. Soll man nun z.B. in einem Profil bei einem UPDATE-Counter alle Queries umbauen mit HIGH PRIORITY (viel Arbeit!) damit, der UPDATE-Query später ausgeführt wird? Sollte man also einen Query vergessen, würde der Effekt gleich negativ aussehen? Grüße!

  7. Jan sagt:

    @Mirco: Ja, High_proirity wäre da gut. Auch wenns viel Arbeit ist. Geht aber per Search & Replace recht fix.

    @Florian: Wieso machst Du denn ein Insert und auf den gleichen Datensatz danach ein Update? Oder hab ich da was falsch verstanden?
    Und für deine zweite Sache würde ich agr nicht prüfen, ob ein Datensatz existiert mit der Zeichenkette sonder neinfach das Update durchführen mit entsprechender WHERE-Bedingung. Wenn es eben keinen Datensatz gibt, der zur Bedingung passt, wird auch nix aktualisiert.

    Erst wenn das gelöst ist, könnte ich oder jemand anderes auf DELAYED oder die PRIORITY-Varianten eingehen.

  8. Florian sagt:

    Ich muss erst einen INSERT und dann ein UPDATE fahren, weil es in zwei verschiedenen Dateien liegt. Die eine Datei wird nur ausgeführt, wenn man JavaScript aktiviert hat. Diese wird auch extern aufgerufen und beinhaltet eben das UPDATE.
    Nun könnte man zwar alles in die externe Datei packen, aber so würden mir die Bots oder User ohne JavaScript durch die Lappen gehen. 😉 Es muss also augenscheinlich bei einem INSERT und situationsabhängig UPDATE bleiben.

    Und auch der SELECT ist zwingend, schließlich speicher ich jeden Besucher und Seitenaufruf ab und führe, falls ein neuer Besucher, auch diverse andere UPDATES, wie beispielsweise das mit dem Betriebssystem, noch durch.

  9. Jan sagt:

    Wahrscheinlich stelle ich mich grad ein bisschen blöd an, aber ich würde dann eben 2 INSERTs machen – einen für JS-Nutzer und einen für solche ohne JS (dann hat jeder Nutzer an dieser Stelle genau einen INSERT.

    Aber um jetzt mal zum Thema zu kommen: Für das INSERT empfehle ich Dir DELAYED (es sei denn Du benötigst den Wert für die anderen Sachen, die sich anschließen).

    Und zu dem Update: Da scheint mir was mit der Normalisierung nicht zu stimmen. Für mich hört es sich so an, als speicherst Du den User-Agent usw. in einer Tabelle und erhöhst den Counter zu einem bestimmten Betriebssystem in einer anderen Tabelle. Das ist Redundanz.
    Besser wärs, wenn Du alle Infos in einer Tabelle speicherst (User-Agent + OS usw.) und später die Anzahl per COUNT(*) holst.

  10. Florian sagt:

    @Jan

    Mal eine Frage, wie kann man Dich privat erreichen? Meine Mailadresse dürfte ja für Dich einsehbar sein.

    (Wenn Du den Post gelesen hast, kannst Du ihn auch gerne löschen). 😉

  11. Mirco sagt:

    Hab ich mir schon fast gedacht, dass man alle per Search & Replace ersetzen muss. 😀

    Es reicht aber auch nicht aus, wenn man nur in einer Datei alles ersetzt, dass muss dann tatsächlich für die Benutzer-Tabelle im gesamten Projek erfolgen. Das schließt auch viele LEFT-Joins mit ein, daher könnte das S&R zu mehr Arbeit ausarten. 🙁

    Schade, dass MySQL hier keine einfachere Lösung nur für bestimmte UPDATEs hergibt.

    Grüße!

  12. GhostGambler sagt:

    Als generelle Alternative zu der Spielerei mit Prioritäten sollte man natürlich auch die Wahl anderer DB-Engines in Betracht ziehen.
    InnoDB z.B. ist mit Row-Level-Locking in bestimmten Situationen deutlich performanter und hat gewisse Restriktionen auch einfach nicht. (Ich erinnere mich an den Fall wo jemand in einem Forum schrieb, dass er damit die Online-Tabelle aufgesetzt hat, weil da bei MyIsam kein Query mehr Fuß gefasst hat durch die schnellen Veränderungen.)

  13. Mirco sagt:

    Das klingt wirklich wesentlich interessanter mit MyIsam. Werde mich über die Vor- und Nachteile auch noch mal informieren. Ein Performance-Test wäre hier auch nicht schlecht. 🙂

  14. Jan sagt:

    InnoDB ist sicherlich interessant, aber MyISAM ist von Grund auf schneller. Allein durch das Row-level-Locking kann InnoDB manchmal schneller sein. Vor einem Umstieg sollte man sich aber genau informieren!

    Aber natürlich können auch InnoDB- und MyISAM-Tabellen in einer Datenbank gemischt werden.

  15. tcomic sagt:

    Ich bin inzwischen eigentlich ganz von MyIsam auf InnoDB umgestiegen, da ich eigentlich nur noch mit relationellen Datenbanken arbeite. So kann ich z.B. das löschen von Verknüpften Datensätzen dem DBMS überlassen und mein Sourcecode wird übersichtlicher…
    Oft könnte ich die selben Ergebnisse mit MyIsam performanter erledigen, allerdings kommt bei mir die Frage der Wirtschaftlichkeit dazu, darum entscheide ich mich meistens dazu das DBMS so viel wie möglich machen zu lassen = weniger Entwicklungsarbeit = billiger!

  16. GhostGambler sagt:

    Schneller, ja, aber ich persönlich würde auch jeden Grunddatenbestand in InnoDB halten, eben aus den Gründen die tcomic schon genannt hat plus ACID-Konformität.

    Cache oder temporäre Tabellen kann man immer noch in MyIsam halten (oder Tabellen, bei denen das Vorhandensein und die Korrektheit der Daten so relevant ist wie Sack Reis@China~)

    (Übrigens, MyIsam ist natürlich auch eine relationale Datenbank…)

  17. Sh1nto sagt:

    Ich betreibe selber ein Browsergame, und die Performanceprobleme mit MyISAM waren irgendwann nicht mehr in den Griff zu bekommen. Hat man verhältnissmäßig viel mehr SELECT’s als Updateoperationen (INS/UPD/DEL), hat MyIsam klare vorteile, hat man allerdings so wie wir, tabellen, die groß sind, und mehrmals pro sekunde ein Update erfahren, sond TableLocks tödlich, oder bei einer Tabelle die quasi in jeder Seite mit reinge-joint wird, killt ein einziges Update auf diese Tabelle, die komplette Serverperfmormance, auch wenn das auf eine row ist, die mit

  18. elexpress sagt:

    Hallo, ein wirklich interessanter Artikel. Leider verstehe ich noch nicht ganz so viel davon, werde aber in Zukunft öfter vorbeikommen ;).

  19. alex sagt:

    Hey,

    schöner artikel.
    könntest du aber eventuell eine quelle nennen, die deine behauptung stützt, dass SELECT HIGH_PRIORITY besser ist als UPDATE LOW_PRIORITY ?

    ich kann die logik dahinter nicht nachvollziehen.
    nach deiner theorie wartet der client bei UPDATE LOW_PRIORITY darauf, dass er endlich ran darf. Aber er wartet doch ebenfalls (genauso lange), wenn vorher etliche SELECTs mit HIGH_PRIORITY dran sind (?) … ich kann da keinen unterschied sehen. in beiden fällen muss aus meiner sicht der client warten. konnte den angeblichen vorteil von SELECT HIGH_PRIORITY nirgends sonst finden – auch nicht in der mysql-doku. also wie gesagt wäre ich für eine quelle dankbar 🙂

    lg

  20. Jan sagt:

    Quellen gibts selten, weil vieles einfach durch ausprobieren ist.

    Das Problem bei UPDATE LOW PRIORITY: Besucher A lädt Seite xyz.php. Darin ist ein einziges UPDATE LOW_PRIORITY und 10 SELECTs. Zur gleichen Zeit öffnet Besucher B die Seite. Nun muss Besucher A durch sein LOW_PRIORITY nicht nur seine eigenen SELECTs abwarten sondern auch noch die konkurrierenden SELECTs der anderen Besucher. Das verzögert den Seitenaufbau bei A derart, dass er die Seite wahrscheinlich verlässt.

    Nun könnte man sagen, dass man das UPDATE geschickterweise ganz ans Ende des Scripts packt und somit die Ausgabegeschwindigkeit von A nicht mehr beeinflusst wird. Korrekt, das ist möglich, nur sieht man es selten (meistens wird das UPDATE als allererstes ganz oben im Script gemacht).

    Aber wenn ich es mir so recht überlege, sehe ich gerade auch nicht den Unterschied. Ich hatte aber damals beides getestet und nicht ohne Grund SELECT HIGH_PRIORITY empfohlen. Vielleicht fällts mir noch ein…
    Außerdem fängt jetzt die 2. Halbzeit Deutschland-Polen an – da kann ich nicht klar denken 😉

  21. alex sagt:

    hmmm nunja, also ob man nun wartet, weil die selectabfragen warten oder die updateabfragen ist im prinzip egal. da man in der regel aber mehr selectabfragen hat, sollte sich ein “update low_priority” vorteilhaft auswirken.
    den nachteil sehe ich im grunde genauso, nur ist mir wie gesagt nicht schlüssig warum high_priority bei select anweisung was anderes sein soll bzw. diesen nachteil nicht haben soll, denn es geschieht genau das gleiche.

    das ist wie
    5 + 5
    oder
    5 – (-5)

    schönes spiel gewesen 🙂

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>