SQLite Überlauf des Speichers verhindern

[DeaD_EyE]

Ich hatte mal Code für eine Fernsteuerung für LiFePO4-Akkus geschrieben. Der Code hat auch mittels sqlite Daten geloggt. Da die sqlite-db nicht zu groß werden durfte, da die Performance auf einer SD-Karte unterirdisch ist (IOPS), habe ich Code geschrieben, der die DB einfach löscht. Wie bereits erwähnt, wird die DB nicht kleiner, wenn man Daten löscht.

Wenn man Daten behalten will, erstellt man am besten einfach eine neue DB mit den Daten der alten DB, die man behalten möchte und danach kann man die alte DB löschen und die neue DB nutzen.

Ich habe mich gegen MySQL/MariaDB/Postgresql entschieden, da der RPi0W nicht der schnellste ist. Wenn ich z.B. vorher gewusst hätte, wie langsam ein ORM (sqlalchemy) auf dem RPi zum Laden benötigt, hätte ich das niemals eingesetzt. Hinterher ist man immer schlauer.

Auf normaler Hardware (Desktop PC/Server) spielt das keine Rolle.

[sparrow]

Wie hier bereits geschrieben: Das macht man nicht selbst sondern nutzt VACUUM.

[noisefloor]

Hallo,

Wie bereits erwähnt, wird die DB nicht kleiner, wenn man Daten löscht.

Wenn man Daten behalten will, erstellt man am besten einfach eine neue DB mit den Daten der alten DB, die man behalten möchte und danach kann man die alte DB löschen und die neue DB nutzen.

Kann man machen, man kann auch direkt den VACCUM Befehl von SQLite nutzen. Oder man erstellt die Datenbank mit auto_vacuum.

@Pitwheazle: beim 1. Link ist nochmal kompakter erklärt, warum beim Löschen von Daten die Datei erst mal nicht kleiner wird.

Gruß, noisefloor

[Pitwheazle]

@noisefloor und @Dead_EyE: Dass die Datenbank nicht kleiner wird, wenn man Daten löscht, erscheint mir logisch, das ist mir auch bekannt. Meine Frage ist eine andere: Wird der Platz von gelöschten Datensätzen genutzt um neue Datensätze zu speichern oder bleibt dieser Platz belegt? Ich vermute, dass, wie bei gelöschten Dateien in Windows (und auch bei anderen Dateisystemen) diese Datensätze weiterhin da sind aber nicht mehr angezeigt werden. Diese Daten könnten dann ja mit neuen Daten überschrieben werden - oder auch nicht.
Ich habe vor langer Zeit dBase3+ programmiert. Dort gab es einen Befehl mit dem man die Datenbank komprimieren konnte. Soweit ich das weiß, wurde bei dieser Gelegenheit auch die primary Keys neu vergeben. In meinem alten Handbuch dazu finde ich den Befehl allerdings nicht mehr - vielleicht irre ich mich ja auch.
Und dann ist mir aufgefallen, dass in Thunderbird man beim Klick mit der rechten Maustaste auch "komprimieren" auswählen kann. Da weiß ich auch nicht, was da wirklich passiert.
.. Dass diese Neuordnung bei Sqlite nicht existiert weiß ich.
@noisefloor: Ich habe versucht, meine Frage mithilfe des Links https://www.sqlite.org/fileformat.html und dem Begriff Freelist Page zu beantworten. Wird da meine Frage beantwortet - ich verstehe es nicht? Betrifft das auch gelöschte Einträge?

[grubenfox]

Ich habe vor langer Zeit dBase3+ programmiert. Dort gab es einen Befehl mit dem man die Datenbank komprimieren konnte.

und bei SQLite nennt sich der Befehl VACUUM...

Kann man machen, man kann auch direkt den VACCUM Befehl von SQLite nutzen. Oder man erstellt die Datenbank mit auto_vacuum.

[Pitwheazle]

Wenn ich richtig informiert bin, erstellt dieser Befehl ja eine neue Datenbank. Das braucht Platz und Zeit. Meine Frage ist, ob man das braucht, wenn man jede Menge Datensätze gelöscht hat.

[noisefloor]

Hallo,

Wird da meine Frage beantwortet - ich verstehe es nicht? Betrifft das auch gelöschte Einträge?

Ja, deine Frage wird damit beantwortet. Auf der genannten Seite steht im Kapitel 1.5 Freelist am Ende des 1. Abschnitts: " Unused pages are stored on the freelist and are reused when additional pages are required.". Auf deutsch: in der Freelist aufgeführt Speicherseiten werden erneut benutzt, wenn neue Speicherseite benötigt würden, d.h. wenn Speicherseiten, wo schon / noch Daten drin sind, voll sind.

Gruß, noisefloor

[Pitwheazle]

OK Danke. Mir war nicht ganz klar, dass das auch für den Platz von gelöschten Dateien zutrifft. In der Datenbank gibt es ja sowieso immer ungenutzem Platz, da in der Datenbank Platz für neue Daten vorgehalten werden muss.

[Pitwheazle]

OK Danke. Mir war nicht ganz klar, dass das auch für den Platz von gelöschten Dateien zutrifft. In der Datenbank gibt es ja sowieso immer ungenutzen Platz, da in der Datenbank Platz für neue Daten vorgehalten werden muss.

[noisefloor]

Wenn ich richtig informiert bin, erstellt dieser Befehl ja eine neue Datenbank.

Nein, der Befehl erstellt per Default _keine_ neue Datenbank. Zitat aus der SQLite Doku: "The VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space.". Es wird dabei eine temporäre Datei angelegt, da am Ende halt wieder gelöscht wird.

Das braucht Platz und Zeit. Meine Frage ist, ob man das braucht, wenn man jede Menge Datensätze gelöscht hat.

Du machst dir echt Gedanken über Sachen, über die du dir - und die allermeisten anderen Nutzer inkl. mir - sich gar keine Gedanken machen müssen. Du hast keine Million von Nutzer, die bist ganz weit weg von den Limits von SQLite, du hast eine immer noch vergleichsweise sehr kleine Datenbank und du hast wahrscheinlich kein Performance Problem. Und wenn du einst hättest müsstest du erst mal die Ursache finden, weil das Multiple sein können und nicht zwingend die DB.

Gruß, noisefloor

[Pitwheazle]

Du machst dir echt Gedanken über Sachen, über die du dir - und die allermeisten anderen Nutzer inkl. mir - sich gar keine Gedanken machen müssen.

Stimmt. Ich möchte halt auch die Hintergründe etwas verstehen. Mein Denken kommt halt noch aus DOS Zeiten und 8Bit Rechnern. Für mein dBase Programm konnte ich damals immer nur Teile jeweils nutzen und dabei immer die Diskette wechseln um auf ein anderen Teil nutzen zu können. Das war damals ein Schneider CPC 664.

[noisefloor]

Wir haben aber nun mal 2025 und 64bit CPUs und 64bit Betriebssysteme. Theoretisch kannst du in die gleichen Probleme laufen. Tun 99,9% der Nutzer aber nun mal nicht. Und das deine Rechentrainer App das neue Google oder AWS oder so wird ist ... unwahrscheinlich.

Ich möchte halt auch die Hintergründe etwas verstehen.

Darum habe ich dir ja den Link mit der Erklärung zum Speicherformat von SQLite gegeben. SQLite ist ziemlich ausführlich dokumentiert. Da findest du auch noch mehr Infos, wie das alles unter der Haube funktioniert.

Gruß, noisefloor

[Pitwheazle]

Und das deine Rechentrainer App das neue Google oder AWS oder so wird ist ... unwahrscheinlich.

[__blackjack__]

@Pitwheazle: Der dBase Ⅲ+-Befehl um als gelöscht markierte Datensätze permanent zu löschen und somit Platz auf dem Datenträger zu schaffen heisst PACK.

[Pitwheazle]

Danke

[DeaD_EyE]

Wie hier bereits geschrieben: Das macht man nicht selbst sondern nutzt VACUUM.

Es gibt unterschiedliche Szenarien und meins war mit limitierten IOPS und defekten SD-Karten. VACUUM hätte die Situation nur noch verschlimmert. Um Daten aus einer DB zu löschen, muss die DB beschrieben werden. Mit VACUUM wird mehr geschrieben und dann geht die SD-Karte noch schneller kaputt, wenn man regelmäßig Datensätze löscht.

Wenn man regelmäßig die ganze Datenbank löscht und eine neue erstellt, wird insgesamt weniger geschrieben.

Auf einem Server im RZ hat man aber genügend Ressourcen und muss sich auch keine Gedanken machen, dass die Datenträger bei Schreiben kaputtgehen. Auf einem Server nutzt man aber eher einen RDMBS.

[sparrow]

Ich denke, da täuscht du dich.
VACUUM macht, soweit ich weiß, exakt das, was du da kompliziert nachgebaut hast

Es legt eine neue Datei an, schreibt dort alle Daten hinein (ohne dead tuple) und swapt dann.
Steht auch so in der Dokumentation.

Ein Vergleich mit anderen Datenbanken macht da auch wenig Sinn, weil es kein einheitliches Vorgehen gibt.
VACUUM bei Postgres macht etwas anderes als VACUUM bei sqlite3. Bei Postgres ist Das eher mit VACUUM FULL zu vergleichen.

[noisefloor]

VACUUM macht, soweit ich weiß, exakt das, was du da kompliziert nachgebaut hast

Würde ich auch so sehen... ist am Ende aber hier in dem Thread auch egal, weil es nicht um die Optimierung der Lebensdauer einer SD Karte geht.

Gruß, noisefloor

[DeaD_EyE]

Ich denke, da täuscht du dich.
VACUUM macht, soweit ich weiß, exakt das, was du da kompliziert nachgebaut hast

Nein, habe ich nicht.
Ich habe die ganze DB gelöscht und keine Daten behalten!

Wenn die DB größer als 10 MiB war, hab ich sie gelöscht. Das hat die Lebenszeit drastisch erhöht.
Ein VACCUUM hätte die Lebenszeit drastisch verkürzt, da durch diesen Vorgang viel mehr Daten geschrieben werden, wenn man z.B. das letzte halbe Jahr löscht.
Wenn man alles löscht, wir nur einen inode-Eintrag geschrieben. Krasser Unterschied.

[noisefloor]

Ich habe die ganze DB gelöscht und keine Daten behalten!

Mal so Interesse halber: warum hast du dann überhaupt eine DB? Der Applikation ist es ja dann scheinbar egal, wenn zu einem beliebigem Zeitpunkt (=wenn du manuell löschst) keine Daten mehr da sind. Dann könntest du doch auch alles direkt im RAM halten.

Gruß, noisefloor