History Adpater (History vs. influxdb vs SQL)

[mctom]

Hallo zusammen,

in den letzten Tagen haben wir dank der klasse Entwickler die Chance bekommen die Daten von ioBroker noch besser historisieren zu lassen.

Hierzu stehen folgende Adapter zur Verfügung.

****- history

• influxdb

• sql (MS-SQL, SQLite, MySQL)****

Leider sind mir noch nicht alle Unterschiede und Vor- und Nachteile der einzelnen Möglichkeiten klar.

Ich habe nun versucht eine erste Gegenüberstellung zu erstellen.

<u>Adapter: history</u>

• keine Installation notwendig

• kann einfach mit notepad/cat angeschaut werden

• keine Datenbank

• kann nicht durch zusätzliche Datenbanktools ausgelesen werden

• es werden ganz viele Dateien mit der Zeit erzeugt

• höhe Speicherverbrauch

<u>Adapter: influxdb</u>

• ist extra für Timeseries entwickelt

• sehr performant

• eingebautes Mechanismus um alte Einträge zu löschen

• aggregation ist im DB gemacht (man muss nicht alle Einträge erst lesen und dann aggregieren)

• muss installiert werden

• gibt es nicht für Windows

<u>Adapter: SQL</u>

Vergleich:

http://db-engines.com/de/system/Microso … L%3BSQLite

MS-SQL

• weit verbreiteter Standard unter Windows

• ist nur für Windows

• muss installiert werden

• free version "nur" bis 4GB

SQLite

• bereits installiert

• nicht für hohe Anzahl von Events geeignet

MySQL

• kann groß sein

• weit verbreiteter Standard

• kann durch zusätzliche Datenbanktools ausgelesen werden

• zusätzliche Installation notwendig (Datenbank)

• es wird eng auf RasPi2

PostgreSQL

• kann groß sein

• muss installiert werden

• es wird eng auf RasPi2

Ich versuche diese Liste während meiner Entscheidungfindung weiter zu ergänzen. Vielleicht hat jemand von euch schon Erfahrungen gesammelt und kann dazu beitragen, dass die Liste weiter ergänz wird.

Vielen Dank.

gruß

Michael

[mctom]

Habe noch einen Link zu einer Datenbankübersicht eingefügt.

[Bluefox]

habe ergänzt

[Dschaedl]

@mctom:

• es wird eng auf RasPi2 `
  Die DB muss ja nicht auf dem Raspi laufen… :mrgreen:

Ich habe versucht meine MySQL vom PC anzubinden - klappt tadellos 8-)

Mein Wunsch: (resp. werde bei nächster Gelegenheit selbst mal reinschauen...)

Die DB auf dem PC ist nicht immer verfügbar, da dieser nicht 24/7 läuft.

Der SQL Adapter soll die Daten so lange selber cachen, bis die DB auf dem PC verfügbar wird und dann alles rüberspielen.

Da mich die History normalerweise sowieso nur interessiert wenn der PC läuft passt das...

Alternative Idee: Eine DB im Internet benutzen.

viele Grüsse

Daniel

[Thisoft]

Ja, das mit dem Cachen der Daten im SQL-Adapter bis diese in die Db geschrieben werden können finde ich eine super Idee. Allerdings könnte das dann wieder das z.Zt. akute RAM-Problem verstärken..?!

[aquapro]

Cachen auf SD Karte, würde dies wieder entschärfen.

[Dschaedl]

endlich habe ich etwas Zeit gefunden um mich um meine Wunschliste zu kümmern. Ich habe den SQL-Adapter als Vorlage genommen und erweitert:

• mit "node-persist" werden alle gewählten Daten gecached und jeweils alle 10min lokal persistiert.

• In Intervallen von X Minuten werden die gecachten Daten in die SQL Datenbank geschrieben (wenn diese erreichbar ist [1])

• Zum Abrufen der Historieserten Daten gibt's dann zwei Vairanten

• DB ist verfügbar: Daten werden per SQL aus der DB gelesen

• DB nicht verfügbar: Daten werden aus dem Cache gelesen (soweit dort verfügbar)

zum Testen:

original SQL-Adapter deinstallieren und mit diesem ersetzen

npm install https://github.com/dschaedl/iobroker.sq … ll/master/

Wichtig: im MySQL Schema habe ich in den Datentabellen eine Spalte "dt" vom Datentyp "datetime" eingefügt um ein lesbares Datum in der DB zu haben.

-> ALTER TABLE iobroker.ts_number ADD COLUMN dt DATETIME NULL AFTER q; (für ts_number, ts_boolean, ts_string)

ein paar Details:

• Die gecachten Werte werden nach dem Transfer in die DB nicht gelöscht, sondern nur markiert; damit beim Abrufen ohne DB genügend Daten zurück kommen

• Ein Aufräum-Job löscht die gecachten Daten nach X Tagen (lässt sich konfigurieren)

• Das überspielen der Daten in die SQL-DB findet synchron statt; Asynchron/parallel ist der Raspi rasch überfordert mit der grossen Menge an Daten.

• Ich arbeite mit MySQL. Die anderen Schemen habe ich aktuell nicht berücksichtigt.

• [1] Ich habe es nicht hingekriegt einen SQL-Verbindungsfehler zu fangen, ohne dass der Adapter beendet wird. Als Workaorund macht der Adapter nun einen 'Ping' um zu überprüfen ob der PC mit der MySQL DB am laufen ist -> nehme gerne Tips entgegen.

• Der Timestamp in der DB enspricht dem ts-Attribut des ioBroker States (was leider nicht einem Javascript Date entspricht)

==> Bitte Vorsicht mit diesem Adapter; es ist eine erste Version eines Updates. Wenn ihr wichtige Daten in der DB habt, unbedingt vorher backuppen.

viele Grüsse

Daniel

[Solear]

Sehr interessantes Thema, Danke! Auch die Lösung mit dem langen cachen der Daten für einen externen Datenbankrechner ist clever gemacht.

Was spricht denn gegen eine SQL-Versionauf dem Raspi selbst? Also dem Raspberry 3 mit zB 32 GB Speicherkarte (Class 10 UHD). Sollte doch problemlos reichen, oder? Würde gerne diesen Raspi 3 mit iobroker betreiben und allen Diensten, samt Weboberfläche für den 7'' Raspi-Touchscreen. Angebunden an einem 2. Raspi mit Homematic.

[Bluefox]

@Dschaedl:

endlich habe ich etwas Zeit gefunden um mich um meine Wunschliste zu kümmern. Ich habe den SQL-Adapter als Vorlage genommen und erweitert:

• mit "node-persist" werden alle gewählten Daten gecached und jeweils alle 10min lokal persistiert.

• In Intervallen von X Minuten werden die gecachten Daten in die SQL Datenbank geschrieben (wenn diese erreichbar ist [1])

• Zum Abrufen der Historieserten Daten gibt's dann zwei Vairanten

• DB ist verfügbar: Daten werden per SQL aus der DB gelesen

• DB nicht verfügbar: Daten werden aus dem Cache gelesen (soweit dort verfügbar)

zum Testen:

original SQL-Adapter deinstallieren und mit diesem ersetzen

npm install https://github.com/dschaedl/iobroker.sq … ll/master/

Wichtig: im MySQL Schema habe ich in den Datentabellen eine Spalte "dt" vom Datentyp "datetime" eingefügt um ein lesbares Datum in der DB zu haben.

-> ALTER TABLE iobroker.ts_number ADD COLUMN dt DATETIME NULL AFTER q; (für ts_number, ts_boolean, ts_string)

ein paar Details:

• Die gecachten Werte werden nach dem Transfer in die DB nicht gelöscht, sondern nur markiert; damit beim Abrufen ohne DB genügend Daten zurück kommen

• Ein Aufräum-Job löscht die gecachten Daten nach X Tagen (lässt sich konfigurieren)

• Das überspielen der Daten in die SQL-DB findet synchron statt; Asynchron/parallel ist der Raspi rasch überfordert mit der grossen Menge an Daten.

• Ich arbeite mit MySQL. Die anderen Schemen habe ich aktuell nicht berücksichtigt.

• [1] Ich habe es nicht hingekriegt einen SQL-Verbindungsfehler zu fangen, ohne dass der Adapter beendet wird. Als Workaorund macht der Adapter nun einen 'Ping' um zu überprüfen ob der PC mit der MySQL DB am laufen ist -> nehme gerne Tips entgegen.

• Der Timestamp in der DB enspricht dem ts-Attribut des ioBroker States (was leider nicht einem Javascript Date entspricht)

==> Bitte Vorsicht mit diesem Adapter; es ist eine erste Version eines Updates. Wenn ihr wichtige Daten in der DB habt, unbedingt vorher backuppen.

viele Grüsse

Daniel `
Eine tolle Erweiterung. Ich denke, falls Caching konfigurierbar währe, dann konnte es in Main rein fließen.

Wenn du so fit bist, kannst du ein Message mit messagebox implementieren, die beliebige quieries ausführen lässt?

sendTo('sql.0', 'query', 'select * from bla', function(result){

// Array

});

[crepp]

Hallo,

Ich weiss nicht ob ich in diesem Thread richtig bin, aber wenn nicht, müsste der Admin verschieben…

Habe bei mir auf SQL History umgestellt. Konfig der DB und sonstige Einrichtung hab ich nach Anleitung gut hinbekommen. Habe aber bei der Einstellung der geloggten Datenpunkte Probleme. Ich kann die Punkt "aktivieren" nicht speichern. (siehe Anhang) Ich denke dass ist ein Berechtigungsproblem ?!

Ein Beispiel-State würde problemlos gespeichert u. ich kann ihn auch über Rickshaw anzeigen lassen.

Nur bei neuen Daten hab ich Probleme.

Wäre dankbar für Unterstützung.

crepp
686_err.png

[frost]

@mctom:

MySQL

…

• es wird eng auf RasPi2

Michael `

Von welchem Gesichtspunkt aus, wird es eng? CPU-Power, RAM oder Festspeicher?

Bin auch gerade am überlegen, ob ich die History auf DB umstellen soll.

[DiJaexxl]

Ich habe eine SQL DB auf meinem NAS laufen.

Klappt perfekt!

[frost]

Hi DiJ,

was ist deine NAS für ein System (vergleichbar mit Rpi3)?

Vom Gefühl her: Loggst du viel/mittel/wenig Datenpunkte?

[jans_ios]

Moin moin!

Ich habe (dämlicherweise) mit einer SQLite-DB angefangen und dort bereits Monate von Daten. Ich würde gerne umstellen auf MySQL von meinem 24/7-laufenden NAS. In die DB schreiben klappt, aber wie kann ich die bisherigen Daten der SQLite übernehmen?

Meine bisherigen Recherchen ergaben keine wirklich sinnvollen Migrationswege, der Adapter selbst "spricht" aber ja sowohl SQLite als auch MySQL - könnte der evtl. eine Art Copy-Job im Konfig-Bereich erhalten? Wäre doch eine Idee, oder?

Danke!

Gruß, Jan

[apollon77]

Du musst die Daten manuell übernehmen …

Ich würde eine zweite SQL-Instanz anlegen , auf die MySQL einrichten um die DB-Struktur anzulegen.

Dann musst du schauen ob es Tools gibt die dir die Daten aus der SQLite exportiern lassen und dann kannst Du das in die MySQL importieren.

Beispiel (Google gefunden): https://stackoverflow.com/questions/186 ... 3-to-mysql

[ratte-rizzo]

Moin

Also bei der Entscheidungsfindung möchte ich auch noch einmal kurz meine Erfahrung beisteuern: Ich habe am 7. September vom History Adapter auf SQL umgestellt. Das ganze "frisst" etwa 1GB Arbeitsspeicher auf meinem Rock64, dafür ist es aber deutlich entspannter bei der Prozessorlast, wenn mal wieder alle Flot-Diagramme gleichzeitig aktualisieren.

Lieben Gruß
Daniel

[Alexxx2005]

Hallo

Verstehe ich das dann richtig das mysql besser ist als influx ? Ist vielseitiger (zum weiter arbeiten) und auch die Prozessorbelastung ?

Grüße Alex