Neues Database Plugin

[bmx]

Ich hätte jetzt gerne drum gebeten, das Du die Vorschläge zum Tuning mal ausprobierst. Bei mir hatten diese Optimierungen nichts grossartiges ergeben. Auch eine Erhöhung von cache_size war ergebnislos.
Den Journal Mode WAL würde ich jetzt nicht setzen wollen. Nachher steigen mir die Leute auf's Dach weil ich Sachen gebaut habe die eine Jahrelang aufgebaute Datenbank zerstört haben ;-)

Mein Skript über die u.a. Zeit hat folgenden Logeintrag ergeben:

2022-04-16 18:46:15 NOTICE logics.funktion_datenbankitem_packen Löschen von 439288 Datensätzen benötigte 97978.37 Sekunden

Das bedeutet pro Datensatz im Schnitt 0,23 Sekunden.
Der zu reinigende Datenbereich umfasst 2 Monate zwischen November 2017 und Dezember 2017 und es sollte alle 10 min ein Datensatz übrig bleiben.

[henfri]

In der Vergangenheit mache ich das bisher nicht grober weil es derzeit halt ein Proof of Concept ist.
Man könnte sich überlegen das database Plugin zu erweitern.

Fände ich super!

Dann aber muss man sich auch gleich überlegen wie man überprüfen will ob die Werte bereits ausgedünnt wurden oder nicht. Ich weiß auch nicht genau, wie rrd das macht. Was würdest Du denn präferieren?

Also eine in die Vergangenheit grober werdende Historie wäre schon super.
Aber schon ein Verfeinern auf minutlich würde wahrscheinlich schon die DB-Größe sehr stark reduzieren.
Man könnte ja diese Ausdünnung täglich machen und beim Abschluss in der DB speichern, von wann bis wann ausgedünnt wurde [für die Variante wo in der Vergangenheit stärker ausgedünnt wird, muss man natürlich speichern welchen Bereich man wie stark ausgedünnt hat].

Und dann: Wo sollen die Grenzen sein? Fliessend oder jeweils an der Jahresgrenze/Monatsgrenze/Tagesgrenze?

Ich denke, das einfachste wäre die Grenze ab heute rückwärts zu rechnen. Also die letzten 365 Tage wären täglich. Davor Monatlich usw.

Und wie würde man das in dem Fall als Item Attribut angeben wollen?

Erstmal wäre hier wichtig ein Default setzen zu können (max_age bei allen Items hinzuzufügen hat einen ganzen Film gedauert :-) )
Ich glaube tatsächlich, dass die Angabe bei einem Item recht kryptisch wäre. Vielleicht macht es Sinn, hier nur einen, oder zwei Defaults zu bieten?

Zur Performance habe ich mich eingelesen:
Man kann einiges tunen. Aber das vieles kann (im Falle eines Crash oder eines Power-Ausfall) zu einer korrupten DB führen.

Folgendes ist sicher, muss aber bei jedem Connect gemacht werden:

pragma journal_mode = WAL;
pragma synchronous = normal;
pragma temp_store = memory;
pragma mmap_size = 5000000000;

Quelle: https://phiresky.github.io/blog/2020...rmance-tuning/

Zudem würde ich noch empfehlen:

PRAGMA LOCKING_MODE = exclusive;

Das bringt auch nochmal einen Faktor 2 und die DB soll ja von sh-ng exklusiv verwendet werden.

Quelle: https://phiresky.github.io/blog/2020...rmance-tuning/

Gruß,
Hendrik

[bmx]

In der Vergangenheit mache ich das bisher nicht grober weil es derzeit halt ein Proof of Concept ist.
Man könnte sich überlegen das database Plugin zu erweitern. Dann aber muss man sich auch gleich überlegen wie man überprüfen will ob die Werte bereits ausgedünnt wurden oder nicht. Ich weiß auch nicht genau, wie rrd das macht. Was würdest Du denn präferieren?
So wie oben geschrieben?
Und dann: Wo sollen die Grenzen sein? Fliessend oder jeweils an der Jahresgrenze/Monatsgrenze/Tagesgrenze?
Und wie würde man das in dem Fall als Item Attribut angeben wollen?

[henfri]

Es dauert halt ein wenig bis die EInträge mit maxage tatsächlich gelöscht werden. Es soll ja alles andere Weiterlaufen.

Hm, laut:

https://github.com/smarthomeNG/plugins/blob/e32b0ef38791667c903e9ffe0c7e88f32a5ba59c/database/__init__.py#L1164

sollte ich ja Meldungen zu maxage bekommen, während das Plugin aufräumt.
Da kam aber nix, obwohl ich einen Logger so konfiguriert habe:

    shng_details_file:
        # This handler writes all entries to the details-file. Log entries with level WARNING
        # and above are additionally written to the warnings-file (through the root logger).
        #
        # The TimedRotatingFileHandler seperates the logentries by day and
        # keeps the entries of the last seven days in seperate files.
        #
        class: logging.handlers.TimedRotatingFileHandler
        formatter: shng_simple
        level: DEBUG
        utc: false
        when: midnight
        backupCount: 7
        filename: ./var/log/smarthome-details.log
        encoding: utf8
        filters: [knx_filter]

Und im Web-Interface wird auch angezeigt, dass fast alle Items ein max_age haben.

Das ausdünnen läuft so, das ich einen Abschnitt festlege von z.B. 10 Minuten und dann nur noch jeweils den letzten Wert behalte und alle anderen lösche.

Aber machst du das in die Vergangenheit grober?
Also:
die letzten Stunden: alle 5 min
die letzten Tage: alle 10 min
die letzte Woche: alle 30 min
letzten Monat: alle 1h
...
vor 5 Jahren: 1 Wert pro Woche

z.B.?

Eventuell wäre es wirklich für so eine grosse Zahl sinnvoll die benötigten Daten erst in eine andere Tabelle zu schreiben und dann alle anderen aus der Datenbank zu löschen. Aber auch da ist das Löschen dann langwierig. Aber Du kannst ja mal dazu eine Logik entwickeln und hier posten.

Ach Bernd, da wüsste ich gar nicht, wo ich anfangen soll :-(

Gruß,
Hendrik

[bmx]

Hallo,
Aber ich habe ja (nachträglich) max_age auf 365 gesetzt.

Es dauert halt ein wenig bis die EInträge mit maxage tatsächlich gelöscht werden. Es soll ja alles andere Weiterlaufen.

Das ausdünnen läuft so, das ich einen Abschnitt festlege von z.B. 10 Minuten und dann nur noch jeweils den letzten Wert behalte und alle anderen lösche.
Eventuell wäre es wirklich für so eine grosse Zahl sinnvoll die benötigten Daten erst in eine andere Tabelle zu schreiben und dann alle anderen aus der Datenbank zu löschen. Aber auch da ist das Löschen dann langwierig. Aber Du kannst ja mal dazu eine Logik entwickeln und hier posten.

[henfri]

Hallo,

Mal 'ne blöde Frage: Hast Du bei der Konfiguration des Database Plugins mal count_logentries: true gesetzt? Damit kannst Du nämlich im Admin Interface sehen wo Deine Memory Hogs bzw. Datenbank Hogs sind.

Ja, das ist bei mir auch der Stromzähler:
12.718.452 Einträge.

Mein Top-Favorit derzeit ist Strom.Zaehler mit über 11 Millionen Einträgen seit 2017

Das ist ein Kandidat um den mal auszudünnen...

Aber ich habe ja (nachträglich) max_age auf 365 gesetzt. Sollte das nicht zum Ausdünnen gereicht haben? Warum habe ich da noch immer 12Mio Einträge?
Wie kann ich herausfinden, von wann das erste Item ist, ohne die 12Mio Einträge als CSV herunterzu laden?

Den Code für das ausdünnen habe ich prinzipiell fertig. Aber das dauert extrem lange die Datensätze da rauszulöschen bei der Datenbankgröße. Ich habe mal Timing Code eingebaut und die Zeit geht wirklich beim database.deleteLog() drauf.

Was ist deine Strategie beim Ausdünnen? Wie beim rrd-tool in die Vergangenheit gröber werdend?
Vielleicht ist es dann schneller das, was man noch braucht/behalten will in einen anderen DB-Eintrag zu schreiben, den ganzen DB-Eintrag zu löschen und dann den neuen Eintrag umzubenennen?

Pro gelöschtem Datensatz derzeit ca. 0.5 Sekunden mit SSD wenn es schon alte Datensätze sind und die eng an eng stehen und wenn im Stück so 100 Datensätze zusammenkommen, dann kann das auch mal in 10 Sekunden erledigt sein, also 0,1 Sekunden pro Datensatz.

Das wären ja 138 Tage um meinen Stromzähler auszudünnen. Und: Ich bekomme momentan vielleicht sogar ähnlich viele neue Einträge pro Sekunde (naja, fast).

Gruß,
Hendrik

[bmx]

Hm. Den Code für das ausdünnen habe ich prinzipiell fertig. Aber das dauert extrem lange die Datensätze da rauszulöschen bei der Datenbankgröße. Ich habe mal Timing Code eingebaut und die Zeit geht wirklich beim database.deleteLog() drauf.

Pro gelöschtem Datensatz derzeit ca. 0.5 Sekunden mit SSD wenn es schon alte Datensätze sind und die eng an eng stehen und wenn im Stück so 100 Datensätze zusammenkommen, dann kann das auch mal in 10 Sekunden erledigt sein, also 0,1 Sekunden pro Datensatz.

[bmx]

Mal 'ne blöde Frage: Hast Du bei der Konfiguration des Database Plugins mal count_logentries: true gesetzt? Damit kannst Du nämlich im Admin Interface sehen wo Deine Memory Hogs bzw. Datenbank Hogs sind.

Mein Top-Favorit derzeit ist Strom.Zaehler mit über 11 Millionen Einträgen seit 2017

Das ist ein Kandidat um den mal auszudünnen...

[henfri]

Hallo,

ok, das verstehe ich.
Habt ihr eine Idee, warum meine Datenbank nicht (wesentlich) kleiner geworden ist?

Vorher:
kein Item mit Maxage. Datenbank existiert seit Jahren. -->17GB

Nachher:
fast alle Items mit MaxAge. VACUUM durchgeführt. -->15GB

Gruß,
Hendrik

[Msinn]

Wenn die Daten mit MaxAge versehen sind, sollte nach einem initialen VACUUM ein regelmäßiger Lauf nicht oder nur seeehr selten notwendig sein. Der Platz, der durch das Löschen der alten Sätze frei wird, wird zwar nicht an das System als freier Plattenplatz zurück gegeben. Allerdings wird dieser Platz für neue Datensätze wiederverwendet, so dass die Datenbank nicht mehr wachsen sollte.

[bmx]

henfri
Also meine alte Datenbank hatte etwa 12,5 GB, die neue nach dem Packen 7,5 GB.
Der ganze Vorgang hat auf einem Intel(R) Celeron(R) J4005 CPU @ 2.00GHz etwa 2,5 Stunden gedauert.

Zu 1) Nein, weil Du für das VACUUM SmartHomeNG anhalten musst.
Zu 2) Nein, weil dann je nach Einstellung nach jedem Commit ein Vaccuum durchführt und das z.B. die SD-Karten von Raspis über Gebühr strapazieren würde.
Ich wäre also nicht derjenige der sowas als Standard einbaut.

Man kann es natürlich cleverer anstellen und den Code anpassen in dem man im laufenden Betrieb eine neue Datenbank erstellt auf der Basis der alten Datenbank und dann aus der alten Datenbank alles reinkopiert. Dann wäre das der gleiche Effekt wie ein Vaccuum. Aber die Fragen hier sind: Wer will sowas machen und sich drum kümmern und was passiert, wenn dabei was unvollständig ist, weil der Rechner zwischenzeitlich abstürzt?

Dann lieber einmal im Jahr für ein paar Stunden eine Wartungsbedingte Stillegung aktzeptieren und die DB packen.

Und zum Thema warum das so gross wird:
Ich habe festgestellt, das ganz viel Daten von Aktoren mit Stromerkennung produziert werden. Die habe ich dann entsprechend reduziert. Es ist immer noch viel aber ich habe noch genug Platz auf der SSD

[android]

Oder kann mir jemand die Funktion im Database-Plugin nennen, die die Abfrage db('max', '5i', '5i') umsetzt?

Danke

Hab es selbst gefunden, es ist die Funktion def _single(self, func, start, end='now', item=None):

[henfri]

Am Wochenende habe ich gerade sowas gemacht und folgenden Code genutzt:

So ein Zufall!

import sqlite3
conn = sqlite3.connect('smarthomeng.db')
conn.execute("VACUUM INTO 'smarthomeng.new.db'")
conn.close()

Verstehe, danke.
Wie lange hat das bei Dir gedauert?
Bei mir läuft es auf einem Pentium-Irgendwas seit heute Mittag. Die DB hat 17GB.
Edit: Obwohl ich database_maxage für fast alle Items auf 365 gesetzt habe und im Web-Interface ein "Cleanup" gemacht habe ist die DB nach dem VACUUM noch 15GB. Hast du eine Idee, woran das liegen kann?
Hat er vielleicht das Cleanup nicht abgeschlossen? Ist das im Log irgendwie zu sehen?

Zu dem Thema noch zwei Fragen:
1) wäre es nicht sinnvoll im WebInterface das "Datenbank-Cleanup" auch ein

conn.execute("VACUUM'")

auslöst, wenn der Treiber sqlite ist?
2) wäre es nicht sinnvoll auto_vacuum zu konfigurieren, wenn die DB erstellt wird (oder nach einem VACUUM, weil es nur dann für eine existierende DB geht)? (https://www.sqlite.org/pragma.html#pragma_auto_vacuum)

Gruß,
Hendrik

[android]

Hallo, gibt eine einfache Möglichkeit zu so einer Abfrage in einer Logik den passenden timestamp zu bekommen:

 sh.messen.zaehler1.erzeugungInKwH.db('max', '5i', '5i')

?

Dazu hätte ich dann noch eine Verständnisfrage. Habe versucht selbst was im Code rauszufinden und die Funktion db() soll ja ein Database-Object zurückgeben. Aber bei obiger Abfrage bekomme ich ja ein float zurück. Passiert das durch die Parameter 'max', '5i', '5i' ? Oder wo ist mein Verständnisproblem? Oder kann mir jemand die Funktion im Database-Plugin nennen, die die Abfrage db('max', '5i', '5i') umsetzt?

Danke

[bmx]

Am Wochenende habe ich gerade sowas gemacht und folgenden Code genutzt:

import sqlite3
conn = sqlite3.connect('smarthomeng.db')
conn.execute("VACUUM INTO 'smarthomeng.new.db'")
conn.close()

Dabei erstellt SQLite eine neue Datenbank ohne die Luftlöcher der alten zu entfernen. Wenn man das INTO ... weglässt, dann passiert das in-place