Mysql getting slow

[deleenheir]

I'm creating a Raspberry pi + arduino combo project where the arduino's are sending sensor data to the raspberry pi.

The raspberry pi reads this data using a python script and stores it in a mysql database.

There are php pages that are used to extract and display the data.

The table that stores the sensordata is pretty simple: it just contains an Primary ID, SensorID, DateTime and SensorValue(float)

Code: Select all

CREATE TABLE IF NOT EXISTS `SensorData` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `SensorID` tinyint(3) unsigned NOT NULL COMMENT 'SensorID',
  `DateTime` datetime NOT NULL,
  `Value` float NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `DateTime` (`DateTime`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1835196 ;

Everything seemed to work fine but things start to slow down as the table gets bigger.

At the moment I filled the table with some data (1 000 000 records) to see how it would perform in real life. 1 000 000 records = +/- 1 year of logging. But the this is getting terribly slow.

My python script does about 5 selects and 1 insert. When the table is nearly empty it goes very fast (< 100ms) but it gets slower as data is added.

Up to the point where the script takes +60 seconds.

Am I asking too much from the raspberry or might there be a solution?

[riklaunim]

Databases like MySQL like to use RAM which is limited on Raspberry Pi. Using SQLite could help, and if the SD card is good at read/writes it could provide acceptable query speeds (but you have to test that).

[nobbit]

At 1 million records, how large is your database?

[Douglas6]

Depending on your queries, adding some indexes might help to prevent scanning entire tables. The EXPLAIN command can be useful here.

[deleenheir]

I fixed the problem. I upped the number of records to 2 million and it remains blazing fast.

the qry that was givving me troubles was:

Code: Select all

SELECT YEAR(max(`DateTime`)) as Year, MONTH(max(`DateTime`)) as Month, DAY(max(`DateTime`)) as Day, HOUR(max(`DateTime`)) as Hour, MINUTE(max(`DateTime`)) as Minute FROM `SensorData` WHERE `SensorID` = %s

My first guess was adding an index on the Sensordata table on the DateTime column => no luck

My second guess was Maintenance plans as explained here:
http://www.laurencegellert.com/2011/07/ ... mysql-5-1/

My third guess was back to the indexes + explain statement:
No indexes on the table gave me:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE SensorData ALL NULL NULL NULL NULL 853 Using where

Index on datetime gave me:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE SensorData ALL NULL NULL NULL NULL 1075 Using where

=> the same result so back to the drawing board

Then i added an idex on Datetime and SensorID:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

=> bingo the optimizer found the index and my qry was fast again

from the mysql optimization documents:

Select tables optimized away:
The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index

http://dev.mysql.com/doc/refman/5.0/en/ ... nformation

[nobbit]

Well done