Homeassistant (Container) und externe Datenbank, bzw die Umstellung mit Import?


sonic6

Recommended Posts

Mahlzeit Zusammen,

 

das Thema bezieht sich zwar eher zweitranging auf Unraid, könnte aber dennoch den ein oder anderen von uns betreffen:

Die Nutzung von Homeassistant mit einer externen Datenbank (zb MariaDB) und der Import einer vorhandenen laufenden DB.

Ich nutze HA als Docker Container und damit das ganze "generell" Anwendbar bleibt, sollte wir das in der Diskussion als Basis behalten.

 

Da ich gewissen Daten aus dem HA gerne länger als 14 Tage verfolgen würde, denke ich über eine Umstellung auf eine externe Datenbank nach.

Hierzu stellen sich mir zwei Fragen:

 

1. Welches Datenbankmanagement System? MariaDB? InfluxDB?

Ich habe MariaDB derzeitig schon für Nextcloud und Ferdi am laufen. Auch habe ich Grafana mit der internen DB, welche ich im gleiche Zuge umstellen würde. (Auch hier stellt sich mir die Frage, zu welchem DB Manager?)

Da das ganze noch Neuland für mich ist, habe ich hier noch keine Entscheidung getroffen.

 

2. Wie Importiere/Migriere ich die bestehende interne HA Datenbank in die neue externe Datenbank, damit ich meine vorhandenen Daten weiter nutzen kann?

Hier geht es mir primär um die Daten aus der Energie Messung und Energie Dashboard, da diese schon seit letztem Dezember am aufzeichnen sind.

 

Vielleicht gibt es ja jemanden, der das ganze schon hinter sich hat, unterstützen können, oder vor den gleichen Fragen/Vorhaben stehen?

 

*edit: HA als Docker Container

 

Edited by sonic6
Link to comment
  • sonic6 changed the title to Homeassistant (Container) und externe Datenbank, bzw die Umstellung mit Import?

...bedenken, daß die genannten DBs zwei grundsätzlich unterschiedliche Technologien darstellen..MariaDB ist ne klassische SQL/RDBMS während influxdb eine TimeseriesDB ist.
Erstere ist für transaktionale Daten (zB Stammdaten, Listen, ...) und ne timeseries ist für Events (was galt wann, IoT).

Keine Ahnung was HA für was braucht und auf welchem Usecase Dein Fokus liegt.
MariaDB scheint auf jeden Fall mal unterstützt zu sein, wird für IoT basierte Anwendung aber langsamer sein und größer werden.

Gesendet von meinem SM-G780G mit Tapatalk

Link to comment
6 hours ago, sonic6 said:

DBs sind für mich noch Neuland.

 

Darf ich Dich fragen was Dich an der SQLite stört? Die Limits sind gigantisch, sie ist schnell - meist schneller als eine voll aufgeblasene SQL Engine, sie ist selbstheilend - ich kenne niemanden der jemals einen Absturz hatte, hat einen extrem kleinen Footprint und lässt sich ganz einfach administrieren.

 

Zum Ausprobieren könntest Du einen Dump ziehen und diesen Dump (DDL/SQL) versuchen in eine MariaDB zu laden. Ich denke bis auf das "auto increment" müsste der Dump eigentlich zu einem großen Teil kompatibel zu den üblichen SQL Standards sein. Ich selbst habe es noch nicht probiert. Meine HA DB dümpelt bei 500 MB, das ist wirklich PillePalle. Selbst meine 4 GB Plex (DB und BLOB DB) ist PillePalle was die SQLite Größe angeht.

 

Im Gegensatz dazu kommt mir die 1,8 GB Nextcloud DB schnarch langsam vor. Aber das kann auch an Nextcloud selbst liegen.

 

Edited by hawihoney
Link to comment
19 minutes ago, sonic6 said:

Rückwirkend sehe ich die Verläufe meiner Entitäten nur 10 Tage.

 

Oh, das ist mir ja noch nicht aufgefallen. Ist bei mir auch so. Ich kann mir nicht vorstellen, dass Daten aus den Datenbanken gelöscht werden. HA wird es wohl aggregieren und nicht mehr anzeigen. Ob das mit einem anderen DB System anders ist wage ich zu bezweifeln.

 

Ich habe dazu folgendes im Web gefunden:

 

Quote

Look at your configuration.yaml and see what your recorder purge length is.
You can set how often HA will keep data. If you don’t have it set then the default is 10 days.

https://www.home-assistant.io/integrations/recorder/

 

Das ist wohl konfigurierbar. Und damit ist es unabhängig vom verwendeten Datenbank System.

 

Ich denke, damit in der configuration.yaml könntest Du das lösen:

 

recorder:
  purge_keep_days: 60

 

 

Edited by hawihoney
  • Like 1
Link to comment
Quote
purge_keep_days integer (optional, default: 10)

Specify the number of history days to keep in recorder database after a purge.

okay, hatte die Variable wohl falsch interpretiert. Habs zum testen mal auf 30 gesetzt und auch ´commit_interval´ auf 5 gesetzt um die SSD ein wenig zu entlasten.

Link to comment
On 10/20/2022 at 6:00 PM, hawihoney said:

Ich denke, damit in der configuration.yaml könntest Du das lösen:

Ich kann nun Rückmelden, dass du recht hattest. Habe den Wert auf "30" gesetzt und kann jetzt schon 13 Tage Rückwirkend die Verläufe sehen.

Meine DB ist aber dafür jetzt schon 1GB groß. (Ich hatte nur leider vor 3 Tage nicht geschaut wie groß diese ist.)

Ich werde das mal weiter beobachten.

Link to comment
5 hours ago, sonic6 said:

Meine DB ist aber dafür jetzt schon 1GB groß.

 

Super, das es klappt. Bzgl. der 1 GB: Ist das wirklich relevant? Datenbank-seitig ist das Kinderkram:

 

https://www.sqlite.org/limits.html

 

Es kann also nur in der VM oder im Container, und dem dort hinterlegten Storage, zum Problem führen. Besser ist hier natürlich der direkte Zugriff auf einen Host-Ordner statt über ein Image ...

 

Da ich gerade erst mit HA im Container neu begonnen habe bin ich mit 120 MB noch einfach belegt. Aber wenn ich mir meine Plex SQLite DB anschaue - irgendwas um 4 GB - dann kann ich sage, dass der Zugriff noch genau so schnell ist wie am ersten Tag.

 

Link to comment
  • 2 months later...

Kann ich davon ausgehen, dass HA deaktivierte Entitäten nicht mehr loggt?

 

Ich habe beim Shelly 3EM nun alles deaktiviert, was mich eh nicht interessiert:

image.thumb.png.020997ff0554631db2ca2f3922174b44.png

 

 

Richtig beknackt ist ja, dass der sogar zB den Bass-Regler meiner Sonos Boxen loggt (wovon ich nicht wenige habe und er macht das auch bei Höhen-Einstellungen usw):

image.png.5d410ac0592146f8268d5145b4159b9a.png

 

Für mich nicht nachvollziehbar, warum man das nicht direkt über die GUI ein- oder ausschalten kann.

Link to comment

Den Beitrag vom anderen Thread:

 

Ich würde gerne deutlich länger den gemessenen Verbrauch der Aktoren darstellen können. Standardmäßig speichert HA ja nur 10 Tage. Nun könnte ich purge_keep_days auf 90 Tage ändern:

recorder:
  purge_keep_days: 90

 

Dem folgenden Thread entnehme ich allerdings, dass dadurch die Datenbank sehr groß wird und man gewisse Dinge wohl rausfiltern sollte:

https://community.home-assistant.io/t/how-to-keep-your-recorder-database-size-under-control/295795

 

Verstehe ich das richtig, dass man Entitäten nur ein- oder ausschließen kann? Ich hätte jetzt erwartet, dass man zB den Verbrauch 90 Tage speichert, aber Spannung und Temperatur nur 7 Tage. Stattdessen muss ich jetzt wohl Spannung und Temperatur komplett ausschließen?!

 

Und wenn ich nun alle Temperatur-Messungen nicht loggen möchte, dann wäre das so korrekt?

 

# Amount of days to store the history (default 10 days)
recorder:
  purge_keep_days: 30
  exclude:
    entities:
      - sensor.lumi_lumi_plug_maeu01_devicetemperature_*
      - sensor.lumi_lumi_plug_maeu01_electricalmeasurementpowerfactor_*
      - sensor.lumi_lumi_plug_maeu01_electricalmeasurementrmsvoltage_*

 

Ich habe jetzt erstmal den Rekorder auf 30 Tage erhöht. Die home-assistant_v2.db ist aktuell noch 186 MB groß. Mal sehen wie groß sie in 30 Tagen ist.

 

Eigentlich schade, dass HA die Speicherbelegung pro Entität nicht einfach direkt über die GUI anzeigt (separate Spalte in der Entitäten-Liste).

 

 

Link to comment

Arbeitsbedingt besitze ich Navicat for SQLite, worüber ich die home-assistant_v2.db entsprechend dem oben verlinkten How-To ausgewertet habe:

 

image.png.f50b8f8b83cdeee1ce13b36339e90dbb.png

 

Keine Ahnung was ich mit dieser Erkenntnis nun anfangen soll ^^

 

Das hier ist schon klarer:

 

image.png.70e7e898095244560e189233a387d0ad.png

 

Ich werde also wohl als erstes dieses sensor.date und sensor.time Entitäten excluden.

 

Hier sieht man auch die völlig unsinnigen Logs für Bass, Höhen, usw:

 

image.png.015fb3ff445248e9586b210b4fb161ab.png

 

Was heißt hier eigentlich "number" im Gegensatz zu "switch"?!

 

 

image.png

Link to comment

Als erstes habe ich mal bei allen Geräten die für mich irrelevanten Entitäten deaktiviert, in der Hoffnung, dass die dadurch auch nicht mehr geloggt werden. zB brauche ich nicht wissen welche Spannung anliegt. Werden halt irgendwas um die 230V sein. Who cares ^^

 

image.thumb.png.0ee16d99a09a313d8ff0078aba49fb5b.png

 

Dadurch habe ich nun bei den Aqara Smart Plugs Spannung, PowerFactor, Device Temperature und den Identify Button deaktiviert:

image.png.ecc6963e4aafed59905507c9a655005c.png

 

image.png.7665d7bd355714b3b005a98f1c7bf85d.png

Link to comment

So ich denke ich habe nun alles deaktiviert. Dauert echt ewig bei vielen Aktoren 🤪

 

Außerdem habe ich nun das in der configuration.yaml ergänzt, damit die Datenbank entsprechend aufgeräumt wird:

 

# Logging
recorder:
  # log period in days (default: 10)
  purge_keep_days: 1100
  # write log to disk interval in seconds (default: 1)
  commit_interval: 60
  exclude:
    domains:
      - device_tracker
      - media_player
      - sun
    entity_globs:
      - sensor.date*
      - sensor.time*
      - sensor.internet_time*
      # sonos
      - switch.*_surround_music_full_volume*
      - switch.*_surround_enabled*
      - switch.*_subwoofer_enabled*
      - switch.*_speech_enhancement*
      - switch.*_night_sound*
      - switch.*_loudness*
      - switch.*_crossfade*
      - number.*_treble*
      - number.*_bass*
      - number.*_gain*
      - number.*_surround_level*
      - number.*_sub_gain*
      - number.*_audio_delay*
      - binary_sensor.*microphone*
      # shelly
      - sensor.*_power_factor
      - sensor.*_voltage
      - sensor.*_current
      - binary_sensor.*_overpowering
      # aqara weather sensor
      - sensor.*_pressure*
      - sensor.*_humidity*
      - sensor.*_battery*
      - sensor.*_temperature*
      # aqara smart plug
      - sensor.*_electricalmeasurementrmsvoltage*
      - sensor.*_electricalmeasurementpowerfactor*
      - sensor.*_devicetemperature*
      - button.*_identifybutton*
      # aqara motion
      - sensor.*_illuminance*
      - sensor.*_battery*
      - sensor.*_aqaramotionsensitivity*
      - binary_sensor.*_occupancy*
      - binary_sensor.*_iaszone*
    entities:
      - weather.forecast_home

 

Wie man sieht habe ich den Log-Zeitraum massiv auf 1100 Tage (3 Jahre) verlängert, weil ich zB den Verbrauch der Wärmepumpe vom letzten mit dem vom vorletzten Jahr vergleichen können möchte und wenn ich das zB Mitte diesen Jahres erst mache, sind das ja schon 2.5 Jahre.

 

Ich bin gespannt welchen Einfluss diese Änderungen nun auf die DB-Größe haben wird. Aktuell hat sie eine Größe von 220 MB. Laut Dokumentation macht Home Assistant jede Nacht um 04:12 einen Datenbank-Cleanup.

Link to comment
8 hours ago, mgutt said:

Verstehe ich das richtig, dass man Entitäten nur ein- oder ausschließen kann? Ich hätte jetzt erwartet, dass man zB den Verbrauch 90 Tage speichert, aber Spannung und Temperatur nur 7 Tage. Stattdessen muss ich jetzt wohl Spannung und Temperatur komplett ausschließen?!

Hatte das ganze Thema nur überflogen und keine unterschiedlichen Log-Perioden finden können. Hatte ähnliche vor, aber aufgrund von Faulheit und vorhandenem Speicherplatz habe ich einfach alles weiter loggen lassen bei 100 Tagen.

 

8 hours ago, mgutt said:

Kann ich davon ausgehen, dass HA deaktivierte Entitäten nicht mehr loggt?

Ich habe zumindestest keine Historie mehr zu deaktivierten Entitäten.

 

8 hours ago, mgutt said:

Richtig beknackt ist ja, dass der sogar zB den Bass-Regler meiner Sonos Boxen loggt

Ja, gilt theoretisch für fast alles was man parametrieren kann... Zustände werden halt geloggt. Ähnliches gilt ja auch für die Identify-Buttons von diversen Zigbee Geräten.

 

6 hours ago, mgutt said:

So ich denke ich habe nun alles deaktiviert. Dauert echt ewig bei vielen Aktoren 🤪

Jo,... die Fritzbox/AVM Integration kann einen da richtig Nerven kosten.

Link to comment

Guten Morgen,

 

In der Developer - Doku ist das ganze Thema auch aufgegriffen, hier... Wobei die ganze Data Rubrik Recht interessant ist.

 

Die Denkweise von Homeassistent ist, das der Recorder erstmal alles für den angegebene Zeitraum in die DB logt und auch wieder aufräumt. Ein Teil der Aufzeichnungen wird allerdings auch in die "Long-Term Statistics" übernommen, wie z.B. die Werte von Energy Dashboard oder halt nach Belieben auch eigene...

Link to comment
8 hours ago, JoMe said:

Long-Term Statistics

Ok und wo kann ich auf die zugreifen?

 

Laut Doku werden die niemals gelöscht, dann bräuchte ich meine lange Rekorder-Zeit ja nicht.

Quote

Statistics are never purged. Because they are summarized every hour, they only create 24 entries per day.

 

EDIT: Ok, man kann sich zb ein Statistik Diagramm hinzufügen. Nehme ich da die vom Aktor ermittelte Summe und dann "Änderung", so sollte das der Vebrauch der jeweiligen Woche sein. Allerdings scheint es Ende Dezember zu irgendeinem Fehler gekommen sein:

 

Screenshot_20230120-165725.thumb.png.1de00ef08faa997e1906977ed45a9e9c.png

 

Ich sollte mir in jedem Fall neue Entitäten erstellen, wo PC + Schreibtisch summiert wird oder TV + Lautsprecher. Dann hat man nachher nur noch eine Linie. Wobei HA das eigentlich auch gleich bei dem Statistik Diagramm anbieten sollte, statt dass der User jetzt basteln muss.

 

9 hours ago, sonic6 said:

Jo,... die Fritzbox/AVM Integration kann einen da richtig Nerven kosten.

Ich habe keine AVM Aktoren. Nur Aqara und Shelly. Aber davon eben immerhin schon 21.

 

 

Link to comment
20 hours ago, mgutt said:

Ok und wo kann ich auf die zugreifen?

 

Laut Doku werden die niemals gelöscht, dann bräuchte ich meine lange Rekorder-Zeit ja nicht.

 

Ja, genau. Die Antwort hast du dir ja mit deinem EDIT schon selbst gegeben. Ob du nun HomeAssistant zur Auswertung benutzt liegt natürlich bei dir. Eine andere Denkweise könnte natürlich sein, das HomeAssistant die DB befüllt und jährliche Abfragen direkt aus der Datenbank abgefragt werden. Wie überall gibt es da wieder 100te Möglichkeiten.

Falls du bei HomeAssitant bleiben magst, schau dir mal die apexcharts an. Dort hast du doch einiges mehr an Formatierungsoptionen und kannst u.a. auch mal "schnell" eine Reihe von Daten per csv direkt im Diagramm herunterladen.

 

Der Inhalt ist damit vorbei, noch kurz mein Senf dazu:
Bin selbst seit vergangenem Jahr von einer ioBroker-Installation zu HomeAssistant am wechseln. Bin sicherlich kein Datenbankspezialist, hab gelegentlich beruflich was mit diesem Thema zu tun. Finde so wie es in HA gelöst ist entgegen mancher Kommentare hier und im restlichen Internet doch sehr schick. Wenn man sich die Datenbankstruktur anschaut, wie diese aufgebaut ist, auch teilweise mit den JSON-Blöcken um weitere Spalten und Tabellen zu sparen - kann da nur meinen Hut ziehen. Man vergisst bei solchen Sachen immer schnell, dass es da ein großes Spektrum an Anforderungen verschiedener Nutzer gibt, demnach kann da immer nur ein Kompromiss rauskommen.

 

Auch wenn sqlite da sicherlich nicht an seine Limits kommt bietet eine MariaDB da doch noch andere Möglichkeiten. Auch von der Performance wenn da mal ein paar mehr Anfragen zusammen kommen. Klar, die Frage ist auch hier ob es sich da lohnt und besonders ob es sich lohnt sich da einzuarbeiten oder ob OUT-of-the-box dann doch wieder gut genug ist. 

 

 

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.