DB Langzeitspeicherung - InfluxDB "richtig" nutzen - Retention Policies

[mathmoe]

Leider habe ich ein paar Geräte, welche mindestens sekündlich Daten liefern, und das bei vielen Entitäten. Dazu zählt der SMA Wechselrichter und Batteriespeicher, sowie diverse Shellys.
Das führt dazu, dass meine Datenbankgröße relativ schnell ansteigt. Zudem wollte ich mich nicht mit 10 Tagen History zufriedengeben und bin auf 180 Tage hoch:

recorder:
    purge_keep_days: 180

Das sorgte dafür, dass die DB schnell auf über 40Gb anstieg. Ich bemerkte das auch erst, weil die Backups ewig brauchten.

Also erstmal recorder filtering genutzt, was viel brachte, aber nicht meine 100%ige Zufriedenheit…

    exclude:
        domains:
        #  - device_tracker
          - media_player
          - uptime
          - update
          - time_date
          - worldclock
          - weather
          - geo_location
        entity_globs:
            - sensor.*uptime*
            - sensor.*voltage*
            - sensor.*frequency*
            - sensor.*_l1
            - sensor.*_l2
            - sensor.*_l3
            - switch.*sync*
            - switch.*reverse*
            - switch.wled_*current*
            - switch.wled_*count*
            - switch.wled_*palette*
            - light.wled*
            - button.*update*
            - button.*restart*
            - sensor.hlk_ld2410b_a2e6*
        entities:
            - sensor.bsb_lan_status
            - homematic.ccu2

Die DB war nun kleiner, wir reden aber noch immer von über 7Gb. Tendenz steigend.

Einer der nächsten Schritte war es nun auf MariaDB zu wechseln und mir danach InfluxDB genauer anzuschauen.

Maria DB für 180 Tage.
InfluxDB ohne Zeitbegrenzung → möchte ich, um auch nach Jahren noch Daten vergleichen zu können.

In InfluxDB hab ich dann weitere Retention Policies angelegt:

Die haben unterschiedliche Gültigkeitswerte für die jeweiligen Daten. InfluxDB kümmert sich von selbst um das löschen der alten Daten.

Nun müssen die Daten in die neuen Retention Policies eingefügt werden. Auch das geschiet automatisch über Continuos Querys:

CREATE CONTINUOUS QUERY cq_downsample_1h ON homeassistant BEGIN SELECT mean(*) INTO homeassistant.infinite.:MEASUREMENT FROM homeassistant.autogen./.*/ GROUP BY time(1h), * fill(previous) END

CREATE CONTINUOUS QUERY cq_downsample_5min ON homeassistant BEGIN SELECT mean(*) INTO homeassistant."1year".:MEASUREMENT FROM homeassistant.autogen./.*/ GROUP BY time(5m), * fill(previous) END

Man achte bei “Group by” auf den Zeitwert in “time”. Hier wird der durchschnittswert errechnet. Die Retention Policy “1year” hat 5 Minuten Durchschnittswerte und “infinite” Stunden Durchschnittswerte.

Somit habe ich 14 Tage alle Werte, Ein Jahr mit 5 Minuten Werten und unendlich mit Stunde.

Man sieht schön, wie die Größe der InfluxDB ab dem 13. April nicht mehr steigt.

Somit sollte auch auf längere Zeit ein zurückgreifen auf Daten möglich sein, ohne das die Datenbank ins unermessliche wächst.

Es gibt bestimmt noch weitere coole Dinge die man mit InfluxDB machen kann. Da beschäftige ich mich dann erst demnächst irgendwann mit

[skynet]

Wow cool, Danke.

Habe auch beides am Start und die Backups werden grösser/Länger. Werde mich dieser Thematik gerne annehmen und bin über deine erkentnisse Infos sehr froh.

[Tueftler]

Bei mir ist es genau das selbe.
Ich habe auch Simon schon einmal angeschrieben ob er über das Thema einmal ein Video machen könnte.
Leider wird es viel zu wenig Beachtung geschenkt finde ich…
Ich wundere mich auch, dass es dazu noch kein Cleanup-Addon oder so gibt.

Danke für Deine Ideen!

[Smartblau]

Danke dir! Wo legt man denn die CQ an und verwaltet diese, sprich eine Übersicht welche CQ aktiv sind?

edit: und zweite Frage: was ist denn der eleganteste (noob-freundlichste) Weg, um einen Überblick darüber zu bekommen, welche domains und entities am meisten Speicherplatz benutzen?

[mathmoe]

@Smartblau

Wo legt man denn die CQ an und verwaltet diese, sprich eine Übersicht welche CQ aktiv sind?

Das machst Du in InfluxDB unter Explore. Dort kann man per Querys die einstellungen vornehmen oder einfach nur auf die Daten zugrifen (SELECT ... FROM ... ). Es gibt rechts so ein Drop-Down Menü “Metaquery Templates” worin man die gängigsten Templates zur Verwaltung der DB finden kann. In dem Fall lautet der Befehl:

SHOW CONTINUOUS QUERIES

Mit DROP wird gelöscht und mit CREATE angelegt.

@Smartblau

was ist denn der eleganteste (noob-freundlichste) Weg, um einen Überblick darüber zu bekommen, welche domains und entities am meisten Speicherplatz benutzen?

Hier hat mir folgender Thread geholfen: How to keep your recorder database size under control - Community Guides - Home Assistant Community

Da ich MariaDB nutze, hab ich noch phpMyAdmin installiert und per folgendem Query schon einen guten Überblick erhalten, welche Entites VIEL Daten liefern:

SELECT
  COUNT(*) AS cnt,
  states_meta.entity_id
FROM states
LEFT JOIN states_meta ON (states.metadata_id=states_meta.metadata_id)
GROUP BY states_meta.entity_id
ORDER BY cnt DESC;

Man kann das ganze, wie im Thread beschrieben, noch mit der genauen Size ausgeben lassen, aber da lief, dank der Datenmenge, das Select ewig.

[Smartblau]

Hey! Vielen Dank

Und sorry für’s thread hijacken… aber da hab ich doch gleich die nächste InfluxDB Frage:
Weisst Du, wie man verhindert, dass negative Werte eines sensors gespeichert werden, bzw. wie man die nachträglich über QC entfernt, wenn ersteres nicht funktioniert?

[ryhoruk]

Das ist eine InfluxDB in der Version 1.x, oder?
In der 2.0 kann ich die “Retention Policy” nicht finden oder stehe ich da irgendwie auf dem Schlauch?

[jove]

Hallo
unter “EXPLORE” schaffe ich es nicht

@mathmoe

.:MEASUREMENT

Ich komme nur bis:

CREATE CONTINUOUS QUERY “cq_basis_1Std” ON “homeassistant” BEGIN SELECT mean() INTO homeassistant.infinity.??? FROM homeassistant.autogen././ GROUP BY time(1h), * fill(privious) END

einzugeben. Welchen Trick gibt es dabei?

[Blubberboy]

Das würde ich auch gerne verstehen. Ich bekomme die Statements auch nicht ausgeführt. Sobald nach dem . das : verwendet wird ist die Explore Funktion kaputt.

[jove]

Hier hat einer versucht ein Issue aufzumachen

Chronograf crashes when a colon is entered

Wurde aber sehr merkwürdig abgeblockt.

[PhUser]

Ja.
Einfach über das Terminal mit ssh auf das addon verbinden. Dann Influx starten und die Quere anlegen.
Das klappt.

[Blubberboy]

Das würde mich ja mal interessieren wie das gemacht wird. Kannst Du das mal näher beschreiben wie man da vorgeht?

Danke

[jove]

It Hassio konnte ich das nicht.
Bin daher auf Victoria Metrics umgestiegen.Erfahrungen habe ich noch nicht

[PhUser]

1. Das Aden “Advanced SSH & Web Terminal” installieren und starten
2. folgendes Eingeben: docker exec -it add-on_a0d7b954_influxdb bash
3. jetzt solltest Du in der Konsole vom influxdb Container sein

[Blubberboy]

Super. Danke für die Informationen.

[moetorbot]

Sorry dass ich den Thread hier nochmal ausgrabe, aber bei mir funktioniert der Query auf Grund des “.: Fehlers” auch nicht.
Und über SSH komme ich nicht auf das Addon. Ich bekomme die Fehlermeldung, dass es das Addon nicht gibt:

Error response from daemon: No such container: add-on_a0d7b954_influxdb

GIbt es noch eine andere Möglichkeit das irgendwie zu lösen?

[PhUser]

Du hast influx als Addon installiert?

[moetorbot]

Ja, über Einstellungen/ Addons

[PhUser]

Hmm, leider keine Idee.
Der Container heißt auch so?
Kannst Du den “Gesicherten Modus” beim “Advanced SSH & Web Terminal” deaktivieren?
Klappt es dann?

[moetorbot]

Der Container ist nicht mit dem Unterstrich, sondern mit Bindestrich geschrieben, dass hatte ich aber auch schon angepasst und den abgesicherten Modus hatte ich auch schon aus

Trotzdem kommt die Fehlermeldung.
Hat es irgendwie was damit zu tun, dass ich Docker nicht direkt installiert habe?