I’ve been monitoring stats from my meter, weather and hotwater tank for over two years now (see http://blog.v-s-f.co.uk/2015/04/home-monitoring-home-made-reborn/) and the application now needs an upgrade.
I now want to log more data from the weather station (temperature and humidity). This should be as simple as adding two new columns to the HSQLDB, changing the application to write in to the two new fields and adding two new fields to the service definition, but it’s not quite that straight forward…
The old app uses an out dated version of Mule on Tomcat in Docker and it’s far too heavy weight for what it needs to be. Therefore it’s time to give it a revamp.
It’s also occured to me recently that instead of storing the data in five separate tables (one for generation, upload info, hotwater, meter and weather data), why not store it in one table. This saves a significant amount of space as there are four less records per minute and it makes adding new columns for additional data sources relatively quick. The HSQLDB that I’ve been using for a while now is over 400M!
So the first task, which is possibly the biggest, is to migrate the data from the five tables in HSQLDB to a single table and then stop using HSQLDB and migrate to MySQL. Why MySQL – it’s actually quite a performant database, it’s free and easy to get running.