DbLog Datenbankstruktur MySQL

[Guybrush]

Hallo zusammen,

ich migriere bei uns gerade alles von dem Gira-M*** weg... Das dateibasierte Loging in FHEM ist allenfalls für kleinere Installationen angezeigt. Dementsprechend gibt es kaum eine Alternative zur datenbankbasierten Protokollierung. Das läuft insoweit auch alles wunderbar. Mir ist allerdings aufgefallen, dass die Datenbankstruktur der history Tabelle schräg erscheint. Vielleicht erkenne ich auch einfach nur den Sinn dahinter nicht. Im Nachfolgenden geht es nur um MySQL.

Die vorgegebene Struktur sieht ja wie folgt aus:

Code Auswählen Erweitern

CREATE TABLE `history` (
  `TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DEVICE` varchar(64) DEFAULT NULL,
  `TYPE` varchar(64) DEFAULT NULL,
  `EVENT` varchar(512) DEFAULT NULL,
  `READING` varchar(64) DEFAULT NULL,
  `VALUE` varchar(255) DEFAULT NULL,
  `UNIT` varchar(32) DEFAULT NULL,
  KEY `IDX_HISTORY` (`DEVICE`,`READING`,`TIMESTAMP`,`VALUE`),
  KEY `DEVICE` (`DEVICE`,`READING`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Daten werden abgelegt wie folgt:

Code Auswählen Erweitern

select * from history limit 1;
+---------------------+-----------------------------------+------+---------------------+---------+-------+--------+
| TIMESTAMP           | DEVICE                            | TYPE | EVENT               | READING | VALUE | UNIT   |
+---------------------+-----------------------------------+------+---------------------+---------+-------+--------+
| 2020-05-21 00:05:42 | ***.Temperatur.IST | KNX  | state: 24.08 °C | state   | 24.08 | °C |
+---------------------+-----------------------------------+------+---------------------+---------+-------+--------+


Wenn man nun viel protokolliert, können da ganz schnell hunderttausende Einträge/Tag zukommen. Ich habe das bei mir insoweit schon weitestgehend auf die wirklich nötigsten Daten beschränkt, was aber immer noch sehr viel ist. Es scheint hier ja nicht wenige zu geben, die in ihrer Datenbank 10 Mio und mehr Einträge haben. Meiner (ersten) Analyse nach gibt es einige Punkte, die unnötigerweise eine dramatische Auswirkungen auf die Performance größerer Datenbanken haben dürften. Da es ja möglich ist, dass dahinter ein Sinn steht, den ich vielleicht noch nicht entdeckt hab, nachfolgende Fragen:

1. Wieso gibt es einen Index DEVICE? Device, Reading sind bereits im Index IDX_HISTORY enthalten, der entsprechend genauso greift. Der Index DEVICE ist dementsprechend überflüssig.

2. Wozu gibt es die Spalte EVENT? Das ist doch nichts anderes als ein CONCAT(READING, ': ', VALUE, ' ', UNIT) ? Das ist ein Varchar(512) und macht z.m. bei mir ca. 15% der Größe aus, obwohl da nur redundantes Zeug drin steht.

3. Warum wird eine dynamische Datenstruktur genutzt? Grundsätzlich wäre es doch erheblich schneller, wenn die Datenstruktur für die Selektion Fix wäre. Das bedingt natürlich joins.

4. Die Indexe scheinen nicht gut zu passen. Z.b. selektieren Plots u.a. so:

Code Auswählen Erweitern


SELECT DATE_FORMAT(TIMESTAMP, '%Y-%m-%d %H:%i:%s'), DEVICE, READING, VALUE FROM history WHERE 1=1 AND DEVICE = 'Wetterstation.Helligkeitsensor1' AND TIMESTAMP >= STR_TO_DATE('2020-05-26 08:55:00', '%Y-%m-%d %H:%i:%s') AND TIMESTAMP <= STR_TO_DATE('2020-05-27 08:54:59', '%Y-%m-%d %H:%i:%s') ORDER BY TIMESTAMP;
Hier greift der Index IDX_HISTORY nicht vollständig. Der ist nach DEVICE, READING, TIMESTAMP, VALUE sortiert. Genutzt würde aber nur DEVICE. Eine weitere Einschränkung anhand des Indexes erfolgt dann nicht. Für dieses Query wäre z.b. ein Index (nur) auf DEVICE, TIMESTAMP deutlich performanter.

5. Wäre eine eine Datenstruktur (Indexe nicht berücksichtigt) nicht sinnvoller, die so ähnlich aufgebaut wäre:

Code Auswählen Erweitern


CREATE TABLE `history` (
  `TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DEVICE_ID` smallint(16) UNSIGNED,
  `TYPE_ID` smallint(16) UNSIGNED,
  `READING_ID` smallint(16) UNSIGNED,
  `VALUE_ID` int(32) UNSIGNED,
  `UNIT_ID` smallint(16) UNSIGNED
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `device` (
  `DEVICE_ID` smallint(16) UNSIGNED AUTO_INCREMENT,
  `DEVICE` varchar(64),
  PRIMARY KEY (DEVICE_ID, DEVICE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `type` (
  `TYPE_ID` smallint(16) UNSIGNED AUTO_INCREMENT,
  `TYPE` varchar(64),
  PRIMARY KEY (TYPE_ID, TYPE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `reading` (
  `READING_ID` smallint(16) UNSIGNED AUTO_INCREMENT,
  `READING` varchar(64),
  PRIMARY KEY (READING_ID, READING)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `value` (
  `VALUE_ID` int(32) UNSIGNED AUTO_INCREMENT,
  `VALUE` varchar(255),
  PRIMARY KEY (VALUE_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `unit` (
  `UNIT_ID` smallint(16) UNSIGNED AUTO_INCREMENT,
  `UNIT` varchar(32),
  PRIMARY KEY (UNIT_ID, UNIT)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Das mag vielleicht als Overkill aussehen und sicherlich für kleine Installationen auch nicht notwendig, aber gerade bei größeren Datenmengen wäre eine Selektion auf die History Tabelle so um Faktoren schneller - die dann notwendigen Joins fallen da kaum ins Gewicht - zumal das alles mit Ausnahme von Value gecached werden könnte. Ein anderer Vorteil wäre auch, dass die Datenmenge nicht mehr linear skaliert, sondern deutlich reduziert würde.

Hat sich damit schon mal jemand beschäftigt bzw. unterliege ich irgendeinem Fehlverständnis? Ich habe FHEM ja noch nicht lange im Einsatz und mag daher sicher vieles noch nicht kennen...

LG
Andreas

[rudolfkoenig]

Das dateibasierte Loging in FHEM ist allenfalls für kleinere Installationen angezeigt.

Ab wann redet man so von grossen Installationen?

[Guybrush]

Ab wann redet man so von grossen Installationen?

Das ist ja eine relative Frage... Ich hab bei mir alleine ca. 200 KNX Geräte, 80 1-Wire Sensoren,10 Modbus und ansonsten noch wie Zigbee/HUE etc. Ich hab ehrlich gesagt keine Ahnung von was für Größenordnungen ihr sonst ausgeht und ob ich mit meiner Installation hier etwas übers Ziel hinausgeschossen bin..

Ich hab insoweit laut FHEM auch 1013 Devices angelegt (Großteil kommt durch KNX Adressen)

[rudolfkoenig]

Eine Installation mit ueber 1000 Geraeten ist selten, ich habe aber auch mit doppelt so grossen Konfigurationen zu tun gehabt.
Falls irgendwo wg. dem Framework Engpaesse sichtbar werden, bitte melden.

[greenBelt]

Exportiere doch ganz einfach das db-log und baue dir extern deine Struktur neu auf. Auf dem T-SQL-Server klappt das wunderbar

Gesendet von meinem Pixel 4 mit Tapatalk

[DS_Starter]

Hallo Andreas,

erstmal vielen Dank dass du dir Gedanken um Verbesserungen machst.
Ganz allgemein ist zu DbLog zu sagen, dass dieses Modul bereits mindestens 10 Jahre im Einsatz ist und bereits einiges "durchlebt" hat. Die Tabellenstruktur wie sie momentan ist, würde ich nur in einem kompletten Neuansatz mit einem weiteren Modul (sagen wir DbLogNG) umbauen. Jetzt in die vorhandene Tabellen-Architektur einzugreifen würde die momentan erreichte Stabilität beeintächtigen zumal die wenigsten FHEM-User DB-Administratoren sind.

Ich kann mir den zu erwartenden Supportaufwand lebhaft vorstellen. 
Und es gibt auch nicht nur DbLog welches mit der DB arbeitet.

Eigentlich hatte ich mir vorgenommen, eine Weiterentwicklung des Loggings in einem weiteren Modul zu bauen welches neben den von dir angesprochenen Verbesserungen dann z.B. auch NoSQL-DB berücksichtigen würde.
Aber ob ich jemals dazu komme steht in den Sternen.

Das vorab, auf einige deiner Fragen möchte ich noch antworten bei denen mir etwas aufgefallen ist.

Wieso gibt es einen Index DEVICE? Device, Reading sind bereits im Index IDX_HISTORY enthalten, der entsprechend genauso greift. Der Index DEVICE ist dementsprechend überflüssig.

Weder den Index DEVICE noch den Index IDX_HISTORY gibt es per default, sondern nur den Search_Idx. Wo hast du die her ?

2. Wozu gibt es die Spalte EVENT? Das ist doch nichts anderes als ein CONCAT(READING, ': ', VALUE, ' ', UNIT) ? Das ist ein Varchar(512) und macht z.m. bei mir ca. 15% der Größe aus, obwohl da nur redundantes Zeug drin steht.

Die Spalte EVENT existiert wiederum aus historischen Gründen. Aber genau aus dem angesprochenen Grund kann man als User auf das Füllen der Spalte verzichten indem man das Attribut colEvent = 0 setzt. Das ist in der commandRef beschrieben !

EDIT: Auch User verwenden die Spalte für alternative, eigene Einträge. So kann man über das Attr valueFn darauf Einfluß nehmen und der addLog Befehl setzt in dieser Spalte auch ein individuelles Kennzeichen.

Die Indexe scheinen nicht gut zu passen. Z.b. selektieren Plots u.a. so:
SELECT DATE_FORMAT(TIMESTAMP, '%Y-%m-%d %H:%i:%s'), DEVICE, READING, VALUE FROM history WHERE 1=1 AND DEVICE = 'Wetterstation.Helligkeitsensor1' AND TIMESTAMP >= STR_TO_DATE('2020-05-26 08:55:00', '%Y-%m-%d %H:%i:%s') AND TIMESTAMP <= STR_TO_DATE('2020-05-27 08:54:59', '%Y-%m-%d %H:%i:%s') ORDER BY TIMESTAMP;

Also bei mir selektieren Plots so:

Code Auswählen Erweitern


2020.05.27 12:43:41.466 4: LogDB1 - Processing Statement:
SELECT
                      DATE_FORMAT(TIMESTAMP, '%Y-%m-%d %H:%i:%s'),
                      DEVICE,
                      READING,
                      VALUE
                       FROM fhemtest1.history WHERE 1=1 AND DEVICE = 'MyWetter' AND READING = 'temperature' AND TIMESTAMP >= STR_TO_DATE('2020-05-27 00:00:00', '%Y-%m-%d %H:%i:%s') AND TIMESTAMP <= STR_TO_DATE('2020-05-28 00:00:00', '%Y-%m-%d %H:%i:%s') ORDER BY TIMESTAMP


Das heißt das Feld READING ist mit enthalten, bzw. alle -> DEVICE, READING, TIMESTAMP.
Übrigens wird der Index der default ohne VALUE gebildet (weiß wieder nicht wo du VALUE hergenommen hast):

Code Auswählen Erweitern


CREATE INDEX Search_Idx ON `fhem`.`history` (DEVICE, READING, TIMESTAMP) USING BTREE;



Soweit vllt. dazu. Jedenfalls nehme ich deine vorgeschlagene Tabellenstrutur zu meinen Unterlagen und werde sie mit verwenden falls ich mal die Energie aufbringen sollte ein weiterentwickeltes DbLogNG zu erstellen.
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.

LG,
Heiko

[Guybrush]

Exportiere doch ganz einfach das db-log und baue dir extern deine Struktur neu auf. Auf dem T-SQL-Server klappt das wunderbar

Das kann man auch alles mit MySQL machen. Grundsätzlich ist MySQL - für allgemeine Anwendungen - meiner Meinung nach die geeignetste Datenbank. Es geht mir ja nicht darum, dass ich extern irgendwas mit den Daten was machen will. Ich finde ja gerade den Reiz an FHEM, dass es als universelle Schnittstelle dienen kann. Ich stellte mir nur die Frage, wie man mit geringst möglichen Aufwand sicherstellen kann, dass die Datenselektion bei größeren Datenmengen noch flott ist. Ich hab hier nur einen Raspberry Pi 3 im Einsatz. Derzeit sind nur ein paar hundert tausend Einträge drin und es geht noch alles halbwegs. Das Problem an relationalen Datenbanken ist nur, dass mit zunehmender Datenmenge die Querytimes länger werden. Insbesondere dann, wenn der Index nicht mehr heiß vorgehalten werden kann, was in Anbetracht des geringen RAMs der Pis sehr schnell passieren wird.

[DS_Starter]

Grundsätzlich ist MySQL - für allgemeine Anwendungen - meiner Meinung nach die geeignetste Datenbank.

Ich mag diese DB auch sehr und benutze sie in meinem Prod. FHEM.
Allerdings muss ich als Maintainer ebenfalls sicherstellen, dass die Lösung auch von SQLite und Postgre funktioniert.
Das nur als Anmerkung damit man nicht zu sehr auf MySQL/MariaDB fixiert ist.

[greenBelt]

Ich bin mehr in der T-SQL Welt unterwegs. Meine Erfahrungen mit großen Datenmengen auf dem Raspberry brachten mich auf den Gedanken die Datenauswertung auf einem externen System laufen zu lassen.
In gewisser Weise lässt sich der Export der RAW Daten automatisieren. Alles andere erledige ich auf dem SQL-Server

Gesendet von meinem Pixel 4 mit Tapatalk

[CoolTux]

Ich nehme gerne Postgresql. Finde die DB sehr schön in der Verwendung. Kommt näher an Oracle ran wo ich früher mal mit tätig war.

[Guybrush]

Ganz allgemein ist zu DbLog zu sagen, dass dieses Modul bereits mindestens 10 Jahre im Einsatz ist und bereits einiges "durchlebt" hat. Die Tabellenstruktur wie sie momentan ist, würde ich nur in einem kompletten Neuansatz mit einem weiteren Modul (sagen wir DbLogNG) umbauen. Jetzt in die vorhandene Tabellen-Architektur einzugreifen würde die momentan erreichte Stabilität beeintächtigen zumal die wenigsten FHEM-User DB-Administratoren sind.

Ich wollte jetzt nicht nur meckern. Ich kenn die Problematik ja, dass man Sachen die man vor Jahren machte, man ggf. heute anders machen würde etc... Mir ist auch klar, dass DB Architektur auch für Softwareentwickler oftmals nur Randthemen sind. Daher dachte ich mir ja, ich stoße mal eine Diskussion an, wie man ohne eine komplette Neuentwicklung ggf. z.m. etwas Feintuning betreiben kann

Eigentlich hatte ich mir vorgenommen, eine Weiterentwicklung des Loggings in einem weiteren Modul zu bauen welches neben den von dir angesprochenen Verbesserungen dann z.B. auch NoSQL-DB berücksichtigen würde.

MySQL ist schon ok als Datenbank. Die ist bereits sehr performant - z.m. wenn die Datenstruktur passt.

Das vorab, auf einige deiner Fragen möchte ich noch antworten bei denen mir etwas aufgefallen ist.
Weder den Index DEVICE noch den Index IDX_HISTORY gibt es per default, sondern nur den Search_Idx. Wo hast du die her ?

Ich hatte es mir ehrlich gesagt sehr einfach gemacht beim Aufsetzen und es nach dem Howto hier gemacht, da ich im FHEM Wiki nicht direkt fündig wurde
https://haus-automatisierung.com/hardware/fhem/2016/05/20/fhem-tutorial-reihe-part-7-mysql-server-fuer-logging-nutzen.html

Wenn die Datenstrukturen offiziell anders sind, dann schau ich mir das selbst nochmal an. Ich wollte mir ohnehin die nötigen Indexe noch selbst erstellen, aber hier erstmal nachfragen... Danke aber für den Hinweis!

Die Spalte EVENT existiert wiederum aus historischen Gründen. Aber genau aus dem angesprochenen Grund kann man als User auf das Füllen der Spalte verzichten indem man das Attribut colEvent = 0 setzt. Das ist in der commandRef beschrieben !

kannte ich nicht, danke für den Hinweis!

Code Auswählen Erweitern


2020.05.27 12:43:41.466 4: LogDB1 - Processing Statement:
SELECT
                      DATE_FORMAT(TIMESTAMP, '%Y-%m-%d %H:%i:%s'),
                      DEVICE,
                      READING,
                      VALUE
                       FROM fhemtest1.history WHERE 1=1 AND DEVICE = 'MyWetter' AND READING = 'temperature' AND TIMESTAMP >= STR_TO_DATE('2020-05-27 00:00:00', '%Y-%m-%d %H:%i:%s') AND TIMESTAMP <= STR_TO_DATE('2020-05-28 00:00:00', '%Y-%m-%d %H:%i:%s') ORDER BY TIMESTAMP


Das heißt das Feld READING ist mit enthalten, bzw. alle -> DEVICE, READING, TIMESTAMP.
Übrigens wird der Index der default ohne VALUE gebildet (weiß wieder nicht wo du VALUE hergenommen hast):

Code Auswählen Erweitern


CREATE INDEX Search_Idx ON `fhem`.`history` (DEVICE, READING, TIMESTAMP) USING BTREE;


Der Indexteil VALUE macht keinen Sinn... aber das war auch aus dem Howto wie vor. Bei deinem Beispiel passt DEVICE, READING, TIMESTAMP als Index. Ich wundere mich nur, wieso der bei mir nach Reading nicht selektiert. Könnte damit zusammen hängen, dass ich die in den Plots nicht angab, sondern nur die devices.

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.

Ich schau mir jetzt erstmal an, was bei mir noch anders ist im Vergleich zur offiziellen Version.

[DS_Starter]

Ich hatte es mir ehrlich gesagt sehr einfach gemacht beim Aufsetzen und es nach dem Howto hier gemacht, da ich im FHEM Wiki nicht direkt fündig wurde
https://haus-automatisierung.com/hardware/fhem/2016/05/20/fhem-tutorial-reihe-part-7-mysql-server-fuer-logging-nutzen.html

Wenn die Datenstrukturen offiziell anders sind, dann schau ich mir das selbst nochmal an. Ich wollte mir ohnehin die nötigen Indexe noch selbst erstellen, aber hier erstmal nachfragen... Danke aber für den Hinweis!

Immer zuerst in der commandRef der Module nachlesen. Die wird von den Entwicklern aktiv gepflegt. Auch das Wiki ist u.U. mit Vorsicht zu genießen je nachdem wer es wann geschrieben hat.
Manches ist schlicht falsch und manches veraltet mit der Zeit wenn nicht aktiv gepflegt.

EDIT: Die Standard Skripte zur Einrichtung findest du hier: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/dblog
Steht auch in der commandRef drin.

[Guybrush]

Immer zuerst in der commandRef der Module nachlesen. Die wird von den Entwicklern aktiv gepflegt. Auch das Wiki ist u.U. mit Vorsicht zu genießen je nachdem wer es wann geschrieben hat.
Manches ist schlicht falsch und manches veraltet mit der Zeit wenn nicht aktiv gepflegt.

als quasi Anfänger und noch kein Brett vorm Kopf habender wäre ein entsprechender Hinweis auf der Startseite nach der Ersteinrichtung ggf. ganz gut. Ich ging z.m. davon aus, dass das Wiki passt. Dass nur die CommandRef maßgeblich ist, wusste ich nicht. Das erspart aber tatsächlich Kummer...

[DS_Starter]

Schau mal auf den Screenshot. Der Hinweis findet überall wo ein FHEM-Modul beschrieben wird, zumindest aber bei DbLog, DbRep ... 

Aber damit kein falscher Einruck entsteht, das Wiki ist wirklich sehr hilfreich um Zusammenhänge zu erklären und ich verfasse auch viel zu meinen Modulen (siehe Footer), aber im Zweifel _immer_ erst die commandRef konsultieren.

[DS_Starter]

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.