Performanceverlust bei ständigen Einfüge- und Löschoperationen

Themen rund um den praktischen Einsatz von Firebird. Fragen zu SQL, Performance, Datenbankstrukturen, etc.

Moderator: thorben.braun

Antworten
Groffy
Beiträge: 78
Registriert: Do 12. Apr 2018, 23:14

Hallo Zusammen,

bei unseren Kundensystemen werden Messdaten eines Sensors binär in Blob Feldern einer Firebird Datenbank gespeichert. Je nach Sensortyp können pro Messung ca. 6-8Mb aber auch Vielfache davon zusammen kommen. Das führt dazu, daß die Datenbankdateien rasch größer werden. Im konkreten Fall : Es werden Mikrolinsen Wafer mit bis zu 30000 Messungen vermessen. Nachdem die Daten ausgewertet wurden, werden die Messdaten wieder aus der Datenbank gelöscht.

Meine konkrete Frage : Ist es möglich, daß durch das ständige einfügen und löschen von (Binär)Daten unterschiedlicher Größe ein stetig anwachsender Geschwindigkeitsverlust bei Lesen/Schreiboperationen des Firebird Servers auftritt? Gibt es so etwas wie eine interne Fragmentierung innerhalb der Datenbankdatei?

Nach Aussage des Kunden, kann nur durch ein Backup/Restore Zyklus diesen Effekt behoben werden. Die Analyse gestaltet sich schwierig, da die Geräte weltweit stehen und wir oft keine Möglichkeit haben mittels Remote Desktop in die Firmennetzwerke auf unser Messsystem zu gelangen.

Vielen Dank und beste Grüße - Ulrich Groffy
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Hallo Ulrich,

die Fragmentierung ist ein "normales" Problem bei Datenbanken. Die Daten werden in Seiten (Pages) abgelegt. Gelöschte Datensätze werden in der DB zunächst nur markiert und erst nach einem Sweep aus der DB gelöscht. Neue Daten können bis dahin nicht in diese Seiten geschrieben werden. Also werden zunächst neue Seiten angelegt. So verteilen sich die Daten schrittweise über mehrere Seiten, was zu Performance-Einbußen führen kann.

Mittels Backup und Restore werden die die Daten sequenziell in der neuen DB abgelegt, also "Defragmentiert".

Ggf. könntest du einen manuellen Sweep durchführen oder z.B. um Mitternacht ausführen lassen. Damit kann man das Anwachsen zumindest einigermaßen in den Griff bekommen.
Siehe auch https://www.ibexpert.net/ibe_de/pmwiki. ... nbanksweep,
https://www.firebirdsql.org/file/docume ... usekeeping

Wie große werden die DBs? Welche Page Size ist eingestellt? Evtl. können die Binärdaten auch in ein relationales Schema überführt werden?

Gruß
Martin
Martin Köditz
it & synergy GmbH
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Nein, durch diese Art der Operationen kann das Performanceproblem nicht auftreten.
Wichtig zu wissen ist ja, dass die Firebird mit Satzversionen arbeitet.
Dafür muss man allerdings auch mit Transaktionen vernünftig umgehen.
Die Satzversionen werden so lange aufgehoben, bis die älteste Transaktion geschlossen wird.
Da man ja eher häufiger seine Verbindung permanent offen hält (ggf. auch per Verbindungs-Pool), und glaubt beim Lesen keine Transaktionen zu benötigen, ergibt dies trotzdem eine offene Transaktion.
Wird nun von einer anderen Transaktion Daten angefordert, müssen alle Satzversionen, die älter sind als die eigene Transaktion, tatsächlich überlesen werden.
Dies ist dann der Verlangsamungseffekt, da ständig neue Daten entstehen.
Wird nun die älteste Transktion geschlossen erfolgt das automatische Löschen aller älteren Daten auf 2 Varianten:
a) beim nächsten Zugriff auf eine Tabelle,
b) durch den Sweeper, der i.d.R. ab einer DIfferenz zwischen ältester und nächter Transaktion > 20000 den Sweep durchführt.
Fragmentierung hat damit nichts zu tun. Ich erstelle die DB's generell mit 16KB Pagesize (Default ist ja 8KB).
Manchmal kann der Sweeper nichts bereinigen, dann ist u.U. ein Save/Restore erforderlich. Offene Transaktionen findet man in der MON$TRANSACTION und die Verbindungen in MON$ATTACHEMENTS.
Die Differenz bzgl. der Transaktionen (Oldest/Next) in der RDB$DATABASE.

Fazit:
Bei wirklich jedem Zugriff ein Transaktion abschließen (commiten). Schreibvorgänge in eine Transaktion bündeln und committen.
Dann gibt es diese Effekte nicht.

Ich habe mir in meiner Anwendung einen eigenen Verbindungspool erstellt, der offene Verbindungen verwaltet und nach ca. 15 Sekunden der Nichtbenutzung die Transaktion commited und die Verbindung schließt.
Der Treiber und die Datenbank wird ohne Verbindungspool verwendet, da dies die Stabilität bzgl. Satzverwaltung erhöht hat.
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Hallo bfeurchau,

deine Ausführungen sind alle korrekt. Jedoch kann ein Performance-Verlust durchaus durch die "Fragmentierung" entstehen. Gerade weil die Daten nicht mehr sequenziell liegen und ggf. ständig von der Platte gelesen werden müssen. Dieses Problem habe ich in ähnlicher Weise schon erfahren. Das Problem war mittels Backup & Restore wieder behoben und hatte nichts mit Transaktionen zu tun, sondern mit der Art und Weise der Datenablage bzw. ständiger Schreib- und Lesezugriffe. Vielleicht ist der Begriff "Fragmentierung" hier auch unglücklich gewählt.

Der Garbage Collector wird unter Umständen auch automatisch ausgeführt. Dies passiert in diesem Falle während eines Lesevorgangs. Und dann kann man schon mal je nach DB-Größe einige Zeit warten.

@Ulrich
Wie groß ist denn die DB?

Gruß
Martin
Martin Köditz
it & synergy GmbH
Groffy
Beiträge: 78
Registriert: Do 12. Apr 2018, 23:14

Hallo Martin, hallo bfeurchau

vielen Dank für eure Antworten. Hier noch ein paar Informationen :

Momentan wird der Firebird Server in der Version 2.5 in der Betriebsart SuperServer installiert. Die Blockgröße ist 16384. Die Datenbankgröße liegt zwischen 500Gb - 600Gb.

Der Zugriff erfolgt aus einer .Net Anwendung mittels FirebirdSql.Data.FirebirdClient.dll. Alle Schreiboperationen werden sofort committed. Ich glaube deshalb nicht, daß es ein Problem mit langlaufenden Transaktionen und der damit verbundenen Multiversionierung von Records gibt.

Was ich noch nicht vollständig verstanden habe, ist - wann werden die intern als "gelöscht" markierten Daten zum überschreiben wieder frei gegeben bzw. von der Garbage Collection erfaßt? Kann ich durch einen regelmäßig durchgeführten SWEEP dies sicherstellen? Es nützt nichts, wenn Datensätze gelöscht werden, aber bei den folgenden Schreiboperationen nicht überschrieben werden und die Größe der Datenbankdatei weiter anwächst.
Es gibt da auch die Aussage : Die Garbage collection wird auch durch ein Select Statement auf die besagte Tabelle ausgelöst....

Das hat jetzt nicht unmittelbar mit dem vom Kunden beschriebenen Effekt zu tun, daß die Schreib/Leseoperationen angeblich träger werden, aber der Kunde möchte die maximale Größe der Datenbankdatei begrenzen.

@Martin : Was meintest Du mit : "Evtl. können die Binärdaten auch in ein relationales Schema überführt werden" ?


Beste Grüße - Ulrich Groffy
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Hi Ulrich,
Was ich noch nicht vollständig verstanden habe, ist - wann werden die intern als "gelöscht" markierten Daten zum überschreiben wieder frei gegeben bzw. von der Garbage Collection erfaßt? Kann ich durch einen regelmäßig durchgeführten SWEEP dies sicherstellen? Es nützt nichts, wenn Datensätze gelöscht werden, aber bei den folgenden Schreiboperationen nicht überschrieben werden und die Größe der Datenbankdatei weiter anwächst.
Es gibt da auch die Aussage : Die Garbage collection wird auch durch ein Select Statement auf die besagte Tabelle ausgelöst....
Daten, die mittels DELETE-Anweisung entfernt werden, sind noch in den DB-Seiten vorhanden, sind nur als gelöscht markiert. Erst nach einem Sweep werden diese Bereiche freigegeben.

Wie bfuerchau bereits geschrieben hat, gibt es das Sweep-Intervall, was bei einer Differenz von 20000 Transaktionen automatisch ausgeführt wird. Dieser Automatismus läuft jedoch nicht bei einem INSERT / UPDATE / DELETE an, sondern immer während eines SELECT, was ja bei großen Datenmengen ungünstig ist.
@Martin : Was meintest Du mit : "Evtl. können die Binärdaten auch in ein relationales Schema überführt werden" ?
Ich gehe davon aus, dass die Binärdaten als BLOBs in die DB geschrieben werden. Eventuell könnte man diese in eine Datenbankstruktur übernehmen. In normalisierter Form könnte man die DB-Größe sicherlich schrumpfen. Da kenne ich die Struktur aber nicht.

Gruß
Martin
Martin Köditz
it & synergy GmbH
Groffy
Beiträge: 78
Registriert: Do 12. Apr 2018, 23:14

Hallo Martin,
martin.koeditz hat geschrieben: Mo 6. Jul 2020, 16:12 Daten, die mittels DELETE-Anweisung entfernt werden, sind noch in den DB-Seiten vorhanden, sind nur als gelöscht markiert. Erst nach einem Sweep werden diese Bereiche freigegeben.

Wie bfuerchau bereits geschrieben hat, gibt es das Sweep-Intervall, was bei einer Differenz von 20000 Transaktionen automatisch ausgeführt wird. Dieser Automatismus läuft jedoch nicht bei einem INSERT / UPDATE / DELETE an, sondern immer während eines SELECT, was ja bei großen Datenmengen ungünstig ist.
Also ganz konkret : Ich lösche aus einer Tabelle 30000 Datensätze, und möchte nicht das nächste automatische Sweep Intervall abwarten, sondern die als gelöscht markierten Daten zum überschreiben freigeben. Die einzige Möglichkeit die ich jetzt habe, ist mittels gfix.exe ein Sweep der Datenbank explizit ausführen und damit die Garbage Collection anstoßen?


Beste Grüße - Ulrich
vr2
Beiträge: 214
Registriert: Fr 13. Apr 2018, 00:13

Hallo Ulrich,

Was garbage collection und sweep angeht, schau mal hier https://www.firebirdsql.org/file/docume ... usekeeping, der ganze Abschnitt.

* Du kannst die drei Varianten der garbage collection policy in firebird.conf einstellen
* Du kannst das sweep interval auf 0 setzen, um es abzuschalten, oder es deutlich hochsetzen

Kannst Du ausprobieren. Ich vermute aber, dass Du einfach sehr viele gelöschte Sätze produzierst, also viel Müll, und statt die Müllabfuhr zu optimieren, könntest Du Müllvermeidung versuchen:

Kannst Du die Messwerte in global temp tables (GTTs) unterbringen? https://firebirdsql.org/refdocs/langref ... table.html. Entweder solchen mit Transaktions-Lebensdauer oder mit connection-Lebensdauer. Dann ersparst Du Dir das Leeren, denn beim commit einer TX (erste Variante) oder beim disconnect sind die Daten in der Tabelle alle weg, gehen dabei nicht durchs sweep oä.

Was ich auch untersuchen würde, sind die BLOBs. BLOBs sind in bestimmten Situation wesentlich performanceintensiver als reguläre Felder. Die Frage ist, ob das hier der Fall ist.

Firebird 4 sollte wegen der Löschproblematik truncate tables bekommen, das wurde aber zurückgestellt.

Die Fährte mit den GTTs halte ich im Moment für am ehesten Erfolg versprechend.

Grüße, Volker
Groffy
Beiträge: 78
Registriert: Do 12. Apr 2018, 23:14

Hallo Volker,

vielen Dank für Deine Antwort. Den Artikel "Database Housekeeping And Garbage Collection" hatte ich mir schon durchgelesen, möchte nur sicher stellen, daß ich ihn auch korrekt verstanden habe :

1.) Da alle Transaktionen sofort committed werden, wird von meinem Verständnis her das Sweep Intervall (Differenz Oldest Snapshot Transaction und Oldest Interesting Transaction ) niemals erreicht und somit überhaupt kein Sweep ausgelöst(?)

Die Aussagen von HK Soft unter https://www.ibexpert.net/ibe_de/pmwiki. ... nbanksweep sind da etwas abweichend...

2.) Die Einstellungen für den Garbage Collector Mode hilft nichts, wenn die Annahme 1.) korrekt ist.

Frage : Gibt es irgendeine Möglichkeit festzustellen, wann zum letzten Mal ein Sweep durchgeführt wurde?

Ich werde Mal versuchsweise die Garbage Collection nach jedem Löschen manuell anzustoßen.


Deine Aussage -
"...statt die Müllabfuhr zu optimieren, könntest Du Müllvermeidung versuchen"
ist natürlich richtig. Dein Ansatz mit global temp tables zu arbeiten ist sehr interessant - vielen Dank


Beste Grüße - Ulrich
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Wie ich schon schrieb:
In der RDB$DATABASE findest du die o.g. Transaktionsnummern.
Wenn die Differenz eben sehr groß ist, stirbt der Sweeper und kann nichts mehr löschen (in der FB3 ist das besser gelöst).

Und ich schrieb auch, dass beim Select die Satzversionen gelöscht werden, die älter sind als die "OldestInterested".
EIn explizites Freigeben gibt es da nicht. Auch der GFIX kann da schon mal scheitern und die Transaktionsnummer wird nicht hochgesetzt, was den Garbagecollector wieder verhindert.

Ich habe da (in meinen Anfängen) schon Differenzen von 2 Mio Transaktionen gehabt! Da hilft wirklich nur ein Save/Restore.

GTT's werden gar nicht in der DB abgelegt sondern im TEMP-Verzeichnis (Firebird.conf!). Auch hier muss man dann für genügend Platz sorgen. Ein Aufräumen (Garbage) ist daher nicht nötig.
Da GTT's maximal sitzungsbezogen sind, habe ich persönlich noch keine Anwendung dafür gefunden. Ich arbeite da gerne mit kleinen Access-MDB's (bis 2 GB), was vollkommen ausreicht. Office/Access ist dafür nicht erforderlich.
Antworten