Database

Our Home Control System (HCS) needs a database to store persistent data and in some rare cases to allow exchange information between processes. We started out with a simple Java class, with the intention of using this as a wrapper to a commercial or open-source database. We rapidly realised that all we actually needed was a simple way or reading and writing name-value pairs. The kind of data we store in our database are things like house status, alarm status, network status, etc. and generally these don't result in large volumes of reads or writes.

To enable initial testing and development, we wrote a simple database Java class that stores data in text files. The text files are were also written as Javascript code, so that they can be directly included in web pages on our HCS web interface. To make the reads and writes as quick as possible, these files are stored on a USB Flash drive. The advantage of this approach is that we can also test scenarios by editing these text files manually. This approach has been 100% reliable for more than eight years, so (following the engineering first law) we left it alone. There have been no issues with file locking either. It's very simple and it does the job required.

The original Java source code is here for those interested. When we find this approach no longer meets our needs, we will deploy a proper database onto our Home Control System (HCS) server. Something like MySQL is the most likely candidate but, this will probably require another server upgrade.

Updates

September 2012

We had a little time spare so we revisited this code, to see if we could make it more efficient and improve the performance. We didn't really need to do this as the code has worked perfectly over many years. We have made the following changes though:

  • We have now added some code to cache the name-value pairs in memory, so database reads are generally much quicker.
  • When new name-value pairs are written to the database, we now also check to see if their are any changes to the cached values and only then write them to the cache and to disk.
  • We have improved the logging of errors and added more debug logging.
  • We now just write the values into text files, as we don't use the JavaScript feature any more.

The new Java source code is here.

We have now also added some counters to capture statistics on database usage each day. This led us to add code to not wite captured temperature changes to the disk (but they are cached), as we have no need to do this. A lot of the database reads are down to the controllers and their associated conditions.

October 2013

A few stats ...

  • Our database is storing approximately 30 name-value pairs now.
  • In a typical working day, the number of DB reads is ~400,000 of which none may be from disk.
  • In a typical working day, there will be ~250 database writes of which ~240 will be written to disk.

Our caching algorithm is working amazingly well and reducing the number of reads from the disk file system by over 150,000 per day.

Share ...
We are on ...
Facebook Twitter
YouTube Flickr Follow us on Pinterest