Autor Thema: 93_DbLog - Überlegungen zur Optimierung (RPI und andere Systeme)  (Gelesen 5425 mal)

Offline JoeALLb

  • Sr. Member
  • ****
  • Beiträge: 939
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #15 am: 29 Januar 2017, 21:50:37 »
> rein theoretisch der gleiche Ablauf wie bei der history-Tabelle denk ich!?

Ja! Oder direkt ohne Zwischenkopie, da diese Tabelle so klein ist sollte das auch gehen (alter table...)

Eine Verständnisfrage dazu: Der Code erfasst nur neue Einträge und wirkt nicht "rückwärts" oder?
korrekt, diese müsste man einmal manuell korrigieren.


So und nun noch eine neue Frage:
Also ich habe nun alles wieder so wie es vorher war und alle Datensätze sind in der "history", nur ist die history-Tabelle "Aria" statt "InnoDB"! So weit so gut.
Nun lasse ich mir die (neue) history (ca. 5Mio. Datensätze) anzeigen, so dauert das ca. 15 Sekunden!!!??? Das komische ist, dass die gleiche Tabelle, als sie noch historyWORK hieß das gleiche in 0.003 Sekunden erledigt hatte. Irgendwas ist doch da noch faul!?
Selbst die alte historyOLD (InnoDB) liegt da bei 0.004Sek!
Wie sieht es aus, wenn du die Abfrage mehrmals ausführst? Klingt nach einem simplen Cache-Problem,wo die Abfrage bereits im Zwischenspeicher war zur schnelleren Beantwortung.
Ich vermute dass noch viel des vorhandenen Speichers für die anderen Tabellen genutzt wird.

sG und gute Nacht,
Joe
FHEM-Server auf IntelAtom+Debian (12 Watt),
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline friesenjung

  • Full Member
  • ***
  • Beiträge: 118
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #16 am: 29 Januar 2017, 23:08:51 »
Wie sieht es aus, wenn du die Abfrage mehrmals ausführst? Klingt nach einem simplen Cache-Problem,wo die Abfrage bereits im Zwischenspeicher war zur schnelleren Beantwortung.
Ich vermute dass noch viel des vorhandenen Speichers für die anderen Tabellen genutzt wird.

Auch wenn ich es kurz nacheinander wiederhole ist keine Änderung zu sehen. Ganz selten, ich glaube 2 mal war das, wurde die Ansicht in 0.04sek geladen. Bei den übrigen 20 Versuchen geht es immer nur langsam (also die 16Sekunden).  :(

Interessanterweise ist die Count-Abfrage genauso schnell wie vorher, also 36 Sekunden.  :o

Bzgl. der Idee mit dem Cache. Wo soll ich da ansetzen. Unter PHPmyAdmin gibts z.B. "Leeren des Tabellenzwischenspeichers (FLUSH)", "Optimiere Tabelle" oder "Repariere Tabelle".

Ich habe mal die Cache-Variablen ausgelesen. Das Ergebnis ist im Anhang. Auffällig ist, dass die Cache_size 0 ist.

VG
FHEM auf Raspi 2, TCM-EnOcean, Jeelink-Clone 1x für PCA301 und 1x für Lacrosse, THZ304, Pushover, CUL_HM

Offline JoeALLb

  • Sr. Member
  • ****
  • Beiträge: 939
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #17 am: 30 Januar 2017, 09:51:38 »
Wie sieht denn dein SQL-Selectbefehl aus, den Du für diese Aktion nutzt?

Nun lasse ich mir die (neue) history (ca. 5Mio. Datensätze) anzeigen, so dauert das ca. 15 Sekunden!!!??? Das komische ist, dass die gleiche Tabelle, als sie noch historyWORK hieß das gleiche in 0.003 Sekunden erledigt hatte. Irgendwas ist doch da noch faul!?
FHEM-Server auf IntelAtom+Debian (12 Watt),
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline friesenjung

  • Full Member
  • ***
  • Beiträge: 118
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #18 am: 30 Januar 2017, 10:02:53 »
Wie sieht denn dein SQL-Selectbefehl aus, den Du für diese Aktion nutzt?

SELECT * FROM `history` ORDER BY `TIMESTAMP` DESC
Update: ohne die Sortierung gehts schnell
« Letzte Änderung: 30 Januar 2017, 10:05:12 von friesenjung »
FHEM auf Raspi 2, TCM-EnOcean, Jeelink-Clone 1x für PCA301 und 1x für Lacrosse, THZ304, Pushover, CUL_HM

Offline JoeALLb

  • Sr. Member
  • ****
  • Beiträge: 939
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #19 am: 30 Januar 2017, 12:09:02 »
Und welchen Index hast Du aktuell auf der Tabelle?
Einfach den Standard-Key von DbLog (der auch in meinem Testscript unverändert, bis auf das PK-Argument enthalten war)?

Deine Abfrage kann den Index nicht nutzen, da Du im WHERE-Abschnitt (den es nicht gibt) keine Devices und keine Reading angegeben hast.
Er macht somit immer einen "Full Table scan" und dieser ist zu groß für den Speicher. Daher dauert dies auch immer gleich lang.
Ich bezweifle jedoch, dass genau diese Abfrage bei der historyWORK ein anderes Verhalten gezeigt hat.

Ich würde gerne einen anderen Index testen, bin bisher jedoch noch nicht dazugekommen.

Ich denke, einer dieser Index könnte für alles ein besseres Ergebnis liefern, zumal in fast allen
Mysql-Abfragem an die DB (zB aus Plots) das Timestamp mit abgefragt wird.

Die aktuell schnellste Indexkombination ist:
ALTER IGNORE TABLE `history` ADD PRIMARY KEY(TIMESTAMP, DEVICE, READING);
CREATE INDEX Search_Idx ON `history` (DEVICE, READING, TIMESTAMP);
# Hinweis: Am "optimalen" Index wird aktuell noch auf Testsystemen experimentiert. Dieser kann sich je nach Erkenntnis nochmal verändern.


Um die Indexfrage optimal entscheiden zu können, benötigen wir auch eine Liste an "Häufig benötigte SELECT-Abfragen AN DIE db".
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.


@DS_Starter: Heiko,  warum ist der INdex von DbLog ohne Devices? Werden hier hauptsächlich Readings ohne Devices abgefragt? Ich würde die zwei separaten Indexe gerne in einem zusammenfassen...


Edit: Habe den Index durch den zweiten Index ergänzt, da dies im Moment die performanteste Kombination ist. GGF. kann sich diese rnochmal ändern.
« Letzte Änderung: 07 Februar 2017, 09:14:31 von JoeALLb »
FHEM-Server auf IntelAtom+Debian (12 Watt),
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline JoeALLb

  • Sr. Member
  • ****
  • Beiträge: 939
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #20 am: 30 Januar 2017, 12:17:08 »
Nachtrag:

Im übrigen würde dieser SQL ja sämtliche (ALLE!) Daensätze zurückgeben,
ich vermute also, dass dein Querybrowser da noch ein "limit 100" oder ähnlich ergänzt.

Nachtrag2:
Wenn Du die Abfrage mit device und reading ergänzt, so in etwa wie hier, kann der Index wieder genutzt werden und es sollte wieder schneller sein.

SELECT * FROM `history` where device='Wohnzimmer' and reading='measured-temp' ORDER BY `TIMESTAMP` DESC limit 10;
« Letzte Änderung: 30 Januar 2017, 13:13:08 von JoeALLb »
FHEM-Server auf IntelAtom+Debian (12 Watt),
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline Wuppi68

  • Developer
  • Hero Member
  • ****
  • Beiträge: 1203
  • Wuppertaler Wimpelbeauftragter
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #21 am: 30 Januar 2017, 12:22:41 »
ich hätte da auch noch eine Idee :-)

bei my/Maria SQL

erzeugung der Current Tabelle/View via stored procedure (asynchron via Trigger auf den Insert?)

Jetzt auf nem I3 und primär Homematic

kein Support für cfg Editierer

Support heißt nicht wenn die Frau zu Ihrem Mann sagt: Geh mal bitte zum Frauenarzt, ich habe Bauchschmerzen

Offline JoeALLb

  • Sr. Member
  • ****
  • Beiträge: 939
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #22 am: 30 Januar 2017, 13:45:54 »
erzeugung der Current Tabelle/View via stored procedure (asynchron via Trigger auf den Insert?)
Hallo Wuppi,

Vielen Dank für deinen Input! Ideen sind immer sehr willkommen ;-).

Die Idee mit  stored procedures sollte jedoch ein recht großer overhead für viele Inserts sein und auf sqlite sind diese nicht wirklich eine gute Lösung.

insert delayed ignore into current ...würde eigentlich das selbe erreichen, jedoch mit weniger overhead. Der Nachteil ist, dass "delayed" die Storageengine "Aria" nicht unterstützt.
Ob hier MyISAM, das dies noch unterstützt eine Alternative wäre, könnte man sich noch genauer ansehen.

Eventuell wäre es eine Option, diese ausschließlich im Speicher zu halten. bei einem Neustart könnte sie aus der Datenbank einmalig befüllt werden...
Ich hätte (wenn ich sie nutzen würde) aktuell 4500 Einträge in der Current_DB. Man müsste mal erheben, wie viel Speicher das benötigen würde...

Als Idee habe ich auch versucht, die current-Tabelle als "View" dynamisch einzubinden. dies dauert leider ca. eine Minute.

Eine weitere Idee wäre, die Daten einfach nur zum Anlegen eines Plots aus der Datenbank abzurufen.
Danzu habe ich folgende Tests als Idee gemacht:

Variante1: stand heute: Ergebnis: 01:45 Minuten
select TIMESTAMP, DEVICE, TYPE, EVENT, READING, VALUE, UNIT, READING from history where TIMESTAMP > (now() - interval 24 hour) group by DEVICE,READING;
Variant2: unter Verwendung der Partition von Heute (also nur Einträge von heute berücksichtigt) = 68s:
select TIMESTAMP, DEVICE, TYPE, EVENT, READING, VALUE, UNIT, READING from history partition(mon) where TIMESTAMP > (now() - interval 24 hour) group by DEVICE,READING;
Variant3: wie 2 jedoch mit Nutzung des neu vorgeschlagenen Indexes (timestamp zuerst) bei kalter Datenbank = 4s: (Achtung, funktioniert nur wennd er Index verändert wurde!)
         die zweite Abfrage davon eine Stunde später war in 0,95s fertig.
select TIMESTAMP, DEVICE, TYPE, EVENT, READING, VALUE, UNIT, READING from history partition(mon) where TIMESTAMP > (now() - interval 24 hour) group by DEVICE,READING;jeweils bei meiner Datenbank mit 34Mio Datensätzen.

Eventuell wäre es im Plot beim editieren durchaus möglich, diese 4 Sekunden (einmalig) zu warten? Zumindest für die Betriebsart "History", also ohne Current könnte man dies überlegen.
Ich stell dies hier einfach mal zur Diskussion.
FHEM-Server auf IntelAtom+Debian (12 Watt),
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline Garry Hayne

  • New Member
  • *
  • Beiträge: 39
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #23 am: 30 Januar 2017, 21:58:58 »
Heiko,

mir werde es reichen die Felder einfach nicht zu fuellen im Moment.


Regards, Garry
Verschiedener Raspberry Pi's im Einsatz. Wohne wieder in meine Heimat Wales nach 32 Jahre in Deutschland. Spricht fliessend SQL, Deutsch, Englisch.

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 1104
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #24 am: 30 Januar 2017, 22:10:27 »
Hi Garry,

Zitat
mir werde es reichen die Felder einfach nicht zu fuellen im Moment.

sehe ich mal mit vor.

Regards,
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 8 64 Bit  (Jessie) & Synology iSCSI-LUNs,
MariaDB auf Synology,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 1104
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #25 am: 30 Januar 2017, 22:25:48 »
Hallo zusammen, hallo Dan & Joe,

ich habe uns für die Implementierung der Altdatenmigration eine Arbeitsversion 93_DbLog_V2.12_Migration gebaut und hier angehängt.
Ich habe bereits als Grundlage den non-blocking Fileimport aus DbRep in das Modul integriert. Man kann es nutzen um die Funktion zu testen und sich mit der Arbeitsweise vertraut zu machen.

Vorgehensweise:

* Attribut "impfile" auf das zu importierende File setzen z.B.  /sds1/backup/test.txt
* bei großen Files Attr timeout hochsetzen -> wird aber auf 3600s gesetzt falls Attr nicht vorhanden
* "set <dbLog> migrateFile" startet den non-blocking import -> state und Logfile werden bedient

Datensatzformat: "TIMESTAMP","DEVICE","TYPE","EVENT","READING","VALUE","UNIT"

# Die Felder "TIMESTAMP","DEVICE","TYPE","EVENT","READING" und "VALUE" müssen gesetzt sein. Das Feld "UNIT" ist optional. Der Fileinhalt wird als Transaktion importiert, d.h. es wird der Inhalt des gesamten Files oder, im Fehlerfall, kein Datensatz des Files importiert.

Ein Beispielfile für einen Testimport habe ich mit angehängt.

Dan, um jetzt meine Funktion mit deiner Migrationsfunktion zu ersetzen mütest du dir die sub "DbLog_impfilePush" genauer anschauen. Ich habe gekennzeichnet wo etwa dein Code statt meinem implementiert werden müßte.
"DbLog_impfilePushDone" ist die reguläre Beendigungsfunktion des BlockingCall, "DbLog_impfilePushAborted" die Abbruchfunktion bei timeout.

Dan, schau mal ob du damit für die ersten Schritte  klarkommst. Wir schauen dann gemeinsam weiter wie wir das implementiert bekommen.

Grüße
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 8 64 Bit  (Jessie) & Synology iSCSI-LUNs,
MariaDB auf Synology,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN

Offline DS_Starter

  • Developer
  • Hero Member
  • ****
  • Beiträge: 1104
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #26 am: 31 Januar 2017, 07:57:30 »
Good morning Garry,

Zitat
Heiko,

mir werde es reichen die Felder einfach nicht zu fuellen im Moment.

Mir ist eingefallen, dass du doch schon mit der aktuellen Version zumindest für das Feld EVENT das machen kannst.
Es gibt die Attribute colEvent, colReading, colValue.  Sie sind zur flexiblen Anpassung der Feldbreiten in der DB gedacht.
Wenn du z.B. colEvent = 0 setzt, werden de facto keine Events in die DB geschrieben.
Probiers mal aus.

Have a good day, best regards
Heiko
ESXi 6.5 auf NUC6i5SYH mit FHEM Gastsystemen auf Debian 8 64 Bit  (Jessie) & Synology iSCSI-LUNs,
MariaDB auf Synology,
Homematic, IT, FS20, Cams in Synology Surveillance Station (SSCAM), CUL 433, CUL 868, HM-CFG-LAN

Offline JoeALLb

  • Sr. Member
  • ****
  • Beiträge: 939
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #27 am: 31 Januar 2017, 09:12:28 »
Es gibt die Attribute colEvent, colReading, colValue.  Sie sind zur flexiblen Anpassung der Feldbreiten in der DB gedacht.
Wenn du z.B. colEvent = 0 setzt, werden de facto keine Events in die DB geschrieben.
Toller Hinweis, vielen Dank! bei mir verkleinert sich die history-Tabelle dadurch immerhin um 25%!
FHEM-Server auf IntelAtom+Debian (12 Watt),
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline JoeALLb

  • Sr. Member
  • ****
  • Beiträge: 939
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #28 am: 31 Januar 2017, 11:44:34 »
Anbei mein bescheidener Versuch, einen Patch für einen
"PimaryKey-Mode" herzustellen.
Dieser führt ein neues Attribut "usePK=0" ein,
und nutzt die neuen Funktionen nur, wenn dieser auf 1 gesetzt wird.
Der Patch funktioniert aktuell nur mit MySQL und MariaDB.

Zuvor muss dieser Index (unten) in der Tabelle "Current" angelegt werden.
Dieser Patch fasst die beiden Vorgänge
# Versuche den Datensatz upzudaten
# bei nichtgelingen versuche ein insert

zu einem einzigen
insert into...zusammen. Auf meinem RPI, der damit massive Performanceprobleme hatte (auch weil die SD-Karte nicht sonderlich schnell ist), ist die CPU-Auslastung
von 0.71 auf 0.19 gesunken.

ALTER TABLE `current`
ALTER `DEVICE` DROP DEFAULT,
ALTER `READING` DROP DEFAULT;
ALTER TABLE `current`
ALTER `DEVICE` DROP DEFAULT,
ALTER `READING` DROP DEFAULT,
CHANGE COLUMN `DEVICE` `DEVICE` VARCHAR(64) NOT NULL AFTER `TIMESTAMP`,
CHANGE COLUMN `READING` `READING` VARCHAR(64) NOT NULL AFTER `EVENT`,
ADD PRIMARY KEY (`DEVICE`, `READING`);

Generell stellt sich die Frage, wie wir zukünftig mit der current-Tabelle umgehen.
Ich könnte mir auch vorstellen, dass diese ausschließlich in einem Memcache gehalten werden kann.
Wie denkt ihr darüber?
FHEM-Server auf IntelAtom+Debian (12 Watt),
RasPi-2 Sonos-FHEM per FHEM2FHEM,RasPi-3 Versuchs-RasPi für WLAN-Tests
Gateways: DuoFern Stick, CUL866 PCA301, CUL HM, HMLan, JeeLink, LaCrosse,VCO2
Synology. Ardurino UNO für 1-Wire Tests, FB7270

Offline Garry Hayne

  • New Member
  • *
  • Beiträge: 39
Antw:93_DbLog - Überlegungen zur weiteren Optimierung
« Antwort #29 am: 31 Januar 2017, 13:09:21 »
Toller Hinweis, vielen Dank! bei mir verkleinert sich die history-Tabelle dadurch immerhin um 25%!

Hi,
hast du das am laufendem DbLog gemacht? Bei mir funkioniert es nicht.

Garry
Verschiedener Raspberry Pi's im Einsatz. Wohne wieder in meine Heimat Wales nach 32 Jahre in Deutschland. Spricht fliessend SQL, Deutsch, Englisch.