DBLog - Historische Werte ausdünnen

[Sunny]

Moin Claudiu,

Bei rereadcfg, hatte ich mich am "Commandref" für DbLog orientiert.
Hatte bis vor kurzem noch eine Db-Datei, die auf 800 GB angewachsen war, und dort war der RPi danach deutlich "schneller".
Werde das rereadcfg at, dann mal deaktivieren. Die Datei kann jetzt eh nicht mehr so groß werden..

Danke & viele Grüße
Sunny

[rapster]

Ups, mein Fehler, du hast ja "<dblog> rereadcfg" gemacht und nicht fhem-rereadcfg, immer wieder dieses flüchtige lesen...

Uff, 800GB ist natürlich heftig!

Allerdings wird dadurch du Datenbankverbindung auf jedenfall neu aufgebaut, ehrlich gesagt k.A. welche Auswirkungen das hat während reduceLog gerade am werkeln ist
Die delete's wurden nicht in ein eval gepackt, also sollte es denke ich zumindest nicht abbrechen... Aber obs noch funktioniert? Vll. doch mit eval arbeiten...

Probiers einfach nochmal mit der aktuellen Version aus, evtl. auch mal manuell aufrufen und schauen was passiert, sollte eigentlich bei dir auch klappen

Gruß
  Claudiu

[Sunny]

Moin Claudiu,

Danke, jetzt wird auch bei mir gelogged, 
Vorher Readings von myDbLog:

Code Auswählen Erweitern

countCurrent 11 2015-09-17 19:55:30
countHistory 16089 2015-09-17 19:55:30
state connected 2015-09-17 19:54:14
Nachher:

Code Auswählen Erweitern

countCurrent 51 2015-09-17 19:58:12
countHistory 15356 2015-09-17 19:58:12
lastReduceLogResult Rows processed: 8462, deleted: 779 2015-09-17 19:57:11
Und im Filelog, steht jetzt auch:

Code Auswählen Erweitern

2015.09.17 19:57:06 3: DbLog myDbLog: reduceLog requested.
2015.09.17 19:57:07 3: DbLog myDbLog: reduceLog deleting 779 records of day: 2015-09-16
2015.09.17 19:57:11 3: DbLog myDbLog: reduceLog executed. Rows processed: 8462, deleted: 779

Falls Du noch etwas getestet haben möchtest...

Schönen Abend und nochmals Danke für diese schönen Funktion 
Sunny

PS: Die 800GB waren ein aus einem Versuch mal alles zu Loggen... Die Neugierde bzw. der Spieltrieb...

[rapster]

Hi Sunny,

super, freut mich dass es bei dir jetzt auch klappt, und ohne Spieltrieb würde doch hier generell nichts voran gehen 

Bei deinen Log-Ausgaben viel mir gerade allerdings was interessantes auf, dein RasPi mit USB-HDD scheint i.M. die Records mit ~11.000/min zu bereinigen.
Anscheinend hat die reduceLog Geschwindigkeit aufgrund des fehlenden primary-keys in DbLog auch sehr sehr viel mit der Datenbankgröße zutun!

Ebenfalls schönen Abend!
Gruß
  Claudiu

[rapster]

Nach der Erkenntnis durch @Sunny habe ich jetzt mal getestet einen INDEX auf die benötigten Zellen zu setzen:

Code Auswählen Erweitern


sqlite> CREATE INDEX index_reduceLog ON history (TIMESTAMP,DEVICE,EVENT);


Das Ergebniss ist natürlich deutlich beeindruckender:

Code Auswählen Erweitern


2015.09.17 20:55:05.106 3: DbLog dbLog: reduceLog requested.
2015.09.17 20:55:06.318 3: DbLog dbLog: reduceLog deleting 5264 records of day: 2015-08-21
...
2015.09.17 20:55:10.179 3: DbLog dbLog: reduceLog executed. Rows processed: 269088, deleted: 143360

Jetzt räumt reduceLog die Einträge mit 2.227.816 / min auf, was das Thema Blocking vollkommen unwichtig erscheinen lässt.

Die Datenbankgröße hat sich durch das erstellen des INDEX von 144 MB auf 200MB erhöht was ich verschmerzbar finde.

Ich überlege ob reduceLog automatisch einen INDEX anlegen soll beim verwenden falls nicht bereits vorhanden, oder ob dies dem User überlassen werden soll?
Was meint ihr?
Bei mir bleibt der INDEX auf jedenfall drin, und mit einer effektiven fhem blockier-Zeit von wenigen ms/Tag kann ich reduceLog nun auch beruhigt jeden Tag laufen lassen 

Auf jedenfall muss ich das zumindest Morgen noch in die Commandref einbringen...
Evtl. könnt ihr das ja mal bei euch mit einer größeren Datenmenge verifizieren und mir eure Meinung mitteilen 

Gruß
  Claudiu

[Sailor]

Ein herzerfrischendes "Moin" vom hintern-Deich vorweg!

Wow!... und ich wollte schon ein eigene Modul namen "93_DbLog_MuckOut.pm" schreiben... Das dürfte sich hiermit erledigt haben.


Mein Ansatz war der, dass man mit dem Alter der Daten immer mehr auf Auflösung verzichten und auf arithmetische Mittel dieser zurückgreift.

Der grobe Ansatz der Daten sollte wie folgt zusammengefasst werden:

Code Auswählen Erweitern


Dies       (             t < 1d):         Werte so lassen
Mensis    (        1d < t < EndOfMonth): Werte auf Mittelwerte eines 15    Minuten-Intervalls zusammenfassen
Annum      (EndOfMonth < t < EndOfYear)   Werte auf Mittelwerte eines 60    Minuten-Intervalls zusammenfassen (1h)
Lustrum    (EndOfYear  < t < 5years):   Werte auf Mittelwerte eines 1440  Minuten-Intervalls zusammenfassen (1d)
Infinitum  (5years     < t):              Werte auf Mittelwerte eines 10080 Minuten-Intervalls zusammenfassen (1w)


Wobei auch nicht alle Daten gespeichert werden brauchen. Man braucht nach einem Jahr ja nicht mehr die genauen Öffnungszeiten der Fenster und Türen, wohl aber einen Mittelwert der entsprechenden Temperaturen im Raum.

Daten die es Wert sind gepeichert zu werden:
Aktiver Tag          Alle Daten bei höchster Auflösung
Aktiver Monat      Alle Daten bei entsprechendem Mittelwert wo möglich
Aktives Jahr >>   Nur entsprechende Mittelwerte von: Temperatur, Feuchtigkeit, Gasverbrauch, Heizkosten, E-Verbrauch, E-Kosten etc.

Euren Ansatz zu sagen, man reduziert jeden Wert auf 1nen pro Stunde ist dabei schon mal ein guter Anfang...
Jetzt müsste man nur noch den Mittelwert dieser letzten Stunde berechnen und diesen auf den Stundenwert der Folgestunde neu schreiben (Mittelwert aller Werte von 01:00 bis 02:00 auf 02:00 speichern)
Dies natürlich nur wenn die Werte nicht bereits auf kleiner 26 pro Tag reduziert worden sind.

Aber dann dürften die Ressourcen des Host-Systems wieder in die Knie gehen. 

Gruß
    Sailor

[Sailor]

Ein herzerfrischendes "Moin" vom hintern-Deich vorweg!

Denke da wird sich bestimmt jemand mit Raspi+DbLog finden der das auch nützlich findet und testet, da musst du nicht extra zurückrudern und das Basteln anfangen.
Grundsätzlich funktioniert es ja auf jeder Platform, hat mich nur interessiert ob dem Raspi bei sowas komplett die Puste ausgeht 

Den habt Ihr gefunden.

Ich werde mal auf meinem Test-Raspi mit meiner gegenwärtigen-DB einen Test am WE machen.

Ich halte Euch auf dem Laufenden...

Gruß
   Sailor

[C0mmanda]

Nach der Erkenntnis durch @Sunny habe ich jetzt mal getestet einen INDEX auf die benötigten Zellen zu setzen:

Code Auswählen Erweitern


sqlite> CREATE INDEX index_reduceLog ON history (TIMESTAMP,DEVICE,EVENT);


Das Ergebniss ist natürlich deutlich beeindruckender:

Code Auswählen Erweitern


2015.09.17 20:55:05.106 3: DbLog dbLog: reduceLog requested.
2015.09.17 20:55:06.318 3: DbLog dbLog: reduceLog deleting 5264 records of day: 2015-08-21
...
2015.09.17 20:55:10.179 3: DbLog dbLog: reduceLog executed. Rows processed: 269088, deleted: 143360

Jetzt räumt reduceLog die Einträge mit 2.227.816 / min auf, was das Thema Blocking vollkommen unwichtig erscheinen lässt.

Die Datenbankgröße hat sich durch das erstellen des INDEX von 144 MB auf 200MB erhöht was ich verschmerzbar finde.

Ich überlege ob reduceLog automatisch einen INDEX anlegen soll beim verwenden falls nicht bereits vorhanden, oder ob dies dem User überlassen werden soll?
Was meint ihr?
Bei mir bleibt der INDEX auf jedenfall drin, und mit einer effektiven fhem blockier-Zeit von wenigen ms/Tag kann ich reduceLog nun auch beruhigt jeden Tag laufen lassen 

Auf jedenfall muss ich das zumindest Morgen noch in die Commandref einbringen...
Evtl. könnt ihr das ja mal bei euch mit einer größeren Datenmenge verifizieren und mir eure Meinung mitteilen 

Gruß
  Claudiu

Ich habe wenig bis überhaupt keine Ahnung von SQL und müsste mich erstmal einlesen welche Vor- und Nachteile so ein Index haben würde

Ich würde also sagen, wenn das keine Auswirkungen auf fhem bzw DBLog hat, INDEX in jedem Fall setzen bei so einem Geschwindigkeitsgewinn!
Ich habe es direkt mal getestet. INDEX hinzugefügt (Die Datenbank ist von ca. 10 auf 14MB gewachsen) und reduceLog laufen lassen:

2015.09.18 08:36:27 3: DbLog logdb: reduceLog requested.
2015.09.18 08:36:28 3: DbLog logdb: reduceLog deleting 9839 records of day: 2015-09-09
2015.09.18 08:36:31 3: DbLog logdb: reduceLog deleting 5571 records of day: 2015-09-10
2015.09.18 08:36:33 3: DbLog logdb: reduceLog executed. Rows processed: 39358, deleted: 15410

In 6 sek. ist er mit 15410 Datensätzen durch.
Macht aktuell 154.100 Datensätze/Minute auf dem Intel NUC DN2820. (Gestern waren es noch 320 Datensätze/Minute.)
Die Datenbank hatte 76.362 Datensätze.

Absolut top, ich bin begeistert

[rapster]

@C0mmanda:
Super dass bei dir dadurch auch ein ordentlicher Geschwindigkeitszusatz entstanden ist.
Bei DbLog hat ein zusätzlicher INDEX erstmal keine negativen Auswirkungen bis auf die um ~ 1/4 vergrößerte Datenbank.
Generell werden durch zusätzliche INDEX, INSERT's und UPDATE's zwar etwas langsamer, weil zusätzlich bei jedem schreibvorgang der INDEX zusätzlich zu schreiben ist. Das ist allerdings bei diesem Modul vollkommen zu vernachlässigen.

Bei SQLITE wird durch DbLog schon Standardmäßig auf (DEVICE,READING,TIMESTAMP) ein INDEX erstellt, das hilft mir bei reduceLog nur nicht weiter, hier brauch ich einen INDEX auf (TIMESTAMP,DEVICE,EVENT).


@Sailor
Habe deine Idee (zwar etwas abgewandt) in die Tat umgesetzt und in Post #4 eine aktuelle Version angehangen.

Du kannst jetzt mit dem optionalen Parameter 'average' die db so bereinigen dass nichtmehr einfach nur der erste Eintrag zurückbleibt, sondern für alle numerischen Werte ein einzelner Mittelwerts-Eintrag pro Stunde um jeweils hh:30:00 zurückbleibt welcher von hh:00:00 bis hh:59:59 berechnet wird.

Bsp.: "set dbLog reduceLog 91 average"

Bitte mal ausprobieren, nicht vergessen zuvor INDEX zu erstellen, und bescheid geben 

Beispiel Logausgabe mit 'average':

Code Auswählen Erweitern


2015.09.18 19:10:32.501 3: DbLog dbLog: reduceLog requested.
2015.09.18 19:10:33.734 3: DbLog dbLog: reduceLog deleting 5729 records of day: 2015-08-25
2015.09.18 19:10:33.914 3: DbLog dbLog: reduceLog updating 302 averages of day: 2015-08-25
2015.09.18 19:10:34.194 3: DbLog dbLog: reduceLog deleting 14778 records of day: 2015-08-26
2015.09.18 19:10:34.494 3: DbLog dbLog: reduceLog updating 706 averages of day: 2015-08-26
2015.09.18 19:10:34.587 3: DbLog dbLog: reduceLog executed. Rows processed: 140774, deleted: 20507, updated: 1008


P.S. Die SQL-Statements werden nun in einem eval aufgerufen, und sollten somit bei einem eventuellen Fehler durch z.B. "rereadcfg" abbrechen und sich nicht mehr undefiniert verhalten.

Gruß
  Claudiu

[rapster]

Nochmal kleines Update in Post #4 nachgeschoben, wodurch im EVENT Column erkennbar ist dass es sich hier um ein average handelt.

Siehe Anhang.

Gruß
  Claudiu

[C0mmanda]

Den INDEX muss ich aber nur einmal erstellen, oder?

Habs heute mal mit der "average" probiert, aber das scheint er nicht gemacht zu haben:

set DbLog reduceLog 7 average

2015.09.19 10:21:07 3: DbLog logdb: reduceLog requested.
2015.09.19 10:21:08 3: DbLog logdb: reduceLog deleting 4978 records of day: 2015-09-11
2015.09.19 10:21:09 3: DbLog logdb: reduceLog executed. Rows processed: 29738, deleted: 4978

Zumindest für den 11.09. hätte er es ja machen sollen?! (Da bereits die DB bereits bereinigt ist sind für alle Tage davor ja keine Werte mehr vorhanden um die average zu berechenen)

[rapster]

Ja, einmal erstellen reicht 

Hast du es mit der Version von gestern Abend nach einem Fhem-Neustart probiert?

Gruß
  Claudiu

[cruser1800]

Ich habe einen Cubitruck 3 und in ca. 6 min 200.000 Daternsätze gelöscht, ohne average!

Seit der letzten Version funtioniert es auch! Vorher ist FHEM immer abgeschmiert!

Aber Klasse Idee die Erweiterung! Meine DB hatte schon 2GB!

Danke Lutz

[C0mmanda]

Ja, einmal erstellen reicht 

Hast du es mit der Version von gestern Abend nach einem Fhem-Neustart probiert?

Gruß
  Claudiu

Argh! Der Neustart... 
Morgen dann der nächste Versuch 

Danke!

[rapster]

Hallo Zusammen,

erstmal sorry,
es ist möglich dass die bisherigen Versionen, alle Datensätze von 00:xx:xx Uhr komplett gelöscht haben ohne einen Wert übrig zu lassen,
sowie dass die letzte Stunde welche mit Angabe von 'average' bearbeitet wurde, beim nächsten 'average' falsch berechnet wurde und der Wert somit falsch war.
Bitte prüft das!

Im Anhang eine neue Version + Patch, die sich an Sailor's wunsch angenähert hat.

Code Auswählen Erweitern


2015.09.19 18:27:14.437 3: DbLog dbLog: reduceLog requested.
2015.09.19 18:27:15.543 3: DbLog dbLog: reduceLog deleting 7078 records of day: 2015-09-02
2015.09.19 18:27:15.710 3: DbLog dbLog: reduceLog (hourly-average) updating 365 records of day: 2015-09-02
2015.09.19 18:27:15.744 3: DbLog dbLog: reduceLog (daily-average) updating 106, deleting 1256 records of day: 2015-09-02
2015.09.19 18:27:16.006 3: DbLog dbLog: reduceLog deleting 25948 records of day: 2015-09-03
2015.09.19 18:27:16.624 3: DbLog dbLog: reduceLog (hourly-average) updating 1096 records of day: 2015-09-03
2015.09.19 18:27:16.702 3: DbLog dbLog: reduceLog (daily-average) updating 102, deleting 1300 records of day: 2015-09-03
2015.09.19 18:27:16.872 3: DbLog dbLog: reduceLog deleting 16699 records of day: 2015-09-04
2015.09.19 18:27:17.265 3: DbLog dbLog: reduceLog (hourly-average) updating 746 records of day: 2015-09-04
2015.09.19 18:27:17.313 3: DbLog dbLog: reduceLog executed. Rows processed: 132179, deleted: 52281, updated: 2415


Entweder wie bisher einfach "set <dblog> reduceLog <days>" um jeweils auf den ersten Device/Reading Eintrag pro Stunde zu reduzieren,

oder "set <dblog> reduceLog <days> average" um nicht mehr nur auf den ersten Eintrag pro Stunde zu reduzieren, sondern für jede Stunde einen Mittelwert zu bilden und diesen um hh:30:00 zu speichern,

oder nun "set <dblog> reduceLog <days> average=day" um alle numerischen Werte auf einen einzigen Mittelwert pro Tag (um 12:00:00 Uhr) zu reduzieren.

average=day ist auch nachträglich möglich, somit kann man sich z.B. ein Konstrukt bauen welches
- jeden record der letzten 2 Wochen behält
- nurnoch 1 record pro Stunde des letzten Jahres behält (optional mit Mittelwert)
- nur den Tages-Mittelwert der letzten 10 Jahre behält

('average=day' impliziert 'average')


Eine Reduzierung auf eine ganze Woche ist mMn zu viel und werde ich (zumindest vorerst) nicht einbauen.

Im Anhang Beispiele für die Ergebnisse der drei Befehle.

Bitte mal testen und bescheid geben falls was nicht passen sollte 

Gruß
  Claudiu

EDIT:
Es wird kein zusätzlicher INDEX mehr benötigt, es wird nun der standard 'Search_Idx' INDEX welcher durch die *.sql Dateien für die DbLog Erstellung erstellt wird verwendet.

Als letztes Argument kann nun "EXCLUDE=deviceRegExp1:readingRegExp1,deviceRegExp2:readingRegExp2,...." angegeben werden um device:reading Kombinationen (mehrere durch Komma getrennt) von reduceLog auszuschließen.
z.B. "set <dblog> reduceLog <days> average exclude=haustuer:state,katzenklappe\d\d:.*,.*:sabotageError"

EDIT2: Datei entfernt, wird nun über fhem-update verteilt!