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

[DS_Starter]

So, nun ist DbLog über eine Stunde in diesem fehlerhaften Zustand gelaufen (19:50-21:08) und es haben sich rund 1800 Einträge im Cache gesammelt.
Hier ein Ausschnitt:

Code Auswählen Erweitern


245566 => 2017-07-15 20:10:18|SMA_Energymeter|SMAEM|Bezug_WirkP_Zaehler_Diff: 0.0045|Bezug_WirkP_Zaehler_Diff|0.0045|
245567 => 2017-07-15 20:10:18|MySTP_5000|SMAINVERTER|etotal: 16151.796|etotal|16151.796|
245568 => 2017-07-15 20:10:33|sysmon|SYSMON|stat_cpu_percent: 0.19 0.00 0.14 99.64 0.03 0.00 0.00|stat_cpu_percent|0.19 0.00 0.14 99.64 0.03 0.00 0.00|
245569 => 2017-07-15 20:11:18|SMA_Energymeter|SMAEM|Bezug_WirkP_Zaehler_Diff: 0.0046|Bezug_WirkP_Zaehler_Diff|0.0046|
245570 => 2017-07-15 20:11:18|MySTP_5000|SMAINVERTER|etotal: 16151.798|etotal|16151.798|
245571 => 2017-07-15 20:11:33|sysmon|SYSMON|swap: Total: 1293.00 MB, Used: 26.13 MB,  2.02 %, Free: 1266.86 MB|swap|Total: 1293.00 MB, Used: 26.13 MB,  2.02 %, Free: 1266.86 MB|
245572 => 2017-07-15 20:12:19|SMA_Energymeter|SMAEM|Bezug_WirkP_Zaehler_Diff: 0.0049|Bezug_WirkP_Zaehler_Diff|0.0049|
245573 => 2017-07-15 20:12:19|MySTP_5000|SMAINVERTER|etotal: 16151.8|etotal|16151.8|
245574 => 2017-07-15 20:12:33|sysmon|SYSMON|ram: Total: 2010.07 MB, Used: 339.73 MB, 16.90 %, Free: 1670.35 MB|ram|Total: 2010.07 MB, Used: 339.73 MB, 16.90 %, Free: 1670.35 MB|
245575 => 2017-07-15 20:13:05|Rep.Show.DbSize|DBREP|state: running|state|running|
245576 => 2017-07-15 20:13:05|Sonnenstrom|SHM|state: disabled|state|disabled|
245577 => 2017-07-15 20:13:19|SMA_Energymeter|SMAEM|Bezug_WirkP_Zaehler_Diff: 0.0047|Bezug_WirkP_Zaehler_Diff|0.0047|
245578 => 2017-07-15 20:13:20|MySTP_5000|SMAINVERTER|etotal: 16151.803|etotal|16151.803|
245579 => 2017-07-15 20:13:33|sysmon|SYSMON|eth0_diff: RX: 1.19 MB, TX: 0.16 MB, Total: 1.35 MB|eth0_diff|RX: 1.19 MB, TX: 0.16 MB, Total: 1.35 MB|
245580 => 2017-07-15 20:14:05|Sonnenstrom_Relative|SHMFORECASTRELATIVE|state: disabled|state|disabled|
245581 => 2017-07-15 20:14:19|SMA_Energymeter|SMAEM|Bezug_WirkP_Zaehler_Diff: 0.0046|Bezug_WirkP_Zaehler_Diff|0.0046|
245582 => 2017-07-15 20:14:20|MySTP_5000|SMAINVERTER|etotal: 16151.805|etotal|16151.805|
245583 => 2017-07-15 20:14:33|sysmon|SYSMON|eth0_diff: RX: 2.35 MB, TX: 0.27 MB, Total: 2.62 MB|eth0_diff|RX: 2.35 MB, TX: 0.27 MB, Total: 2.62 MB|
245584 => 2017-07-15 20:15:12|MyWetter|WEATHER|wind: 7|wind|7|km/h
245585 => 2017-07-15 20:15:20|SMA_Energymeter|SMAEM|Bezug_WirkP_Zaehler_Diff: 0.0046|Bezug_WirkP_Zaehler_Diff|0.0046|
245586 => 2017-07-15 20:15:20|MySTP_5000|SMAINVERTER|etotal: 16151.807|etotal|16151.807|
245587 => 2017-07-15 20:15:33|sysmon|SYSMON|loadavg: 0.03 0.04 0.00|loadavg|0.03 0.04 0.00|
245588 => 2017-07-15 20:16:19|SMA_Energymeter|SMAEM|Bezug_WirkP_Zaehler_Diff: 0.0042|Bezug_WirkP_Zaehler_Diff|0.0042|
245589 => 2017-07-15 20:16:20|MySTP_5000|SMAINVERTER|etotal: 16151.81|etotal|16151.81|
245590 => 2017-07-15 20:16:33|sysmon|SYSMON|loadavg: 0.01 0.03 0.00|loadavg|0.01 0.03 0.00|
245591 => 2017-07-15 20:17:19|SMA_Energymeter|SMAEM|Bezug_WirkP_Zaehler_Diff: 0.0038|Bezug_WirkP_Zaehler_Diff|0.0038|
245592 => 2017-07-15 20:17:20|MySTP_5000|SMAINVERTER|etotal: 16151.812|etotal|16151.812|
245593 => 2017-07-15 20:17:33|sysmon|SYSMON|stat_cpu_percent: 0.28 0.00 0.15 99.54 0.03 0.00 0.00|stat_cpu_percent|0.28 0.00 0.15 99.54 0.03 0.00 0.00|


Nun habe ich das PW in der DB wieder korrekt gesetzt und die DB-Verbindung konnte sich wieder aufbauen.
Die im Cache gespeicherten Datensätze wurden in die DB geschrieben.

Z.B. den Satz rausgepickt "245584 => 2017-07-15 20:15:12|MyWetter|WEATHER|wind: 7|wind|7|km/h  " und als Screenshot aus phpMyAdmin angehängt.

[DS_Starter]

Hallo zusammen,

wegen dem von SusisStrolch hier https://forum.fhem.de/index.php/topic,74234.0.html#msg659694 beschriebenen Sachverhalt habe ich im Modul kleine Änderungen vorgenommen um bei einem state-Event auch das Reading "state" mit im Event zu erhlalten.

Anbei die V2.20.0 die ich euch bitte, insbesondere SusisStrolch, zu testen ob bei euch ebenfalls alles wie gewohnt sauber funktioniert.

VG
Heiko

[Omega]

Die Möglichkeit zum configCheck finde ich super!!

Zu folgender Ausgabe habe ich allerdings Fragen:

Code Auswählen Erweitern


Result of encoding check

Encoding used by Client (connection): LATIN1
Encoding used by fhem: UTF8
Recommendation: Both encodings should be identical. You can adjust the usage of UTF8 connection by setting the UTF8 parameter in file '/opt/fhem/dblog/sqldb.conf' to the right value.


Unter Linux verwende ich de_DE.UTF-8.
Die DB habe ich angelegt mit

Code Auswählen Erweitern


CREATE DATABASE `fhem` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Allerdings ohne Parameter ,,utf8 => 1".

In /etc/mysql/my.cnf habe ich nichts definiert bzgl. character-set.

Frage 1:
Vielleicht weißt du ja aus dem Stegreif (ohne einen Blick in die Glaskugel zu werfen  ), woher LATIN1 kommt, obwohl ich zumindest an 2 Stellen explizit UTF8 definiert habe?

Frage 2:
Nur den Parameter ,,utf8 => 1" in der Config-Datei zu definieren, recht vermutlich nicht. Muss die DB neu angelegt werden? Reicht ein Export/Import? Gibt es noch etwas, was ich beim Wechseln der Codierung berücksichtigen sollte?

LG
Holger

[SusisStrolch]

Bin heute zeitlich eingeschränkt. Schaue, ob ich heut Abend testen kann.
Noch ne Frage zu Deiner my.cnf:
Ist das die Einzige oder die, die von der mitgelieferten inkludiert wird?

[DS_Starter]

Hi Holger,

ja, deine Fragen kann ich beantworten denke ich.

Die Angabe zu Encoding aus dem configCheck bezieht sich tatsächlich auf die Connection zwischen dem Client (FHEM) und der Datenbank. Ich will nicht lügen, aber es gibt glaube ich bis zu 10 Stellen im MySQL die sich auf Zeichensätze beziehen. Die meisten davon werden aus der Servereinstellung übernommen. Wenn du DbRep einsetzt, kannst du dir mit "get ... dbvars" einen Überblick über die Parameter mit uft8-Einstellungen verschaffen (siehe Screenshot).

Also konkret ist es so dass das Perl-DBI-Interface im Standard beim Verbindungsaufbau KEIN UTF8 verwendet (warum man das auch immer so gemacht hat). Deswegen muß ich bei Verbindungsaufbau einen Parameter mitgeben sofern UTF8 gewünscht ist.

Das passiert über die Angabe ,,utf8 => 1" in der Config-Datei und ist insofern auch ausreichend wenn man ansonsten auch so wie von dir beschrieben für die DB bzw. Tabelle UTF8 benutzt (ist sicherlich der Standard).
JoeAllb verzichtet z.B. bewußt auf UTF8 wegen Platzbedarf bzw. Performance, glaube ich. Vllt. kann er noch etwas dazu sagen.

LG
Heiko

[DS_Starter]

Ist das die Einzige oder die, die von der mitgelieferten inkludiert wird?

Das ist die inkludierte. In letzter Zeit hat Syno etliche Updates (inkl. MariaDB 10) ausgeliefert. Ich muß mich mal wieder damit beschäftigen ob sich bezüglich der DB-Konfiguration dadurch Änderungen ergeben haben. Habe auch einfach zu wenig Zeit 

[DS_Starter]

Hallo SusisStrolch,

jetzt habe ich mal den Aufwand betrieben und eine meiner Testdatenbanken über Backup/ Restore (mit DbRep dumpMySQL serverSide, restoreMySQL) auf ca. 6GB Größe (nur Daten ! ) aufgepumpt.
Vor dieser Maßnahme habe ich alle Indizes gelöscht damit die Importe schnell laufen.

Ausgangspunkt war nun eine 6 GB history Tabelle mit 56 Mio Datensätzen.
Im phpMyadmin habe ich dann die Indexerstellung:

Code Auswählen Erweitern


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


gestartet. Das war 12:52.
Nach ca. 20 Minuten ! war der Prozess 13:11 fertig.

Während dieser Zeit hat sich der Cache aufgebaut (ca. 800 Einträge) sowie im state kam die erwartete Mitteilung "Commit is already running ..." (siehe Screenshot).
Nachdem der Index erstellt war (Indexgröße 2,4 GB), wurden genau wie bei meinem anderen Test die Cacheeinträge in die DB geschrieben.
Die Test-DB ist nun insgesamt 8,3GB groß und kommt deinem Aufbau schon recht nahe.

Die Performance ist nach wie vor vollkommen positiv zu bewerten entgegen deinen beschriebenen 12H .

LG
Heiko

[Omega]

Hallo Heiko,

danke für deine Ausführungen. Sie haben mich auf den richtigen Weg gebracht.
Kleiner Hinweis jetzt: die Ausgabe der Variablen in DBREP zeigt nur die globalen Einstellungen. Da ich die DB aber bereits mit UTF8 definiert hatte, war die Ausgabe von DBREP mMn nicht ganz korrekt.
Ich habe mir die Variablen dann mit DBREP, direkt von der MySQL-Console und mittels Windows Programm HeidiSQL angeschaut. Die Unterschiede habe ich mal als HC angehängt.

Etwa verwirrt mich noch die Angabe von utf8mb4 in HeidiSQL, obwohl die Console etwas anderes ausgibt.

Ich habe jetzt in my.cnf (nach Hinweisen aus dem Netz) folgendes eingefügt:

Code Auswählen Erweitern


skip-character-set-client-handshake
collation-server=utf8_general_ci
character-set-server=utf8


Jetzt habe ich definitiv UTF8. Da das jetzt der Standard ist, müsste ich doch eigentlich auf den Parameter utf8 => 1, verzichten können (oder habe ich mir mit ,,skip-character-set-client-handshake" ein Eigentor geleistet)?

LG
Holger

[DS_Starter]

Hi Holger,

Kleiner Hinweis jetzt: die Ausgabe der Variablen in DBREP zeigt nur die globalen Einstellungen.....

Guter Hinweis, danke. Vllt. kann ich die Abfrage etwas verbessern. Schaue ich mal.

Jetzt habe ich definitiv UTF8. Da das jetzt der Standard ist, müsste ich doch eigentlich auf den Parameter utf8 => 1, verzichten können (oder habe ich mir mit ,,skip-character-set-client-handshake" ein Eigentor geleistet)?

Also meiner Meinung nach solltest du "utf8 => 1" weiterhin setzen und ,,skip-character-set-client-handshake" verzichten obwohl es eventuell auf das Gleiche rauskommt (müsste man testen). Wenn ich das richtig im Netz lese, wird dieser Parameter vor allem eingesetzt wenn man das alte Verhalten von MySQL 4.0 bei einem Upgrade behalten will. Wollen wir ja nicht 
Außerdem werte ich den Parameter "utf8 => 1" über den DbLog-Hash auch in den DbRep-Devices für bestimmte Operation wie Import/Export aus und setze dementsprechend die Verbindungsvariablen.

LG
Heiko

[Omega]

Du hast mich überzeugt - Danke
LG
Holger

Und noch ein besonderes Dankeschön für deine gute und ausführliche Dokumentation!

[DS_Starter]

Gern geschehen

Danke dir auch für den Tipp bzgl. DbRep -> habe ich schon geändert und eingecheckt. Ist dann mogen früh im Update.

LG
Heiko

[JoeALLb]

@SUSISTROLCH,
kann es sein, dass Du viele doppelten Einträge hast, also zur selben Uhrzeit einträge mit den selben Device-Readings-Kombinationen?
Ich denke mich erinnern zu können, dass in dieser Konstellation MariaDB5 beim Index erstellen selbst bei mir auch langsam war....
Ich hatte das dann bereinigt in dem ich einen primary Key statt dem normalen Index nutze. Seither und seit dem Update auf MariaDB 10
läuft meine Indexerstellung ähnlich performant wie die von Heiko. Ob diese Maßnahmen jedoch ausreichen kann ich nicht sagen, da ich es(den langsamen Index) nicht mehr reproduzieren kann.

[SusisStrolch]

@JoeALLb, kann ich ausschließen:

Code Auswählen Erweitern

--
-- Indizes für die Tabelle `history`
--
ALTER TABLE `history`
  ADD PRIMARY KEY (`DEVICE`,`READING`,`TIMESTAMP`),
  ADD KEY `DbLog_Group` (`TIMESTAMP`,`DEVICE`,`READING`),
  ADD KEY `Search_Idx` (`DEVICE`,`READING`,`TIMESTAMP`),


Ich zieh' mir heut mal die Daten in eine MariaDB10, um auszuschließen dass ich mir in der Vergangenheit ein Problem mit meiner alten Datenbank eingefangen habe.

[JoeALLb]

aber dein PK und "Search_Idx" sind ja redundant, hast Du das nur für einen Test so angelegt?

[SusisStrolch]

Den PK hatte ich schon, den SearchKey habe ich nach "Anraten" von "check Config" angelegt.