SQL-Adapter verursacht hohe CPU-Last

[apollon77]

Wie viele States habt Ihr so aktiviert?

Wer "Lust" hat mal was auszuprobieren: https://github.com/ioBroker/ioBroker.sql/issues/127#issuecomment-647346141

Farmer-cb created this issue in ioBroker/ioBroker.sql

closed sql-adapter verursacht hohe CPU-Auslastung #127

[Urs]

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).

[s.bormann]

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

[apollon77]

@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 ...

[Zappie]

Hallo,

Für mich ist das Upgrade auf Version 1.14.2 die Lösung.

Gruss

[apollon77]

@Zappie Danke die Antwort habe ich gebraucht

[bvol]

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.

[bvol]

@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...

[s.bormann]

@apollon77 Bei mir läuft auch wieder alles! Danke fürs Fixen!

[Urs]

@bvol hey, cool, vielen Dank. Muss mal schauen wann ich dazu komme das mal auszuprobieren. Melde mich wieder.

[etv]

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_MB

Auch meine Frage - wie bekommt man die weg? Wie kann man das SQL Logging für nicht mehr existente Datenpunkte abdrehen?

Liebe Grüße
Tom

[Dr. Bakterius]

@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.

[apollon77]

@Dr-Bakterius Dem Adapter ist egal was in der DB steht ... kann es sein das aus irgend einem Grund nur der STate gelköscht ist aber das Objekt noch da ist ... ja das Thema steht noch auf der Liste ...

@etv
mach bitte mal an der Kommandozeile

iobroker object get vodafone-speedtest.0.Results.ping.packetLoss

und

iobroker state get vodafone-speedtest.0.Results.ping.packetLoss

Und sag mal was das gibt

[liv-in-sky]

@apollon77

habe da auch massig davon

kam durch json-import von anderen usen - für script-testen
datenpunkte sind mitlerweile gelöscht

[apollon77]

@liv-in-sky Und das State? das noch da?

[liv-in-sky]

@apollon77

der wird doch mitgelöscht, wenn man die datenpunkte löscht ? oder

habe mal abgefragt:

log(getState('maxcube.0.devices.thermostat_101881.setpoint').val)

[apollon77]

@liv-in-sky Ja sollte er ... ich versuche es zu verstehen ... Also alles ausschliessen

An sich liesst der sql Adapter zu Beginn die Objekte die ein custom haben ... also wenn Objekte gelöscht sind sollte er die nie bekommen

[liv-in-sky]

@apollon77

genau so hätte ich mir das auch vorgestellt

wenn ich mit dem tool in der datenbank suche nach max kommt auch nix

[liv-in-sky]

@apollon77

in der sql datenbank ist noch was - aber das ist wohl das problem

[apollon77]

@liv-in-sky Nein ist es nicht. Dem Adapter ist egal was in der DB steht. Er nimmt die Objekte die sql aktiviert haben und arbeitet damit. Er schaut nur dann in der DB nach ob ein Objekt schon bekannt ist oder nicht.