Firebird - update BLOB-Field - Datenbank wird immer größer

[lxo]

Hallo zusammen,

kann mir jemand erklären wieso sich das so verhält bzw. was ich machen kann damit es sich nicht so verhält. :)

Folgendes Problem, ich schreibe kontinuierlich etwas in einen Blob rein.
Können schon mehrere tausend Male sein.
Das führt dazu, dass sich die Datenbank irgendwann extrem schnell aufbläht.
Nach dem Backup/Restore ist sie wieder deutlich kleiner.

Ich kenn diesen Effekt bei Firebird, wenn man immer wieder Delete/Insert macht, dann wird die Datenbank größer und größer und nach einem Backup/Restore ist sie wieder kleiner.
Aber wieso tritt der Effekt bei Update auf Blob-Fields auf?

Delphi-Quellcode:

  
// Verallgemeinert, aber so in etwa schreibe ich immer wieder in das Blobfeld
var lQuery := TUniQuery.Create(nil);
  try
    lQuery.Connection := GetConnection;
    lQuery.SQL.Text := 'Select t.* from TEST t ' +
                       'Where t.ID = 1';
    lQuery.Execute;
    if not lQuery.IsEmpty then
    begin
      lQuery.Edit;
      lQuery.FieldByName( 'BLOBTEST').AsString := lQuery.FieldByName( 'BLOBTEST').AsString  + sLineBreak +
                                                  Now.toString + ';' + Random(100000).ToString + ';' + Random(100000).ToString;
      lQuery.Post;
    end;
  finally
    lQuery.Free;
  end;

[Uwe Raabe]

Blobs werden ja nicht im eigentlichen Record gespeichert, sondern in speziellen Pages. Daher entspricht ein Update auf ein Blob-Feld einem Insert/Delete.

[lxo]

Zitat:

Zitat von Uwe Raabe (Beitrag 1520136)

Blobs werden ja nicht im eigentlichen Record gespeichert, sondern in speziellen Pages. Daher entspricht ein Update auf ein Blob-Feld einem Insert/Delete.

Und das kann man nicht umgehen, in dem man immer wieder auf die selbe Pages schreibt?

[mjustin]

Exakt das gleiche Problem habe ich mit Oracle 19c (und Db2 for i) dieses Jahr beobachtet. Oracle Tablespace, der noch ca. 10 GB Platz hat, ist nach wenigen Minuten voll. Geschrieben wurden aber nur maximal wenige MB mit einem Protokoll, immer in das gleiche DB-Feld. Der "unbenutzte", aber dennoch blockierte Tablespace kann nur durch einen Admin wieder freigemacht werden.

Eine Lösung wäre, auf normale Char-Datentypen umzustellen und das Log darin normal (Satz für Satz inserten) zu schreiben...

[lxo]

Zitat:

Zitat von mjustin (Beitrag 1520139)

Exakt das gleiche Problem habe ich mit Oracle 19c (und Db2 for i) dieses Jahr beobachtet. Oracle Tablespace, der noch ca. 10 GB Platz hat, ist nach wenigen Minuten voll. Geschrieben wurden aber nur maximal wenige MB mit einem Protokoll, immer in das gleiche DB-Feld. Der "unbenutzte", aber dennoch blockierte Tablespace kann nur durch einen Admin wieder freigemacht werden.

Eine Lösung wäre, auf normale Char-Datentypen umzustellen und das Log darin normal (Satz für Satz inserten) zu schreiben...

Da habe ich aber die Grenze von 32.767 Bytes beim Typ VARCHAR. Das reicht an der Stelle nicht.

[TBx]

Wenn es sich hier tatsächlich um ein Log handelt, so würde ich das nicht in ein Blob-Feld schreiben.
Ich würde das über eine LogTabelle lösen, in der jeder Logeintrag ein Datensatz ist.
Logs in Blobfelder zu schreiben nimmt einem ja alle Auswertungsmöglichkeiten, die einem die Datenbank sonst bietet.

[Uwe Raabe]

Zitat:

Zitat von lxo (Beitrag 1520137)

Und das kann man nicht umgehen, in dem man immer wieder auf die selbe Pages schreibt?

Das solltest du besser die Entwickler von FireBird fragen. Allerdings kannst du die gleiche Frage dann auch für das UPDATE eines normalen Record stellen. Ich gehe mal davon aus, das ist ganz tief in der Interbase/Firebird-Architektur verankert und wird wohl nicht so schnell geändert werden. Hat ja vermutlich auch was mit Transaktionssicherheit zu tun.

[lxo]

Zitat:

Zitat von TBx (Beitrag 1520142)

Wenn es sich hier tatsächlich um ein Log handelt, so würde ich das nicht in ein Blob-Feld schreiben.
Ich würde das über eine LogTabelle lösen, in der jeder Logeintrag ein Datensatz ist.
Logs in Blobfelder zu schreiben nimmt einem ja alle Auswertungsmöglichkeiten, die einem die Datenbank sonst bietet.

Ja, das sehe ich auch als beste Lösung. :thumb:

[hoika]

Halo,
man müsste ja eigentlich den Garbage-Collector (gfix -sweep ?) anwerfen,
damit der die "alten" Blob-Pages als "kann wieder benutzt werden" markiert.

Das geht auch ohne direkten gfix-Zugriff und vor allem ohne Backup/Restore.

Es gab mal einen Artikel dazu (ibphoenix?).
Finde ich aber gerade nicht/ keine Zeit zum Suchen.

[IBExpert]

dein code mit edit/post kann man zwar so machen, muss dann aber auch damit leben, das dabei transaktionen nicht wirklich kotrolliert committed werden

d.h. dein edit/post sorgt bei jedem schreibvorgang für einen komplett neuen kompletten blob als record version, weil nämlich zB
ein backup das du mitten drin gestartet hättest, nur genau die eine record version hätte lesen können, die zu dem Zeitpunkt
committed war. alle anderen record version die danach enstanden sind könnten ggf spätere Transaktionen noch lesen, wenn die denn irgendwo
committed wären. da du aber in einem Blob in einer Record Version pro record/column je nach pagesize bis zu 128 GB unterbringen
kannst muss firebird den kram irgendwo temporär loswerden und dabei bleibt mangels 128GB*x RAM meistens nur das schreiben in blob
pages.

Sinngemäß erzeugt dein code in der firebird db dafür das es record versionen alle varianten gibt.
wenn das 100000 mal durchgelaufen ist und du nirgendwo für transaction control udn auslösen garbage collection/sweep
gesorgt hast für
-in 100000 blobpages ist deine erste zeile enthalten
-in 99999 blobpages ist deine erste zeile und deine zweite zeile enthalten
-in 99998 blobpages ist deine erste zeile und deine zweite zeile und deine dritte zeile enthalten
....
solange irgendeine transaktion offen ist, kann firebird keine dieser recordversion rausschmeissen und fast jedes offene dataset hält fast immer eine transaktion offen, die den garbage collector blockiert. Und nach x durchläufen sind das nicht mehr nur eine blobpage pro record sondern gleich mehrere, daher wird die db so groß.

Wenn du das besser machen willst, dann sorge dafür, das jeder update committed wird und du dann auch nicht noch von irgendwo anders eine
alte aktive Transaktion offen hältst (sieht man in der database statistics bzw in ibexpert auch im database monitoring).

Um die recordversionen dieses blobs aufzuräumen, die niemand mehr sehen kann, muss du Firebird einen Hinweis geben. Einfachster weg:
Mach nach deinem update/post und dem noch einzufügenden commit ein simples select count(*) from tabelle where .... mit genau dem
key, wo du gerade deine updates machst. wenn es da zum pk nämlich zB 5 Record versionen gibt, von denen nur 2 sichtbar
sind und keine zu alte transaktion das alles komplett blockiert, dann wird der garbagecollector asynchron beginnen, die nicht mehr benutzen blob pages
wieder freizugeben und diese werden dann bei späteren updates wieder benutzt.

Aber ganz im Vertrauen, nutze für sowas nicht ein select in einer request live query, die dann per edit/post die updates bekommt. Das hat auf dataset
ebene so gruselige nebenwirkungen, die du nie wieder los wirst. wenn du was anzeigen bzw lesen willst, mach ein select, wenn du was ändern
willst, dann mach in einer anderen query den update als update befehl mit commit und wenn dich das im client zur anzeige interessiert, was du da gerade geschrieben
hast, mach den einen neue select (viele Komponenten können da mit einer extra readonly transaction vieles automatisieren für read operationen, aber
unter high load funktioniert das nur performant, wenn du das von anfang an richtig programmierst. Das hat nichts mit Firebird zu tun, das ist bei jeder
plattform auf die eine oder andere art ähnlich.

Datasets sind nice to have für dbgrids o.ä., aber für high performance application nicht wirklich der bringer.
Unidac kann da zwar im Hintergrund einiges optimieren, aber wenn dein code sich nicht um Transaction control kümmert, können die da nicht ungefragt für sorgen, vielleicht fällt dir ja nach dem 100000. update dann ein das du doch ein rollback brauchst.

p.s.: bitte nicht hoffen das automcommit irgendwas besser macht, eigentlich sollte autocommit nicht autocommit sondern besser autostarttransaction heissen, weil es nur in ausnahmefällen für echte commits sorgt.

und wenn dich die details nicht ur dazu interessieren, in diesem videos hatte ich dazu reichlich erklärt
https://www.youtube.com/playlist?lis...0oMvOmx-wHKcCU

[IBExpert]

Zitat:

Zitat von lxo (Beitrag 1520146)

Zitat:

Zitat von TBx (Beitrag 1520142)

Wenn es sich hier tatsächlich um ein Log handelt, so würde ich das nicht in ein Blob-Feld schreiben.
Ich würde das über eine LogTabelle lösen, in der jeder Logeintrag ein Datensatz ist.
Logs in Blobfelder zu schreiben nimmt einem ja alle Auswertungsmöglichkeiten, die einem die Datenbank sonst bietet.

Ja, das sehe ich auch als beste Lösung. :thumb:

wenn das wie in deinem Beispiel dynamisch erweiterte Dateninhalte sind, dann ist das sowieso der Weg, den man gehen sollte.

Die Inhalte von so einem Log File sind ja im Filesystem unter anderen Voraussetzungen in einer Datei zusammengefasst, da
gibt es aber auch kein multiuser read/write zugriff, kein commmit, kein rollback, keine versionierung usw.

Zeilenweise einelne Records erfassen und die dann bei Bedarf gefiltert lesen ist bei der Idee von Log Files der Weg, den
man damit sauber umsetzt.

Beim Speichern von pdf dateien oder Bildern gibt es das Problem aber nahezu gar nicht und wenn jemand da zum Beispiel ein Memofeld
mit Text oder Html oder RTF Inhalten speichert, dann werden die sich sicherlich nicht hundertmal pro Sekunde um neue Zeilen ergänzen,
weil die daten dann dort eher durch Interaktion in einem Texteditor ergeben und dann verändert gespeichert werden, wenn der Anwender das möchte.

Es ist also wie fast immer, für den jeweiligen Anwendungsfall muss man immer die passenden Datentypen auswählen.

[lxo]

Zitat:

Zitat von IBExpert (Beitrag 1520155)

…

Um die recordversionen dieses blobs aufzuräumen, die niemand mehr sehen kann, muss du Firebird einen Hinweis geben. Einfachster weg:
Mach nach deinem update/post und dem noch einzufügenden commit ein simples select count(*) from tabelle where .... mit genau dem
key, wo du gerade deine updates machst. wenn es da zum pk nämlich zB 5 Record versionen gibt, von denen nur 2 sichtbar
sind und keine zu alte transaktion das alles komplett blockiert, dann wird der garbagecollector asynchron beginnen, die nicht mehr benutzen blob pages
wieder freizugeben und diese werden dann bei späteren updates wieder benutzt.
…

Danke für die ausführliche Antwort. Jeder edit/Post also der Update Vorgang wird in einer eigenen Transaktion ausgeführt, habe das Beispiel nur klein gehalten um das wesentliche Problem zu verdeutlichen. Das gleiche Verhalten habe ich auch wenn ich direkt Update statements ausführe.

Verstehe ich das richtig, das ein einfaches select count(*) den garbage collector anstößt und ich bräuchte kein gfix sweep aufrufen damit die Datenbank wieder kleiner wird?
Oder würde das nur dafür sorgen, das alte Pages wiederverwendet werden?

[TBx]

Zitat:

Zitat von lxo (Beitrag 1520164)

Verstehe ich das richtig, das ein einfaches select count(*) den garbage collector anstößt und ich bräuchte kein gfix sweep aufrufen damit die Datenbank wieder kleiner wird?
Oder würde das nur dafür sorgen, das alte Pages wiederverwendet werden?

Nein, die Datenbank wird nie kleiner, auch durch gfix nicht. Es wird lediglich Speicherplatz in den Pages zur erneuten Benutzung freigegeben.
Die Datenbankgröße kannst Du ausschließlich durch Backup/Restore verringern.

[IBExpert]

Zitat:

Zitat von lxo (Beitrag 1520164)

Verstehe ich das richtig, das ein einfaches select count(*) den garbage collector anstößt und ich bräuchte kein gfix sweep aufrufen damit die Datenbank wieder kleiner wird?

Richtig verstanden, setzt aber voraus das nicht irgendeine andere alte Transaktion das noch lesen könnte, was da aufgeräumt werden könnte, egal ob die daten in dieser Transaktion überhaupt schon mal gelesen wurden oder überhaupt relevant sind.

Die Pages werden durch den Sweep bzw die garbagecollection in der Page Inventory page dann nicht mehr als belegt markiert,sondern als verfügbar (und nicht nur für andere Blobs, sondern für alles was fb damit dann machen kann). Und wie Thomas schon sagt, die DB Datei wird nicht verkleinert, weil Firebird bei Bedarf zwar neue Dateibereiche anfordert für Pages, auf den was gespeichert werden muss, diese werden an nie wieder zurück an das Betriebssystem gegeben.

Durch den Neuaufbau der Datenbankdatei beim Restore werden die unbenutzten pages, die auch schon gar nicht im Backup sind, auch nicht wieder aufgebaut, so das dabei die Datei so groß bleibt wie es erforderlich ist.

Die 80% Füllgrad Regel (use all space) lass ich mal in der Erklärung weg, die ist dabei erst mal unwichtig.