93_DbLog - Überlegungen zur Optimierung (RPI und andere Systeme)

[stromer-12]

Oder Bitte um nähere Details!

Ich hatte die komplette Datenbank auf den Primärindex umgestellt mittels umkopieren.
Getestet mit der normalen DbLog Version mit eingefügten IGNORE.
Plots ganz normal für die letzten 24Stunden.
Selbst ganz einfache Plots mit der Syntax: #myDbLog <Device>:<Reading>
wurden langsamer dargestellt.
Ich hatte sogar plotfork abgeschaltet, damit sich die Plots nicht gegenseitig beinflussen.

[ToKa]

Hallo Joe,

ich nutze die DB-Daten vorwiegend für Plots. Insofern habe ich keine anderen, speziellen Anforderungen. Meine mysql DB läuft zudem nicht auf dem FHEM RPi, sondern auf einem anderen Server unter Ubuntu. Das Schreiben und Lesen der Daten über's Netz funktioniert prima und durch das Puffern der Daten spielt es auch keine Rollen, wenn ich den DB-Server mal neustarte.

Gruß
Torsten

[ioT4db]

Und welchen Index hast Du aktuell auf der Tabelle?
...
Würdest Du sagen, dass deine Abfrage schon dazugehört, denn automatisch (also vom Modul) wird diese, so denke ich, so niemals an die Datenbank abgesetzt.
...

Hallo Joe, komme erst jetzt zum antworten. sorry...

Genau, ich verwende den Standard-Index (ohne PK), wie in Deinem Testscript:

Code Auswählen Erweitern

INDEX `DbLogDefault` (`DEVICE`, `READING`, `TIMESTAMP`)

Die Abfrage ohne "where" ist die Standardeinstellung in phpmyAdmin, wenn ich auf die Tabelle klicke. Leider hab ich noch keinen Weg gefunden, wie ich das anpassen kann. Ich klick einfach nicht mehr drauf!
Diese Abfrage gehört aber keinesfalls zu den Standardabfragen von Fhem würde ich sagen! Wir können Sie, m.M.n. außen vor lassen!
Hauptsächlich erstelle ich Plots, das ist bei mir der Hauptanwendungsfall. Direkt in der DB über phpmyAdmin bewege ich mich nur im Problem- bzw. Fehlerfall. Dafür habe ich aber eher auch solche select-Abfragen, wie Du Sie einen Beitrag weiter unten beschreibst:

Code Auswählen Erweitern

SELECT * FROM `history` where device='Wohnzimmer' and reading='measured-temp' ORDER BY `TIMESTAMP` DESC limit 10;

Mit dieser "verfeinerten" Abfrage läufts auch sehr performant!

Ich würde mal probieren den Index auf 

Code Auswählen Erweitern

INDEX `DbLogDefault` (`TIMESTAMP`, `READING`, `DEVICE`) umzustellen. Klingt logisch, dass dieser für Plots besser ist!
Meinst Du ich soll dafür eine neue Tabelle machen und die Werte umkopieren? Oder gibt es eine Möglichkeit einen alten Index zu löschen und einen neuen aufzubauen? Mehrere Index in der DB zu haben finde ich nicht so gut, deshalb lieber einen neuen erzeugen und den alten entfernen.

Ich möchte noch 2 weiter Punkte in den Raum werfen:
1. Wiki: so ein Thread ist ja schön, aber am Anfang ist es gerade für "Neulinge" zu denen ich mich auch noch zähle, wichtig einen Platz zu haben an dem die aktuellen Erkenntnisse bzw. Vorgehensweisen in Verbindung mit dem aktuellen Versionsumfang des DbLog-Moduls dokumentiert sind. Vielleicht könnte man da am Ende, wenn klar ist welche Index, in welcher Kombi mit oder ohne PK usw. für den "Standard"-User bzw. den "Advanced"-User die Richtige ist. Auch eine kurze Erklärung bzgl. DB-Typen (z.B. Aria vs. innoDB) oder wozu ist ein Index oder PK gut, wäre da gut aufgehoben meine ich. Ich weiß, alles viel Arbeit, aber ich wollts mal ansprechen, denn ich habe so langsam auch meine Mühe den tlws. sehr fachlichen Diskussionen und Ideen gänzlich zu folgen...
2. Event-Logging: coole Idee, über colEvent=0 die Events nicht mitzuloggen! Ich bin kurz davor auch auf die Events zu verzichten und meine DB dementsprechend auch rückwirkend zu leeren. Aber bevor ich das tue, wollte ich fragen, ob Ihr einen Anwendungsfall habt, wo Ihr das Event in der DB braucht?

so, wieder viel zu viel Text 
VG...

[ghayne]

Hi Garry,

Deine message kann ich nicht einordnen. Ich vermute es handelt sich um deine Messungen am Energiezähler an sich, d.h. die gemessenen Werte sind zu hoch.

best regards,
Heiko

Ich habe Gestern alles auf ein pi3 mit Dietpi(minimal Jessie installation) und Mysql umgebaut. Ich vermute das die 400kw readings waren einfach timing Problemen mit der alte Pi1.
Es sieht viel besser aus obwohl ich weiss das eigentlich fhem kein Echtzeit system ist.

Regards, Garry

[JoeALLb]

hallo!

Ich würde mal probieren den Index auf 

Code Auswählen Erweitern

INDEX `DbLogDefault` (`TIMESTAMP`, `READING`, `DEVICE`) umzustellen. Klingt logisch, dass dieser für Plots besser ist!

Wenn Du es nicht eilig hast, warte noch etwas ab. Jemand meinte, dass ein anderer besser funktionieren könnte und ich bau mir gerade eine recht große
testumgebung auf, um das noch etwas detailierter testet zu können! Ansonsten kannst Du das gerne machen!

Meinst Du ich soll dafür eine neue Tabelle machen und die Werte umkopieren?
Oder gibt es eine Möglichkeit einen alten Index zu löschen und einen neuen aufzubauen?

Umkopieren, oder du nutzt async=1 und stellst den wert syncInterval entsprechend hoch ...
Solch eine Möglichkeit kenne ich bei Mysql etc. nicht.

Mehrere Index in der DB zu haben finde ich nicht so gut, deshalb lieber einen neuen erzeugen und den alten entfernen.

Wäre aber für Tabellen prinzipiell nichts ungewöhnliches...

Zu Wiki: Wir suchen Helfer, die uns 1.) beim code schreiben und 2.) auch bei der Doku im Wiki helfen. Ich kann das leide rnicht übernehmen, könnte lediglich "korrekturlesen" ...
Zu: Event-Logging: Ich nutze es nur im Debug-Fall, ansonsten kenne ich keinen Nutzen davon. Webradios schreiben dort beispielsweise den gespielte Lieder rein, das benötige ich aber nicht!


schöne Grüße

Joe

[ioT4db]

hallo!
Wenn Du es nicht eilig hast, warte noch etwas ab.
...

ok, dann warte ich erstmal ab.

Wäre aber für Tabellen prinzipiell nichts ungewöhnliches...

wie wird denn der richtige Index in der Abfrage berücksichtigt, wenn es mehrere gibt?

Zu Wiki: Wir suchen Helfer, die uns 1.) beim code schreiben und 2.) auch bei der Doku im Wiki helfen. Ich kann das leide rnicht übernehmen, könnte lediglich "korrekturlesen" ...
...

ich helfe gern. dazu muss ich es aber erst einmal richtig kapieren (siehe meine Frage weiter oben). wir können ja Themen (Überschriften) festlegen. Ich schreib was dazu und die "Spezialisten" lesen gegen.
Nur grundsätzlich müsste man vorher abklären wer beim Wiki die Hand drauf hat und die Arbeit verteilt...

Zu: Event-Logging: Ich nutze es nur im Debug-Fall, ansonsten kenne ich keinen Nutzen davon. Webradios schreiben dort beispielsweise den gespielte Lieder rein, das benötige ich aber nicht!
...

ok, ich glaub dann entferne ich die Events bei Gelegenheit...

VG

[DS_Starter]

Hallo friesenjung, hi Joe,

Oder gibt es eine Möglichkeit einen alten Index zu löschen und einen neuen aufzubauen?

Mit phpMyAdmin kannst du Indexe bequem löschen und neue aufbauen, selbsverständlich auch mehrere.

wie wird denn der richtige Index in der Abfrage berücksichtigt, wenn es mehrere gibt?

Diese Entscheidung übernimmt der Optimizer. Er ist Bestandteil des Datenbankmanagement-Systems. Im Detail habe ich mich auch noch nicht beschäftigt, aber hier ist ein Einstieg in das Thema zu finden: http://use-the-index-luke.com/de/sql/anatomie/langsame-indizes

Ein kleines Beispiel macht das deutlcih. Habe nun zusätzlich zum PK auch noch den Search-Index wieder angelegt. Die Tabelle hat also diese zwei Indizes:

Code Auswählen Erweitern


ALTER IGNORE TABLE `history` ADD PRIMARY KEY(TIMESTAMP, DEVICE, READING);
CREATE INDEX Search_Idx ON `history` (DEVICE, READING, TIMESTAMP);


Dann kann habe ich in phpMyAdmin dieses Statement ausgeführt.

Code Auswählen Erweitern


SELECT * FROM `history` WHERE TIMESTAMP='2017-02-06 20:53:19' AND DEVICE='SMA_Energymeter' AND READING='state'


Welcher Index benutzt wird, sieht man wenn man den Link "SQL erklären" anklickt. In diesem Fall ist es der PK (SQL1 im Anhang).
Nach der Ausführung von:

Code Auswählen Erweitern


SELECT * FROM `history` WHERE DEVICE='SMA_Energymeter' AND READING='state' AND TIMESTAMP BETWEEN '2016-02-06 20:53:19' AND  2017-02-06 20:53:19'


zeigt "SQL erklären" jedoch dass diesmal der Search_Index verwendet wurde.

@Joe, es wird sinnvoll sein den Search_Index zusätzlich zum PK zu behalten da gerade Plots diese Time-range selects verwenden werden.

Grüße,
Heiko

[JoeALLb]

Hallo Heiko,

ich seh mir das gerade genau an.  Prinzipiell kann der PK auch  Timeranges,  er filtert aber bei großen gemischten Datenmengen im ersten Step weniger heraus...

SG
Joe

[DS_Starter]

Hallo miteinander,

in der angehängten 93_DbLog_V2.12.2_Test ist nun auch der PK-Support für SQLIte implemnetiert.
Leider kann man (soviel ich weiß) auf SQLite nicht so einfach einen PK hinzufügen wie auf MySQL. Ich habe eine neue DB erstellt und den PK gleich beim Tabellencreate mit angegeben.
Auch hier ist eine deutliche Beschleunigung von DbRep-Abfragen/Auswertungen feststellbar.

Jetzt fehlt noch der Support für PostGreSQL. Dabei brauche ich eure Unterstützung. Die entsprechenden Befehle kann ich mir vllt. noch ausdenken, nur habe ich dafür keine Testumgebung.

@Joe, hast du in deinem Labor auch eine Postgre mit aufgebaut ?

Wer könnte mich diesbzgl. noch unterstützen .... Freiwillige bitte vortreten 

EDIT: Joe, ich habe die Update-Statements nochmal gändert. Die waren nicht optimal weil ungenügende Unterstützung für update/insert in einem Schreitt. Jetzt ist es besser.

schönen Tag und VG
Heiko

[JoeALLb]

Hallo Heiko,

@Joe, hast du in deinem Labor auch eine Postgre mit aufgebaut ?

Im Moment leider nein, aus Festplattenplatz-Gründen. Meine Testumgebung ist aktuell 120GB groß, mehr SSD-Platz habe ich auf dem Testserver nicht.
Sobald ich ein paar "exotische" Testvarianten aussondieren und somit löschen kann, werde ich PostGreSQL mit dazu aufnehmen.

[JoeALLb]

Ich sehe schon ... du hast viele Ideen    Vielleicht hilft noch mal jemand mit das umzusetzen sonst wird das einen never ending story für mich (wollte ja eigentlich nur das Logging non-blocking gestalten)   

Vielleicht haben ja Benni und Dan lust, an der weiteren Optimierung mitzuwirken?

Gruß
Dan

Gruß Benni.

[DeeSPe]

Vielleicht haben ja Benni und Dan lust, an der weiteren Optimierung mitzuwirken?

Generell habe ich nichts gegen "Mitwirken"!

Hab noch 2-3 andere Baustellen, für die gerade wenig Zeit ist.
Bin auch bisher, bis auf die Funktion DbLog_ExecSQL und eigene Nutzung von DbLog, noch NULL mit dem Modul vertraut!

Wenn sich die Programmierarbeit in kleinere Tasks zerlegen lässt bin ich gerne bereit (Teil)Funktionen beizusteuern.

Gruß
Dan

[DS_Starter]

Nabend miteinander,

es ist bestimmt nicht einfach im gegenwärtigen Fluß Einzelaufgaben zu verteilen. Momentan habe ich selbst noch verschiedene Releasestände zu verwalten die hier im Test sind und erst nach und nach eingecheckt werden. Ist mühsam und muß aufpassen nicht durcheinder zu kommen.

Hilfe ist natürlich immer gern willkommen  . Manchmal reicht es schon wenn ein anderer mal über ein Problem schaut weil man selbst feststeckt und den Wald vor lauter Bäumen nicht sieht.

In DbLog schreibe ich auf jeden Fall immer die Commandref weiter. Aber, wie schon festgestellt wurde, wäre es auch wichtig die Features im Wiki zu beschreiben damit die Nutzer nachlesen können was man mit dem asynchronen Mode anstellen kann, welche Vor- und Nachteile er hat und wie es geht.
Wenn sich jemand darum kümmern könnte, wäre das schon eine ungemeine Bereicherung und Unterstützung !

So wie Dan geht es mir auch ... habe noch zwei eigene Module die ich zu Gunsten von DbLog sträflich vernachlässige ... ich hoffe die User von DbRep und SSCam bzw. SMAInverter (kommissarisch) sehen es mir nach ..

Die nächsten direkten Ziele sind:
* reduceLog non-blocking gestalten. Finde ich sehr wichtig weil es FHEM stark beeinträchtigt wenn es genutzt wird
* PK Support für Postgre (schon erwähnt)

Dan, könnte mir vorstellen dass du, wenn du magst, dir die reducelog-Funktion vornehmen könntest um sie auf Blockingcall umzustellen oder mich dabei zu unterstützen.

Grüße
Heiko

[DeeSPe]

Dan, könnte mir vorstellen dass du, wenn du magst, dir die reducelog-Funktion vornehmen könntest um sie auf Blockingcall umzustellen oder mich dabei zu unterstützen.

Daran habe ich auch als Erstes gedacht! Wo ich doch gerade warm geworden bin mit BlockingCall!

Ich schaue mir das mal in den kommenden Tagen an! Evtl. kann ich ja dabei meine SQL Query Kenntnisse mal wieder etwas auffrischen. Versprechen will ich aber so schnell nicht die Top-Lösung.
Kann ich diesbezüglich auf der aktuellen in SVN verfügbaren Modul Version aufbauen? Gab's da überhaupt irgendwelche Änderungen dran von Euch?

Gruß
Dan

[DS_Starter]

Wo ich doch gerade warm geworden bin mit BlockingCall!

Genau    Kein Stress, mache erstmal und ich gucke dann auch noch drüber. Ideal wäre vllt. die non-blocking reducelog parallel zum bisherigen reducelog einzubauen um Vergleichsläufe machen zu können.

Als Grundlage nimm am Besten das hier angehängte Modul V2.12.3.
Hier habe ich heute Abend noch ein "set ... clearReadings" eingebaut damit man auch mal Readings wieder los wird die von verschiedenen Funktionen geschrieben werden. In das SVN will ich die Tage die V 2.11.4 einchecken, die schon gut ausgetestet ist.

@all, ihr könnt gern die clearReadings Funktion mit dem angehängten Modul bei euch probieren.

Grüße
Heiko