Gateway log engine upgrade

So far the Moteino Framework Gateway server application has been using neDB for storing node metadata/settings, and also to store historical metric data logs. Since neDB keeps all data in memory, it gets slower over time as data grows. Metrics with frequent updates could take 10sec to load 1 week worth of data (a few thousand data points). Visually it’s completely pointless to load 10K points on a graph that’s 1000px wide (you can’t see 10 points in 1 screen pixel). So a lot of wasted time and performance to search/move/display all that “invisible” data.

I researched a few options to improve storage/graph loading time. The list is below and if you want more details about each and about this effort see this page.

  • I tried implementing a binary search for neDB to get the graph data, but it wasn’t much better, the core engine of neDB is too slow for this purpose
  • I searched online other databases to store timestamped sensor data. There are expensive enterprise engines and also open source toolchains like Apache Hadoop – this looks very nice but it’s complex and a steep learning curve (Pi support?).
  • There’s mongoDB which is wonderfully capable and similar to neDB (or neDB similar to mongo rather) but still not officially supported on the Pi so it’s a no-go for now.
  • There’s Timestore – a C++ app by Mike Stirling. This is a great standalone engine but I have a requirement: I cannot assume when a data point comes in to be stored, it can be highly variable and I can have a thousand points in 1 hour, then nothing for a week. So I need to store the timestamp.
  • The guys over at OpenEnergyMonitor have done a lot of work based on Timestore. They had the same problems to solve as I did. Their EmonCMS app used MySQL to log sensor data, which was increasingly slow as tables got very deep. So they created PHP storage variations based off Timestore to allow logging variable interval data. Thanks guys for sharing your work and research results!

After considering all these options, I decided to build my own node.js storage engine based on bits and pieces from Timestore and the variable interval engine from OEM. The main reason is that it needed to be all in node.js. Doing it in PHP and then hitting a PHP script from a node.js socket app to query/post data felt very wrong (not sure how performant either).

The result is a much faster binary storage engine that can query and search and aggregate a month of data in about 500ms (on a Pi B Rev1 256RAM!). That’s more than a magnitude faster than neDB. However neDB will still be used to store node metadata, it’s perfect for this purpose and will be very fast and use very little memory.

The Gateway guide has been refactored to include this new engine details along with a few other changes and features (mostly UI):NodeView2

  • vastly improve graph loading times regardless of zoom and span viewed
  • add nice customizable tooltips to data points (customize in metrics.js)
  • add customizable legends on the graps (customize in metrics.js)
  • using upstart for starting/stopping/restarting the gateway.js script. This replaces the init.d way to start it and also ensures the gateway script will be respawned in case it crashes
  • add Font-awesome icon pack (over 580 awesome icons to use with jQuery-mobile) and use some of these new icons for some buttons
  • toggling node/metric visibility and metric graphing is now done via buttons instead of sliders which were not working very well on mobile devices. This also removes clutter on the node and metric pages

Upgrading from old neDB gatewayLog.db data

If you’ve used my Gateway interface before, you probably have log data stored as JSON in gatewayLog.js. No problem. Just save your existing gatewayLog.db, upgrade your Pi to the latest image, copy the gatewayLog.db into /home/pi/moteino, then run this upgrade script in the same directory – it parses your gatewayLog.db data and puts it into binary files in a db subdirectory, ready for the new gateway.js script to use. Otherwise gateway.js will just create logs from scratch as data comes in.

UpgradeGateway

It was a lot of work to get this right, I’ve been tweaking all this for the past few weeks. It’s free to use non-commercially, I hope you like it, I think it’s a major improvement overall, both back-end server and front-end UI. If I missed something in the guide or there’s any bug let me know. There’s more good stuff coming, just not enough time to document and blog it all. Cheers!

4 thoughts on “Gateway log engine upgrade

  1. Had you considered OpenTSB, Graphite, InfluxDB, or Prometheus? They’re built to handle time series data and aggregate queries.

    • There are many many options, just a sample list here. I reviewed some of these. My needs are quite basic. I decided to just do my own thing because 1) I could 2) I wanted to control it the way I wanted. The result is just a few hundred lines of code, that are really simple to use, require no additional packages or software and has a very small CPU and memory footprint.

  2. This sounds like an awesome improvement Felix! I can’t wait to try it out, will get the upgrade done this weekend 🙂 Thank you for all the excellent work.

  3. Got the Pi updated to the latest image today, and runs great! Graphs look good and load fast as you said. The data point detail is great. Thanks Felix!!

Leave a Reply

Your email address will not be published. Required fields are marked *