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.
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.
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.
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.
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.