Datenbank Größe (sqlite) weshalb wird sie nicht kleiner?

derberg · 29. Dezember 2024 um 23:49

Hallo zusammen,
ich habe seit Monaten das Problem, dass meine Datenbank kaputt geht und dann neu anfangen will. Das “löse” ich dann temporär mit dem SQLite3 .repair mithilfe einer Datenbank aus dem Backup.

Inzwischen passiert es alle paar Tage und ich denke die Größe der Datenbank ist das Problem (aktuell 31 GB).
Daher habe ich mich an die recorder Einstellungen gemacht und einige Ausschlüsse definiert, unter Anderem auch nachdem ich mit den SQL Queries aus dieser Anleitung (How to keep your recorder database size under control - Community Guides - Home Assistant Community) meine, sinnvolle Ausschlüsse in meiner yaml definiert zu haben.

Wenn ich den purge mir repack durchführe, wird die Datenbank höchstens ein paar MB kleiner, nicht aber wie erhofft sehr viel kleiner.

Ziel ist grundsätzlich, alles nach einigen Tagen zu verwerfen, das nicht für das Energy Dashboard wichtig ist (was die energy Werte der Einzelgeräte einschließt).

Hier meine yaml (der recorder Teil)

recorder:
  purge_keep_days: 30  # Standard-Aufbewahrungszeit für weniger wichtige Daten: 30 Tage
  auto_purge: true     # Automatische Bereinigung aktivieren
#  commit_interval: 1   # Daten werden häufiger geschrieben, um Stabilität zu erhöhen
  include:
    entities:          # Wichtige Entitäten für Langzeitaufbewahrung
      # Gesamtstromverbrauch
      - sensor.energy_consumption_sum
      # Grid Consumption und Kosten
      - sensor.energy_import_daily
      - sensor.energy_import_daily_cost
      - sensor.energy_import_sum
      # Grid Return
      - sensor.energy_export_monthly
      - sensor.energy_export_sum
      # Solar Production
      - sensor.pv_schuppen_lifetime_production
      - sensor.pv_donato_lifetime_production
      # Wasserverbrauch und Kosten
      - sensor.watermeter_value
#      - sensor.watermeter_value_cost
      # Geräte-Verbrauch
      - sensor.garten_fahrrad_ladegerat_energy
      - sensor.waschmaschine_energy
      - sensor.keller_trockner_energy
      - sensor.kuche_kuhlschrank_energy
      - sensor.kuche_wassersprudler_energy
      - sensor.keller_technikraum_julian_pc_energy
      - sensor.kuche_kaffeemaschine_energy
      - sensor.keller_technikraum_server_energy
      - sensor.keller_kuhlschrank_energy
      - sensor.julian_stecker_klimagerat_energy
      - sensor.warmepumpe_stromverbrauch_ohne_cee
      - sensor.kuche_spulmaschine_energy
      - sensor.kuche_backofen_energy
#      - sensor.rack_stromverbrauch_ohne_pc # aktuell ohne server
#      - sensor.julian_kogeek_schreibtisch
      - sensor.wohnzimmer_media_energy
      - sensor.all_lichter_energy
      - sensor.shelly_pro_3em_cee_dose_total_active_energy
      - sensor.keller_werkstatt_pflanzenlicht_energy
      - sensor.keller_pflanzenlicht_alle_energy
      # general include
      - sensor.solar_total
#      - sensor.shelly_pro_3_em_warmepupe_total_active_power

  exclude:
    event_types:
      - call_service # ausgeschlossen weil viele Daten
      - automation_triggered # ausgeschlossen weil viele Daten
      - service_registered # ausgeschlossen weil viele Daten
      - entity_registry_updated # ausgeschlossen weil viele Daten
    domains:           # Ausschließen unwichtiger Domains
      - script
      - logbook
      - mobile_app
      - updater
      - media_player
      - persistent_notification
    entities:          # Ausschließen von spezifischen Entitäten
      - sensor.switch_device_power
      - sensor.watermeter_rate_per_digitalization_round
        # Voltage
      - sensor.shellypro3em_0cb815fd745c_phase_b_voltage
      - sensor.shellypro3em_0cb815fd745c_phase_c_voltage
      - sensor.shelly_pro_3_em_warmepupe_phase_a_voltage
      - sensor.shelly_pro_3_em_warmepupe_phase_b_voltage
      - sensor.shelly_pro_3_em_warmepupe_phase_c_voltage
      - sensor.shelly_pro_3em_cee_dose_phase_a_voltage
      - sensor.shelly_pro_3em_cee_dose_phase_b_voltage
      - sensor.shelly_pro_3em_cee_dose_phase_c_voltage
      # Current
      - sensor.shellypro3em_0cb815fd745c_phase_c_current
      - sensor.shelly_pro_3_em_warmepupe_phase_a_current
      - sensor.shelly_pro_3_em_warmepupe_phase_b_current
      - sensor.shelly_pro_3_em_warmepupe_phase_c_current
      - sensor.shelly_pro_3em_cee_dose_phase_a_current
      - sensor.shelly_pro_3em_cee_dose_phase_b_current
      - sensor.shelly_pro_3em_cee_dose_phase_c_current
      # Frequency
      - sensor.shellypro3em_0cb815fd745c_phase_c_frequency
      - sensor.shelly_pro_3_em_warmepupe_phase_a_frequency
      - sensor.shelly_pro_3_em_warmepupe_phase_b_frequency
      - sensor.shelly_pro_3_em_warmepupe_phase_c_frequency
      - sensor.shelly_pro_3em_cee_dose_phase_a_frequency
      - sensor.shelly_pro_3em_cee_dose_phase_b_frequency
      - sensor.shelly_pro_3em_cee_dose_phase_c_frequency
      # Apparent Power
      - sensor.shellypro3em_0cb815fd745c_phase_c_apparent_power
      - sensor.shelly_pro_3_em_warmepupe_phase_a_apparent_power
      - sensor.shelly_pro_3_em_warmepupe_phase_b_apparent_power
      - sensor.shelly_pro_3_em_warmepupe_phase_c_apparent_power
      - sensor.shelly_pro_3em_cee_dose_phase_a_apparent_power
      - sensor.shelly_pro_3em_cee_dose_phase_b_apparent_power
      - sensor.shelly_pro_3em_cee_dose_phase_c_apparent_power
      # Power Factor
      - sensor.shelly_pro_3_em_warmepupe_phase_a_power_factor
      - sensor.shelly_pro_3_em_warmepupe_phase_b_power_factor
      - sensor.shelly_pro_3_em_warmepupe_phase_c_power_factor
      - sensor.shelly_pro_3em_cee_dose_phase_a_power_factor
      - sensor.shelly_pro_3em_cee_dose_phase_b_power_factor
      - sensor.shelly_pro_3em_cee_dose_phase_c_power_factor
      # Device Temperature
      - sensor.shelly1pm_ba5328_device_temperature
      - sensor.shellypro3em_0cb815fd745c_temperature
      - sensor.shelly_pro_3_em_warmepupe_temperature
      - sensor.shelly_pro_3em_cee_dose_temperature
  
    entity_globs:
      - sensor.006_*
      - switch.006_*
      - binary_sensor.006_*
      - number.006_*
      - cover.006.*
      - select.006_*

Ist die YAML inkorrekt definiert? Mache ich beim Purge was falsch? Was mache ich falsch?

Danke schon mal!

tarag · 30. Dezember 2024 um 04:53

Ohne mir die YAML näher anzusehen… Könnte es sein, dass Dein Datenträger ein Problem hat?

Wieso purge nur alle 30 Tage?

Findest Du Probleme in den Logs?

Und insbesondere da Du ja Probleme hast, mit der Stabilität.

Wieso zum Teufel werden solche Dinge auskommentiert?!

dp20eic · 30. Dezember 2024 um 07:46

Moin,

Kannst Du zu Deiner Gesamtinstallation vorher noch etwas sagen, auf welchem System läuft HA, wie sieht es da mit Memory und Plattenplatz aus?

Ein purge, alle 30 Tage, wurde ja schon angesprochen, das bedeutet ja, dass die Maschine alles zusammensammeln muss, was die letzten 30 Tage in die Datenbank geschrieben wurde, wenn dann ein purge nicht zu Ende kommt, weil zu viel Daten, dann kommt er auch beim nächsten Mal nicht durch, und Daten bleiben liegen.
Dann muss man noch wissen, dass bei jedem purge die Daten, die gelöscht werden, nicht einfach verschwinden, sondern erst einmal in eine Art Backup geschrieben werden, sodass man auf den ersten Blick keinen Platz auf der Platte freibekommt, erst wenn der purge erfolgreich gelaufen ist, wird das Backup auch bereinigt.
Die Datei, liegt parallel, zum Datenbankfile, mit der Endung .wal.


  Home Assistant URL:       http://homeassistant.local:8123
  Observer URL:             http://homeassistant.local:4357
➜  ~ ls -la homeassistant/home-assistant_v2.db*
-rw-r--r--    1 root     root     5971570688 Dec 30 08:18 homeassistant/home-assistant_v2.db
-rw-r--r--    1 root     root       6200632 Dec 30 08:19 homeassistant/home-assistant_v2.db-wal
➜  ~

Du kannst erst einmal damit anfangen, die ungewollten Daten (exclude) aus der Datenbank zu bekommen.

Dabei nicht mit dem größten Brocken anfangen, sondern langsam nach oben arbeiten, ich würde auch mindestens 7-10 Tage stehen lassen, wenn Du dann alles auf ein gewünschtes Format gebracht hast, dann kann man sich auch noch einmal um den Rest kümmern

Hier hängt es davon ab, wie alt die Daten schon sind, liegen da Daten vor, die z. B. schon vor 90 Tagen in die Datenbank geschrieben wurden, würde ich auch erst einmal versuchen alles, was älter ist loszuwerden, damit die zu löschende Datenmenge nicht zu groß wird.

Lange Rede, kurzer Sinn, Du musst erst einmal von Hand aufräumen, damit die Automatik wieder greift.

VG
Bernd

crypton · 30. Dezember 2024 um 07:52

@derberg welches Speichermedium benutzt du?

Eventuell, weil er den Default von 5 Sekunden benutzen möchte, um ggf. das Speichermedium zu schonen ?

derberg · 30. Dezember 2024 um 10:51

Vielen Dank für die Ideen,
erst mal zum System: Hass läuft als VM in Unraid (2 CPU, 8 GB RAM, 256 GB vdisk, zu 60% voll).

nur als versuchtes Troubleshooting aus Verzweiflung, also wieder anmachen?

Ja, es gibt immer Probleme die so oder so ähnlich in den Logs auftauchen:

Das ist eine gute Idee, ich muss nun erstmal aus dem Backup wiederherstellen, heute Nacht ist es wieder kaputt gegangen…

EDIT:

wie kann ich die größten Brocken identifizieren?

Ich hatte auch versucht, aus dem known good Status im SQLite Plugin zu exportieren, mit dem Ziel die Dinge in eine neue Datenbank zu importieren.
Leider failt es schon beim Import:

dp20eic · 30. Dezember 2024 um 13:20

Moin,

das sind nur Vermutungen, da mir die Metriken fehlen, ich denke du achtest auch nicht gezielt darauf, was beim Lauf vom purge passiert, es kann also sein, das der Speicher von 8 GB nicht ausreicht, um alle Daten zu sammeln.
Wie gesagt alles nur Glaskugel raten.

sorry Bildchen, schaue ich mir nicht mehr an, ist zu anstrengend für meine Augen und zitieren kann man daraus auch nicht, Logs, Code, gehört in Code-Tags </>

Aus einem anderen Thread

Das sql muss Du im Add-on `

SELECT states_meta.entity_id, COUNT(*) as count FROM states INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id GROUP BY states_meta.entity_id ORDER BY count DESC LIMIT 10;

kann ich nichts zu sagen, kann an einer kaputten Datenbank liegen, oder, oder.

Ich würde erst einmal anfangen aufzuräumen, und hoffen, dass du dabei ohne Fehler durchkommst.

Ansonsten fallen mir nur noch Hacks ein, wo man eine neue Datenbank anlegen lässt und dann zusätzliche neue Tabellen erstellt, in die dann, die alten Statistikdaten kopiert werden und dann die Daten von da aus in die Originaltabellen geschrieben werden.
Das müsste ich mir dann auch erst einmal genauer anschauen, aber es gab hier schon mal jemand, der das gemacht hat, müsste ich aber auch erst suchen.

VG
Bernd

derberg · 30. Dezember 2024 um 20:09

ich habe mehrfach versucht einzelne der Funde zu löschen, das hat quasi fast immer das direkte korrumpieren der Datenbank zur Folge.

auch wenn ich Einträge von weiter unten nehme…
Gibt es noch etwas, was ich machen kann?

Moekli · 30. Dezember 2024 um 22:01

Hatte das gleiche Problem vor einiger Zeit auch und habe da auch lange dran rum experimentiert aber keine Lösung zu gefunden. Am Ende des Tages habe ich die DB einfach unbenannt und HA neu gestartet sodass er eine neu angelegt hat. War zwar mega doof für mich da alle History Daten weg waren aber danach lief es zumindest wieder und bis heute keine Probleme mehr mit der größe.

dp20eic · 31. Dezember 2024 um 08:57

Moin,

ja, hatte ich oben schon gesagt, Monitoren, was macht das System in den Situationen!
Ich kann mir halt sehr gut vorstellen, dass Dir die Ressourcen ausgehen, also, versuch es mal mit >= 16 GB Memory.

Hast Du denn mittels HA → Entwicklerwerkzeuge → Aktionen die Daten versucht zu löschen?

VG
Bernd

P.S.: wie lauten denn eventuelle Fehlermeldungen der SQLite? Es kann ja sein, dass man an den SQLite Einstellungen noch etwas optimieren.

derberg · 2. Januar 2025 um 17:24

@dp20eic vielen Dank für die Antwort. Gutes neues Jahr allerseits!
Ich habe der VM inzwischen 16 GB RAM zugewiesen. Das half leider nichts.

Der Fehler ist seit ein paar Tagen nicht gehabt, weil ich aus Zeitmangel recorder auto purge abgeschaltet habe. Leider ist das letzte Event aus den Logs herausgelaufen.

The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-12-30T03:13:39.980766+OO:00 in order to allow startup to proceed
04:13:39  - (FEHLER) Recorder

Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed [SQL: UPDATE states SET WHERE states.old_state_id IN (?, ?,? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
04:13:39  - (FEHLER) Recorder

Error executing query
04:13:39 — (FEHLER) helpers/recorder.py

Das obenstehende konnte ich den Screenshots noch entnehmen.

Ja, auch dann geht die Datenbank kaputt.

dp20eic · 2. Januar 2025 um 18:29

Moin,

hast Du denn mal darauf geachtet, wie es bei einem Versuch zu löschen oder einen purge auszuführen, mit dem Memory bestellt ist, läuft da etwas voll?

Hast Du das Add-on SQLite WEB installiert, kannst Du mal das eingeben, und schauen, ob Deine Datenbank wirklich i. O. ist auch nach dem Wiederherstellen?

PRAGMA integrity_check

Kann bei einer sehr großen Datenbank lange laufen, also Geduld, das ist ein i. O resultat.

VG
Bernd

derberg · 2. Januar 2025 um 20:20

Das ist sehr interessant, der check gibt einen Fehler aus obwohl ich die Datenbank vorher (mit Windows und folgendem Befehl) repariert habe
sqlite3 ./home-assistant_v2.db .recover | sqlite3 ./home-assistant_v2.db.fix
die .fix file habe ich dann, wie schon oft, bei gestopptem Home Assistant Core per SMB draufkopiert und im Anschluss die .db file damit ausgetauscht.

dp20eic · 2. Januar 2025 um 20:38

Moin,

ich habe von solchen versuchen, schon gelesen, aber noch nie selbst gemacht.

Es sieht ja so aus, dass der Index nicht korrekt erstellt ist, es scheinen mehr Daten im Index zu sein als in der Tabelle states_attributes

Wenn man sich die Tabellenstruktur anschaut, dann wird ein Index auf den hash gelegt, um schneller darauf zugreifen zu können,

CREATE INDEX ix_state_attributes_hash
ON state_attributes (hash)

Du könntest den Index löschen und dann neu erzeugen, dann nochmals den health check machen.

Das alles aber nicht ohne Backups und drei Ave Maria

VG
Bernd

derberg · 2. Januar 2025 um 21:07

Backups sind vorhanden. Das würde ich gerne versuchen.

beinhaltet der von dir gezeigte Befehl das Löschen des Indexes?
CREATE INDEX ix_state_attributes_hash
ON state_attributes (hash)

oder muss ich das hier über SQlite web machen?

Ich sehe gerade hier gibt es mehrere Lost and Found, hat das damit zutun?

dp20eic · 2. Januar 2025 um 21:22

Moin,

nein, das ist ja nur das create Statement.

Löschen kann man das im SQLite Web

Erstellen dann halt so

Das sind wohl zusätzliche Tabellen, die beim Reparieren erstellt wurden, ich kenne die nicht, man müsste mal schauen was da so an Content enthalten ist.
Aber mein Bauchgefühl sagt mir, dass man diese Tabellen löschen kann, den die Daten daraus wird man nicht mehr zu den original Tabellen zuordnen können.

VG
Bernd

derberg · 2. Januar 2025 um 21:44

ok, muss dann nur der Index von state_attributes oder von den andren Tabellen auch?

dp20eic · 2. Januar 2025 um 21:48

Moin,

wenn ich mir dieses Bild anschaue

Dann sehe ich da nur
grafik

Was sich hinter diesem verbirgt, kann ich nicht sagen

Wenn das noch ein anderer Index ist, dann müsste man sich das auch noch mal anschauen.

VG
Bernd

derberg · 2. Januar 2025 um 23:05

nach dem erneuten Integrity check bekomme ich weniger Fehler

und nach den bisherigen Aktionen läuft HASS noch (inkl. meiner Energy daten).
Das scheint sich nicht auf den Index einer bestimmten Tabelle zu beziehen.

dp20eic · 3. Januar 2025 um 09:01

Moin,

ich habe noch nicht weiter nachgeschaut, aber der Fehler hört sich für mich nach einem Problem in der Organisation der Daten auf der Platte an, weil die Daten ja linear geschrieben werden, wenn dann Daten gelöscht werden, entstehen Lücken, diese Lücken können nicht wiederverwendet werden, daher gibt es die Möglichkeit die SQLite Datenbank mittels repack neu auf der Festplatte zu schreiben.

Hier mal etwas Lesestoff

In den Entwicklerwerkzeugen → Aktionen

Du könntest auch hier mal den Tagen spielen.

VG
Bernd

derberg · 3. Januar 2025 um 11:04

Ja, repack war das Ziel zu verwenden wenn die Datenbank in einem “no error” Zustand ist, um sie zu verkleinern.
Leider war es gestern abend zu spät um weiter zu machen und eben sehe ich: Es gibt wieder einen DB crash:

Logger: homeassistant.components.recorder.core
Quelle: components/recorder/core.py:882
Integration: Recorder (Dokumentation, Probleme)
Erstmals aufgetreten: 04:12:09 (1 Vorkommnisse)
Zuletzt protokolliert: 04:12:09

Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed [SQL: PRAGMA OPTIMIZE;] (Background on this error at: https://sqlalche.me/e/20/4xp6)
Traceback (most recent call last):
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.DatabaseError: database disk image is malformed

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 882, in _process_one_task_or_event_or_recover
    task.run(self)
    ~~~~~~~~^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 160, in run
    periodic_db_cleanups(instance)
    ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 788, in periodic_db_cleanups
    connection.execute(text("PRAGMA OPTIMIZE;"))
    ~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
        self,
        distilled_parameters,
        execution_options or NO_OPTIONS,
    )
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self, distilled_params, execution_options
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
        dialect,
    ...<8 lines>...
        cache_hit=cache_hit,
    )
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ~~~~~~~~~~~~~~~~~~~~~~~~~^
        dialect, context, statement, parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        e, str_statement, effective_parameters, cursor, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
[SQL: PRAGMA OPTIMIZE;]
(Background on this error at: https://sqlalche.me/e/20/4xp6)

Das ist sehr seltsam, ich dachte das automatische Bereinigen hätte ich mit

recorder:
  purge_keep_days: 7 Daten: 7 Tage
  auto_purge: false
  commit_interval: 1

deaktiviert.