DbLog Datenbankstruktur MySQL

Guybrush · 28 Mai 2020, 14:06:29

Zitat von: DS_Starter am 27 Mai 2020, 13:22:04
Wenn du Vorschläge zu Verbesserunegn hast, die sich relativ komplikationslos in die vorhandene Lösung einfügen lassen würden, könne wir das gerne tun.

Hattest du oder irgendwer anders das einmal in Erwägung gezogen, die Spalte DEVICE beim schreiben/lesen jeweils mit gzip zu behandeln? Soweit ich das in der dblog.pm sah, dürfte das mit nicht allzuviel Aufwand möglich sein. Das Problem wäre dann nur, dass damit ein Reset der Daten oder wenigstens ein nachträgliches Update auf alle früheren Einträge nötig wäre. Bei READING, VALUE, UNIT dürfte das wenig Sinn machen, da die auch bei mir meist nur aus 5 Zeichen bestehen.

Bei mir sind allerdings meine Devicenames relativ lang, da ich alle Devices bei mir hierarchisch halte (z.b. Zimmer.Gerät.Wert // Kinderzimmer.Temperatur.IST). Das finde ich im Zusammenspiel mit der Regex Möglichkeit sehr angenehm global z.b. das Licht auszuschalten (set .*\.Licht off) - ka obs da eleganteres gibt - für alle Sachen extra Strukturen einrichten war mir etwas viel Aufwand

Ich gehe aber mal davon aus, dass vermutlich die meisten eher kürzere Devicenames nutzen?

Wenn das Befüllen der EVENT Spalte jedenfalls bereits abschaltbar ist und demnach nur DEVICE, READING,VALUE,UNIT übrig blieben, dann würde davon bei mir Device > 50% der row length ausmachen. Ich weiß noch nicht, wie sich row format Compressed bei der Tabelle verhält. Dafür hab ich noch nicht genug Daten bei mir drin. Soweit da alles über den Index gecovered wird, dürfte compressed aber kaum was ausmachen. Dann wäre das ggf. bereits ausreichend und eine Codeanpassung sinnlos. Das ist am Ende wie immer die Frage, ob der gzip Overhead nicht die gesparten IO kompensiert. Ich denke gerade bei raspberry Pi & Co sollte eine kleinere Größe weit wichtiger sein, als ein paar gesparte CPU Cycles.

Guybrush · 28 Mai 2020, 14:10:47

Zitat von: DS_Starter am 28 Mai 2020, 13:59:48
Wahrscheinlich meintest du aber die FHEMWEB Startseite mit deiner Anregung.
Das wäre ein Vorschlag für Rudolf König der FHEMWEB entwickelt.

Den Vorschlag kannst du im Forum

FHEM » Frontends » FHEMWEB

platzieren wenn du magst.

Ja, genau das meinte ich. Ganz offen, dein Screenshot ist eindeutig, aber den markierten Bereich hab ich nicht gesehen... ich bin immer direkt runter zum content. Ich mach da mal wie vorgeschlagen einen Post.

DS_Starter · 28 Mai 2020, 14:40:33

Hallo Andreas,

Zitat
Hattest du oder irgendwer anders das einmal in Erwägung gezogen, die Spalte DEVICE beim schreiben/lesen jeweils mit gzip zu behandeln? Soweit ich das in der dblog.pm sah, dürfte das mit nicht allzuviel Aufwand möglich sein.

Nein, habe zumindest ich noch nicht in Erwägung gezogen.

Zitat
Das Problem wäre dann nur, dass damit ein Reset der Daten oder wenigstens ein nachträgliches Update auf alle früheren Einträge nötig wäre.

Genau, das ist ein Migrations- / Support-problem. Und wie ich schon Eingangs erwähnte sind meisten FHEM User keine Admins. Und ich müßte eine Migrationsroutine schreiben und Fehlersituationen abfangen sowie den Ansturm an Supportanfragen abfangen etc.pp.

Deswegen wäre so etwas m.M. nach auch etwas was in einem Modul DbLogNG einfließen könnte.
Nehme ich mit zu den Ideen !

Edit: Frage ... Hättest du Interesse mitzuwirken sobald ich dazu komme etwas Neues in Fragen Datenbank-Logging anzufangen ?

LG,
Heiko

Guybrush · 29 Mai 2020, 00:23:40

Zitat von: DS_Starter am 28 Mai 2020, 14:40:33
Edit: Frage ... Hättest du Interesse mitzuwirken sobald ich dazu komme etwas Neues in Fragen Datenbank-Logging anzufangen ?

Würde ich. Ich brauche mittelfristig für mich ohnehin eine Lösung. Ich seh schon jetzt, dass das mit MySQL auf ner kleinen Möhre Probleme geben wird. Und unnötig auf historische Daten verzichten ist auch blöd...

ToKa · 29 Mai 2020, 20:56:08

Hallo Andreas,

es klingt so, als hättest Du nicht nur einen Pi für fhem im Einsatz. Falls ich mich da nicht täusche, warum installierst Du die MySQL Datenbank nicht auf einem anderen Rechner?

Mein Pi sendet die Daten an einen virtualisierten DB Server, von den ich dann die Daten für die Visualisierung auf einer weiteren VM lese.

Beste Grüße
Torsten

Guybrush · 30 Mai 2020, 11:56:20

Zitat von: ToKa am 29 Mai 2020, 20:56:08
es klingt so, als hättest Du nicht nur einen Pi für fhem im Einsatz. Falls ich mich da nicht täusche, warum installierst Du die MySQL Datenbank nicht auf einem anderen Rechner?

Ich habe nur den Pi und daneben noch einen Gira FacilityServer. Das Ganze Zeug von Gira (FacilityServer, Controlpanel etc) will ich aber bei mir aber raushauen. Mein neuer Pi hat aber eine X825 Extension, woran ich eine SSD dran hab. Das Problem beim Pi ist vor allem der Ram von nur 4 GB Ram... Bei den alten Pis mit 512MB/1GB mal ganz abgesehen

Zitat von: ToKa am 29 Mai 2020, 20:56:08
Mein Pi sendet die Daten an einen virtualisierten DB Server, von den ich dann die Daten für die Visualisierung auf einer weiteren VM lese.

Das wäre sicher eine Idee, aber wenn wir ehrlich sind, ist das meiste wofür man mehr als ein Pi bräuchte nur Spielerei - wie das meiste an der Hausautomation, auch wenns Spaß macht..

Ich könnte hier sicherlich auch einen leistungsfähigeren server hinstellen, aber der Pi verbraucht im Vergleich unschlagbar wenig Strom und es ist ja möglich, dass auch nur mit dem Pi zu betreiben. Bessere Hardware ist nicht immer die beste Lösung

Frank_Huber · 30 Mai 2020, 14:50:12

Zitat von: Guybrush am 30 Mai 2020, 11:56:20
Das Problem beim Pi ist vor allem der Ram von nur 4 GB Ram... Bei den alten Pis mit 512MB/1GB mal ganz abgesehen

Der 4er PI wurde gerade mit 8GB RAM angekündigt.
Das könnte ne Variante sein.

Gesendet von meinem S68Pro mit Tapatalk

Guybrush · 30 Mai 2020, 15:50:51

Zitat von: Frank_Huber am 30 Mai 2020, 14:50:12
Der 4er PI wurde gerade mit 8GB RAM angekündigt.
Das könnte ne Variante sein.

bring nichts, solang rasbpian nur als 32bit verfügbar ist. Ob 4 oder 8 GB ist egal. MySQL kann unter raspbian maximal 3 GB allokieren. Man kann zwar auch Debian etc drauf installieren, aber damit geht auch etwas mehr händische Arbeit einher. Insofern (noch) nicht wirklich eine Option (für mich)

Frank_Huber · 30 Mai 2020, 18:47:15

Zitat von: Guybrush am 30 Mai 2020, 15:50:51
bring nichts, solang rasbpian nur als 32bit verfügbar ist. Ob 4 oder 8 GB ist egal. MySQL kann unter raspbian maximal 3 GB allokieren. Man kann zwar auch Debian etc drauf installieren, aber damit geht auch etwas mehr händische Arbeit einher. Insofern (noch) nicht wirklich eine Option (für mich)

Die 8GB Variante des Raspberry ist für mich aber auch ein Zeichen dass ein 64bit OS "auf dem Weg" ist.
Denke ein 64bit Raspbian wird nicht lange auf sich warten lassen.

Gesendet von meinem S68Pro mit Tapatalk

Icinger · 30 Mai 2020, 19:32:59

https://www.heise.de/newsticker/meldung/Raspberry-Pi-Erste-Fassung-des-64-Bit-Kernels-verfuegbar-4524121.html

Muenzi · 03 Juni 2022, 16:37:08

Ich betreibe mal Leichenfledderei und hole den Thread aus der Versenkung hoch. Ich muss Guybrush recht geben, dass die Datenstruktur aktuell nicht perfekt gelöst ist. Bei einigen hunderttausend bis Millionen Zeilen (was bei einem Klimalogger oder vielleicht HM-Thermosten recht schnell erreicht ist) dürfte ein Query (auf einem Raspi) inperformant werden.

Ich bin gerade dabei für meine Zwecke etwas Optimierung zu betreiben, wobei ich aber die Besonderheit habe Plots per Grafana zu erstellen und nicht per FHEM. Das erspart mir Anpassungen in Perl.

Vielleicht möchte ja der eine oder andere mal schauen oder hat gute Anregungen für mich was man verbessern könnte. Aktuell ist der Ansatz Daten aus der History thematisch in verschiedene Tabellen zu splitten und history durch einen Trigger regelmäßig zu leeren (noch nicht implementiert). Das ermöglicht thematische Anpassungen und so Speichereinsparungen. Demnächst soll eine (partielle) Normalisierung wie auch von Guybrush vorgeschlagen, folgen.

https://github.com/StephanMuenzberg/fhem_docker/tree/main/scripts/sql

P.s.: Leider kann die logDb anscheinend aktuell keine Strings als Value verarbeiten. Das ist etwas schade, da ausgeschaltete Homematic-Thermostate "off" statt einer Zieltemperatur senden. Daher musste ich meine Thermostate vorerst auf die niedrigste Nicht-Aus-Temperatur (5 °C) stellen. Gleiches gilt für einige IoT-Geräte mit Batterieanzeige, die gerne eine Akkuwarnung, statt einem integer Wert, schreiben möchten.

ch.eick · 05 Juni 2022, 09:35:44

Moin,
Wenn Du auf dem RPI4 ein 64 Bit Image mit Docker verwendest, dann kannst Du den original Oracle MySQL Container laden. Da sollte MySQL Partitioning möglich sein, was ich aber noch nicht getestet habe.
VG
Christian

Muenzi · 06 Juni 2022, 02:00:41

Zitat von: ch.eick am 05 Juni 2022, 09:35:44
Moin,
Wenn Du auf dem RPI4 ein 64 Bit Image mit Docker verwendest, dann kannst Du den original Oracle MySQL Container laden. Da sollte MySQL Partitioning möglich sein, was ich aber noch nicht getestet habe.
VG
Christian

Hi Christian,

besten Dank für den Tipp. Du hast Recht, aber Partitionierung unterstützt MariaDB auch. Partitionierung in MySQL/MariaDB wird aber häufig eher kritisch gesehen, denn oft bringt ein guter Index ebenso viel (Ausnahme, beim Löschen einer ganzen Partition). Das ist anders als beispielweise bei Databricks' Delta Tables, die aber auch keine richtige Datenbank sind.

Der Grund, dass ich die Tabellen hier splitte ist mehr, dass ich dadurch mein Tabellenschema anpassen kann. Beispielweise sollten Klimalogger (diverse T, p, Humidity) nur DOUBLE Werte enthalten. Batteriedaten können hingegen sowohl Gleitkommazahlen sein als auch eine Einschätzung ob der Status "gut", "okay" oder "schlecht" ist. Oder ich speichere Verspätungen (und Gründe dafür) des örtlichen ÖPNVs, da können sogar Arrays notwendig werden.

Das kann ich zwar alles in die History packen, aber dann muss halt alles als String abgelegt werden, was aus Speichersicht suboptimal wäre.

Die zusätzliche Normalisierung findet außerdem noch zur Perfomance- und Maintenanceoptimierung statt. Zum Beispiel ist ein Index auf Basis von Integer-Werten meines Wissens in MySQL performanter (wobei ich das gelernt und nie getestet habe) als über Strings, zum anderen reduziert das den Speicherbedarf noch einmal enorm.

Was meinst du, ergibt das soweit Sinn oder habe ich etwas übersehen?

Viele Grüße
Stephan

ch.eick · 06 Juni 2022, 09:19:55

Zitat von: Muenzi am 06 Juni 2022, 02:00:41
Hi Christian,

besten Dank für den Tipp. Du hast Recht, aber Partitionierung unterstützt MariaDB auch. Partitionierung in MySQL/MariaDB wird aber häufig eher kritisch gesehen, denn oft bringt ein guter Index ebenso viel (Ausnahme, beim Löschen einer ganzen Partition). Das ist anders als beispielweise bei Databricks' Delta Tables, die aber auch keine richtige Datenbank sind.

Der Grund, dass ich die Tabellen hier splitte ist mehr, dass ich dadurch mein Tabellenschema anpassen kann. Beispielweise sollten Klimalogger (diverse T, p, Humidity) nur DOUBLE Werte enthalten. Batteriedaten können hingegen sowohl Gleitkommazahlen sein als auch eine Einschätzung ob der Status "gut", "okay" oder "schlecht" ist. Oder ich speichere Verspätungen (und Gründe dafür) des örtlichen ÖPNVs, da können sogar Arrays notwendig werden.

Das kann ich zwar alles in die History packen, aber dann muss halt alles als String abgelegt werden, was aus Speichersicht suboptimal wäre.

Die zusätzliche Normalisierung findet außerdem noch zur Perfomance- und Maintenanceoptimierung statt. Zum Beispiel ist ein Index auf Basis von Integer-Werten meines Wissens in MySQL performanter (wobei ich das gelernt und nie getestet habe) als über Strings, zum anderen reduziert das den Speicherbedarf noch einmal enorm.

Was meinst du, ergibt das soweit Sinn oder habe ich etwas übersehen?

Viele Grüße
Stephan

Hallo Stephan,
so tief bin ich da nicht drin. Bei mir wird nur irgend wann man die Datenbank mit den Files zu groß, weshalb ich immer schon fleißig aufräume

Das mit den Partitionen hatte ich in dem zusammenhang mal nachgelesen. Ich habe bisher nur minütliche Stromverbräuche vom ganzen Haushalt wegen des Wechselrichters, was aber noch nicht weh tut.

VG
Christian

Muenzi · 06 Juni 2022, 12:26:04

Zitat von: ch.eick am 06 Juni 2022, 09:19:55
Hallo Stephan,
so tief bin ich da nicht drin. Bei mir wird nur irgend wann man die Datenbank mit den Files zu groß, weshalb ich immer schon fleißig aufräume Das mit den Partitionen hatte ich in dem zusammenhang mal nachgelesen. Ich habe bisher nur minütliche Stromverbräuche vom ganzen Haushalt wegen des Wechselrichters, was aber noch nicht weh tut.

VG
Christian

Hallo Christian,

hier hat mal jemand eine schöne Zusammenfassung zu Pros/Cons/Use Cases für Partitioning in Mysql geschrieben.
http://mysql.rjweb.org/doc.php/partitionmaint

Beruflich nutze ich bisher Partitioning eigentlich nur für Zeitreihen-Partitionierung weil die meisten User ohnehin nur die letzten paar Tage Daten sehen wollen und nicht x Jahre. Zugegeben, da ergibt Partition Pruning dann auch Sinn. Allerdings muss der Query das dann auch unterstützen.

Aber ich gebe dir vollkommen recht. Eine Cleanup-Strategie ist meist VIEL besser als Datenbank-Tweaking.