sql 2.0.0 verfügbar - eine Zusammenfassung

[apollon77]

Hallo alle zusammen,

Nach einer Alpha-Phase (Danke an alle Alphatester!) kommt im laufe des heutigen nachmittags ein Major-Update des SQL(und History und InfluxDB)-Adapters ins Beta/Latest Repo. Viel Spass!

Diese neue (Major!) Version des SQL Adapters räumt einige Dinge auf und sorgt bei einigen Themen für mehr Transparenz und Klarheit und bringt noch dazu einige Features mit. Der Adapter wird sich an einigen Stellen was die aufgezeichneten Daten angeht künftig anders verhalten als bisher!
Ebenso haben sich Einstellungen geändert - es sollten die Einstellungen der alten Version übernommen werden und sollten auch bei einem Downgrade noch vorhanden sein wie vorher eingestellt, dennoch ist diese Version ein Breaking change. Also stellt bitte sicher Backups der Datenfiles und des Systems zu haben! An den Daten in der Datenbank bzw deren Strukturen ändert sich nichts!

Bei Fehlern bitte ggf hier schreiben und dann GitHub Issues anlegen.

Auf die wichtigsten Änderungen will ich gern jetzt noch genauer eingehen.

Wichtige Änderungen erklärt

Konfiguration ausschliesslich in der neuen Admin UI verfügbar!

Die Konfiguration ist ausschliesslich in der neuen Admin5 UI verfügbar

Debounce vs "Aufzeichnung zeitlich blockieren"

In der Konfiguration kann man eine sog. "Debounce-Zeit" angeben. Debounce bedeutet an sich, dass ein neuer Wert erst dann als "stabil" gilt wenn er sich für die angegebene Zeit nicht geändert hat. So war es auch (von komischen Effekten mit "Nur Änderungen aufzeichnen mal abgesehen", dazu später mehr) implementiert. Leider hat die Readme davon gesprochen das der Wert sofort geloggt wird aber dann für die angegebene Zeit kein weiterer Wert. Hier herrschte also Verwirrung.

Mit dieser Version gibt es jetzt zwei Werte die man setzen kann:

• Debounce-Zeit: Der Wert wird wirklich erst aufgezeichnet, wenn er sich für die hier angegebene Zeit nicht geändert hat! Wird hier also ein Wert eingetragen der höher ist als die übliche Änderungsfrequenz des States, dann wird faktisch NIE etwas geloggt! Also Achtung hier!
• Block-Zeit: Dieser Wert entspricht dem was die Doku bisher zum "Debounce" gesagt hat und erlaubt es, erst frühestens nach Ablauf dieser Zeit neue Werte zu loggen. Wichtig hier ist das die obige "Debounce-Zeit" vorher geprüft wird, und auch das ein "Gleiche Werte nochmals loggen" diesen Check nicht macht.

Aus Kompatibilitätsgründen wird der frühere "debounce"-Wert als "Block-Wert" übernommen. Also bitte die Einstellungen prüfen falls dies keinen Sinn macht!

"Nur Änderungen aufzeichnen" vs "Debounce"

In dieser Kombination hatten sich einige Bugs und Effekte eingeschlichen, welche dafür gesorgt haben das teilweise Werte geloggt wurden die nicht den Regeln entsprachen. Dies ist jetzt aufgeräumt.

"Nur Änderungen aufzeichnen" und Aufzeichnung zusätzlicher Werte zur besseren Grafischen Darstellung

Ein weiterer großer Verwirrungspunkt war öfters, warum denn noch zusätzliche Werte aufgezeichnet werden, die an sich durch die Regeln (vor allem "Minimale Differenz") gar nicht hätten aufgezeichnet werden dürfen. Dies kam daher, dass der Adapter versucht die Werte so aufzuzeichnen das eine Grafische Darstellung sinnvoll möglich ist, weil in vielen Fällen die Werte nur zur grafischen Darstellung genutzt werden. Vor allem dabei ist es aber ein potentiell größerer Unterschied ob ein Wert der sich mit 1h Abstand geändert hat sich zu einem Zeitpunkt schlagartig geändert hat oder über die gesamte Zeit langsam geändert hat. Die automatisch gewählten zusätzlichen Werte stellen hier eine bessere Darstellung sicher.

Die neue Version nutzt diese darstellungsoptimierte Aufzeichnung immer noch standardmäßig, kann allerdings pro Datenpunkt deaktiviert werden! Dann werden nur wirklich die Werte aufgezeichnet die laut den angegebenen Regeln definiert waren.

Neue Features

Zusätzlich zu den oben genannten Änderungen gibt es einige neue Features in der neuen Version:

• Buffering und Massen-Inserts: Pro Datenpunkt (und ein Default) kann nun angegeben werden wie viele Datenpunkt erst einmal im RAM gehalten werden sollen und dann wenn die Anzahl erreicht ist (spätestens aber alle 10 Minuten) zusammen in die Datenbank geschrieben werden. Wer also viele Datenpunkte nutzt kann so die Anzahl der parallelen Queries auf die Datenbank verringern. Falls der Adapter allerdings abstürzen sollte sind die Daten weg, also sinnvoll nutzen. Im Standard wird kein Buffering genutzt wie bisher auch.

• Mittels zwei neuer Einstellungen pro Datenpunkt ("Nicht loggen wenn kleiner als" und "Nicht loggen wenn größer als") können noch besser Fehlerwerte ausgeklammert werden. Die Einstellung "Nicht loggen wenn kleiner als" ersetzt das vor kurzem hinzugefügte "Keine Werte kleiner als 0 loggen", die Einstellung wird aber automatisch konvertiert.

• Eine Einstellung pro Datenpunkt ist dazugekommen, mit der man angeben kann auf wie viele Stellen nach dem Komma die Werte beim lesen (GetHistory) gerundet werden.

• Neue Aggregationsmethoden "percentile" und "quartile" wurden hinzugefügt, um das n-te Percentile bzw. das 0.x Quartile zu ermitteln. Ohne weitere Infos wird das 50er Percentile bzw. das 0.5er Quartile (Mean) zurückgegeben, was ein besserer Average ist.

• Neue Aggregationsmethode "integral" wurde hinzugefügt, um das Integral (Fläche unter den Werten) zu errechnen. Im Standard ist die Integral-Unit 1s und eine schrittweise Interpolation wird genutzt (also quasi keine, weil die werte für die ganze Zeit konstant berechnet werden). Alternativ kann eine lineare Interpolation genutzt werden. Mit einem Integral und zB Unit von 3600s kann man aus einem Stromverbrauch in Watt die Wh ermitteln,

• Standardmäßig ermitteln die Aggregationsmethoden immer auch zwei Randwerte und geben diese im Ergebnis zurück um die grafische Darstellung zu optimieren. Mittels der neuen Option "removeBorderValues" bei einem GetHistory Aufruf können die zurückgegebenen Werte so limitiert werden wie Sie per step bzw. count angefordert wurden und diese Randwerte entfernt werden. Dies ist vor allem Hilfreich wenn ein Skript die Daten verarbeiten soll. Mit dem (kommenden!) Admin 5.3.8 ist auch die Darstellung der Werte bei den Datenpunkt-Einstellungen wieder korrekt, wenn man Zeitbereiche von mehr als 24 gewählt hat und mehr als 500 Werte in dem Zeitraum geloggt wurden.

• Da das schreiben von Debug Logs vor allem bei schwächeren Systemen ziemlich auf die performance drückt und auch ein Debug Log immer sehr unübersichtlich wurde, weil alles oder nichts geloggt wurde, kann nun zusätzliches Debug pro Datenpunkt aktiviert werden! Dies wird aber nur dann ausgegeben wenn der Loglevel der Instanz auch auch Debug steht.

• Die Aufbewahrungszeit kann nun neben den vorgeschlagenen zeiträumen auch selbst in tagen definiert werden (Custom)

• Die 1.x des Adapters hat Probleme mit der MySQL8, welche jetzt behoben sein sollten.

• Der SSL-Connection Support hat jetzt die Option bekommen Zertifikatsfehler durch selbstsignierte Zertifikate zu ignorieren und generell wurde Datenbankübergreifend der SSL Support geprüft und aktualisiert.

Die neuen Aggregationsmethoden sind (natürlich) noch nicht in flot oder echarts verfügbar und können daher nur in Skripten genutzt werden. Hierzu müssten, wenn alles geht dann noch Issues an den relevanten Stellen angelegt werden.

Die neuen Optionen für getHistory sind in https://github.com/ioBroker/ioBroker.history/blob/master/docs/en/README.md#access-values-from-javascript-adapter auch alle erklärt

Weitere Änderungen

Weiterhin gibt es folgende relevante Änderungen im Verhalten

• GetHistory-Anfragen müssen nun bei start/end Angaben in ms erfolgen! Zeitangaben in Sekunden werden nicht mehr umgerechnet! Bitte sicherstellen das alle UIs und Charting-Adapter aktuell sind!
• Die Objekt-ID wird nun immer wenn angefordert mit in den Ergebnissen von GetHistory zurückgegeben
• Spezielle Behandlung von früher aufgezeichneten Daten mit Timestamps in Sekunden bzw. Logiken die verhindert haben Werte vor 1.1.2010 zu verarbeiten wurden entfernt.

Neue Aufzeichnungslogik erklärt

Am Ende gilt jetzt folgende Reihenfolge der Checks:

• Ein Wert ist erst nach Debounce-zeit stabil. Unstabile werte werden nicht aufgezeichnet
• Wenn die Blockzeit seit dem zuletzt regulär aufgezeichneten Wert nicht erreicht ist, wird der Wert nicht aufgezeichnet
• Wenn "Null-Werte" ignoriert werden und der Wert 0 ist, dann wird der Wert nicht aufgezeichnet
• Wenn Grenzen der Werte definiert sind (Nicht loggen wenn kleiner/größer als) und der Wert ist ausserhalb der Grenzen, wird der Wert nicht aufgezeichnet
• Wenn "Nur Änderungen aufzeichnen" definiert ist:
  • Wenn der Wert seit letzter Aufzeichnung unverändert war und "gleichen Wert aufzeichnen" deaktiviert ist, wird der Wert nicht aufgezeichnet. Der Wert wird ggf. erinnert für spätere Aufzeichnung zur Darstellungsoptimierung.
  • Wenn der Wert seit letzter Aufzeichnung unverändert war und "gleichen Wert aufzeichnen" aktiviert ist und das angegebene "Nochmals Aufzeichnen Interval" noch nicht erreicht ist, wird der Wert nicht aufgezeichnet. Der Wert wird ggf. erinnert für spätere Aufzeichnung zur Darstellungsoptimierung.
• Wenn der Wert eine Zahl ist und eine "Minimale Differenz" definiert ist, diese aber nicht erreicht ist, wird der Wert nicht aufgezeichnet. Der Wert wird ggf. erinnert für spätere Aufzeichnung zur Darstellungsoptimierung.

Der zuletzt erinnerte Wert wird geschrieben sobald auch der nächste reguläre Wert geschrieben wird, allerdings mit seinem alten Zeitstempel - und nur wenn die Darstellungsoptimierung nicht deaktiviert wurde..

Ingo

[kalled]

@apollon77 said in sql 2.0.0 verfügbar - eine Zusammenfassung:

Buffering und Massen-Inserts: Pro Datenpunkt (und ein Default) kann nun angegeben werden wie viele Datenpunkt erst einmal im RAM gehalten werden sollen und dann wenn die Anzahl erreicht ist (spätestens aber alle 10 Minuten) zusammen in die Datenbank geschrieben werden. Wer also viele Datenpunkte nutzt kann so die Anzahl der parallelen Queries auf die Datenbank verringern. Falls der Adapter allerdings abstürzen sollte sind die Daten weg, also sinnvoll nutzen. Im Standard wird kein Buffering genutzt wie bisher auch.

Da habe ich entweder einen Fehler oder ein Verständnisproblem:
Per default steht Buffering auf 0 in den Adapter-Einstellungen -> deaktiviert. Ich bekomme aber für alle meine Daten die Werte nur alle 10min in die DB geschrieben. Setze ich den Wert hilfsweise auf 1 kommen sie direkt.
Adapter Version ist 2.0.2

[apollon77]

@kalled Was steht denn bei den einzelnen Datenpunkten? Auch 0?

[kalled]

@apollon77 Die habe ich nicht manuell eingestellt, sind anscheinend alle per default auf 10. Da es ziemlich viele sind wäre es schön wenn ich das global im Adapter ausschalten kann. Geht das?

[apollon77]

@kalled Ooohh . ok da hatte sich ein blöder Default eingeschlichen wenn nichts gesetzt war ... wird in nächster Version auf 0 gesetzt

[apollon77]

Hallo alle zusammen,

mit dem Feedback hier und auf GitHub und noch ein paar Sentry Fehlermeldungen, gibts jetzt hier die 2.1.0 mit noch ein paar Neuerungen:

Die wichtigste Neuerung ist das storeState und auch GetHistory jetzt auch für "unbekannte Objects-IDs" geht. Es fehlen natürlich sämtliche Filteroptionen und alles was man pro Datenpunkt einstellen kann, aber das sollte klar sein

Ebenso haben wir endlich den Bug gefunden warum manchmal viel mehr Connections genutzt wurden als in den Einstellungen als maximum konfiguriert.

• (Apollon77) Fix crash cases reported by Sentry
• (Apollon77) Fix several places where pooled connections might have not been returned to pool correctly and add logging for it
• (Apollon77) Work around an issue in used Pooling library that potentially gave out too many connections
• (Apollon77) Optimize retention check to better spread the first checks over time
• (Apollon77) Default to not use datapoint buffering as in 1.x when set to 0
• (Apollon77) Make sure disabling "Log changes only" also really do not log the changes anymore
• (Apollon77) Allow storeState and GetHistory also to be called for "unknown ids"
• (Apollon77) Adjust the fallback logic for type detection to use the type of the state value to log as last fallback
• (Apollon77) Fix storing booleans on MSSQL

Das Update sollte im Laufe des Abends im latest Repo aufauchen,

Viel Spass damit.

Ingo

[apollon77]

Und damit es nicht ganz langweilig wird hier noch ein kleines Update auf die 2.1.2 ... Ich habe nochmal in so einige Performancethemen vor allem bei GetHistory reingeschau und optimiert.

Wenn alles klappt geht das dann demnächst auch in Stable.

[apollon77]

Hey all,

von sql gibts jetzt die 2.1.5 mit letzten Fixes. Wenn keiner mehr was meldet wäre das die Version die die Tage in Stable geht.
Hier hatte sich noch ein Bug eingeschlichen das neue Datenpunkte vielleicht nicht korrekt initialisiert wurden.

Bitte checkt es nochmal

Ingo

[Sneak-L8]

@apollon77 Hab meinen ioBroker von einer alten Stretch-Version auf bullseye umgezogen mit Backitup.
Neben den bekannten Problemem mit vis hänge ich aktuell am SQL-Adapter.
Er scheint ähnliche Probleme zu haben, die sich aber nicht nach dem glecihen Schema lösen lassen.
Ich hab den Adapter schon mit

iob del sql

gelöscht und dann über die Oberfläche wieder installieren wollen.
Aber er hängt immer an der gleichen Stelle:

$ iobroker upgrade sql@2.1.7
Update sql from @0.0.0 to @2.1.7
NPM version: 8.11.0
Installing iobroker.sql@2.1.7... (System call)

Danach friert das System ein, nicht mal über SSH reagiert er noch auf Eingaben.
Hast Du einen Tipp, wie ich den SQL-Adapter wieder sauber zum Laufen bekomme?

[haselchen]

@sneak-l8

Wenn er gelöscht wäre würdest du kein Upgrade machen.
Sieht man auch an der Version 0.0.0
Da musste vermutlich nochmal Hand anlegen .

[Sneak-L8]

@haselchen Ok, danke.
Ich probiere es nun nochmal von der Konsole aus. Aber bleibt dabei, die Installation läuft enfahcn icht an:

pi@raspberrypi:~ $ iob stop
pi@raspberrypi:~ $ iob del sql
Delete adapter "sql"
host.raspberrypi object sql deleted
host.raspberrypi object sql.admin deleted

removed 182 packages, and changed 2 packages in 16s

105 packages are looking for funding
  run `npm fund` for details
pi@raspberrypi:~ $ iob install sql
NPM version: 8.11.0
Installing iobroker.sql@2.1.7... (System call)

An dieser Stelle geht es nicht weiter ...

[Thomas Braun]

@sneak-l8

ioveoker add sql --debug

[Sneak-L8]

@thomas-braun Mist, ich war wohl nur zu ungeduldig. Jetzt von der Konsole hatte er sql nach ca. 5-10 Minuten (war nicht am Rechner) installiert. Und jetzt läuft es...

[apollon77]

@sneak-l8 Meine Empfehlung wäre gewesen ioBroker mal zu beenden für die Installation. Am Ende kompiliert der sqlite3 und das kann je nach System durchaus mal bissl dauern und auch RAM brauchen ...

[Sneak-L8]

@apollon77 Danke für den Tipp. Treffer . Genau das hatte ich getan. Da es keinen Grund gab, warum die Installation nicht klappen sollte, habe ich den ioBroekr wie oben beschrieben erst gestoppt. Dadurch lief er dann wohl durch.

[apollon77]

@sneak-l8 dann war es zu wenig RAM und swapping.

[mlapp]

@apollon77 Hy, gibt es eine Möglichkeit, historische Werte (die nicht aus ioBroker stammen) im entsprechenden Format in die Datentabellen der ioBroker Datenbank zu schreiben um diese dann mit Flot zu nutzen? Ich würde mir gerne Charts meiner PV-Anlage konsolidiert auf Tage, Monate und Jahre graphisch anzeigen lassen.
Die Daten im entsprechenden Format kann ich problemlos in die Tabellen einfügen, nur Flot nutzt diese Daten einfach nicht.
Ich hab mir dazu z.B. auch schon einen Datenpunkt angelegt und im Admin zum speichern eingestellt. Werte die ich nach der Erstellung in die DB speichere werden angezeigt. Daten davor leider nicht. Gibt es dafür eine Lösung außer Grafana zu verwenden?

[apollon77]

@mlapp also idealerweise nutzt du Adapter wie sourceanalytics oder statistics und hast damit die aggregierten Werte wieder in States die du historisieren und ggf nutzen kannst. Oder was genau meinst du?

Welche Daten kamen da wie in die db?

[mlapp]

@apollon77 Ne, ich möchte die Daten schon aggregiert in die Datenbank schreiben.
Ich hab die ganzen Tageswerte und zeig sie mir aktuell in einer Tabelle an. Das funktioniert problemlos. Aber, ein altes Sprichwort sagt... Ein Bild sagt mehr als tausend Worte.
Ich würde mir diese Daten eben auch gerne in einer Graphik anzeigen lassen. Nach vorne verschieben, rein zoomen usw.
Die PV Tagesdaten hab ich mal aus einem Onlineportal extrahiert und in die DB (eigene Tabelle) geschrieben. Flot weigert sich aber hartnäckig diese Daten als Quelle anzuerkennen.
Wenn das ginge, wäre alles geritzt. Aber da kommt mir gerade eine Idee... vielleicht geht es ja mit einem JSON Chart Objekt... Ich spiel damit mal ein wenig rum....

[apollon77]

@mlapp Naja das ganze History Ansatz basirt darauf das der Adapterdas zu States und so zuordnen kann und weiß wie die Daten strukturiert und gespeichert sind. Wenn du irgendwo selbst Dinge in die DB schreibst dann ist die "getHistory" Funktion und damit die History Adapter raus und ja dann bleibt Dir nur Grafana ... oder machst es so wie beschrieben. Kann ja sein das Du die Werte selbst aggrgierst ... aber warum nicht in passenden States in die DB schreiben "lassen" ... dann haste wieder alles