SQL-Adapter verursacht hohe CPU-Last
-
@Nashra
joi -sollte reichen - falls mir noch was einfällt , sage ich bescheid - im moment eher keine ideen mehr -
@liv-in-sky sagte in SQL-Adapter verursacht hohe CPU-Last:
@Nashra
joi -sollte reichen - falls mir noch was einfällt , sage ich bescheid - im moment eher keine ideen mehr@liv-in-sky Trotzdem Danke
-
@Bluefox @apollon77 Hi, bei mir macht der SQL-Adapter auch Probleme. Hohe Prozessorlast. Nach deaktivieren von SQL alles gut. Nach neuem aktivieren langsamer Anstieg über mehrere Stunden, bis fast nix mehr ging... Das lief vorher ewig ohne Probleme. SQL und js-controller sind aus dem latest.
-
ich bin bei Euch, bei mir sieht es genauso aus....
Vermutlich konnte ich das nun lösen und erfolgreich wieder auf stabile wechseln. Aus meiner Sicht ist der ursprüngliche Auslöser Adapter "sql": 1.13.1
Warum auch immer bin ich in den latest abgerutscht und hatte plötzlich massive Probleme mit der CPU Last im Docker auf meiner DS.
Ich habe ein Downgrade aus dem Latest hin zum. Stable gemacht.
pkill io npm install iobroker.admin@4.0.10 npm install iobroker.flot@1.9.2 npm install iobroker.hm-rega@2.5.5 npm install iobroker.hm-rpc@1.14.2 npm install iobroker.hue@3.3.2 npm install iobroker.info@1.7.2 npm install iobroker.simple-api@2.4.5 npm install iobroker.socketio@3.0.7 npm install iobroker.vis@1.2.4 npm install iobroker.weatherunderground@3.1.6 npm install iobroker.web@3.0.8 npm install iobroker.js-controller@3.1.4 npm install iobroker.sql@1.12.5Docker Container neu gestartet und erstmal Freude gehabt. Plötzlich ging aber die CPU wieder durch die Decke....
Da ich eine zweite nahezu identische Installation habe, mit genau den gleichen Paketversionen und aktiven Instanzen, konnte nur noch die DB einen Einfluss haben. Diese habe ich dann via phpMyadmin kopiert und siehe da, in der ts_number Tabelle war ein haufen Schrott drin, den phpMyadmin nicht kopieren wollte. Somit habe ich dann den entschrotteten Inhalt auf der neuen DB und siehe da, bisher ist die sql.0 Instanz wieder bei 0.3% - da wo sie auch hingehört.
Meine Vermutung ist nun, dass sql 1.13.1 irgendein Quatsch mit der DB anstellt
-
@AxelF1977 Danke für die Widgets. Sorry, hat etwas gedauert, hatte ein Problem mit dem Proxmox und etliche Fehlermeldungen im Iobroker und musste das zuerst noch hinbiegen. Daher hab ich deine Widgets auch noch nicht näher angeschaut...
Muss auch überlegen ob ich im VIS noch weitermache, denn bei der Suche nach Lösungen zu meinen anderen Fehlern bin ich über lovelace gestolpert. Nach 4 Tagen bin ich da wesentlich näher an eine brauchbare Visualisierung als im VIS nach 3 Monaten...natürlich hat sowohl VIS wie auch lovelace Vor- und Nachteile...mal schauen was daraus wird, ich freu mich auf jeden Fall wenn Du Dein Projekt mal vorstellst.@bvol Wie sah der Datenmüll konkret aus? Auf den ersten Blick erkennbar oder auf den ersten Blick plausible Daten? Hab meine Daten mal überflogen und ein paar Stichproben bezüglich Plausibilität gemacht und die sehen unauffällig aus.
@all Bin nicht sicher ob das Problem beim SQL-Adapter oder bei anderen Adaptern oder in deren Kombination zu suchen ist.
Bei mir dümpelt das Teil seit ein paar Tagen so unauffällig wie es sein sollte vor sich hin. Bei mir lag es, wie weiter oben schon geschrieben, an den beiden internet-speed-Adapter (Vodafone und Speedy) in Kombination mit SQL. Auch hatte ich immer wieder Fehler wie die folgenden:gree_aircon.0 2020-06-07 00:53:52.613 warn (1358) This object will not be created in future versions. Please report this to the developer. gree_aircon.0 2020-06-07 00:53:52.612 warn (1358) Object gree_aircon.0.quiet is invalid: obj.common.type has an invalid value (text) but has to be one of number, string, boolean, array, object, mixed, file, json gree_aircon.0 2020-06-07 00:53:52.607 warn (1358) This object will not be created in future versions. Please report this to the developer. gree_aircon.0 2020-06-07 00:53:52.607 warn (1358) Object gree_aircon.0.air is invalid: obj.common.type has an invalid value (text) but has to be one of number, string, boolean, array, object, mixed, file, json gree_aircon.0 2020-06-07 00:53:52.606 warn (1358) This object will not be created in future versions. Please report this to the developer. gree_aircon.0 2020-06-07 00:53:52.605 warn (1358) Object gree_aircon.0.fanSpeed is invalid: obj.common.type has an invalid value (text) but has to be one of number, string, boolean, array, object, mixed, file, json gree_aircon.0 2020-06-07 00:53:52.603 warn (1358) This object will not be created in future versions. Please report this to the developer. gree_aircon.0 2020-06-07 00:53:52.603 warn (1358) Object gree_aircon.0.mode is invalid: obj.common.type has an invalid value (text) but has to be one of number, string, boolean, array, object, mixed, file, json yamaha.0 2020-06-07 01:39:44.586 warn (1028) This object will not be created in future versions. Please report this to the developer. yamaha.0 2020-06-07 01:39:44.585 warn (1028) Object yamaha.0.volume is invalid: obj.common.type has an invalid value (integer) but has to be one of number, string, boolean, array, object, mixed, file, json yamaha.0 2020-06-07 01:39:44.583 warn (1028) This object will not be created in future versions. Please report this to the developer. yamaha.0 2020-06-07 01:39:44.583 warn (1028) Object yamaha.0.Commands.adjustVolume is invalid: obj.common.type has an invalid value (integer) but has to be one of number, string, boolean, array, object, mixed, file, json yamaha.0 2020-06-07 01:39:44.582 warn (1028) This object will not be created in future versions. Please report this to the developer. yamaha.0 2020-06-07 01:39:44.581 warn (1028) Object yamaha.0.Commands.volumeDown is invalid: obj.common.type has an invalid value (integer) but has to be one of number, string, boolean, array, object, mixed, file, json yamaha.0 2020-06-07 01:39:44.580 warn (1028) This object will not be created in future versions. Please report this to the developer. yamaha.0 2020-06-07 01:39:44.579 warn (1028) Object yamaha.0.Commands.volumeUp is invalid: obj.common.type has an invalid value (integer) but has to be one of number, string, boolean, array, object, mixed, file, jsonInsgesamt gab es die Fehler bei 10-15 Adapter von ungefähr 30. Lösung war de- und wieder installieren der entsprechenden Adapter. Bis auf den Mihome-Vacuum sind alle seitdem Ruhig. Einzig der Mihome war gestern auf gelb (seit wann weiss ich nicht) und brachte beim Neustart wieder folgendes:
mihome-vacuum.0 2020-06-17 18:05:05.413 warn (607877) This object will not be created in future versions. Please report this to the developer. mihome-vacuum.0 2020-06-17 18:05:05.412 warn (607877) Object mihome-vacuum.0.info.dnd is invalid: obj.common.type has an invalid value (switch) but has to be one of number, string, boolean, array, object, mixed, file, jsonSeit dem Neustart gestern keine Probleme mehr. Keine Ahnung ob zwischen den Fehlern und der Auslastung ein Zusammenhang besteht, denn wie geschrieben ist die bei mir im Moment ok. Vielleicht sind das auch Folgefehler vom Upgrade vom js-controller oder anderer Adapter...
Gruss
Urs -
@Urs Bei mir waren string datensätze in der ts_number tabelle. Betroffene Adapter: weatherunderground, coronavirus-statistics, netatmo usw., also eigentlich alle adapter, die strings liefern. Die hm-* adapter waren nicht betroffen, da die keine strings liefern.
Wirklich Ruhe ist bei mir erst eingekehrt, nachdem ich konsequent alle string Lieferanten vom logging excluded habe. Zudem waren haufenweise Dubletten in der DB, was man recht schnell mit einer Kopie der Tabellen via sql sehen kann. So hatte die ts_number Tabelle, die zuvor 12Mio Datensätze hatte, nach der Kopie nur noch 8 Mio Datensätze...Ich habe den Ärger nun auch genutzt und die Tabellen via php Script entrümpelt und alle Datensätze mit sort by id asc, ts asc eingelesen und dann, wenn val und id gleich waren, Zeile für Zeile geprüft und nur die nicht gleichen Datensätze in eine neue Tabelle geschrieben. Jetzt hat z.B. die ts_number nur noch 1.2 Mio Datensätze. Man sollte auf dem SQL Adapter einfach nie „trotzdem gleiche Werte aufzeichnen (Sekunden)“ aktivieren....
Die String Werte (weatherunderground) lese ich jetzt via simple-api aus und schreibe sie dann auch via simple-api in die richtige Tabelle. Das Ganze via php und halt nur für die Werte, die ich sowieso nur von Zeit zu Zeit pullen will.
Meine sql.0 Instanz konsumiert nun nur noch 0.3% mit Peaks bis 1% CPU im Docker Container, zuvor waren es dauerhaft 90-100%!
Gleiches habe ich auch auf einer zweiten, durch mich betreuten Installation durchgeführt, mit gleichem Ergebnis. Hierbei bin ich aber über ein MySQL Performance Thema gestolpert: Ich nutze ein Synology DS 918+ mit NVME cache, dort schluckte die MariaDB die sql inserts fast in Echtzeit (ca. 1Mio records pro Minute), auf der zweiten Installation auf einer DS 216+II war das Disksystem sofort am Anschlag und ich konnte nur knapp 10'000 records pro Minute schreiben.
-
@bvol Danke für die Ausführungen.
Strings in der ts_number wären mir aufgefallen, hatte ich nie. Ich bin aber auch nicht so Fan von Strings. Ausser ein paar good, average und bad gibt es noch up down und stable, wobei ich die letzten 3 auch noch rausschmeissen werde, kann man ja anhand der Daten bei Bedarf sehen/errechnen. Am Anfang hatte ich gerade bei Netatmo auch die min/max Datum in die SQL geschrieben, aber auch das ist eigentlich Sinnfrei da ja jeder Datensatz mit der Unix-Zeit gespeichert wird.
Danke für den Tip mit dem Kopieren. Da scheint wirklich was faul zu sein:
Entrümpeln wäre daher sicher gut...aber spätestens bei php Script muss ich da aussteigen. Gibt es da echt auch eine einfache Lösung für Nicht-Admin und Nicht-Programmierer -sprich Laien- wie mich? Oder eine Schritt-für-Schritt-so-entrümpele-ich-meine-SQL-Datenbank-auch-ohne-Doktortitel-Anleitung?
Performance Thema: Hmm, da hab ich heute auch was erlebt. Wollte Die SQL-Datenbank über HeidiSQL von Hand sichern, und zwar nicht wie sonst auf dem Windows PC sondern direkt auf einem Netzlaufwerk. Das dauerte 1h 20 Minuten für ungefähr 120Mb! Wenn ich auf dem PC sichere dann dauert es etwa 45 Sekunden. Hinter dem Netzlaufwerk verbirgt sich eine USB3 HDD welche an der Fritzbox per SMB freigegeben ist. Da erwarte ich keine Performance-Wunder aber das heute war dann doch eher sehr enttäuschend, zumal das hin und herschieben von Dateien auf dem Netzlaufwerk -in Anbetracht der eingesetzten Hardware - eine durchaus akzeptable Performance liefert. Muss kein SQL-Problem wie bei dir sein, kann genau so gut auch an meiner Unkenntnis liegen und ich hab irgendwo einen Schrott konfiguriert...
-
@bvol sagte in SQL-Adapter verursacht hohe CPU-Last:
Meine Vermutung ist nun, dass sql 1.13.1 irgendein Quatsch mit der DB anstellt
Mal Frech gefragt ... über Admin könnt Ihr ja auf frühere Versionen zurück ... Könntet Ihr mal schauen ob Ihr die Version findet wo das losgeht? Also - ist es wirklich die 1.13.1? Dann schaue ich da mal rein
-
Wie viele States habt Ihr so aktiviert?
Wer "Lust" hat mal was auszuprobieren: https://github.com/ioBroker/ioBroker.sql/issues/127#issuecomment-647346141
closed sql-adapter verursacht hohe CPU-Auslastung #127
-
Ich schreibe ungefähr 150 States aus ca 30 Adaptern in die DB. Waren aber auch schon bis 190 und als ich die Probleme hatte waren es ungefähr 160.
Die Versionen zurück oder Dein Link hatte ich noch nicht den Mut und die Zeit es auszuprobieren, da es im Moment bei mir läuft (ohne die beiden Internet-Speedtest-Adapter wie oben beschrieben).
-
Hallo,
ich habe vor einigen Tagen auch alle Werte auf Typ "Nummer" umgestellt. Seither habe ich keine Probleme mit der Prozessorlast mehr. Irgendwas beim Speichern von anderen Datentypen scheint problematisch zu sein!
LG
-
@s-bormann Das mag sein ... der Fix den wir gemacht haben kann sehr gut damit zu tun haben ... wenn nämlich datentypen nicht definiert waren kam es vor das danach dinge mehrfach ausgeführt wurden und sowas ...
-
Hallo,
Für mich ist das Upgrade auf Version 1.14.2 die Lösung.
Gruss
-
@Zappie Danke die Antwort habe ich gebraucht
-
1.14.2 verhält sich unauffällig. CPU Last im erwarteten Bereich, also gem. top zwischen 0 und 0.4%. Wie es sein soll.
-
@Urs hier das Cleanup Script: (es legt eine zusätzliche Tabelle mit den "gesäuberten" Daten an. Es sortiert nach ID und TS und schreibt nur die zum vorigen Wert geänderten Werte.)
<?php // SQL Verbindung $sqlserver = "MYSQLSERVERIP:3307"; // DB Server:Port $sqluser = "admin"; // DB User $sqlpw = "*******"; // DB User PW $sqlDB = "iobroker"; // DB Name $sourceTable = "ts_number"; // Source Table $cleanTable = $sourceTable ."_cleaned"; // Temp Table $writedatasets =100; // Number of datasets written to Temp Table using single SQL Statement $SQLqueryLIMIT=0; // For Testing purposes. 0 selects all Source sets. // DB Verbindung bauen error_reporting(0); $db = mysqli_connect($sqlserver, $sqluser, $sqlpw, $sqlDB); print_r ($db->connect_error); if ($db->connect_errno) { die('Sorry - gerade gibt es ein Problem'); } // Attempt select query execution if ($SQLqueryLIMIT > 0) { $sql = "SELECT * FROM ".$sourceTable." ORDER BY id ASC,ts ASC LIMIT ".$SQLqueryLIMIT; } else { $sql = "SELECT * FROM ".$sourceTable." ORDER BY id ASC,ts ASC"; } // load Table to memory if($result = mysqli_query($GLOBALS['db'],$sql)){ // do nothing } // Create Cleantable $sql ="DROP TABLE IF EXISTS `".$cleanTable."`"; mysqli_query($GLOBALS['db'],$sql); $sql ="CREATE TABLE `".$cleanTable."` SELECT * FROM `".$sourceTable."` WHERE 1 = 2"; mysqli_query($GLOBALS['db'],$sql); $sql ="ALTER TABLE `".$cleanTable."` ADD PRIMARY KEY (`id`,`ts`)"; mysqli_query($GLOBALS['db'],$sql); $idprev=0; $valprev=0; $saetze=0; $DataArr = array(); while($row = mysqli_fetch_array($result)){ if (($row['val']==$valprev) && ($row['id']==$idprev)) { //echo $idprev . " " . $valprev . " " . $tsprev . " Behalten \n"; //echo $row['id'] . " " . $row['val'] . " " . $row['ts'] . " Loeschen \n"; } else { $fieldVal1 = ($row['id']); $fieldVal2 = ($row['ts']); $fieldVal3 = ($row['val']); $fieldVal4 = ($row['ack']); $fieldVal5 = ($row['_from']); $fieldVal6 = ($row['q']); $DataArr[] = "('$fieldVal1','$fieldVal2','$fieldVal3','$fieldVal4','$fieldVal5','$fieldVal6')"; $idprev=$row['id']; $valprev=$row['val']; $tsprev=$row['ts']; $saetze++; } if ($saetze==$GLOBALS['writedatasets']){ $sql = "INSERT INTO ".$cleanTable." (id, ts, val, ack, _from, q) values "; $sql .= implode(',', $DataArr); $sql .= ";"; // echo $sql; mysqli_query($GLOBALS['db'], $sql); $saetze=0; unset($DataArr); // $DataArr is gone $DataArr = array(); // $DataArr is here again } } if ($saetze>=0){ //echo implode(',', $DataArr); $sql = "INSERT INTO ".$cleanTable." (id, ts, val, ack, _from, q) values "; $sql .= implode(',', $DataArr); $sql .= ";"; //echo $sql; mysqli_query($GLOBALS['db'], $sql); } echo mysqli_num_rows($result); // Free result set mysqli_free_result($result); // DB Verbindung schliessen mysqli_close($db); ?>Code als php Script speichern und mit php ausführen. ACHTUNG: Script zieht die gesamte Tabelle in den Speicher, sorgt also für genügend RAM, je nachdem wie gross die Tabelle ist.
Zum Testen $SQLqueryLIMIT mal auf 1000 setzen, dann werden nur die ersten 1000 Tabelleneinträge geholt.
$writedatasets =100; definiert die Anzahl an Datensätzen, die mittels einem SQL Statement in die cleaned Tabelle geschrieben werden. Sorgt für mehr Speed, kann aber den MySQL Server überfordern. Mit dem Wert 100 habe ich gute Erfahrungen gemacht.
$sourceTable = "ts_number"; -> gibt die zu lesende Tabelle an.
Wenn das Ergebnis den Erwartungen entspricht, dann mittels phpMyAdmin o.Ä. die Tabellen entsprechend umbenennen...
-
@apollon77 Bei mir läuft auch wieder alles! Danke fürs Fixen!
-
@bvol hey, cool, vielen Dank. Muss mal schauen wann ich dazu komme das mal auszuprobieren. Melde mich wieder.
-
Ich habe die selben Meldungen für Datenpunkte die ich leider gelöscht habe bevor ich das Logging disabled hab...
Aber durch die ganze Testerei hab ich jetzt verschiedene hässliche Meldungen vom SQL Adapter bzw von MariaDB:
sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for web-speedy.0.Results.server.ping sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for web-speedy.0.Results.speeds.upload_MB sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for web-speedy.0.Results.speeds.download_Mb sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for web-speedy.0.Results.speeds.download_MB sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for vodafone-speedtest.0.Results.ping.packetLoss sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for vodafone-speedtest.0.Results.ping.avg sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for vodafone-speedtest.0.Results.ping.max sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for vodafone-speedtest.0.Results.ping.min sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for vodafone-speedtest.0.Results.upload_Mb sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for vodafone-speedtest.0.Results.upload_MB sql.0 2020-06-04 03:59:59.727 warn (3939) No realID found for vodafone-speedtest.0.Results.download_MBAuch meine Frage - wie bekommt man die weg? Wie kann man das SQL Logging für nicht mehr existente Datenpunkte abdrehen?
Liebe Grüße
Tom -
@etv Geloggt wird eh nicht mehr, aber die Werte befinden sich noch in der Datenbank. Das wäre auch so, wenn du das Logging vorher deaktiviert hättest. Ich habe die entsprechenden Werte direkt aus der Datenbank gelöscht. Da sollte man aber wissen was man tut.
