First using the INNODB storage engine:
A load of 3.5 causes an issue where the time that is recorded for data packets coming in gets messed up creating bunched up datapoints:
Switching the storage engine over the MYISAM, reduced the load to around 0.2 and the timing issue is no longer present:
To convert your raspberry pi emoncms Innodb tables to MYISAM you can run the following script on your raspberrypi which will go through each table converting them in turn:
$mysqli = new mysqli("localhost","root","raspberry","emoncms");
$result = $mysqli->query("SHOW tables");
while ($row = $result->fetch_array())
{
echo "ALTER TABLE `".$row[0]."` ENGINE=MYISAM\n";
$mysqli->query("ALTER TABLE `".$row[0]."` ENGINE=MYISAM");
}
Switching the storage engine over the MYISAM, reduced the load to around 0.2 and the timing issue is no longer present:
To convert your raspberry pi emoncms Innodb tables to MYISAM you can run the following script on your raspberrypi which will go through each table converting them in turn:
$mysqli = new mysqli("localhost","root","raspberry","emoncms");
$result = $mysqli->query("SHOW tables");
while ($row = $result->fetch_array())
{
echo "ALTER TABLE `".$row[0]."` ENGINE=MYISAM\n";
$mysqli->query("ALTER TABLE `".$row[0]."` ENGINE=MYISAM");
}
The script worked flawlessly, took about 10 minutes to complete.
ReplyDeletePaul
Hi,
ReplyDeleteit's interesting that InnoDB caused such high load. I am wondering if your using any large transactions?
MyISAM tables are probably better for this application as it'll be only a single concurrent write. However I guess the data can be read by a number of connections to the web interface. MyISAM files are a lot quick at writes but do have full table locks. InnoDB should be able to keep up as in MySQL 5.5 InnoDB is used over MyISAM as default. However I think that the disk IO used by the transactions in InnoDB is causing high load. Does your app use a lot of transactions?