Nachtest zu gepufferten und ungepufferten SQL-Abfragen

Aufgrund eines fatalen Denkfehlers im letzten Beitrag schiebe ich hier nochmal einen korrigierenden Nachtest nach. Es geht wieder um gepuffertes vs. ungepuffertes Ausführen von SQL-Querys.

Das letzte Mal hab ich eine völlig ungeeignete Abfrage verwendet:

  1. Sie war zu schnell für einen Vergleich bzw. eine eindeutige Entscheidung, ob mysql_unbuffered_query() Sinn macht
  2. Sie enthielt ein ORDER BY, was nicht clever ist, denn MySQL weiß ja auch erst nach Behandlung aller Datensätze, welcher der größte bzw. kleinste Wert ist.

Außerdem habe ich beim letzten Mal lediglich die Gesamtausführungszeit gemessen, was auch sinnfrei war.

Jedenfalls hab ich diesmal eine wesentlich komplexere Abfrage verwendet. Mangels ordentlicher Testdatenbank, mit der ich lange dauernde Querys erzeugen kann (habs einfach nicht gebacken bekommen, eine Abfrage zu schreiben, die 1 – 5 Sekunden dauert – entweder wars im Zehntelsekunden-Bereich oder im Minutenbereich) habe ich folgende, völlig sinnfreie Abfrage verwendet.

SELECT * 
FROM `tabelle` a1 
INNER JOIN tabelle a2 ON a1.ID=a2.ID 
INNER JOIN kategorien ON a1.kategorien_ID=kategorien.ID 
WHERE RAND()>0.5 AND RAND()<0.5 AND RAND()>0.5 AND RAND()<0.5 AND RAND()>0.5 AND RAND()<0.5 AND RAND()>0.5 AND RAND()<0.5 AND RAND()>0.5 AND RAND()<0.5 AND RAND()>0.5

Mir war es wichtig, eine Anfrage zu wählen, die so beschaffen ist, dass die Prüfung eines einzlenen Datensatzes genügt, um zu bestimmen, ob er zum Result-Set gehört oder nicht. Dies wäre mit ORDER BY oder GROUP BY nicht möglich.
Die eigentliche Abfrage ist aber völlig egal, denn ihr habt bestimmt auch “richtige” Abfragen, die einige Sekunden dauern. Grundsätzlich sei an dieser Stelle schon mal gesagt: Je länger eine Abfrage dauert (Gesamtzeit laut PHPMyAdmin), desto sinnvoller ist der Einsatz von mysql_unbuffered_query().

Jedenfalls habe ich mit obiger Query die gepufferte Abfrage-Variante getestet, also mysql_query(). Dabei kam ich auf folgendes Ergebnis:
Zeit bis zur Verarbeitung des ersten Datensatzes: 5.005784034729 s
Zeit bis zur verarbeitung aller Datensätze: 5.0073800086975 s

mysql_unbuffered_query() im Gegenzug kommt auf dieses Ergebnis:
Zeit bis zur Verarbeitung des ersten Datensatzes: 0.24099016189575 s
Zeit bis zur verarbeitung aller Datensätze: 5.2411839962006 s

Leider konnte ich für diesen Test nicht mein Benchmark-Script nutzen, da ich damit ja nur die Gesamtbearbeitungszeit (basierend auf den Ergebnissen des Apache Benchmark-Tools ab.exe) herausbekomme. Das ist auch der Grund, warum es diesmal keine hübsche, bunte Tabelle gibt – ich hoffe die blanken Zahlen reichen diesmal.

Nun also zur Auswertung:
Der Unterschied in der Gesamtzeit ist irrelevant, weil ich wie gesagt nur per microtime() messen konnte. Die Aussage, dass die ungepufferte Version grundsätzlich langsamer sei, würde ich so nicht unterschrieben wollen.
Viel interessanter ist aber der riesige Unterschied bis zur Bearbeitung des ersten Datensatzes. Und genau das ist ja das Ziel von mysql_unbuffered_query(), wie ich dank Tims Kommentar nun auch begriffen habe ;-). Das bedeutet, dass die Bearbeitung durch PHP bereits sehr viel früher beginnen kann.

Also funktionierts doch hervorragend. Eines muss allerdings gesagt werden: mysql_unbuffered_query() macht keinen Sinn bei Querys mit ORDER BY, da sind die Ausführungszeiten zwischen mysql_query() und mysql_unbuffered_query() gleich (Grund: MySQL scheint Quicksort zu verwenden. Mit Heapsort wäre ein Geschwindigkeitsunterschied durchaus möglich).
Außerdem möchte ich noch sagen, dass ungepufferte SQL-Behandlung auch nur sinnvoll ist, wenn die Verarbeitung des Datensatzes schneller geht als die Ermittlung des nachfolgenden im Result-Set. Ansonsten muss MySQL zwangsläufig puffern, weil die Anwendung nicht hinterherkommt.

Außerdem ist wichtig, dass die Wirkung des schnelleren Ausgebens durch PHP verpufft (zumindest merkt der Client davon nix), wenn ihr die Ausgabe durch PHP puffert (z.B. um den Output zu gzippen). Deshalb ists für mich wohl uninteressant, aber ich wollte euch trotzdem die Möglichkeit aufzeigen, die MySQL und PHP da bieten.

Falls Rechtschreibfehler in diesem Beitrag entdeckt werden, bitte ich dies zu entschuldigen. Ich wollte den Test und den Bericht möglichst schnell veröffentlichen, damit ihr nicht von dem vorigen Beitrag verstimmt werdet.

Jan hat 152 Beiträge geschrieben

16 Kommentare zu “Nachtest zu gepufferten und ungepufferten SQL-Abfragen

  1. Florian sagt:

    Hallo,

    vielen Dank für den Test! Soweit ich das nun richtig verstanden habe, ist doch mysql_query dann immer mysql_unbuffered_query vorzuziehen. Mir fällt jetzt gerade nur ein Beispiel ein, wo mysql_unbuffered_query tatsächlich sinnvoll sein könnte. In deinem neuen Beispiel ist mysql_unbuffered_query ja auch wieder langsamer:

    $query = mysql_unbuffered_query(“SELECT string FROM tabelle”);
    $status = 0;

    while($row = mysql_fetch_row($query))
    {
    if(substr_count($string, $row[“0”]) != 0)
    {
    $status = 1;
    break;
    }
    }

    Bei dieser Abfrage (bezogen auf deine Werte) wäre er ja günstigenfalls bereits nach 0,24 Sekunden fertig – wenn er direkt passenden String finden sollte – und ungünstigstenfalls nach og. 5,2 Sekunden…

  2. Jan sagt:

    Selbst da ist es schlecht möglich Florian, denn wie immer gilt, dass zuerst das gesamte Ergebnis abgeholt werden muss, bevor Du eine weitere Abfrage durchführen kannst. Es sei denn, du schießt den Prozess ab, aber das halte ich dann doch für etwas unsauber (zumal Du auch dafür eine zweite mySQL-Verbindung bräuchtest).

  3. Florian sagt:

    Aber in welchem Alltagsszenario ist mysql_unbuffered_query dann überhaupt sinnvoll? Auch das hier gezeigte spiegelt ja wider, dass mysql_unbuffered_query in der Summe nicht schneller ist – und darauf kommt es letztlich an…

  4. protocols sagt:

    @Florian: irgendwie nicht.

    Vor allem bei vielen gleichzeitigen Usern sollten Skripte weniger RAM verbrauchen, da nicht der gesamte Datensatz in den RAM muss, sondern immer nur ein kleiner Teil.

    Ähnlich wie:
    file_get_contents() vs. fopen+fget

    bei file_get_contents wird der komplette Dateiinhalt in den RAM geschoben, bei fopen+fget immer nur stückchenweisen.

    übrigens: lustig ist so ne unbuffered query auch, wenn die inhalte via ob_* direkt an dem verschickt werden…

  5. Thomas Worm sagt:

    Um nochmal auf das ORDER BY einzugehen. Wäre da nicht folgendes denkbar:

    MySQL ordnet erst die in Frage kommenden Datensätze in einer temporären Tabelle in prüft dann die Conditions durch.
    => Der erste Datensatz kommt durch den unbuffered query trotzdem schneller als beim buffered query!

    Wäre toll, wenn du das nochmal prüfst!

    Gruß, Thomas

  6. Jan sagt:

    Das tut MySQL auch so, wie Du es sagst: Zuerst alle Datensätze in ne temporäre Tabelle schrieben, die zu den WHERE-Bedingungen passen und anschließend diese sortieren.

    Vielleicht habe ich Dich auch falsch verstanden – deswegen komme ich nicht ganz mit deinem 2.Satz klar. Wo ist der Unterschied zwischen “in Frage kommenden Datensätzen” und “prüft dann die Conditions nach”? Für mich sind in Frage kommende Datensätze genau die, die die Conditions (=WHERE-Bedingungen) erfüllen.

    Und kurze Anmerkung noch: Ich habe es durchaus auch mit ner Order-By-Abfrage getestet. Da dauert es bis zur Behandlung des ersten Datensatzes genauso lang wie mit der gepufferten Variante.

  7. Thomas Worm sagt:

    Hi,

    meine Überlegung war, dass MySQL evtl. zuerst alle Datensätze der angegebenen FROM-Tabellen mit entsprechender Spaltenauswahl (das meinte ich mit in Frage kommenden) sortiert und dann erst die WHEREs ausführt. Aber nachdem du das geschrieben hast:

    > Und kurze Anmerkung noch: Ich habe es durchaus
    > auch mit ner Order-By-Abfrage getestet. Da dauert
    > es bis zur Behandlung des ersten Datensatzes
    > genauso lang wie mit der gepufferten Variante.

    weiß ich ja, dass es nich so ist. Aber das wär doch mal ne verbesserung der SQL-Syntax wenn man für meine Variante ein Flag setzen könnte, damit der das so macht.

    Gruß, Thomas

  8. Florian sagt:

    Hallo,

    noch einmal eine kurze Zwischenfrage. Wenn sortiert, haben wir ja nun herausgefunden, dass mysql_unbuffered_query nichts bringt. Wenn nur ein Datensatz ausgegeben wird, bringt mysql_unbuffered_query auch keine Vorteile, oder?

    Beispiel:
    SELECT * FROM tabelle WHERE id = 1450

    Jetzt mal davon ausgehend, dass auf id ein PRIMARY-KEY liegt…

  9. Jan sagt:

    @Florian: Vermutlich nicht, denn dann wird da eh nix gepuffert. Die Ausführungszeiten sollten gleich sein.

    Sowohl mysql_query() als auch mysql_unbuffered_query() schicken das Ergebnis sofort raus, sobald dieser eine Datensatz gefunden wurde.

  10. Pingback: Pablo Theissen
  11. alex sagt:

    nach meinen kenntnissen ist mysql_unbuffered_query sinnvoll, wenn man UDPATE, INSERT und DELETE querys benutzt, denn diese erzeugen keine ausgabe und benötigen daher keine pufferung. quelle kann ich nicht nennen, aber habe es schon mehrmals gelesen.

    lg

  12. da ich beruflich viel mit großen datenmengen zu tun hatte (40.000.000 datensätze in einer tabelle) kann ich eigentlich empfehlen für kleinere sachen wie z.b. eine website die gebufferte variante zu nehmen, da es eben für die verarbeitung ein paar vorteile bringt

    bei dem performencetest würde ich eher sagen ist die gesamtlaufzeit schon aussagekräftig.

    an einem konkreten beispiel erklärt:

    diese 40 mio datensätze hatten ne größe von gut 6 GB, ich wollte nun die tabelle auf einen anderen server kopieren, dabei musste ich an der struktur einige änderung durchführen, ein dump wollte ich da nicht machen, also einfach 2 datenbankverbindungen auf (quellserver und zielserver) und die daten per unbuffert query rausgeholt, geändert und wieder weggeschrieben, der prozess hat die überliche speicher größe von ca 10 MB nicht überschritten und war recht fix fertig

    mit buffert wäre das nicht gegangen, da ja der ram bei weitem nicht ausgereicht hätte (2 GB)

    ich würde also sagen, bei einer menge > 10000 datensätze sollte man die unbuffert variante nutzen, da es dort einfach die laufzeit verringert und resourcenschonender arbeitet

  13. SLi-T sagt:

    Och…ich kann mir da schon Anwendungsfälle vorstellen.
    Immer dann, wenn man große Datenmenge unsortiert oder schon sortierte abholen möchte und dabei nicht viel Speicher belegen möchte.
    Ein Beispiel könnten Bestellungen eines bekannten Onlineshops sein, die einfach sequentiell abgearbeitet werden müssen, zB an einen Webservice geschickt werden. Da könnte man zum Beispiel als Job entwickeln der durchaus eine Laufzeit von mehreren Minuten haben kann. Nachteilig kann hier sein, dass es nicht transaktionell ist, dh. was übertragen ist, ist übertragen und wird nicht ohne weiteres rückgängig gemacht. Manchmal kann aber genau dies gewünscht sein, wenn die Laufzeit einfach zu hoch ist.

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>