Gepufferter und ungepufferter Zugriff auf MySQL-Tabellen

Wer schon mal mit höheren Programmiersprachen zu tun hatte, kennt gepufferte und ungepufferte Datenströme. Auch MySQL bietet uns die Möglichkeit das Result-Set einer SELECT-Anfrage gepuffert und ungepuffert zurückzugeben. PHP bietet aus diesem Grund auch zwei verschiedene Funktionen dafür an – je nach gewünschter Pufferung: mysql_query() (gepufferter Datenstrom) und mysql_unbuffered_query (ungepuffert, sagt ja der Name schon 😉 ). Was sind nun die Vor- und Nachteile der beiden unterschiedlichen Arten des Zugriffs? Dieser Beitrag soll es erklären.

Zuerst möchte ich kurz erklären, was Pufferung eigentlich bedeutet. Inputs und Outputs sind in modernen Programmiersprachen nicht über Strings oder primitive Datentypen zugänglich sondern über Streams (Datenströme). Um aus einem Strom nun eine bestimmte Einheit herauszulösen, muss man auf ein bestimmtes Stopp-Zeichen warten (meist \n, also newline bzw. LF=line feed). Durch Zusammenbauen der Zeichen zwischen den Stoppzeichen entsteht eine Gruppe, die man nun auch wieder als String behandeln kann. Das bedeutet, dass während des Wartens auf das Stopp-Zeichen nichts weiter passiert als dass gewartet wird. Außerdem müssen die vorher eingelesenen Zeichen natürlich zwischengespeichert werden, damit anschließend der String zusammengebaut werden kann.
So viel kurz zur Theorie im Hintergrund, wir wollen uns ja eigentlich hier mit Scriptsprachen (insbesondere PHP) beschäftigen, wo man sich darum eigentlich nicht kümmern muss.

Warum habe ich nun so ausgeholt? Ganz einfach: MySQL kann das auch 😉
mysql_query() weist MySQL an, dass das Result-Set zuerst seitens MySQL gepuffert werden soll. Das bedeutet, dass das Result-Set komplett am Stück an PHP übergeben wird, um es anschließend dort weiter verarbeiten zu können.
Viel schlauer wäre es doch aber, wenn man nicht warten müsste bis das gesamte Result-Set zur Verfügung steht, sondern direkt mit der Verarbeitung beginnen könnte, sobald der erste Datensatz feststeht. Und genau da kommt mysql_unbuffered_query ins Spiel. Das bedeutet insbesondere eine Speicherersparnis beim PHP Interpreter, da dieser stets nur 1 Datensatz im Speicher hat (was aber auch Nachteile birgt –> siehe unten).

Hier ist das beim Test zum Einsatz gekommene Script:

$rs = mysql_query("SELECT * FROM hotels WHERE name IS NOT NULL"); //bzw. mit mysql_unbuffered_query()
while($data = mysql_fetch_assoc($rs)) {
  echo $data['name'];
}

Diese Variante liefert einfach alle Hotel-Datensätze, deren Name nicht NULL ist. Die Query liefert ungefähr 19000 Datensätze zurück, sie dauert laut PHPMyAdmin ca. 0,0011 s.

Das Ergebnis sieht dabei so aus:

Datei Gesamtlaufzeit durchschnittliche Laufzeit pro Durchlauf Verhältnis zur schnellsten Variante
result_mysql_ unbuffered_query.php 86.864906 s 868.649 ms 100 %
result_mysql_ buffered_query.php 88.397108 s 883.971 ms 102 % (+ 2%)

Die ungepufferte Variante ist demzufolge etwas schneller.

Trotzdem verwundert mich der geringe Unterschied – hätte mehr erwartet. Habe noch andere Querys durchgetestet, aber der Unterschied war nirgends größer als hier. Teilweise war sogar die gepufferte Variante schneller. Und das obwohl selbst die PHP-Doku von einer Beschleunigung spricht:

On the one hand, this saves a considerable amount of memory with SQL queries that produce large result sets. On the other hand, you can start working on the result set immediately after the first row has been retrieved: you don’t have to wait until the complete SQL query has been performed.

Zumal die ungepufferte Variante noch einige Nachteile mit sich bringt: So können beispielweise die Funktionen mysql_num_rows(), mysql-result() und mysql_data_seek() nicht genutzt werden. Außerdem sind Konstrukte wie folgendes nicht möglich (nur mit separater DB-Verbindung):

$rs = mysql_unbuffered_query("SELECT * FROM table");
while($data = mysql_fetch_assoc($rs)) {
  $rs1 = mysql_query("SELECT * FROM table2 WHERE fk_ID='".$data['ID']."'");
}

Bedeutet also das komplette Result-Set muss erst abgeholt werden, bevor die nächste Anfrage abgeschickt werden kann. Und das bedeutet wiederum, dass man beim Verarbeiten der Daten möglichst schnell sein sollte, weil in der Zwischenzeit die gesamte Tabelle gelockt bleibt. David beschreibt das in den User Comments von php.net so:

Also note that if you are using this function, you should be quick about processing the result set, or you will tie up the MySQL server (other threads will be unable to write to the tables you are reading from).

Habt ihr noch Ideen, was ich übersehen haben könnte? Oder sind meine 19000 Datensätze einfach noch zu wenig? Ich finde die Laufzeiten nämlich jetzt schon recht hoch. Außerdem habe ich extra eine schnelle Query, die recht viele Daten zurückliefert genutzt, damit die Pufferung erst richtig ins Gewicht schlagen kann.
Oder wirkt sich das wirklich alleinig auf den Speicherverbrauch aus, wie es ein Buch bei Google Books suggeriert.

Die Kommentare bei diesem Beitrag haben neue Denkansätze geliefer, die ich umgehend in einem Nachtest untergebracht habe. Dort werden teilweise Aussagen aus diesem Beitrag relativiert, also bitte auch lesen!

Jan hat 152 BeitrÀge geschrieben

13 Kommentare zu “Gepufferter und ungepufferter Zugriff auf MySQL-Tabellen

  1. Reto sagt:

    Netter Bericht. War mir bis jetzt gar nicht bewusst, dass PHP das gesamte ResultSet buffert. Aber ehrlich gesagt verwundert es mich nicht, dass die unbuffered-Version nicht wirklich schneller ist oder sogar langsamer. Dazu ein paar Anregungen:
    1) Bei einer solchen einfach Query kann die DB die Datensätze praktisch ohne Verzögerung ausgeben. Da die Datenmenge gleich gross ist, wird gleich viel an PHP übergeben.
    2) wenn noch PHP-Code zwischen jedem fetch dazu kommt, wird natürlich unbuffered langsamer -> da Code-Latenzen.
    3) wie verhält es sich eigentlich wenn man mal die ‚echo‘-Anweisen weg lässt? Meiner Erfahrung nach hat dies einen nicht zu vernachlässigenden Einfluss auf die Laufzeit.
    4) Vorteile von unbuffered kommt sicher auch dann zum Zuge, wenn komplexere oder aufwendigere Queries abgesetzt werden. Versuchs mal mit ein paar joins. Dann hat die DB die Möglichkeit bereits Daten zu senden, bevor alles gejoint wurde.
    5) wie sieht es eigentlich mit dem Memoryverbrauch aus? mich würde dies in diesem Zusammenhang sehr interessieren. Auch Memory gehört aus meiner Sicht zur Performance.

    Dies sind ein paar Anregungen. Ich bin kein DB-Spezialist und liege vielleicht mit der einen oder anderen Aussage daneben. Dann entschuldigt dies bitte und korrigiert mich.

  2. Tim sagt:

    Evtl. liege ich auch ganz falsch, aber ich glaube, deine Messung setzt an der falschen Stelle an.

    Der wichtigste Punkt ist nach meiner Ansicht hier:
    you don’t have to wait until the complete SQL query has been performed

    Nehmen wir an, dein SELECT braucht zur Ausführung 60 Sekunden.
    Bei der gepufferten Abfrage müsstest du 60 Sekunden warten und hast dann das komplette Ergebnis.
    Bei der ungepufferten Abfrage kannst du sofort die ersten Ergebnisse abrufen, während der Server noch arbeitet und musst keine 60 Sekunden warten sondern z.B. nur 40 Sekunden.

    Der Geschwindigkeitsgewinn liegt also in der kürzeren Zeit bis das erste Ergebnis verwertbar ist und nicht in der Gesamtlaufzeit.

    Wie eingangs schon gesagt, kann ich da auch komplett daneben liegen, da ich das selbst noch nie verwendet oder ausprobiert habe.
    Würde mich freuen, wenn du das durch eine Messung belegen könntest 🙂

  3. Jan sagt:

    @Reto: Gepuffert wird ja auch seitens MySQL, aber das war wohl nur ein kleine Schreibfehler Deinerseits.

    Zu Deinen Anregungen:
    1. Könnte sein, aber welche Query könnte man nehmen. Was mit ORDER BY geht ja nicht, weil das gepuffert werden muss, da sonst die Reihenfolge nicht unbedingt stimmt. Könnte höchstens mehrere WHERE-Sachen nehmen (möglichst noch ohne Index).
    Ah ja, und Dein 4. werde ich mit reinziehen.

    2. und 3.: Es bringt ja nix echo-Befehl rauszunehmen, denn in der Praxis steht da meistens mindestens ein echo oder andere Befehle (irgendwas will man ja mit den Daten machen).

    5. Wie könnte ich den messen? Das Apache-Benchmark-Log gibt da nix her.

  4. Jan sagt:

    @Tim: Das ist schon korrekt, nur denk das mal weiter: Wenn ich früher beginnen kann die Datensätze zu verarbeiten, müsste ich ja am Ende auch schneller fertig sein.
    Ich greife mal Dein Beispiel auf:
    Anzahl DS: 10
    Zeit bis Daten anliegen (gepuffert): 60 s
    Zeit bis erster DS anliegt (ungepuffert): 40 s
    Verarbeitungszeit eines Datensatzes: 1 s
    ——-
    Gesamtzeit gepuffert: 60 + 10*1 = 70
    Gesamtzeit ungepuffert: 40 + 10*1 = 50

    Voraussetzung ist natürlich, dass die Bearbeitungszeit kleiner ist als die Zeitdifferenz um einen zusätzlichen Datensatz zu holen. Aber ich werd versuchen auch das nochmal zu testen. Werd ich dann aber nur per microtime() machen können.

  5. Tim sagt:

    Die Gesamtzeit der SQL-Verarbeitung bleibt gleich, nur du kannst mit der Ausgabe früher anfangen.
    Nur weil eine Anfrage ungepuffert ist, wird sie deswegen nicht schneller vom Server abgearbeitet.
    Auch wenn also nach 40 Sekunden schon die ersten Ergebnisse zurückkommen, bräuchte der Server wohl noch die restlichen 20 Sekunden für die übrigen Ergebnisse.

    Die Gesamtzeit wäre nach meiner Meinung also 60 + X. Wobei das Ergebnis für unser Beispiel zwischen 60 und 70 Sekunden liegen müsste. Im Idealfall wohl 61 Sekunden (letztes Ergebnis zur 60. Sekunde + 1 Sek. für die Verarbeitung).

  6. Reto sagt:

    @Jan: Nochmals zu meinem Punkt 5. Beim debugen meiner PHP-Skripte verwende ich regelmässig die Funktion
    memory_get_usage().
    Dies gibt mir in etwa einen Überblick über die von PHP alloziierten Speicherbereich. Ich denke es ist nicht unbedingt die Weissheit aller Dinge, sollte aber doch einen ungefähre Ahnung über den Memoryverbrauch geben.

  7. Jan sagt:

    Hmm ok, memory_get_usage() wäre ein Weg – allerdings nur um den Speicherverbrauch von PHP herauszubekommen, oder? Uns ging es ja hier eigentlich um den Speicherverbrauch von MySQL, denn der seitens PHP sollte gleich sein.

    Aber ich schau mir die Funktion gleich nochmal bei php.net an…

  8. Banger sagt:

    Ich habe selbst vor kurzer Zeit noch einen großen Nachteil von mysql_unbuffered_query() entdeckt: Wurde der Apache-Prozess unerwartet beendet, bevor das Resultset komplett abgearbeitet war (z.B. Timeout oder Speicherlimit überschritten), hing die zuvor per mysql_unbuffered_query() abgesetzte Abfrage bis in alle Ewigkeit im Status „Sending Data“ und blockierte dementsprechend auch Tables. Nicht schön. (FYI: MySQL 5.0.32, PHP 5.2, Apache 2.0.54, Apache/PHP und MySQL auf getrennten Servern.)

  9. Jan sagt:

    Jepp, das ist klar – es muss zuerst das komplette ResultSet abgeholt werden, bevor eine neue Abfrage bearbeitet werden kann. Allerdings könnte man das ja so lösen, dass man bei einem Neustart des Apache MySQL auch kurz killt und dann beides neustartet. Aber sicherlich ist mysql_query() der bequemere Weg.

  10. Banger sagt:

    Verstehe ich das richtig – ich soll jedes Mal MySQL neu starten, wenn ein PHP-Script abgeraucht ist und ein verwaistes ResultSet zurückgelassen hat? 😉

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>