MYSQL Datenbank zu groß trotz verkleinerter Daten
Thomas Plehn
ich habe eine einfache SQL Tabelle in einer Datenbank Test, die in jeder
Zeile einen BLOB enthält. Die Daten werden per JSP geschrieben. Zuvor
hatte ich große Bilddateien von 2-3 MB größe gespeichert, nun habe ich
diese größtenteils durch meine Java Webapp gegen kleinere Versionen
ausgetauscht (ca. 50kb). Trotzdem wird die SQL Tabelle nicht kleiner,
Heidi SQL zeigt unverändert die alte Größe an.
Als Laie dachte ich mir: Das ist vielleicht genauso wie mit den
Mail-Datenbanken von eMail-Programmen, die man auch von Zeit zu Zeit neu
"komprimieren" (sprich mit gleichen Daten neu aufbauen) muss, aber gibt
es Tools dafür?
Thomas Rachel
> ich habe eine einfache SQL Tabelle in einer Datenbank Test, die in jeder
> Zeile einen BLOB enthält. Die Daten werden per JSP geschrieben. Zuvor
> hatte ich große Bilddateien von 2-3 MB größe gespeichert, nun habe ich
> diese größtenteils durch meine Java Webapp gegen kleinere Versionen
> ausgetauscht (ca. 50kb).
Schau Dir die entstandene Datei an, und Du weißt, warum man so
umfangreiche Daten nur in begründeten AUsnamefällen in eine DB legen sollte.
> Trotzdem wird die SQL Tabelle nicht kleiner,
> Heidi SQL zeigt unverändert die alte Größe an.
Das Handbuch empfiehlt hierfür OPTIMIZE TABLE.
Thomas
Thomas Plehn
> Schau Dir die entstandene Datei an, und Du weißt, warum man so
> umfangreiche Daten nur in begründeten AUsnamefällen in eine DB legen
> sollte.
>
>
> > Trotzdem wird die SQL Tabelle nicht kleiner,
>> Heidi SQL zeigt unverändert die alte Größe an.
>
> Das Handbuch empfiehlt hierfür OPTIMIZE TABLE.
>
Danke! Hat funktioniert. Heidi SQL bietet sogar einen Menüpunkt dazu an.
Sind 25KB immer noch zu viel? Die Qualität ist dann schon ziemlich gammelig.
Claus Reibenstein
> Als Laie dachte ich mir: Das ist vielleicht genauso wie mit den
> Mail-Datenbanken von eMail-Programmen, die man auch von Zeit zu Zeit neu
> "komprimieren" (sprich mit gleichen Daten neu aufbauen) muss, aber gibt
> es Tools dafür?
Dafür brauchst Du keine Tools. Das kann MySQL selber. Der zugehörige
SQL-Befehl lautet:
OPTIMIZE TABLE
Gruß. Claus
Dominik Echterbruch
>
>> Schau Dir die entstandene Datei an, und Du weißt, warum man so
>> umfangreiche Daten nur in begründeten AUsnamefällen in eine DB legen
>> sollte.
>
> gammelig.
Es geht eher darum, dass diese Art von Daten grundsätzlich nicht in ein
RDBMS gehören. Da geht es nicht nur um Dateigrößen, etc., sondern auch
darum, wie performant das ist, welchen Aufwand man treiben muss, um
wieder an die Daten ran zu kommen, etc.
Die Empfehlung lautet, die Dateien in einer Verzeichnisstruktur auf der
Platte abzulegen und nur den Pfad zur Datei in die DB zu schreiben. Wenn
man den Pfad ein bisschen geschickt wählt, kann man sogar auf das
Auslesen des Pfades verzichten und direkt anhand der ID eines
Datensatzes auf die Datei zugreifen. Aber das nur am Rande.
Grüße,
Dominik
--
"Wo kämen wir hin, wenn alle sagten, wo kämen wir hin, und niemand
ginge, um einmal zu schauen, wohin man käme, wenn man ginge."
Kurt Marti
"Nichts ist praktischer, als eine gute Theorie." - Todor Karman
Andreas Scherbaum
Dominik Echterbruch <new...@crosslight.de> wrote:
> Thomas Plehn wrote:
>>
>>> Schau Dir die entstandene Datei an, und Du weißt, warum man so
>>> umfangreiche Daten nur in begründeten AUsnamefällen in eine DB legen
>>> sollte.
>>
>> Sind 25KB immer noch zu viel? Die Qualität ist dann schon ziemlich
>> gammelig.
>
> Es geht eher darum, dass diese Art von Daten grundsätzlich nicht in ein
> RDBMS gehören. Da geht es nicht nur um Dateigrößen, etc., sondern auch
> darum, wie performant das ist, welchen Aufwand man treiben muss, um
> wieder an die Daten ran zu kommen, etc.
>
> Die Empfehlung lautet, die Dateien in einer Verzeichnisstruktur auf der
> Platte abzulegen und nur den Pfad zur Datei in die DB zu schreiben. Wenn
> man den Pfad ein bisschen geschickt wählt, kann man sogar auf das
> Auslesen des Pfades verzichten und direkt anhand der ID eines
> Datensatzes auf die Datei zugreifen. Aber das nur am Rande.
Eine weitere anzudenkende Möglichkeit wären die ganzen dokumentorientierten
Datenbanken, die derzeit den Markt überschwemmen ;-)
CouchDB fällt mir da auf Anhieb ein.
Bis dann
--
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
(Ferenc Mantfeld)
Sam Kang
> Es geht eher darum, dass diese Art von Daten grundsätzlich nicht in ein
> RDBMS gehören. Da geht es nicht nur um Dateigrößen, etc., sondern auch
> darum, wie performant das ist, welchen Aufwand man treiben muss, um
> wieder an die Daten ran zu kommen, etc.
Blödsinn. Das ist deine private Meinung. SQLite z.B. erlaubt Low Level Zugriff
auf die Blobs. Das ist dann meist noch schneller als ein Dateisystemzugriff.
Der Overhead um ein 25 Kb Bild aus in einem Dateisystem zu laden kann
wesentlich komplexer sein wie einen Blob aus Mysql zu laden.
Mal abgesehen von Features wie Replikation.
> Die Empfehlung lautet, die Dateien in einer Verzeichnisstruktur auf der
> Platte abzulegen und nur den Pfad zur Datei in die DB zu schreiben.
Ich weiss nicht wer "Die Empfehlung" von sich gegeben hat, aber du gehörst
sicherlich zu denen denen die M$ und G$ jegliche Kompetenz absprechen.
Besonders wenn deren Webseiten Milliarden von Bilden/Dokumenten in Datenbanken
speichern weil die Dateisysteme ineffizient sind.
> man den Pfad ein bisschen geschickt wählt, kann man sogar auf das
> Auslesen des Pfades verzichten und direkt anhand der ID eines
> Datensatzes auf die Datei zugreifen. Aber das nur am Rande.
Schreib hat mal eine Mio Dateien unter W$ oder ext2/3/4....
Nachlabern von obsoleten FAQs hilft Anfängern auch nicht weiter.
Sam
--
Sufficiently advanced incompetence is indistinguishable from malice
(J. Porter Clark)
Bernd Hohmann
>> Sind 25KB immer noch zu viel? Die Qualität ist dann schon ziemlich
>> gammelig.
>
> Es geht eher darum, dass diese Art von Daten grundsätzlich nicht in ein
> RDBMS gehören. Da geht es nicht nur um Dateigrößen, etc., sondern auch
> darum, wie performant das ist, welchen Aufwand man treiben muss, um
> wieder an die Daten ran zu kommen, etc.
Hm... Wie macht MySQL das? Bei der DB2 lagen die Blobs je nach
Konfiguration einfach als Datei im Dateisystem herum (oder auf einer
eigenen Partition in einem speziellen Format, je nach Wunsch).
Also im Grunde genommen so, wie Du es definiert hast mit dem Vorteil
(oder Nachteil, je nach Sichtweise), dass man den Stream nicht separat
abgreifen musste sondern vom Treiber in die Hand gedrückt kam (was
natürlich die entsprechende DB Instanz belagert hat).
Bernd
--
Life was much easier when Apple and Blackberry were just fruits.
Axel Schwenke
> Dominik Echterbruch schrieb:
>
>> Es geht eher darum, dass diese Art von Daten grundsätzlich nicht in ein
>> RDBMS gehören. Da geht es nicht nur um Dateigrößen, etc., sondern auch
>> darum, wie performant das ist, welchen Aufwand man treiben muss, um
>> wieder an die Daten ran zu kommen, etc.
>
> Blödsinn. Das ist deine private Meinung. SQLite z.B. erlaubt Low Level Zugriff
> auf die Blobs. Das ist dann meist noch schneller als ein Dateisystemzugriff.
Da hast du natürlich ein bisschen Recht, hauptsächlich weil oben
unspezifisch "ein RDBMS" steht. Im Kontext dieser Gruppe (MySQL)
liegst du aber falsch.
> Der Overhead um ein 25 Kb Bild aus in einem Dateisystem zu laden kann
> wesentlich komplexer sein wie einen Blob aus Mysql zu laden.
Nein.
MySQL legt die Daten ja selber im Filesystem ab. Beim Zugriff auf
das BLOB durch den SQL-Layer kommt zwangsläufig Overhead dazu.
So richtig interressant wird das, wenn die Daten anschließend über
das Netzwerk gehen sollen (typisch: Webserver) und diese Software
normalerweise sendfile() verwenden würde. Das schlägt dann den
Zugriff via SQL nicht mehr nur um ein paar %, sondern um um Faktor
2 oder 3.
> Ich weiss nicht wer "Die Empfehlung" von sich gegeben hat, aber du gehörst
> sicherlich zu denen denen die M$ und G$ jegliche Kompetenz absprechen.
Wer ist G$ ?
> Besonders wenn deren Webseiten Milliarden von Bilden/Dokumenten in Datenbanken
> speichern weil die Dateisysteme ineffizient sind.
Wenn M$ und G$ normal Filesysteme verwenden, bei denen der Zugriff
auf ein File langsamer ist als der auf ein BLOB per SQL - dann, ja
dann müßte man ihnen tatsächlich jegliche Kompetenz absprechen.
Bevor ich das tue, würde ich aber lieber erstmal einen Beweis für
deine Behauptung sehen.
Und was die Websites mit den tollen CMS angeht, die jeglichen Content
in einer Datenbank abspeichern UND FÜR JEDEN POPELIGEN ZUGRIFF WIEDER
NEU aus der Datenbank fischen - nach 9/11 gab es einen heftigen Sturm
auf Nachrichten-Websites jeder Art. Eine Menge Websites, die nach o.g.
Schema arbeiteten, sind damals wegen Überlast down gegangen.
Es sind damals extra vollstatische Websites aufgesetzt worden, weil
die genau dieses Problem nicht hatten. Ein paar Leute haben daraus
gelernt. Nicht alle, natürlich.
> Schreib hat mal eine Mio Dateien unter W$ oder ext2/3/4....
Ich kann jetzt nichts über W$ erzählen, aber die ext* Familie von
Filesystemen hat sich schon vor längerer Zeit davon abgewendet,
Verzeichnisse als lineare Listen zu implementieren und können seit
dem vorzüglich auch mit großen Mengen Files umgehen.
XL
Niels Braczek
> Dominik Echterbruch schrieb:
>> Die Empfehlung lautet, die Dateien in einer Verzeichnisstruktur auf der
>> Platte abzulegen und nur den Pfad zur Datei in die DB zu schreiben.
> [...]
> Nachlabern von obsoleten FAQs hilft Anfängern auch nicht weiter.
Zumal die FAQ in diesem Punkt im Januar aktualisiert wurde:
Ist es sinnvoll, Bilder in einer Datenbank abzulegen?
http://www.php-faq.de/q-db-blob.html
MfG
Niels
--
| http://www.kolleg.de · Das Portal der Kollegs in Deutschland |
| http://www.bsds.de · BSDS Braczek Software- und DatenSysteme |
| Webdesign · Webhosting · e-Commerce · Joomla! Content Management |
------------------------------------------------------------------
Dominik Echterbruch
>
>>> Sind 25KB immer noch zu viel? Die Qualität ist dann schon ziemlich
>>> gammelig.
>>
>> Es geht eher darum, dass diese Art von Daten grundsätzlich nicht in
>> ein RDBMS gehören. Da geht es nicht nur um Dateigrößen, etc., sondern
>> auch darum, wie performant das ist, welchen Aufwand man treiben muss,
>> um wieder an die Daten ran zu kommen, etc.
>
> Hm... Wie macht MySQL das? Bei der DB2 lagen die Blobs je nach
> Konfiguration einfach als Datei im Dateisystem herum (oder auf einer
> eigenen Partition in einem speziellen Format, je nach Wunsch).
Leider ist MySQL an der Stelle nicht ganz so effizient. Hier werden die
BLOBs gemeinsam mit den übrigen Daten abgelegt. Das gilt IIRC für alle
derzeit unterstützten Tabellentypen.
Sehr interessant ist aber auch die Frage, was man anschließend mit den
Daten machen will. Üblicherweise wird man Bilddaten ja nicht aus der DB
holen, um sie zu verarbeiten und wieder zu speichern. Das passiert
natürlich auch ab und an mal, aber im Allgemeinen werden sie dann doch
eher an andere Prozesse weiter gegeben oder aber an einen Browser gesendet.
In letzterem Fall ist es deutlich effizienter, den anderen Prozess (also
z.B. Apache) direkt an einen Dateipfad zu verweisen, als ein Programm zu
beauftragen, die Daten aus der DB zu lutschen und dann auszugeben.
Voraussetzung ist natürlich eine brauchbare Verzeichnisstruktur auf der
Platte. Sonst wird's schnell langsam ;)
Dominik Echterbruch
>
>>> Die Empfehlung lautet, die Dateien in einer Verzeichnisstruktur auf der
>>> Platte abzulegen und nur den Pfad zur Datei in die DB zu schreiben.
>> [...]
>
>
> Ist es sinnvoll, Bilder in einer Datenbank abzulegen?
> http://www.php-faq.de/q-db-blob.html
Ah, gut zu wissen. Danke.
Axel Schwenke
> Hm... Wie macht MySQL das? Bei der DB2 lagen die Blobs je nach
> Konfiguration einfach als Datei im Dateisystem herum (oder auf einer
> eigenen Partition in einem speziellen Format, je nach Wunsch).
Bei MySQL hängt das von der Storage Engine ab. Bei MyISAM ist ein
Record ein großer Klumpen Daten und wird entweder am Stück oder auch
in Chunks (wenn das Datenfile fragmentiert ist) in das Datenfile
geschrieben.
InnoDB speichert BLOBs außerhalb der Row auf dezidierten BLOB-Seiten
(als Liste von Chunks $Pagesize-$Overhead)
Cluster speichert die ersten 256 Bytes eines BLOBs in der Row und den
Rest in 2K Chunks in einer extra Tabelle. etc. pp
Aber in keinem Fall kann man auf BLOBs von außerhalb zugreifen. Es
gibt auch kein dezidiertes BLOB-API. Für nonprepared Statements liegt
der BLOB als (entsprechend escaped'ter) String vor. Beim Lesen oder
für prepared Statements bekommt man immerhin einen binären Buffer.
Und man kann BLOBs chunkweise schreiben (aber nicht lesen).
XL
Sam Kang
>> Ich weiss nicht wer "Die Empfehlung" von sich gegeben hat, aber du gehörst
>> sicherlich zu denen denen die M$ und G$ jegliche Kompetenz absprechen.
>
> Wer ist G$ ?
Google?
Die speichern ihre 8.000.000.000+ gecachten Dokumente alle im Dateisystem?
Btw. IBM ist daher auch doof weil deren Mainframesysteme eigentlich
Datenbanken mit OS Funktionen. Oracle verdient kein Geld wiel niemand[tm]
Documente, Fingerabdrücke, Krankenakten, *akten in Datenbanken ablegt.
>> Schreib hat mal eine Mio Dateien unter W$ oder ext2/3/4....
>
> Ich kann jetzt nichts über W$ erzählen, aber die ext* Familie von
> Filesystemen hat sich schon vor längerer Zeit davon abgewendet,
> Verzeichnisse als lineare Listen zu implementieren und können seit
> dem vorzüglich auch mit großen Mengen Files umgehen.
Schon mal probiert 50 Millionen Kacheln a la maps.*.com zu speichern?
Upzudaten? Löschen? Backup? Viel spass.
Bei MySql sind das gerade mal ein Paar GB. Absolut kein Problem[tm] für Mysql.
Geeignetes Tabellendesign vorrausgesetzt.
Sam Kang
> Zumal die FAQ in diesem Punkt im Januar aktualisiert wurde:
>
> Ist es sinnvoll, Bilder in einer Datenbank abzulegen?
> http://www.php-faq.de/q-db-blob.html
Deswegen wird die FAQ weder richtiger noch qualifizierter. Sogar das Fazit
widerlegt sich:
<cite>
Sind die Anforderungen hingegen höher, muss dies bereits bei der Konzeption
bedacht und sorgfältig geprüft werden.
</cite>
In paar Bilder eines pseudo CMS in eine DB zu packen mag nicht sinnvoll sein.
Bei Bilddatenbanken aber sehr wohl.
Also erst prüfen dann entscheiden. Wofür auch immer - Worstcase-Scenarios
helfen niemanden.
Niels Braczek
> Niels Braczek schrieb:
>
>> Zumal die FAQ in diesem Punkt im Januar aktualisiert wurde:
>>
>> Ist es sinnvoll, Bilder in einer Datenbank abzulegen?
>> http://www.php-faq.de/q-db-blob.html
>
> Deswegen wird die FAQ weder richtiger noch qualifizierter.
Widerlege bitte die in deinen Augen falschen Angaben. Wenn das fundiert
ist, haben weder Torsten noch ich ein Problem damit, den Artikel anzupassen.
> Sogar das Fazit
> widerlegt sich:
>
> <cite>
> Sind die Anforderungen hingegen höher, muss dies bereits bei der Konzeption
> bedacht und sorgfältig geprüft werden.
> </cite>
Willst du damit dagen, dass es sinnvoller ist, *ohne* Konzept daran zu
gehen?
> In paar Bilder eines pseudo CMS in eine DB zu packen mag nicht sinnvoll sein.
> Bei Bilddatenbanken aber sehr wohl.
>
> Also erst prüfen dann entscheiden. Wofür auch immer
Das genau ist doch Fazit des FAQ-Artikels.
> - Worstcase-Scenarios
> helfen niemanden.
Exteme helfen zu verdeutlichen. Unstrittig ist jedoch, dass der
Auslieferungsaufwand bei der DB-Lösung erheblich höher ist, während die
Verwaltung in vergleichbarem Maße einfacher wird. Das muss man *im
Einzelfall* gegeneinander abwägen. Und zwar - wie Torsten in dem
FAQ-Artikel richtig anmerkt - bereits bei der Konzeption.
Sam Kang
> Widerlege bitte die in deinen Augen falschen Angaben. Wenn das fundiert
> ist, haben weder Torsten noch ich ein Problem damit, den Artikel anzupassen.
Zum Beispiel der Teil der Cache Validierung. Wenn man den Images(Dateie) eine
entsprechende "Expires" bei der Erstübertagung mitgibt wird garnicht erst
nachgefragt und der angegebene Nachteil verpuff. Die (in der Regel) dynamische
Webseite muss halt bei Änderung des Images(Dateie) eine neue imageID senden.
Spart sogar dem Server noch eine überflüssige Anfrage.
>> Sogar das Fazit
>> widerlegt sich:
>>
>> <cite>
>> Sind die Anforderungen hingegen höher, muss dies bereits bei der Konzeption
>> bedacht und sorgfältig geprüft werden.
>> </cite>
>
> Willst du damit dagen, dass es sinnvoller ist, *ohne* Konzept daran zu
> gehen?
Nein ich sagte erst denken dann entscheiden. In der FAQ wird die Entscheidung
vorweggenommen.
>> - Worstcase-Scenarios
>> helfen niemanden.
>
> Exteme helfen zu verdeutlichen. Unstrittig ist jedoch, dass der
> Auslieferungsaufwand bei der DB-Lösung erheblich höher ist, während die
Nein, hier werden Extreme genannt die durch falsche Konzepte entstanden sind.
Siehe oben.
> Verwaltung in vergleichbarem Maße einfacher wird. Das muss man *im
> Einzelfall* gegeneinander abwägen. Und zwar - wie Torsten in dem
> FAQ-Artikel richtig anmerkt - bereits bei der Konzeption.
Ja aber bitte den gesamten Technikstrang. Dazu gehören auch die Möglichkeiten
des Apachen, der Scriptsoftware sowie der Anwendungsoftware - falls es sich
überhaupt um eine Webanwendung handelt.
Axel Schwenke
> Axel Schwenke schrieb:
>
>>> Ich weiss nicht wer "Die Empfehlung" von sich gegeben hat, aber du gehörst
>>> sicherlich zu denen denen die M$ und G$ jegliche Kompetenz absprechen.
>>
>> Wer ist G$ ?
>
> Google?
Aha. Dann sag das doch.
> Die speichern ihre 8.000.000.000+ gecachten Dokumente alle im Dateisystem?
Ich weiß nicht wo sie die speichern. Aber ich bin mir *sehr* sicher,
daß Google praktisch keinen Content direkt aus einer SQL-Datenbank
serviert. Und schon gar nicht werden sie BLOBs aus dem Filesystem in
eine Datenbank verlagern, weil das schneller wäre.
Genau das behauptest du aber (ohne den Hauch eines Belegs)
Was Google macht, sind riesige Clouds, massives Sharding und dann
versuchen, alle relevanten Daten auf wenigstens einem Knoten im
RAM zu halten.
> Btw. IBM ist daher auch doof weil deren Mainframesysteme eigentlich
> Datenbanken mit OS Funktionen.
Du wirst nicht ernsthaft DB2 mit MySQL vergleichen wollen. Aber in
der Tat ist DB2 (auf dem Mainframe) eine Besonderheit, weil *sehr*
tief in das OS integriert.
> Oracle verdient kein Geld wiel niemand[tm]
> Documente, Fingerabdrücke, Krankenakten, *akten in Datenbanken ablegt.
Das hab gar nicht behauptet. Aber sogar Oracle hat es nicht auf die
Reihe gebracht, Filesysteme durch ihre Datenbank zu ersetzen. Vor
etlichen Jahren haben sie solche Pläne mal angekündigt. Auch Microsoft
wollte mal eine Filesystem/Datenbank Chimäre rausbringen. Alles nur
heiße Luft.
Datenbanken sind *anders* als Filesysteme. Und damit für *manche*
Aufgaben auch besser. Wenn die Aufgabe ist "ein PRIMARY KEY, ein BLOB"
dann sind Filesysteme in der Regel die bessere Lösung, es sei denn du
hast zusätzliche Randbedingungen.
Und genau so "diskutierst" du die ganze Zeit. Du bringst zusätzliche
Randbedingungen rein und sagst dann "aber dann bin ich besser". Das
ist unehrlich.
>> Ich kann jetzt nichts über W$ erzählen, aber die ext* Familie von
>> Filesystemen hat sich schon vor längerer Zeit davon abgewendet,
>> Verzeichnisse als lineare Listen zu implementieren und können seit
>> dem vorzüglich auch mit großen Mengen Files umgehen.
>
> Schon mal probiert 50 Millionen Kacheln a la maps.*.com zu speichern?
> Upzudaten? Löschen? Backup? Viel spass.
>
> Bei MySql sind das gerade mal ein Paar GB. Absolut kein Problem[tm] für Mysql.
> Geeignetes Tabellendesign vorrausgesetzt.
So so. 50 Mio Kacheln sind "ein Paar GB" (Rechtschreibfehler von dir
kopiert). Also <1KB pro Kachel. Etwa vergleichbar mit einem Mail-
oder News-Spool. Sowas halten etliche Leute vollkommen problemlos in
Filesystemen. Geeignetes Layout vorausgesetzt.
Bevor du weiter "diskutierst": zeig mir doch bitte erst mal einen Fall,
wo eine SQL-Tabelle aus (PK, BLOB) einem Filesystem überlegen ist.
XL
Kristian Köhntopp
> Blödsinn. Das ist deine private Meinung. SQLite z.B. erlaubt Low Level
> Zugriff auf die Blobs. Das ist dann meist noch schneller als ein
> Dateisystemzugriff.
Das ist es niemals.
Wenn Du statische Dateien mit einem modernen Webserver aus dem
Dateisystem lädst, dann wird dabei der sendfile(2) Systemaufruf
verwendet. Dieser Systemaufruf nimmt einen in_fd Filedescriptor und
sendet count viele Bytes ab Offset offset auf diesem Filedescriptor in
out_fd.
Im gängigen Fall ist out_fd Dein Netwerk-Socket und in_fd der
Filedescriptor einer Datei, etwa eines Bildes, das bei einem gut
dimensionierten Fileserver auch schon im File System Buffer Cache, also
im RAM liegt. In diesem Fall verschwindet der Webserver mit dem
sendfile(2) Systemaufruf im Kernel und kehrt in den Webserver zurück,
wenn die Datei komplett versendet worden ist. Hat man eine
Netzwerkkarte mit geeigneter DMA gar eigener Offload-Engine, dann
kostet das Ausliefern einer solchen Datei gar nichts an Kontextwechseln
oder CPU, und selbst bei einer dummen Netzwerkkarte können die
FSBC-Pages direkt an die Netzwerkkarte gesendet werden (Zero Copy I/O).
Legt man dieselbe Bilddatei als BLOB in einem Datenbankserver ab, dann
muß die Auslieferung des Bildes mit Hilfe einer Programmiersprache wie
PHP oder Java erfolgen, denn der Webserver selber kann zwar Dateien aus
dem Dateisystem ausliefern, nicht jedoch Daten aus einer Datenbank
pulen. Du landest also im PHP-Modul Deines Apache und hast dort die
Aufgabe, einen Webrequest in ein SQL-Statement umzuwandeln. Das SQL via
Socket an den Datenbankserver gesendet und dort geparsed.
Die Datenbank wandelt dann die Bild-ID in eine Liste von
Datenbank-Blöcken um, die aus dem File System Buffer Cache in den
Speicher der Datenbank kopiert werden, dort in das Netzwerk-Format des
Datenbank-Protokolles konvertiert, und dann Block für Block unter
vielen Kontextwechseln via Socket in das PHP kopiert. Es existieren
also Kopien der Blöcke im File System Buffer Cache, im User-Kontext der
Datenbank. im Kernel-Kontext als Netzwerk-Puffer und im
Datenbank-Bibliothek-Kontext des PHP/Apache-Prozeß als
Datenbank-Resultat. Datenbank-Resultate sind keine PHP-ZVAL-Strukturen,
es sei denn, man verwendet mysqlnd. Das heißt, beim Holen der Resultate
mit mysql_fetch_wasimmer() werden die Daten noch ein weiteres Mal aus
dem libmysql-Kontext in PHP-ZVAL umkopiert.
PHP zimmert daraus dann wieder ein Bild, und das Bild wird noch ein
letztes Mal in den Kernel kopiert, um endlich im outfd des Apache zu
verschwinden.
Selbst mit mysqlnd ist das ein gar schauerlicher Overhead von einem
Haufen sinnlosen memcpy-Aufrufen, ein guter Teil davon zwischen User-
und Kernelspace.
> Ich weiss nicht wer "Die Empfehlung" von sich gegeben hat, aber du
> gehörst sicherlich zu denen denen die M$ und G$ jegliche Kompetenz
> absprechen. Besonders wenn deren Webseiten Milliarden von
> Bilden/Dokumenten in Datenbanken speichern weil die Dateisysteme
> ineffizient sind.
Dateisysteme sind für die Speicherung von BLOBs gemacht und sie kommen
ausgezeichnet mit einer großen Anzahl von Objekten in den
unterschiedlichsten Größen zurecht.
Traditionelle UNIX-Dateisysteme hatten eine Zeit lang mit vielen
Objekten in einem einzelnen Verzeichnis Probleme. Das ist ungefähr der
Stand von 1984, also von BSD FFS aka Sun ufs und Linux ext2.
Seit 1994, also seit SGI xfs, Linux ext3 und Reiserfs 3.6 haben wir als
Standard bei Verzeichnissen B-Bäume und es ist kein Problem, einige
Millionen Objekte in einem einzelnen Verzeichnis zu haben. Hat das
Dateisystem Tail Packing (wie zum Beispiel Reiserfs 3.6), dann ist es
sogar kein Problem, einige Millionen sehr kleine Objekte zu haben - die
Fragmentierung ist minimal.
Und mit Dateisystemen von 2004, also etwa ZFS und BTRFS und den dort
seit neustem vorhandenen Deduplication Technologien ist es sogar kein
Problem mehr, einige Millionen sehr kleine identische Objekte zu haben.
Noch dazu ist es mit Hilfe von SGI fam (Linux dnotify/inotify, MacOS
FSEvents etc) möglich, ein Dateisystem so zu überwachen, daß man
referentielle Integrität sicherstellen kann (In Mac OS X zum Beispiel
überwacht FSEvents ganze Dateisysteme so, daß Time Machine schnell
weiß, welche Dateien im inkrementellen Backup zu sichern sind ohne daß
das ganze Dateisystem nach geänderten mtimes gescanned werden muß).
Im Fall von MySQL kommt noch dazu, daß die Datenbank selber noch immer
keine BLOB API hat (BLOBs also nicht effizient partiell angefaßt werden
können) und daß max_allowed_packet ein sehr enges oberes Limit für die
Objektgröße setzt. http://forums.mysql.com/read.php?94,159458 verweist
auf http://www.blobstreaming.org/ und das Projekt ist grad mal wieder
tot.
Wäre es das nicht, würde man eventuell irgendwann einmal sendfile(2)
auf MySQL Datenbankobjekte anwenden können (wenn auch nicht 100% mit
der Effizienz eines Kernel-Dateisystem-Treibers).
Kris
Kristian Köhntopp
> Schon mal probiert 50 Millionen Kacheln a la maps.*.com zu speichern?
Ja, das habe ich.
Ok es waren nur 36 Millionen Dateien. Aber dafür war es auch nur ein
lahmer Strato MR2 von vor vier Jahren und eine einzige Platte statt
eines Arrays.
Den Benchmark findest Du hier:
http://mysqldump.azundris.com/archives/37-Serving-Images-from-a-File-System.html
und
die Eckdaten sind:
Eine Platte http://www.hitachigst.com/hdd/support/d7k250/d7k250.htm
(160G) mit 8.8ms Seek Time, als 113 Seek/s. Dabei bekomme ich mit
Reiserfs 3.6 320000 Files pro Sekunde erzeugt, sustained über 36 Mio
Files. Dabei wird die Platte zu etwa 50% ausgelastet (mit %util von
iostat gemessen). Beim Lesen wird eine Read-Rate von etwa 45000
Dateien/s aus dem Speicher abgefeiert, von Platte bekomme ich ca. 5000
Dateien/s geöffnet.
Das mit einem Perl-Script, da ist sicher auch noch Optimierungspotential.
Reiserfs 3.6 wurde verwendet, da es Tail Packing hat und B-Bäume als
Verzeichnisstrukturen verwendet, weil es Metadaten schnell
aktualisieren kann und weil es logbasierend ist, also schnell aus einem
Crash recovern kann.
Heute würde man vermutlich xfs (kein Tail Packing, sonst vergleichbar),
btrfs (sehr verschieden, aber vermutlich dennoch sehr schnell) oder
ext4 verwenden.
Meine Erkklärung aus dem anderen Artikel findest Du auch noch mal in
länger in
http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database.html.
Will
man Datenbanken, Replikation und sendfile(2)-Optimierungen kombinieren,
dann würde man in etwa so wie in
http://mysqldump.azundris.com/archives/59-Statification.html
geschildert vorgehen. Dieser Ansatz hat den Nachteil, daß er das
Datenvolumen in mindestens verdoppelt, da die Daten einmal in einer
Datenbank und einmal in einem lokalen Dateisystem liegen.
Dazu kommt noch, daß Datenbanken die Daten selber nicht sehr dicht
packen - speziell InnoDB erzeugt eine vergleichsweise geringe
Datendichte, insbesondere wenn BLOB oder TEXT-Typen verwendet werden.
Der Overhead wäre also wahrscheinlich eher so Faktor 3-4.
Kris
Kristian Köhntopp
> Hm... Wie macht MySQL das? Bei der DB2 lagen die Blobs je nach
> Konfiguration einfach als Datei im Dateisystem herum (oder auf einer
> eigenen Partition in einem speziellen Format, je nach Wunsch).
In MyISAM sind die BLOBs Teil der Row eines Records. Durch einen BLOB
wird das Row Format der MyISAM-Tabelle also in jedem Fall Dynamic, da
die Recordlänge der Records variabel wird.
In InnoDB gibt es verschiedene Formate (REDUNDANT, COMPACT, DYNAMIC und
COMPRESSED), die in verschiedenen Versionen von InnoDB zum Einsatz
kommen.
Grundsätzlich organisiert InnoDB die Daten in 16 KB Seiten. Eine Seite
hat einen Page Header, Platz für die Daten und einen Page Footer. Im
Footer steht unter anderem eine Seitenprüfsumme, im Datenraum stehen
die Rows, und jede Menge freier Platz.
Im Header ist unter anderem das Page Directory enthalten. Dort findest
Du für jede Row der Page einen Zeiger auf den Anfang der Row in der
Seite. Dadurch ist es möglich, Rows in einer Seite zu verlängern, ohne
daß die Row-Adresse sich von außerhalb der Seite gesehen ändern würde.
Andere Seiten haben also Adressen der Form "Page 17, Row 3" - in der
Page steht dann im Page Directory, wo genau in der Page Row 3
gespeichert ist. Wird Row 3 verlängert ohne daß die Seite überläuft,
kann es sein, daß die Position von Row 3 oder anderen Rows in der Seite
sich verändern, aber die Row-Adresse bleibt weiter (17, 3).
In InnoDB muß eine Row so klein sein, daß mindestens 2 Rows in eine
Seite passen. Die Ausnahme von dieser Regel sind variable length
columns, also etwa VARCHAR und BLOB. Diese Spalten können ganz oder in
Teilen in anderen Pages ('overflow pages') gespeichert werden.
Antelope InnoDB kennt REDUNDANT und COMPACT Row Formats. Hier hat
InnoDB die ersten bis zu 768 Bytes einer variable length column im
Record gespeichert und nur die Bytes der Spalten, die noch länger waren
in overflow pages abgelegt. Der Vorteil ist, daß man beim Lesen solcher
kurzer Blobs keine weiteren Seeks erzeugt. Der Nachteil ist, daß man
recht wenige Rows in einer Page unter bekommt und daher die Datendichte
sehr gering ist.
Barracuda InnoDB kennt das Row Format DYNAMIC und COMPRESSED. In diesen
Formaten werden breite Spalten komplett in Overflow Pages abgelegt. Im
Record selbst findet man nur einen Zeiger auf diese Daten, er belegt 20
Bytes. Welche Spalten InnoDB als 'breit' ansieht ist nicht direkt
kontrollierbar - es hängt von der Seitengröße (Default: 16K) und der
Row Size ab. Wenn InnoDB eine Zeile als zu breit ansieht werden die
breitesten Spalten zuerst in Overflow Pages verschoben. Jede Spalte,
die verschoben wird, bekommt jedoch eine eigene Overflow Page, sodaß
wir hier eine ganze Menge Verschnitt haben (InnoDB kann nicht das, was
in einigen Dateisystemen als Tail Packing bezeichnet wird).
Bei COMPRESSED wird außerdem zlib auf die Row angewendet.
Kris
Kristian Köhntopp
> Leider ist MySQL an der Stelle nicht ganz so effizient. Hier werden die
> BLOBs gemeinsam mit den übrigen Daten abgelegt. Das gilt IIRC für alle
> derzeit unterstützten Tabellentypen.
Siehe mein anderes Posting, in InnoDB ist der Sachverhalt
komplizierter. In InnoDB Barracuda sehr viel komplizierter.
Kris
Bernd Hohmann
> On 2010-04-14 14:24:27 +0200, Sam Kang said:
>> Blödsinn. Das ist deine private Meinung. SQLite z.B. erlaubt Low Level
>> Zugriff auf die Blobs. Das ist dann meist noch schneller als ein
>> Dateisystemzugriff.
>
> Das ist es niemals.
Keine Ahnung, was Du da geraucht hast - ich will es aber auch haben.
Relevant bei der Betrachtung sind nur 3 Parameter:
1) Anzahl der CPU- und IO-Zyklen bis gefunden hat, was mach gesucht hat
2) Den ersten Node des BLOBs finden
3) Auslieferung des Streams selber.
Datenbanken werden auf (1) optimiert und schlagen damit jedes Dateisystem.
(2) ist eine Sache der Datenablage (DB2 muss auf den selbstorganisierten
Partitionen einen BLOB nicht suchen sondern weiss anhand des Datensatzes
wo er ist).
Der Punkt 3 hängt davon ab, wie mutig man ist: eine dafür ausgelegte
Datenbank kann das Resultset abschliessen und die Instanz freigeben -
aber trotzdem den Stream separat wegnudeln (die paar Locks dafür zu
setzen sind nicht so schwierig).
Dass die meissten Datenbanken daran scheitern, dass sie nach finden des
Blobs erstmal zusätzlich das Dateisystem befragen müssen um ein Handle
zu bekommen und die Instanz für das Ausliefern des Streams nicht
freigeben wollen ist eine Sache des Designs - aber kein grundsätzliches
Problem.
Kristian Köhntopp
> Hallo,
>
> ich habe eine einfache SQL Tabelle in einer Datenbank Test, die in
> jeder Zeile einen BLOB enth�lt. Die Daten werden per JSP geschrieben.
> Zuvor hatte ich gro�e Bilddateien von 2-3 MB gr��e gespeichert, nun
> habe ich diese gr��tenteils durch meine Java Webapp gegen kleinere
> Versionen ausgetauscht (ca. 50kb). Trotzdem wird die SQL Tabelle nicht
> kleiner, Heidi SQL zeigt unver�ndert die alte Gr��e an.
Welche Storage Engine hast Du verwendet?
Wenn Du das nicht wei�t, hast Du die MyISAM Storage Engine verwendet.
In MyISAM ist es so, da� beim Speichern von BLOBs das Row Format
DYNAMIC verwendet wird. In diesem Fall besteht Deine MYD Datei aus
Records unterschiedlicher l�nge und in Deiner MYI-Datei werden Index
Records erzeugt, die Positionen von Datens�tzen in der MYD-Datei mit
Byte-Offsets bezeichnen. Die Byte Offsets sind per Default 6 Byte (gut
f�r 256TB) gro�, aber Du kannst den Default
root@localhost [(none)]> show global variables like '%pointer%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| myisam_data_pointer_size | 6 |
+--------------------------+-------+
1 row in set (0.00 sec)
�ndern oder pro Tabelle mit MAX_ROWS einen anderen Wert indirekt bestimmen.
Wenn Du Datens�tze aus Deiner MYD-Datei l�scht, indem Du DELETE
verwendest oder indem Du den Record mit UPDATE verkleinerst oder
vergr��erst, wird die Gr��e der Datei nicht ver�ndert. Es entstehen
stattdessen L�cken von freiem Platz in der MYD-Datei, die sp�ter wieder
verwendet werden. Die Summe aller L�cken kannst Du mit SHOW TABLE
STATUS LIKE 'tablename' anzeigen lassen - der Wert Data_free sagt Dir,
wie viel Platz in Deiner MYD-Datei derzeit frei ist, sagt aber nichts
�ber die Fragmentierung dieses Platzes aus.
Um die Datei zu verkleinern mu�t Du das Kommando OPTIMIZE TABLE auf die
Tabelle anwenden, oder die Tabelle 'in sich selbst' konvertierten
('ALTER TABLE tablename ENGINE = MyISAM'). In beiden F�llen erzeugt
MySQL eine verdeckte neue Version der Tabelle, kopiert die brauchbaren
Daten aus der alten Version der Tabelle in die neue Version, benennt
dann die alte Version der Tabelle in etwas verdecktes um, benennt
danach die neue Version der Tabelle in das sichtbare 'tablename' um,
und droppt schlie�lich die alte Version der Tabelle. Zwischendurch kann
dadurch eine ganze Menge Platz verbraucht werden, und der Vorgang kann
unakzeptabel lange dauern.
Du kannst das Problem auch einfach ignorieren und Daten in die
MYD-Datei einf�gen. Wenn die Fragmentierung nicht gar zu schlimm ist,
f�llten die neuen Records die L�cken irgendwann auf und die Tabelle
w�chst ohne da� die Datei dazu gr��er wird.
Wie Du erkennen kannst, ist MyISAM haarstr�ubend ineffizient. Wenn Du
einen VARCHAR hast, dann sitzt dieser pa�genau in der Mitte zwischen
anderen Rows. Wenn Du den VARCHAR (oder BLOB) jetzt vergr��erst, dann
ist hinten kein Platz vorhanden, um den VARCHAR zu verl�ngern. MyISAM
verschiebt den Record komplett an das Ende der Tabelle, um ihn dort zu
verl�ngern. An der alten Position bleibt eine L�cke zur�ck.
Da MyISAM au�erdem in allen Indices den Record mit einem Byteoffset
bezeichnet, und da sich dieser Byteoffset gerade ge�ndert hat, m�ssen
nun die Byteoffsets f�r diesen Record in allen Indices aktualisiert
werden. MyISAM Index Pages sind 1KB klein - f�r jeden Index der Tabelle
hast Du nun also eine dirty key page, die irgendwann einmal zur�ck
geschrieben werden mu� - wir haben einen Index Update Storm.
root@localhost [(none)]> show global status like 'key_blocks_not%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0 |
+------------------------+-------+
1 row in set (0.01 sec)
Wenn Dir MyISAM crashed w�hrend dieser Z�hler nicht 0 ist, sind Deine
Indices f�r diese Tabelle im Eimer und REPAIR TABLE braucht so seine
Zeit. Wenn Du
root@localhost [(none)]> show global variables like 'delay_key%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| delay_key_write | ON |
+-----------------+-------+
1 row in set (0.00 sec)
auf ALL setzt (oder auf ON hast und die Tabelle mit Delayed Key Writes
definiert ist, siehe CREATE TABLE im Handbuch), dann werden diese Index
Updates so gut wie nie zur�ck geschrieben, und ein Crash von MyISAM
zerrei�t Dir garantiert alle Indices. Aber immerhin sind die Updates
sch�n schnell.
InnoDB hat eine komplizertere interne Struktur, die aber darauf
angelegt ist, die �nderungen am Prim�rschl�ssel und an den Sekund�ren
Schl�sseln zu minimieren und so die Anzahl der Pages, die durch Updates
Dirty werden klein zu halten. Dadurch kommt InnoDB mit wachsenden
Records und sich �ndernden Daten (OLTP Load) viel besser zurecht.
In MySQL 5.5.4 und h�her wird InnoDB die Default Storage Engine sein.
In Drizzle ist sie es schon seit mehr als einem Jahr.
Kris
Kristian Köhntopp
> Kristian K�hntopp wrote:
>> On 2010-04-14 14:24:27 +0200, Sam Kang said:
>>> Dateisystemzugriff.
>> Das ist es niemals.
>
> Keine Ahnung, was Du da geraucht hast - ich will es aber auch haben.
oprofile.
> Relevant bei der Betrachtung sind nur 3 Parameter:
>
> 1) Anzahl der CPU- und IO-Zyklen bis gefunden hat, was mach gesucht hat
> 2) Den ersten Node des BLOBs finden
> 3) Auslieferung des Streams selber.
Lies den Artikel von mir, den Du da quotest noch mal. In realen
Betriebssituationen ist mehr relevant, da sonst sendfile(2) nicht zum
Zuge kommen kann. Ohne sendfile(2) hast Du Datenkopien bis der Arzt
kommt (eine pro Proze� und zwei im Kernel plus was immer sonst noch so
kaputt ist an der API).
> Datenbanken werden auf (1) optimiert und schlagen damit jedes Dateisystem.
Die Datenstruktur im Dateisystem ist ein B-Baum, genau wie in der
Datenbank. Die Datenbank hat hier keine Vorteile bei der
Datenorganisation.
> Der Punkt 3 h�ngt davon ab, wie mutig man ist: eine daf�r ausgelegte
> Datenbank kann das Resultset abschliessen und die Instanz freigeben -
> aber trotzdem den Stream separat wegnudeln (die paar Locks daf�r zu
> setzen sind nicht so schwierig).
Das ist das, wof�r sendfile(2) genau worden ist, nur da� es als
Kernel-Funktion den Vorteil hat, Zero Copy I/O machen zu k�nnen. Das
kann ein Userland-Proze� prinzipbedingt nicht leisten.
Kris
Bernd Hohmann
>> Keine Ahnung, was Du da geraucht hast - ich will es aber auch haben.
>
> oprofile.
> [...]
> Lies den Artikel von mir, den Du da quotest noch mal.
Ich?
Ich schärfe jetzt meine Kettensäge um morgen Thujas zu schneiden weil es
mir völlig Banane ist was Du an Linuzentrischen Weisheiten schreibst.
Kristian Köhntopp
> Ich schärfe jetzt meine Kettensäge um morgen Thujas zu schneiden weil
> es mir völlig Banane ist was Du an Linuzentrischen Weisheiten schreibst.
Dies ist eine MySQL Newsgroup. In der Zeit, in der ich MySQL-Kunden
betreut habe, waren alle MySQL-Deployments, die ich gesehen habe, auf
einer Form von Linux, bis auf fünf, die Windows verwendet haben (zwei
wegen Gerätetreibern und die anderen drei waren Migrationen nach
Linux), eins auf FreeBSD und eins auf Solaris.
Die o.a. Betrachtungen betreffend sendfile(2) gelten für Linux, Solaris
und wahrscheinlich auch für FreeBSD, wobei ich bei den letzteren beiden
nicht weiß, ob sie Zero oder Single Copy I/O machen.
Kris
Axel Schwenke
> Kristian Köhntopp wrote:
>> On 2010-04-14 14:24:27 +0200, Sam Kang said:
>>> Blödsinn. Das ist deine private Meinung. SQLite z.B. erlaubt Low Level
>>> Zugriff auf die Blobs. Das ist dann meist noch schneller als ein
>>> Dateisystemzugriff.
>>
>> Das ist es niemals.
> Relevant bei der Betrachtung sind nur 3 Parameter:
> 1) Anzahl der CPU- und IO-Zyklen bis gefunden hat, was mach gesucht hat
> 2) Den ersten Node des BLOBs finden
> 3) Auslieferung des Streams selber.
> Datenbanken werden auf (1) optimiert und schlagen damit jedes Dateisystem.
Aber nicht, wenn der Ordungsparameter ein einteiliger Key ist. Dann
verwenden Filesysteme (aus diesem Jahrtausend) und Datenbanken jeweils
einen B-Tree und sind über den Daumen gepeilt gleich schnell.
Das Filesystem hat dabei noch einen Vorteil: wenn es (Index)Blöcke vom
I/O Layer anfordert, muß es dazu keinen Usermode/Kernelmode Wechsel
machen.
> (2) ist eine Sache der Datenablage (DB2 muss auf den selbstorganisierten
> Partitionen einen BLOB nicht suchen sondern weiss anhand des Datensatzes
> wo er ist).
Das Filesystem ist an dieser Stelle schon fertig. Hmm. Die Datenbank
eigentlich auch. Oder was glaubst du denn, was ein Index referenziert,
wenn nicht die Lokation der Daten?
> Der Punkt 3 hängt davon ab, wie mutig man ist: eine dafür ausgelegte
> Datenbank kann das Resultset abschliessen und die Instanz freigeben -
> aber trotzdem den Stream separat wegnudeln
Genau. EINE DAFÜR AUSGELEGTE DATENBANK
Da fällt mir genau eine ein, die das überhaupt können kann. DB2
Komischer Zufall, was?
Und das, wo wir hier(!) eigentlich über MySQL reden und oben ganz
spezifisch SQLite genannt wurde. Also wenn du mitdiskutieren willst,
dann bleib doch bitte beim Thema.
> Dass die meissten Datenbanken daran scheitern, dass sie nach finden des
> Blobs erstmal zusätzlich das Dateisystem befragen müssen um ein Handle
> zu bekommen und die Instanz für das Ausliefern des Streams nicht
> freigeben wollen ist eine Sache des Designs - aber kein grundsätzliches
> Problem.
Klar doch. Daß die meisten Firmen im Webumfeld bei der Beschaffung
ihrer Datenbank-Hard- und Software eher bei Linux auf x86_64 und MySQL
landen statt bei DB2 auf dem Mainframe, ist nur eine Sache der Beweg-
lichkeit zwischen Daumen und Zeigefinger, kein prinzipielles Problem.
XL
Thomas Plehn
du schreibst sehr viel über eine andere storage engine, aber ich
verwende bereits INNO DB. jetzt habe ich wenigstens eine Vorstellung
davon, was in einer Datenbank so ungefähr passiert (ich stelle mir das
nun ähnlich vor, wie auf einer Festplatte, die ebenfalls fragmentiert
und irgendwo Lücken aufweist, die später gefüllt werden und auch eine
file allocation tabele verwendet, ähnlich wie hier der IDX Datei.)
Am 15.04.2010 21:51, schrieb Kristian Köhntopp:
> On 2010-04-13 15:28:13 +0200, Thomas Plehn said:
>
>> Hallo,
>>
>> ich habe eine einfache SQL Tabelle in einer Datenbank Test, die in
>> jeder Zeile einen BLOB enthält. Die Daten werden per JSP geschrieben.
>> Zuvor hatte ich große Bilddateien von 2-3 MB größe gespeichert, nun
>> habe ich diese größtenteils durch meine Java Webapp gegen kleinere
>> Versionen ausgetauscht (ca. 50kb). Trotzdem wird die SQL Tabelle nicht
>> kleiner, Heidi SQL zeigt unverändert die alte Größe an.
>
> Welche Storage Engine hast Du verwendet?
>
> Wenn Du das nicht weißt, hast Du die MyISAM Storage Engine verwendet.
>
> In MyISAM ist es so, daß beim Speichern von BLOBs das Row Format DYNAMIC
> verwendet wird. In diesem Fall besteht Deine MYD Datei aus Records
> unterschiedlicher länge und in Deiner MYI-Datei werden Index Records
> erzeugt, die Positionen von Datensätzen in der MYD-Datei mit
> Byte-Offsets bezeichnen. Die Byte Offsets sind per Default 6 Byte (gut
> für 256TB) groß, aber Du kannst den Default
>
> root@localhost [(none)]> show global variables like '%pointer%';
> +--------------------------+-------+
> | Variable_name | Value |
> +--------------------------+-------+
> | myisam_data_pointer_size | 6 |
> +--------------------------+-------+
> 1 row in set (0.00 sec)
>
> ändern oder pro Tabelle mit MAX_ROWS einen anderen Wert indirekt bestimmen.
>
> Wenn Du Datensätze aus Deiner MYD-Datei löscht, indem Du DELETE
> verwendest oder indem Du den Record mit UPDATE verkleinerst oder
> vergrößerst, wird die Größe der Datei nicht verändert. Es entstehen
> stattdessen Lücken von freiem Platz in der MYD-Datei, die später wieder
> verwendet werden. Die Summe aller Lücken kannst Du mit SHOW TABLE STATUS
> LIKE 'tablename' anzeigen lassen - der Wert Data_free sagt Dir, wie viel
> Platz in Deiner MYD-Datei derzeit frei ist, sagt aber nichts über die
> Fragmentierung dieses Platzes aus.
>
> Um die Datei zu verkleinern mußt Du das Kommando OPTIMIZE TABLE auf die
> Tabelle anwenden, oder die Tabelle 'in sich selbst' konvertierten
> ('ALTER TABLE tablename ENGINE = MyISAM'). In beiden Fällen erzeugt
> MySQL eine verdeckte neue Version der Tabelle, kopiert die brauchbaren
> Daten aus der alten Version der Tabelle in die neue Version, benennt
> dann die alte Version der Tabelle in etwas verdecktes um, benennt danach
> die neue Version der Tabelle in das sichtbare 'tablename' um, und droppt
> schließlich die alte Version der Tabelle. Zwischendurch kann dadurch
> eine ganze Menge Platz verbraucht werden, und der Vorgang kann
> unakzeptabel lange dauern.
>
> Du kannst das Problem auch einfach ignorieren und Daten in die MYD-Datei
> einfügen. Wenn die Fragmentierung nicht gar zu schlimm ist, füllten die
> neuen Records die Lücken irgendwann auf und die Tabelle wächst ohne daß
> die Datei dazu größer wird.
>
>
> Wie Du erkennen kannst, ist MyISAM haarsträubend ineffizient. Wenn Du
> einen VARCHAR hast, dann sitzt dieser paßgenau in der Mitte zwischen
> anderen Rows. Wenn Du den VARCHAR (oder BLOB) jetzt vergrößerst, dann
> ist hinten kein Platz vorhanden, um den VARCHAR zu verlängern. MyISAM
> verschiebt den Record komplett an das Ende der Tabelle, um ihn dort zu
> verlängern. An der alten Position bleibt eine Lücke zurück.
>
> Da MyISAM außerdem in allen Indices den Record mit einem Byteoffset
> bezeichnet, und da sich dieser Byteoffset gerade geändert hat, müssen
> nun die Byteoffsets für diesen Record in allen Indices aktualisiert
> werden. MyISAM Index Pages sind 1KB klein - für jeden Index der Tabelle
> hast Du nun also eine dirty key page, die irgendwann einmal zurück
> geschrieben werden muß - wir haben einen Index Update Storm.
>
> root@localhost [(none)]> show global status like 'key_blocks_not%';
> +------------------------+-------+
> | Variable_name | Value |
> +------------------------+-------+
> | Key_blocks_not_flushed | 0 |
> +------------------------+-------+
> 1 row in set (0.01 sec)
>
> Wenn Dir MyISAM crashed während dieser Zähler nicht 0 ist, sind Deine
> Indices für diese Tabelle im Eimer und REPAIR TABLE braucht so seine
> Zeit. Wenn Du
>
> root@localhost [(none)]> show global variables like 'delay_key%';
> +-----------------+-------+
> | Variable_name | Value |
> +-----------------+-------+
> | delay_key_write | ON |
> +-----------------+-------+
> 1 row in set (0.00 sec)
>
> auf ALL setzt (oder auf ON hast und die Tabelle mit Delayed Key Writes
> definiert ist, siehe CREATE TABLE im Handbuch), dann werden diese Index
> Updates so gut wie nie zurück geschrieben, und ein Crash von MyISAM
> zerreißt Dir garantiert alle Indices. Aber immerhin sind die Updates
> schön schnell.
>
>
> InnoDB hat eine komplizertere interne Struktur, die aber darauf angelegt
> ist, die Änderungen am Primärschlüssel und an den Sekundären Schlüsseln
> zu minimieren und so die Anzahl der Pages, die durch Updates Dirty
> werden klein zu halten. Dadurch kommt InnoDB mit wachsenden Records und
> sich ändernden Daten (OLTP Load) viel besser zurecht.
>
> In MySQL 5.5.4 und höher wird InnoDB die Default Storage Engine sein. In