DbLog-MySQL

Aus FHEMWiki
DbLog-MySQL
Zweck / Funktion
Protokolliert Ereignisse in einer Datenbank
Allgemein
Typ Hilfsmodul
Details
Dokumentation EN / DE
Support (Forum) Automatisierung
Modulname 93_DbLog.pm
Ersteller tobiasfaust (Forum /Wiki)
DS_Starter (Forum /Wiki)
Wichtig: sofern vorhanden, gilt im Zweifel immer die (englische) Beschreibung in der commandref!


Einleitung

Da bei der Inbetriebnahme und der Migration von MySQL bzw MariaDB-Datenbanken einige kleine Fallen auftreten können, gibt's hier ein paar Hinweise.

Server version: 10.1.40-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

db.conf

DbLog wird durch 2 verschiedene Einträge aktiviert/definiert. In einer Datei namens db.conf werden die Parameter für eine Verbindung zur Datenbank (host, username, password, etc.) hinterlegt. Diese Datei kann in einem beliebigen Verzeichnis angelegt werden. Für eine MariaDB-Datenbank sieht die db.conf folgendermaßen aus:

%dbconfig= (
    connection => "mysql:database=fhem;host=db;port=3306",
    user => "fhemuser",
    password => "fhempassword",
);

Im Verzeichnis contrib/dblog der FHEM-Installation befindet sich eine Beispielkonfiguration mit der Syntax für jeden unterstützen Datenbanktyp. Bei der Verwendung von MariaDB muss die MySQL-Datei verwendet werden. Es wird empfohlen diese Datei zu kopieren und erst dann entsprechend zu bearbeiten. Am Besten kopiert man diese Datei in das FHEM Home Directory /opt/fhem/ und achtet auf die entsprechenden Rechte!

chown fhem:dialout /opt/fhem/db.conf

Tabellen

Die Datenbank ist relativ simpel gestaltet und besteht lediglich aus den folgenden beiden Tabellen:

  • current
  • history

DbLog ist auf eine feste Tabellenstruktur angewiesen. Man muss daher in seiner Datenbank eine Tabelle mit folgenden Spalten anlegen:

Spalte Beschreibung (en) Beschreibung (de) Beispiel
TIMESTAMP timestamp of event Zeitstempel 2007-12-30 21:45:22
DEVICE device name Device-Name Wetterstation
TYPE device type Device-Typ KS300
EVENT event specification as full string Eventspezifikation als Text humidity: 71 (%)
READING name of reading extracted from event Bezeichnung des Readings humidity
VALUE actual reading extracted from event Wert des Readings 71
UNIT unit extracted from event Einheit des Readings %

Im Verzeichnis contrib/dblog befindet sich das Script db_create_mysql.sql , welches eine neue Datenbank samt Tabellenstruktur anlegt. Ausserdem wird ein Benutzer "fhemuser" mit einem (zu ändernden) "fhempasswort" angelegt.

Primary Keys

current

Die Tabelle current enthält für jedes zu loggende Device lediglich den letzten Wert. Falls noch kein Wert geloggt wurde, ist diese Tabelle leer. Falls der Inhalt gelöscht wird, bauen sich die Daten automatisch wieder auf. Es gehen durch das löschen der Tabelle current keine Log-Informationen verloren. Der Inhalt wird aber u.a. für die Dropdown-Felder beim Plot-Editor verwendet.

Um doppelte Einträge in der Tabelle zu vermeiden, wurden die Möglichkeit geschaffen Primary Keys zu definieren. Da in der Spalte READING u.U. bei verschiedenen Geräten gleiche Namen vorkommen können, sollte der Primary Key um den Gerätenamen erweitert werden. Der Primary Key sollte also aus DEVICE und READING bestehen. Um in der Datenbank fhem diesen PK zu setzen, kann folgender SQL Code verwendet werden:

ALTER TABLE `fhem`.`current` 
CHANGE COLUMN `DEVICE` `DEVICE` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
CHANGE COLUMN `READING` `READING` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
ADD PRIMARY KEY (`DEVICE`, `READING`);
history

Die Tabelle history enthält alle bisher geloggten Daten. Löschen in dieser Tabelle bedeutet automatisch Datenverlust (gewollt oder nicht ... ) Der Inhalt dieser Tabelle wird verwendet, um die Plots zu zeichnen oder Auswertungen mit DbRep anzufertigen

Um Problem beim Import von cacheFiles zu vermeiden, kann in der Datenbank ein PK angelegt werden, welcher Timestamp, Device und Reading umfasst. Dadurch werden doppelte Einträge wirksam verhindert.

ALTER TABLE `fhem`.`history` 
CHANGE COLUMN `DEVICE` `DEVICE` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
CHANGE COLUMN `READING` `READING` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
ADD PRIMARY KEY (`TIMESTAMP`,`DEVICE`, `READING`);

Beispiel: Anlegen und Nutzung einer Mysql-Datenbank

Anstatt nano kann jeder andere kompatible Editor verwendet werden. Weiterhin bitte beachten, dass die hier genannten Befehle teilweise root-Rechte voraussetzen. Entweder komplett als root arbeiten, oder mittels sudo.

Unter Ubuntu/debian:

apt-get update && apt-get install mysql-server mysql-client libdbd-mysql libdbd-mysql-perl


Bei der Installation sollte man aus Sicherheitsgründen ein Passwort für den mysql-root vergeben, wenn man nicht sogar ganz den Login verbietet.

Hinweis: im Folgenden ist "#" der normale Prompt und "mysql>" der prompt innerhalb mysql, dieser kann mit exit verlassen werden.

Zum Test mal mit mysql verbinden:

# mysql -p -u root
Enter password:
mysql> exit

Jetzt die Tabellenstruktur anlegen. Hierfür kann die Datei /opt/fhem/contrib/dblog/db_create_mysql.sql als Vorlage verwendet und das Passwort und der Benutzername geändert werden.

cd /opt/fhem/contrib/dblog/
nano db_create_mysql.sql

Dann wird die Datei eingelesen (root Passwort wird abgefragt):

# mysql -u root -p < db_create_mysql.sql

Jetzt kann man den Zugang testen:

# mysql -p -u <fhemuser>
Enter password: <fhempassword>
mysql> show databases;

Nun müsste eine Datenbank "fhem" angezeigt werden, die die Tabellen current und history enthält.

Nun in der Datei db.conf den mysql-Block auskommentieren und ebenfalls Benutzername, Passwort UND HOST anpassen. Leider ist hier nicht standardmäßig localhost eingestellt.

nano /opt/fhem/db.conf

Jetzt kann unter FHEM ein DbLog-Device angelegt werden (mit dem beispiel wird alles geloggt:

define logdb DbLog ./db.conf .*:.*

Als State muss ein "connected" angezeigt werden.

Ein rereadcfg in FHEM stellt sicher, dass die neue Konfiguration übernommen wird - ein Neustart ist nicht erforderlich.

Nun kann die Funktion noch einmal überprüft werden:

 # mysql -u <fhemuser> -p
 Enter password: <fhempassword>
 mysql> use fhem;
 Database changed
 mysql> show tables;
 +----------------+
 | Tables_in_fhem |
 +----------------+
 | current        |
 | history        |
 +----------------+
 2 rows in set (0,00 sec)
 mysql> select * from history; # Achtung, kann sehr groß werden .... #

Beispiel: Abfragescript PHP/MySQL

Um eine schnelle Übersicht zu bekommen habe ich mir dieses Script geschrieben:

<?php $pdo = new PDO('mysql:host=localhost;dbname=fhem', 'fhemuser', 'fhempasswort');
echo '<h2>Tabelle Current</h1><br><table border="1">';
  echo "<tr><th>Anzahl</th><th>Name</th><th>Readings</th></tr>";
$sql = "SELECT COUNT(*), DEVICE, GROUP_CONCAT(DISTINCT READING ORDER BY READING DESC SEPARATOR '</li><li>') FROM current GROUP BY DEVICE;"; foreach ($pdo->query($sql) as
$row) {
  echo "<tr><td>" . $row[0] . "</td><td>" . $row[1] . "</td><td><ol><li>" . $row[2] . "</li></ol></td></tr>";
}
echo "</table>";


echo '<h2>Tabelle History</h1><br><table border="1">';
  echo "<tr><th>Anzahl</th><th>Name</th></tr>";
$sql = "SELECT COUNT(*), DEVICE FROM history GROUP BY DEVICE;"; foreach ($pdo->query($sql) as
$row) {
  echo "<tr><td>" . $row[0] . "</td><td>" . $row[1] . "</td></tr>";
}
echo "</table>";
?>

Bitte passt fhemuser und fhempasswort an. Das Ganze kommt dann nach /var/www/html/fhemdb.php und ist mit <IP>/fhemdb.php aufrufbar. Wenn ihr den 2. Block für die history Tabelle ausklammert oder entfernt läuft das Script viel schneller ab - klar die history Tabelle ist meist randvoll.

Integration von DBLog in eigene Module

Bereitstellung der UNITS

Mit der DbLog_splitFn kann der Modulautor selbst festlegen, wie die Events des Moduls in die Bestandteile Reading/Value/Unit zerlegt werden um ein korrektes Logging per DbLog zu gewährleisten.

Dazu muss der Modulautor in der Initialize-Funktion eine DbLog_splitFn bereitstellen:

sub X_Initialize($)
{
	my ($hash) = @_;
	...
	$hash->{DbLog_splitFn}      = "X_DbLog_splitFn";
}

Die genaue Aufrufsyntax und Funktionweise einer DbLog_split-Funktion findet man hier.

Werte auslesen

Manchmal möchte man Daten aus den Logs abrufen ohne händisch in der Datenbank herumzuwühlen (s.u.). Dies ist insb. auch dann hilfreich, wenn man eigenen Funktionen, Notifys oder spezielle Plots entwirft, bei denen man auf Logdaten zugreifen möchte.

Grundsätzlich beschrieben ist dies in der commandref/DbLog und unterscheidet sich minimal (aber entscheidend) von der Struktur bei FileLogs.

Hier ein paar Beispiele, was man damit anstellen kann:

  • get meineDB - - 2016-10-01 2016-10-03 meinSensor alle Einträge des meinSensor vom 01.10.-03.10.2016
  • get meineDB - - 2016-10-01_08:00:00 2016-10-01_16:00:00 meinSensor alle Einträge des meinSensor von 8-16 Uhr am 01.10.2016
  • get meineDB - - 2016-10-01_08:00:00 2016-10-01_16:00:00 meinSensor:temperature nur die temperature Werte
  • { ReadingsTimestamp("meinSensor","state","0") } Timestamp des aktuellen state des meinSensor
  • { OldTimestamp("meinSensor") } Timestamp des letzten state des FHT_3a32
  • { time_str2num(OldTimestamp("meinSensor")) } Timestamp in Sekunden des letzten state des meinSensor
  • ...

Bearbeitung von Datenbank-Einträgen

Info blue.png
Dieser Abschnitt soll lediglich eine kleine Einführung in die Datenbank-Bearbeitung liefern. Für vertiefende Informationen sollte man sich grundsätzlich mit SQL beschäftigen. Eine umfassende und gut verständliche Anleitung zu SQL bietet bspw. w3schools.


Irgendwann wird der Fall eintreten, dass in der Datenbank Einträge drinstehen, die geändert oder gelöscht werden sollen (zB. fehlerhafte Sensor-Rückmeldungen, umbenannte Readings). In klassischen Log-Dateien würde man diese einfach bearbeiten und löschen/anpassen (wobei man aber tunlichst zuvor FHEM stoppt, um Datenfehler zu vermeiden). Eine Datenbank kann bearbeitet werden, ohne FHEM stoppen zu müssen.

Datenbanken kann man ohne weitere Hilfsmittel direkt von der Kommandozeile/Shell aus bearbeiten. Alternativ gibt es auch verschiedenste Tools (webbasiert oder als Applikation), die einen dabei unterstützen (Bsp. findet man u.a. hier). Für einfache Arbeiten reicht allerdings idR. Shell.

SQLite-Datenbanken

Öffnen der DB unter Linux:

(Es werden Schreibrechte benötigt,ohne kann man die DB zwar öffnen, aber nichts machen)

sudo sqlite3 fhem.db

Dadurch öffnet sich ein SQL-Konsole, auf der alle weiteren Befehle ausgeführt werden.

Schliessen der DB:

sqlite> .exit


Hilfe anzeigen:

sqlite> .help


Alle Tabellen anzeigen:

sqlite> .tables


Das Schema der DB anzeigen:

(vgl. oben DbLog#Datenbanken und DbLog#Beispiel: Anlegen und Nutzung einer SQLite-Datenbank)

sqlite> .schema


Alle Eintäge anzeigen:

Die Einträge liegen alle in der Tabelle "History".

Ganz wichtig ist immer das ";" am Ende Zeile (bei allen Kommandos, die nicht mit einem "." anfangen). Wenn es vergessen wurde zeigt die Konsole solange neue Zeilen bis ein ";" eingegeben wird. So kann ein Befehl auch bequem über mehrere Zeilen geschrieben werden.

sqlite> select * from HISTORY;

Dies kann sehr lange dauern und kann ggf. mit STRG-C abgebrochen werden.


Alle Einträge eines Geräts anzeigen:

In where-Statements werden Strings in einfache Anführungsstriche gesetzt, Zahlen nicht.

sqlite> select * from HISTORY where DEVICE='Pollenflug';


Alle Einträge eines Readings eines Geräts anzeigen:

sqlite> select * from HISTORY where DEVICE='Pollenflug' and READING='Graeser';


Alle Einträge eines bestimmten Wertes eines Readings eines Geräts anzeigen:

sqlite> select * from HISTORY where DEVICE='Pollenflug' and READING='Graeser' and VALUE>1;


LÖSCHEN aller Einträge eines bestimmten Wertes eines Readings eines Geräts anzeigen:

Achtung: Löschen kann nicht rückgängig gemacht werden!! Also IMMER erst die entsprechenden SELECT-Statements solange verfeinern bis wirklich nur die gewünschten Einträge angezeigt werden. Dann das select * durch delete ersetzen.

sqlite> delete from HISTORY where DEVICE='Pollenflug' and READING='Graeser' and VALUE>1;


Datenbank reparieren

Es kann immer wieder mal vorkommen, dass Datenbanken Fehler enthalten. Das muss im Alltag garnicht auffallen und auch nicht immer schlimm enden. Wenn man auf der SQL-Konsole aber bspw. eine Meldung Error: database disk image is malformed erhält, sollte man ein Reparatur vornehmen.

SQLite-Datenbanken

Die folgenden Schritte beschreiben, wie man eine SQLite-DB reparieren kann (Quelle: [1]):

  1. DB öffnen:
    sudo sqlite3 fhem.db
  2. Integritäts-Check durchführen:
    sqlite> pragma integrity_check;

    Kommt hier ein "ok" ist die DB gesund. Ansonsten erscheint etwas wie

    *** in database main ***
    On tree page 118786 cell 1: Rowid 75 out of order (previous was 816660)
    On tree page 118786 cell 4: Rowid 815704 out of order (previous was 816727)
    Corruption detected in cell 0 on page 118786
    Multiple uses for byte 132 of page 118786
    ...
    
  3. Datenbank-Dump erstellen (Export gesamten DB in die Datei "dump_all_20160516_1043.sql") und DB verlassen:
    sqlite> .mode insert
    sqlite> .output dump_all_20160516_1043.sql
    sqlite> .dump
    sqlite> .exit
    
  4. Neue Datenbank erstellen und den Dump einlesen, Integritäts-Check machen und verlassen:
    sudo sqlite3 fhem-neu.db
    sqlite> .read dump_all_20160516_1043.sql
    sqlite> pragma integrity_check;
    ok
    sqlite> .exit
    
  5. Spätestens jetzt FHEM stoppen:
    sudo service fhem stop
  6. Alte DB sichern und neue aktivieren:
    sudo mv fhem.db fhem.db.sv_20160516
    sudo mv fhem-neu.db fhem.db
    
  7. Kontrollieren, dass die neue DB die gleichen Rechte wie die alte DB hat (und ggf. korrigieren):
    ~/fhem$ ls -lha
    insgesamt 6,3G
    drwxr-xr-x 12 fhem root    4,0K Mai 16 11:07 .
    drwxr-xr-x  4 root root    4,0K Dez 25 17:50 ..
    ...
    -rw-r--r--  1 root root    1,4G Mai 16 11:04 fhem.db
    -rw-r--r--  1 fhem root    2,6G Mai 16 10:59 fhem.db.sv_20160516
    ...
    
    ~/fhem$ sudo chown fhem:root fhem.db
    
    ~/fhem$ ls -lha
    insgesamt 6,3G
    drwxr-xr-x 12 fhem root    4,0K Mai 16 11:07 .
    drwxr-xr-x  4 root root    4,0K Dez 25 17:50 ..
    ...
    -rw-r--r--  1 fhem root    1,4G Mai 16 11:04 fhem.db
    -rw-r--r--  1 fhem root    2,6G Mai 16 10:59 fhem.db.sv_20160516
    ...
    
  8. FHEM wieder starten (und natürlich kontrollieren):
    sudo service fhem start


Datenbank migrieren

Eine schöne Anleitung zur Migration von SQLite zu MySQL/MariaDB mit Hilfe von DbRep findet sich hier: [2].

Hinweis: Wenn die SQLite-DB sehr groß wird, kann es sein, dass der oben beschriebene Weg nicht funktioniert (konkret war dies bei meiner 15 GB großen DB nicht möglich, der Prozess hat sich immer nach mehreren Stunden aufgehängt).

Nützliche Codeschnipsel

Anbei ein paar nützliche Codeschnipsel rund um DbLog


Dateigrösse mitloggen

Da die Datenbank ins Unermessliche wachsen kann, empfiehlt es sich - je nach Speicherplatz - ab einer bestimmten Grösse tätig zu werden. Dazu muss diese Grösse allerdings ermittelt werden. Diese geschieht mittels des Userreadings, welches man vorteilshafterweise mit im DbLog-device anlegt:

attr myDbLog userReadings DbFileSize:reduceLogState.* { (split(' ',`du -m fhem.db`))[0] }

Mittels dieses Attributs wird die Grösse der .db-Datei immer nach dem Ausführen des ReduceLog in das Reading "DbFileSize" in ganzzahligen MByte abgelegt.

Basierend auf diesem Reading können dann weitere Aktionen, beispielsweise ein Plot, erstellt werden.

Die oben beschriebene Möglichkeit ist für SQLite verwendbar. Zur Ermittlung der DB-Größe andere DB-Typen (aber auch für SQLite nutzbar) kann wie hier beschrieben vorgegangen werden.


Performance-Optimierung

Auch eine Datenbank kann mit der Zeit langsamer werden. Dies hängt von mehreren Faktoren ab:

  • Menge der gelogten Daten (zB. > 4-5 GB)
  • Eingesetzte Hardware (zB. langsame SD-Karte vs. schnelle SSD)
  • Eingesetztes Datenbanksystem (zB. SQLite, MySQL)
  • Komplexität der Abfragen (zB. für aufwändige Graphen oder Berechnungen)

Diese Punkte sollen im folgenden diskutiert werden:


Komplexität der Abfragen

Dies ist kein Problem der Datenbank, sondern rein der Abfrage. Dem entsprechend muss die Optimierung auch in der Abfrage oder im Skript gesucht werden. Dies ist nicht Ziel dieses Abschnittes und wird hier nicht weiter behandelt.


Eingesetztes Datenbanksystem

Welches Datenbanksystem eingesetzt wird (zB. SQLite oder MySQL) hat auf die Performance der Datenbank gar keinen so großen Einfluss, wie vielleicht zuerst gedacht. Selbst SQLite kann problemlos Datenbanken mit etlichen GB Größe performant verarbeiten. Der Flaschenhals ist hier viel mehr die darunter liegende Hardware (s.u.).

Die Performance der Datenbank an sich, kann aber durch verschiedene Maßnahmen verbessert werden:

  • Pflegemaßnahmen bzgl. der Daten
  • Erstellung von Indizes


Menge der Daten und Pflegemaßnahmen bzgl. der Daten

Die Menge der geloggten Daten hat natürlich Einfluss auf die Geschwindigkeit von Abfragen - je mehr Daten vorhanden sind, desto mehr Daten müssen auch durchforstet werden um eine Abfrage zu bedienen. Die Reduzierung der geloggten Datenmenge hat also direkten Einfluss auf die Größe und damit auch die Geschwindigkeit der Datenbank. Die Menge der zu loggenden Daten lässt sich an zwei Stellen einschränken:

  • bei der Definition jedes Devices (s. Kapitel oben)
  • bei der Festlegung des fhem-weiten Log-Levels (s. Loglevel)

Die Menge der bereits geloggten Daten kann zB. mit Hilfe von DbRep verringert und optimiert werden, bspw.

  • löschen unnötiger Daten
  • vacuum der Datenbank

Insgesamt haben diese Maßnahmen aber nur einen eingeschränkten Effekt auf die Performance der DB. Deutlich effektiver ist die Erstellung eines Index (s.u.).


Erstellung von Indizes

Die Erstellung von Indizes hat mit Abstand den größten Einfluss auf die Performance einer Datenbank (auf unveränderter Hardware). Extrem zusammengefasst ist ein Index eine extrem optimiertes Nachschlageverzeichnis für einen bestimmten Typ Daten (ein Index wie im Buch halt). Eine wunderbare Einführung in Indizes bietet [[3]].

In fhem sind Indizes sogar sehr einfach einzurichten da die Datenbank-Nutzung sehr stark vorgegeben ist. Nahezu jede Abfrage folgt dem Schema Device -> Reading -> Datum -> Wert. Ein Index kann genau diese Abfrage bedienen und beschleunigen. Ein Index nur über die Devices wäre ein erster Schritt, brächte aber noch keinen großen Gewinn (wie um Link oben gut beschrieben). Über die gesamten ersten drei Schritte erstellt (Device -> Reading -> Datum) bringt der Index aber sofort eine deutliche Geschwindigkeitssteigerung.

Die Erstellung eines Index erfolgt direkt in der Datenbank (und nicht aus fhem heraus), hier am Beispiel einer SQLite-DB:

Öffnen der DB:

 sudo sqlite3 fhem.db 

Erzeugen des Index auf der DB-Konsole (das Semikolon am Ende ist wichtig):

 create index idx_device_reading_timestamp on history (device, reading, timestamp); 

Verlassen der DB:

 .exit 

Einzig zu berücksichtigen ist, dass dieser Index die Datenbank um bis zu 1/3 vergrößert. Er kann aber bei Bedarf auch wieder entfernt werden. Bei meiner 15 GB SQLite-Datei (auf einem Mac Mini mit SSD) hat dies ca. 15 min gedauert (den fhem hatte ich vorsichtshalber währenddessen deaktiviert).

Sollte jemand spezielle Berechnungen oder Skripte ausführen, die nach einem anderen Abfrage-Schema arbeiten, könnte man dafür spezialisierte zusätzliche Indizes erstellen. Das sollte aber dann mit dem Wissen des obigen Links erarbeitet werden, da dann etwas mehr Hintergrundwissen sehr hilfreich ist.


DB-Backup

Ein anderer Aspekt, der eigentlich nichts mit der Performance der DB zu tun hat, ist der Einfluss aufs Backup. Wird bspw. ein Systembackup per RSYNC gemacht, muss bei SQLite immer die komplette ggf. riesige Datei gesynct werden - bei MySQL würden nur die veränderten DB-Elemente gesynct. Dies soll hier nicht weiter vertieft werden, sollte aber bei einer Gesamtstrategie bedacht werden.


Eingesetzte Hardware

fhem hat grundsätzlich sehr viele kleine Datenzugriffe, unabhängig davon ob FileLog oder DbLog eingesetzt wird. Deshalb ist der absolut größte Performance-Gewinn durch den Einsatz schneller Festplatten zu erreichen - ganz einfache Aussage: je schneller desto besser ;-). Konkret bietet eine SSD mit mind. 250MB/s Datenzugriff eine ordentliche Basis für jedes datengestützte System, wie den fhem.

Wenn die Datenmenge größer und die Abfragen komplexer werden, müssen natürlich irgendwann auch die Prozessorleistung und der Arbeitsspeicher mit wachsen. Aber auch an einem Raspi wird eine SSD deutlich performanter sein, als eine einfache SD-Karte oder eine klassische rotierende Festplatte.


Links