plotfork und dblog

[Afterburner]

Ich schließe mich hier mal mit an da ich nachdem ich heute auf meinem PI2 auf Plotfork umgestellt habe wegen langsamer Ploterstellung
http://forum.fhem.de/index.php/topic,45296.msg375358.html#msg375358

Seit dem Zeitpunkt der Umstellung wird nichts mehr geloggt, habe dann FHEM neu gestartet und es wurde wie es ausschaut ein Datensatz geloggt und danach dann wieder nichts mehr.

Den Bugfix von hier einzuspielen wird wohl nach über einem Jahr nichts bringen zumal sich die 93_DbLog ja weiterentwickelt hat und ich auch vermute das der Bugfix schon eingearbeitet wurde.

[Afterburner]

Ich bin jetzt vom PI2 auf einen Intel NUC (4 x 1,6 GHz mit 8 GB RAM und SSD) umgezogen und Plotfork funktioniert auch dort auf dem frisch aufgesetzten Debian (stable) nicht

[marvin78]

Plotfork hat bei mir nie richtig funktioniert (MySQL). Ich habe schnell aufgehört, mich damit zu beschäftigen. Das bringt nur Frust. Mit der Maschine (NUC) wirst du das allerdings auch nicht benötigen. Der sollte die Plots so schnell raus hauen, dass du gar nicht so schnell schauen kannst.

Wenn das nicht der Fall sein sollte, loggst du deutlich zu viel und dann würde ich an dieser Stelle ansetzen (event-on-change-reading, DbLogExclude, Regex in der DEF von DbLog etc.)).

[dev0]

29 Readings auf einer Seite mit Werten im Minutentakt (siehe oben) sind ~15 Millionen Datensätze bei der Jahresansicht, wenn ich mich nicht verrechnet habe. Da wird auch einen NUC ins schwitzen kommen

[marvin78]

Ich sage ja, dann wird zu viel geloggt bzw. man müsste ausdünnen. Ich frage mich, von welchen Daten man Werte im Minutentakt benötigt und das auch noch über mehr als ein Jahr hinweg!? Plotfork wird nicht die Lösung sein. Der Tag an dem das funktioniert ist noch in keinem Kalender

[JoeALLb]

29 Readings auf einer Seite mit Werten im Minutentakt (siehe oben) sind ~15 Millionen Datensätze bei der Jahresansicht, wenn ich mich nicht verrechnet habe. Da wird auch einen NUC ins schwitzen kommen

Man kann auch viel an MySQL drehen Ich arbeite beispielsweise gerade mit Partitionen und einem anderen Index.

[Afterburner]

[OFFTOPIC]

Im Gegensatz zum PI gehts jetzt mit dem NUC (um 18:00 Uhr) auch ohne plotfork,

4 Readings in einem SVG im Minutentakt in 2 Sekunden

10 SVGs mit insgesamt
4 x 5 Readings
1 x 2 Readings
3 x 1 Readings

also 25 Readings im Minutenabstand  in 15 Sekunden
Load Average beim NUC während der Erstellung der 25 Readings war 0,30

DbLogExclude ist schon drin (sollte aber beim Auslesen der Daten egal sein da darauf ja kein Select gemacht wird)
event-on-change-reading kommt noch rein, muss mich da noch einlesen wie das geht das zumindest im Plot immer der letzte Wert angezeigt wird auch wenn er nicht geloggt wurde

@JoeALLb
welchen Index ? Ich habe einen Index auf TIMESTAMP, DEVICE und READING
ansonsten immer her mit Deinen Infos

[/OFFTOPIC]

ansonsten wäre es schön wenn es trotzdem mit plotfork funktionieren würde ^^

[marvin78]

DbLogExclude ist schon drin (sollte aber beim Auslesen der Daten egal sein da darauf ja kein Select gemacht wird)
event-on-change-reading kommt noch rein, muss mich da noch einlesen wie das geht das zumindest im Plot immer der letzte Wert angezeigt wird auch wenn er nicht geloggt wurde

Weniger Daten in der DB -> schnellere Plots. Das kann man einsehen, muss man aber nicht. Und hierbei ist es nicht (nur) entscheidend, ob die entsprechenden Daten auch im Log autauchen. Es muss ja die komplette DB (oder der Index) durchsucht werden.

Es kann auch helfen, mehrere DBLog Instanzen (verschiedene DBs) zu verwenden. Ich logge beispielsweise Daten, die ich längerristig benötige (Temperatur, Energie) in eine andere DB als die die ich nach 35 Tagen weg werfen kann, weil sie danach uninteressant werden.

Jedes einzelne Event zu loggen, macht weder Sinn, noch ist es der Performance zuträglich (sowohl beim loggen, als auch beim Anzeigen).

[Afterburner]

Also beim SELECT stimme ich Dir allgemein gesehen nur bedingt zu

Einfach mal nen Select über 4 Spalten aus der DB meines Forums (3 GB) so das möglichst viele Einträge erscheinen

SELECT * FROM `wbb42_1_post` WHERE `parentPostID` = 0 AND `userID` >= 0 AND `message` != 'sfhsajfhsajfhsahfa' AND `time` >= 0
Zeige Datensätze 0 - 24 (3211818 insgesamt, Die Abfrage dauerte 0.0012 Sekunden.)

Ich glaube die 0.0012 Sekunden sind da zu vernachlässigen, die dann aufzuarbeiten ist natürlich ein anderes Thema genau wie die Größe der DB die mit mehr (unnützen) Daten immer weiter anschwillt aber hier ging es ja nur im den Select


In FHEM (122 MB) sieht es etwas anders aus
SELECT * FROM `history` WHERE `TIMESTAMP` > '2015-12-17 00:00:00' AND `DEVICE` = 'Heizung.Badezimmer' AND `READING` = 'temperature' ORDER BY `TIMESTAMP` DESC
Zeige Datensätze 0 - 24 (984 insgesamt, Die Abfrage dauerte 0.3415 Sekunden.)

Ist da halt schon um einiges langsamer, warum habe ich noch nicht raus gefunden, eine fehlender PRIMARY Key spielt da sicherlich auch irgendwie eine Rolle, auch den TIMESTAMP als unixtimestamp zu speichern könnte meiner Meinung nach die Abfragen ein wenig beschleunigen. (auf jeden Fall wird die Datenbank minimal kleiner )

Dem Device könnte man auch statt dem Namen eine Nummer geben für den DB Einträge, evtl auch dem TYPE sofern darauf eine Abfrage gemacht wird (der hat bei mir nämlich noch keinen Index bekommen)

Heizung.Badezimmer = 12
SELECT * FROM `history` WHERE `TIMESTAMP` > '1450306800' AND `DEVICE` = '12' AND `READING` = 'temperature' ORDER BY `TIMESTAMP` ASC
wäre vermutlich etwas schneller

Das Problem ist das dies mit dem flexiblen Aufbau von FHEM nicht so ganz funktionieren würde, aber wobei wenn ich den Device Namen in der FHEM Config ändere wird die Datenbank ja auch nicht geändert.

Theoretisch könnte man das Ganze auch automatisieren mit einer "Device Config DB" beim Start von FHEM oder beim hinzufügen von Geräten

SELECT device_id from DEVICES WHERE device_name = 'Heizung.Badezimmer'
if ($result['device_id']){
attr Heizung.Badezimmer devid $result['device_id']
}else{
INSERT INTO DEVICES .....
attr Heizung.Badezimmer devid mysql_insert_id();
}

ob es allerdings was bringt ist schwer zu sagen

[marvin78]

DbLog ist alles andere als perfekt. Wir könnten jetzt hier über Datenbankstrukturen diskutieren, ändern damit aber nichts daran, dass DbLog so ist, wie es ist. In MySql kann man noch einiges selbst machen (partitionen und ein wenig an den Indizes schrauben kann hier nicht schaden). Es bleibt aber dabei: Es ist sonnlos, jedes Event zu loggen (gerade bei HM-Devices).

Ich habe etwa 300 Devices die Log-Daten produzieren (in verschiedene DBs) und eine meiner DBs hat mehrere Gigabyte an Daten. Es sind jeweils nur die Events darin geloggt, die ich auch benötige. Mein NUC lacht darüber beim anzeigen von 30 Plots auf einer Seite

[Afterburner]

Aber es ist zumindest interessant sich darüber zu unterhalten ^^

Ich habe gerade mal ein EXPLAIN gemacht auf

Code Auswählen Erweitern


EXPLAIN SELECT DATE_FORMAT(TIMESTAMP, '%Y-%m-%d %H:%i:%s'), DEVICE, READING, VALUE FROM history WHERE 1=1 AND DEVICE = 'Heizung.Schlafzimmer' AND READING = 'temperature' AND TIMESTAMP >= STR_TO_DATE('2015-12-15 09:00:00', '%Y-%m-%d %H:%i:%s') AND TIMESTAMP < STR_TO_DATE('2015-12-16 09:00:01', '%Y-%m-%d %H:%i:%s') ORDER BY TIMESTAMP
was wohl das Format der Abfragen in FHEM sein soll

Code Auswählen Erweitern


id select_type table   type         possible_keys                         key                         key_len ref rows Extra
1 SIMPLE         history index_merge DEVICE,TIMESTAMP,READING READING,DEVICE 34,52 NULL 11353 Using intersect(READING,DEVICE); Using where; Using filesort

was mich jetzt wundert ist das TIMESTAMP nicht genutzt wird obwohl es die größte Kardinalität hat, in dem Fall könnte ich den Index zu TIMESTAMP ja theoretisch wieder löschen

[marvin78]

Nun. Eigentlich erscheinen deine Indizes nicht so, wie vorgeschlagen. Es sollte eigentlich einen kombinierten Index "Search_idx" über die 3 Spalten TIMESTAMP, DEVICE und EVENT geben.

[Afterburner]

Also ich habe bisher nur 3 einzelne Indexe angelegt da das ja aber MySQL 5 wohl egal sein soll oder habe ich da was falsch verstanden ?
Wenn ein Index über 3 dann sollte die Reihenfolge dann nicht
DEVICE_READING_TIMESTAMP
sein ?

Ich habe gerade das Query mal umgedreht und TIMESTAMP an den Anfang direkt hinter WHERE verschoben ?
aber trotzdem nutzt MySQL nur die Indexe READING,DEVICE

[marvin78]

Du hast Recht. Ich habe oben nicht die richtige Reihenfolge aufgeschrieben. Mir ging es nur um den kombinierten Index. Ich habe mich auch boß gewundert, es sollte tatsächlich bei MySQl 5 egal sein. Hier kann man mal ein wenig spielen und messen.

[Afterburner]

Also es scheint in MySQL wohl doch nicht egal zu sein, habe gerade einen Index über die 3 Spalten erstellt und siehe da
Zeige Datensätze 0 - 24 (1288 insgesamt, Die Abfrage dauerte 0.0052 Sekunden.)

Auf jeden Fall eine Steigerung um mehrere 100 Prozent

Also hier die Anleitung wie es geht (in PhpMyAdmin)

1. In die Tabelle "history" gehen
2. dort auf "Struktur" klicken
3. weiter unten auf "+ Indizes" klicken
4. bei Index über: 3 auswählen
5. auf OK klicken
6. Bei Indexname: DEVICE_READING_TIMESTAMP eintragen
7. Bei Indextyp: INDEX auswählen
8. Die Spalten in folgender Reihenfolge auswählen
- DEVICE
- READING
- TIMESTAMP
9. auf OK klicken und sich freuen ^^