Some quick notes on the MySQL database I structured to capture key IoT events in my home. As per a previous post, I showed that the domain of IoT does not only include a sensor, and an actor based on the sensor, (like Light LUX level is low, switch on a light..), but “analytics” and “big-data” as well. These I will explain later, in context of examples in my home. The data tracked in the database I use in my front-end application, like graphs, or hot water consumed this week compared to last week, etc. But later on, to analyse- for instance, the relation between hot water used, and the outside temperature, or when hot water is used per hour category, vs when the geyser is on or off.
After lots of thinking and reading, I decided to go with a relational database, the other options were to go with a 'no-sql’ database, which are often used in parallel - large big data implementations. And all the options I had, runs on Raspberry Pi. I had some previous experience with using MySQL, this gave me one less thing to learn, as the learning curve with all the other things I had to get to work were steep. I have 2 months of data in the table capturing the events now, more than 22500 events captured, and the whole database is still under 1 MB.
The design I came up with aligns with the software I use on the ESP8266’s to which the sensors are attached, called ESPEasy, at http://www.esp8266.nu.
Future improvements? This database is stored on the Raspberry Pi's SD card, and this will become an issue, as the amount of writes to the tables will eventually corrupt the SD card, it was not built for many frequent writes. A good plan to this, which I found on http://tech.scargill.net/home-control-2016/ is to write the events of a day into a table, mounted in memory, and at tend of day to write the events into table on SD Card, thus limiting failure to once a day. Then, the SD Card table can be written to cloud or other. Right now, I only backup once a day, but this strategy will improve the potential issues later.
Post edit Jul'16 - implemented above now, capturing the day's data in a table SysTaskVal2day - which runs in memory, and 1 min past midnight I summarise the data into hourly buckets, then move it into the sensor table, SysTaskVal, all in one write, thereby saving SD Card writes !
Data is captured in three tables, two for setup, the last for the events. ESP devices are registered in System, the tasks that can be performed by sensors on these ’Systems’ are captured in Tasks, and the sensor Events are inserted the table SysTaskVal. In a future blog entry, more on what ‘analytics’ things I can do.
See the tables I use below. Let me know what you think of this !
Thus, row 2 in this table was captured on ESP device 1, "ESP01", with the sensor 1, which is Air Temperature, for a time stamp on the 28th of March, and the value was 24 degrees C.
(Post originally posted on IoTPlay Blogspot on 26 May 2016)