Home Monitoring Upgrade (Part 2) – HSQLDB to MySQL

As mentioned in my previous post (see http://blog.v-s-f.co.uk/2017/04/home-monitoring-upgrade/), the first task I have is to migrate from HSQLDB to MySQL.

Because the system logs data every minute while I have power in the house and I want to minimise downtime when I actually have a fully working upgrade, I’ve experimented with a copy of the live HSQL database.

Once I’d copied it from my server to my laptop I then attempted to view the data in notepad++ – yeah, not a particularly smart move! NP++ cannot handle files over about 100M. It also turns out (having more’d the .data file) that the data is not in a readable state. I had a performance issue with a select query a long time back and changed the table to cached.

So, luckily at work a colleague had introduced us all to a great database tool called SQL Workbench. It can work with most databases and unlike SQuirreL, it doesn’t crash when looking at the work DB2 database.

Using SQL Workbench, I’ve then created a script file which creates the new consolidated table and loads data from the old tables in to the new + drops the old tables. The end result is a 122,398KB HSQLDB script file which is human readable.

Next step was to get MySQL running on my server. Instead of installing it directly though, there’s a Docker image available.

My first few attempts at inserting the data from the HSQLDB file in to the MySQL database were less than impressive! One of the attempts had the server running flat out (100% cpu) for over an hour when I finally decided that it was probably not going to complete the import this year and nuked it!

So having learnt a few lessons about not using single row inserts(!), but batching them in to multi-row inserts of 100,000 and a few MySQL deafult parameter increases (although I’m not sure if these are necessary as the batch inserts seemed to make most difference), I was finally able to import the data. It still took a few minutes from running the MySQL container to it being available – but that’s significantly better than running for hours importing the data!!

Now that I have the necessary scripts and knowledge to migrate the data, the next part is re-writing the application that receives the Arduino data, uploads to PVOutput and serves the hot water display Arduino.

Elasticsearch, Logstash and Kibana (E.L.K.) on Docker – Part 3 Kibana

This is the final part of setting up Elasticsearch, Logstash and Kibana using the official Docker Hub images. If you haven’t already read Part 1 Logstash or Part 2 Elasticsearch, it might be good to read them first.

So far I have a running Logstash sending messages to Elasticsearch both of which are running in separate Docker containers and now I’m going to add Kibana. Kibana adds the graphical UI that enables you to visualise, create dashboards and search for messages etc.

Kibana talks to Elasticsearch to query the data, so for our Docker run statement, we need to link the kibana instance to the elasticsearch-node. For consistency, the Kibana instance is given the name kibana-node and it’s given a parameter to talk to the elasticsearch-node via a fully qualified search url.

docker run --link elasticsearch-node:elasticsearch-node --name kibana-node -p 5601:5601 -d -e ELASTICSEARCH_URL=http://elasticsearch-node:9200 kibana

When you first log into Kibana, it will ask for an index to be created. This bit did catch me out as you can’t create an index until Elasticsearch has received a few events. Once it has, it’s as simple as giving it a name and selecting the right date field, then clicking create.

Clicking on the Discover menu will then allow you to see the rececent events and start creating new queries for turning into dashboard widgets.


I haven’t yet had enough time to create any fancy dashboards to give an example as it’s a little different to the tool I’m more familiar with (Splunk).

The last point to mention is that all of the examples so far have not saved data other than to the Docker image. Therefore if the Docker image is removed, so is all your historic data! To persist the data, I’d suggest having a look at Docker volumes – but since I haven’t tried it yet, can’t guarantee it is the right answer!

That concludes my mini-series on the subject of Elasticsearch, Logstash and Kibana (E.L.K.) on Docker 🙂

Elasticsearch, Logstash and Kibana (E.L.K.) on Docker – Part 2 Elasticsearch

It’s worth reading Part 1 Logstash first.

So, today I had a chance to try out Elasticsearch on docker and it was semi easy to get it to work… the trickiest part was linking the Logstash and Kibana instances with the Elasticsearch instance. The trick is to name everything!

So here’s the command to run Elasticsearch, note I’ve given it a name of elasticsearch-node.

docker run -d --name elasticsearch-node elasticsearch

In order to then link Logstash to the Elasticsearch node, we need to change the command used to run Logstash from this…

docker run -p 13456:9999 -it --rm -v "$PWD":/config-dir logstash -f /config-dir/logstash.conf

To this…

docker run -p 13456:9999 -d -it -v "$PWD":/config-dir --link elasticsearch-node:elasticsearch-node --name logstash-node logstash -f /config-dir/logstash.conf

The differences being that the Logstash image now has a name of logstash-node when it’s run and it links to the Elasticsearch node via the name and identical alias.

The Logstash config file has also been changed to reference the Elasticsearch node as shown below.

input {
  tcp {
    port => 9999
    codec => line

filter {
  kv {
    source => "message"
    recursive => "true"

output {
  stdout {codec => rubydebug}
  elasticsearch {
    hosts => ["elasticsearch-node"]

Coming up next… Kibana on Docker!