MySQL BLOB Geschwindigkeit

[buggy84]

Hallo Experten,

ein Projekt erfordert es, dass ich mich mit der Speicherung von Daten in einer MySql Datenbank auseinandersetzen muss.

In meiner Recherche haben 2 grundsätzliche Meinungen herauskristallisuert:
1 - Man benutzt ein BLOB
2 - Man legt die Datei in einen definierten Ablageort am Server und speichert statt dem BLOB nur den Pfad zu dieser Datei.

Nummer 1 wäre für mich jetzt erstmal ok - ich sehe den Vorteil darin, dass ich mir um das Speichermanagement auf dem Server keine Gedanken machen muss. Heißt, solange die Datenbank existiert, existiert auch meine Datei als BLOB. Würde ich nur den Pfad abspeichern, ist nicht garantiert, dass die Datei auch wirklich noch dort ist, wenn ich sie brauchen würde.

Die Frage ist aber: ist die bloße Existenz von BLOBS ein Performancekiller? Heißt also, wenn ich in einer Tabelle die ID der Datei suche und als Result beispielsweise nur das Feld "Size" zurück haben will, ist gibt es eine Performanceeinbuße, nur weil in der gleichen Tabelle auch das BLOB liegt?

Ich hoffe die Frage ist verständlich. Falls dazu jemand Erfahrungswerte hat, würde ich mich freuen, wenn er sie mit mir teilen würde.

Ich kann übrigens noch nicht abschätzen, wie groß und wieviel BLOBS es am Ende sein werden.

 

[httpdigest]

Ein RDBMS speichert BLOBs überlicherweise nicht inline mit allen anderen Spalten einer Tabelle in der Datenbank, so dass große Sprünge zwischen den jeweiligen Spalten eines Datensatzes existieren, und der Schreib-/Lesekopf der Festplatte dann hinundher springen muss (es sei denn natürlich man benutzt eine SSD); sondern es wird nur eine Referenz in die BLOB-Spalte geschrieben und die eigentlichen Nutzdaten des BLOBs werden in einem separaten Datenbereich gehalten. Also in etwa so ähnlich wie dein eigener Dateiname eine Referenz auf eine Datei in dem Dateisystem wäre, nur dass die BLOB-Referenz natürlich kein String ist sondern eben irgendein Integer-Wert. Wenn der BLOB in einer Query projiziert wird, denn holt sich das RDBMS die Nutzdaten des BLOBs aus dem separaten Speicherbereich. Falls der BLOB nicht projiziert wird, dann passiert damit auch nichts. Es sollte also überhaupt keine Performanceeinbuße geben.

 

[buggy84]

Hallo httpdigest,

das sind wertvolle Informationen. Vielen Dank.
Trotzdem empfehlen einige Programmierer die BLOBS mit einem Primary Key in einer separaten Tabelle zu speichern und eine Abfrage mit JOIN zu machen (sofern es darum geht, den BLOB auch im Result zu bekommen).

Das das BLOB nicht inline gehalten wird, war mir neu. Ich habe in meiner Recherche genu das Gegenteil gelesen, muss aber zugeben, dass dieser Artikel auch schon älter war und vielleicht auch schlecht recherchiert. Es beruhigt mich auf jeden Fall zu wissen, dass es offenbar nicht so ist, und meine Performancebedenkenten wahrscheinlich unbegründet sind.

 

[JuKu]

Wenn man die BLOBs inline halten würde, würde das vermutlich die Performance der gesamten Tabelle herunterziehen.
Wieso willst du denn überhaupt BLOBs speichern? Musst du denn bytes hinterlegen?

 

[buggy84]

Hallo JuKu,
ich liege vielleicht falsch, aber in einem BLOB kann ich eine größere Datei speichern (Bild, XML-Datei, etc.).
Das hat für mich 2 Vorteile:
1. Ich muss mich nicht um das Speichermanagement kümmern
2. Ich muss bei einem Serverumzug nur meine Datenbank umziehen. Ansonsten müsste ich vielleicht jede einzelene Referenz anfassen.

Ich lasse mich gern auch beraten, wie es besser geht.
Wie gesagt, ich kann nicht viel abschätzen, aber grob könnte man vorraussagen, dass es etwa 10k - 20k Dateien im Jahr wären, die durchaus 1MB groß sein könnten. In den meisten Fällen rechne ich mit ca. 200kb.

Ich hätte es halt ungern, dass meine Datenbank plötzlich sehr langsam wird, nur weil ich eine Select Abfrage machen will.

 

[JuKu]

@buggy84 Ich verstehe.
Allerdings muss ich dich darauf hinweisen, dass die Dateigröße je nach Datenbank & Storage Treiber trotzdem begrenzt ist!
Ein normales BLOB unter MySQL kann nur 64KB speichern:
https://stackoverflow.com/questions...h-of-data-i-can-put-in-a-blob-column-in-mysql

Außerdem müssen diese Daten zum Client übertragen werden und große Daten brauchen länger, was wiederum den Connection Pool & die Worker Threads blockiert.
Also die schnellste Variante ist das so oder so nicht.

 

[buggy84]

Hallo JuKu,
ja, statt dem "normalen" BLOB kann man ja auch das Medium BLOB nehmen. Ich denke, da kann ich sogar flexibel sein und eben Dateigrößen limitieren. Es wird einige Daten geben, die nur wenige byte oder kbyte brauchen. dafür könnte ich ein normales BLOB nehmen, für größere Dateien dann das medium BLOB.

Aber das mit der Übertragung zum Client habe ich noch nicht ganz nachvollziehen können - die Übertragung habe ich doch sowieso, unabhängig davon, ob ich ein BLOB benutze oder die Datei per definiertem Management auf dem Server ligen habe. Oder stehe ich irgendwie auf dem Schlauch?
Beispielsweise der Avatar in einem Forum - ich habe Forensoftware gesehen die es auf die eine oder eben andere Weise abspeichern. Aber geladen werden muss das Avatarbildchen doch sowieso. Oder gibt es da einen signifikaten Unterschied?

 

[mihe7]

Oder gibt es da einen signifikaten Unterschied?

Nehmen wir mal diesen Thread, um bei der Forensoftware zu bleiben. Wenn ich die Seite lade, setzt der Browser etwa 30+ Requests ab. Einer davon antwortet mit der HTML-Seite. Der Rest dürften Bilder und JavaScript sein. Wie auch immer, der Browser arbeitet mit einer bestimmten Anzahl an parallelen Verbindungen/Domain, sagen wir mal 10 (einige haben mehr die anderen weniger).

Fiktiv gehen wir mal davon aus, es müssten 30 BLOBs aus der DB geladen werden. Der Browser setzt die ersten 10 Requests ab, Deine Anwendung muss 10 DB-Anfragen parallel stellen, um "maximale" Geschwindigkeit zu erreichen. Die Anwendung muss also mit der DB kommunizieren (benötigt Ports, Puffer), die DB muss SQL verarbeiten, Zugriffsrechte und ggf. Sperren verwalten. Stehen der Anwendung Daten zur Verfügung, werden diese wieder gepuffert an den Browser geschickt. Sobald ein Request beantwortet ist, schiebt der Browser den nächsten nach usw.

Der Seitenabruf eines einzelnen Benutzers ist somit wahnsinnig kostspielig, wenn man das mit einem Abruf über einen gewöhnlichen Web-Server vergleicht: dort gibt es keine DB, kein SQL, keine zusätzlichen Puffer etc.

 

[Thallius]

mal Davon abgesehen, dass ein als Bild, also ein als url/xy.png geladenes avatarimage vom Browser gecached wird, während das Ergebnis einer Datenbankabfrage jedesmal neu geladen werden muss da der Browser ja nicht wissen kann ob immer noch das gleiche in der DB steht.

 

[mrBrown]

mal Davon abgesehen, dass ein als Bild, also ein als url/xy.png geladenes avatarimage vom Browser gecached wird, während das Ergebnis einer Datenbankabfrage jedesmal neu geladen werden muss da der Browser ja nicht wissen kann ob immer noch das gleiche in der DB steht.

Ähm, dir ist aber schon bewusst, dass der Browser in beiden Fällen genau die gleiche Anfrage stellt und genau gleich cachen kann?
Wenn das auf Browserseite einen Unterschied macht, hat der Entwickler gewaltig etwas falsch gemacht. (Also - außer das Bild liegt lokal, aber ich hoffe das meinst du nicht.)

 

[buggy84]

Der Seitenabruf eines einzelnen Benutzers ist somit wahnsinnig kostspielig, wenn man das mit einem Abruf über einen gewöhnlichen Web-Server vergleicht: dort gibt es keine DB, kein SQL, keine zusätzlichen Puffer etc.

@mihe7 Danke für Deinen ausführlichen Text, der aber leider an meiner Frage vorbei geht. Ich brauche keinen Vergleich Webserver mit oder ohne Datenbank.
Für unser Projekt ist eine Datenbank unabdingbar. Die Frage war eigentlich ob es nach der Aussage von JuKU

Außerdem müssen diese Daten zum Client übertragen werden und große Daten brauchen länger, was wiederum den Connection Pool & die Worker Threads blockiert.

einen Unterschied macht, ob ich die Datei als solche irgendwo auf dem Server ablege oder in einem BLOB speichere.
Für mich macht es auf den ersten Blick keinen Unterschied ob ich bspw. ein 100kb image vom server lade, oder das BLOB vom 100kb image (Gemessen habe ich das noch nicht, es ist erstmal nur eine Vermutung).

 

[mihe7]

Ich versuche es noch einmal, vielleicht wird es verständlicher:

Für mich macht es auf den ersten Blick keinen Unterschied ob ich bspw. ein 100kb image vom server lade, oder das BLOB vom 100kb image (Gemessen habe ich das noch nicht, es ist erstmal nur eine Vermutung).

Die Aussage von @JuKu war nicht, dass es länger dauert 100 kb zu übertragen, weil sie aus der DB kommen. Die Aussage war, dass die Übertragung größerer Datenmengen länger dauert und im Fall von BLOBs in dieser Zeit die DB-Verbindung belegt wird. Das kann dazu führen, dass eine Anwendung auf eine Verbindung warten muss, was wiederum dazu führt, dass die Anwendung langsamer wird.

Bei 5 Clients, die sowieso nur eine Connection für alles verwenden, sollte das keine große Rolle spielen.

 

[buggy84]

HI mihe7,

jetzt gibts für mich ein Bild. Das sind wichtige und wertvolle Informationen, die tatsächlich einen impact auf meine Wahl haben, vielen Dank.
Eventuell werde ich das ganze mischen und kleine Dateien als BLOB abspeichern, und größere Daten, wie eventuell Bilder, doch dierekt auf dem Server. Dann ist der Aufwand beim eventuellen Serverumzug nicht ganz so groß.

Außerdem schaue ich mir mal die Vorgehensweise bei unserem Content Management an. Das ist im Grunde ja auch ähnlich.

Vielen Dank nochmal, der Thread und eure Antworten haben mir sehr weiter geholfen.

 

[Dukel]

Ich würde das nicht mischen. Dann musst du programmatisch Unterschiede behandeln und der Aufwand bei einem Serverumzug ändert sich auch nicht. Sobald du DB und FS hast musst du beides umziehen, egal ob 1 GB oder 10 GB an Daten im FS.

 

[JuKu]

Wie auch immer, der Browser arbeitet mit einer bestimmten Anzahl an parallelen Verbindungen/Domain, sagen wir mal 10 (einige haben mehr die anderen weniger).

Schön wärs! Die Standard Browser wie Firefox bauen meines Wissens nur 3 Verbindungen zur selben Domain gleichzeitig auf. Bei 30 Bildern werden also nicht alle 30 Bilder gleichzeitig geladen, sondern immer 3 "zusammen", der Rest hintereinander.

Fiktiv gehen wir mal davon aus, es müssten 30 BLOBs aus der DB geladen werden. Der Browser setzt die ersten 10 Requests ab, Deine Anwendung muss 10 DB-Anfragen parallel stellen, um "maximale" Geschwindigkeit zu erreichen.

Ja, in der Theorie, aber praktisch würde das keiner so programmieren.
Du brauchst auf jeden Fall einen Cache, da deine Datenbank bei 30 Queries pro Aufruf sehr schnell in die Knie gehen würde, falls es genug Aufrufer gibt.

Der Seitenabruf eines einzelnen Benutzers ist somit wahnsinnig kostspielig, wenn man das mit einem Abruf über einen gewöhnlichen Web-Server vergleicht: dort gibt es keine DB, kein SQL, keine zusätzlichen Puffer etc.

Wie gesagt:
Ohne Caching (was kein ordentlicher Entwickler machen würde) wohl schon, mit Caching kannst du auch auf gute Performance-Werte kommen.
Dass ein normaler Webserver ohne DB & Co. schneller ist, ist ja wohl klar.

Und nur um Missverständnisse auszuräumen:
Normalerweise baut man nicht bei jedem Aufruf eine neue Datenbankverbindung auf, sondern diese wird wie gesagt in einem Connectionpool vorgehalten.
In PHP passiert das z.B. schon automatisch im Hintergrund.

Die Aussage von @JuKu war nicht, dass es länger dauert 100 kb zu übertragen, weil sie aus der DB kommen. Die Aussage war, dass die Übertragung größerer Datenmengen länger dauert und im Fall von BLOBs in dieser Zeit die DB-Verbindung belegt wird. Das kann dazu führen, dass eine Anwendung auf eine Verbindung warten muss, was wiederum dazu führt, dass die Anwendung langsamer wird.

Exakt! Es ging mir darum, dass während der Übertragung der Thread- & Connectionpool blockiert wird und sozusagen die anderen Requests währenddessen in einer Art Warteschlange warten müssen.

Eventuell werde ich das ganze mischen und kleine Dateien als BLOB abspeichern, und größere Daten, wie eventuell Bilder, doch dierekt auf dem Server. Dann ist der Aufwand beim eventuellen Serverumzug nicht ganz so groß.

Brauchst du gar nichts! Du kannst schon alles in der Datenbank lagern, aber solltest halt Server-seitig lokal cachen, sodass du nicht bei jedem Abruf die ganzen Bilder neu aus der Datenbank holen musst, sondern z.B. die Bilder nur alle 30 Minuten einmal von der DB geupdatet werden (oder wenn das Bild noch nicht im Cache existiert --> dann musst du es logischerweise auch direkt aus der DB holen und dann cachen).

 

[mihe7]

Die Standard Browser wie Firefox bauen meines Wissens nur 3 Verbindungen zur selben Domain gleichzeitig auf.

Das war einmal. Ursprünglich wurden vom RFC 2 Verbindungen "empfohlen", mittlerweile wird keine konkrete Anzahl mehr genannt. FF dürfte standardmäßig 6 Verbindungen zulassen, kann aber sein, dass das mittlerweile auch nicht mehr stimmt. IE hatte mal über 10.

Du brauchst auf jeden Fall einen Cache, da deine Datenbank bei 30 Queries pro Aufruf sehr schnell in die Knie gehen würde, falls es genug Aufrufer gibt.

Genau das ist ja der Punkt: beim Einsatz der DB werden mehr Ressourcen verbraten, es wird mehr Aufwand betrieben, um zu erreichen, was das Dateisystem sowieso hergibt. In der Regel sollte es genügen, die BLOBs (Dateien) von der "Platte" (OS-Cache) zu lesen.

 

[Bontik]

Das sehe ich auch soo