Choosing a TSDB for one-off smart-home installation

Question

I'm building a one-off smart-home data collection box. It's expected to run on a raspberry-pi-class machine (~1G RAM), handling about 200K data points per day (each a 64-bit int). We've been working with vanilla MySQL, but performance is starting to crumble, especially for queries on the number of entries in a given time interval.

As I understand it, this is basically exactly what time-series databases are designed for. If anything, the unusual thing about my situation is that the volume is relatively low, and so is the amount of RAM available.

A quick look at Wikipedia suggests OpenTSDB, InfluxDB, and possibly BlueFlood. OpenTSDB suggests 4G of RAM, though that may be for high-volume settings. InfluxDB actually mentions sensor readings, but I can't find a lot of information on what kind of resources are required.

Okay, so here's my actual question: are there obvious red flags that would make any of these systems inappropriate for the project I describe?

I realize that this is an invitation to flame, so I'm counting on folks to keep it on the bright and helpful side. Many thanks in advance!

Hamitic

performance is starting to crumble, especially for queries on the number of entries in a given time sounds more like insufficient/inappropriate indexes or poorly-written (non-sargable) queries, to me. – Zucchetto

Not being familiar with raspberry-pi, I wonder what Linux distributions are being supported. Is there a list of distros you could share? – Josejosee

Here's the list: raspberrypi.org/downloads – Josejosee

I'd love to hear about dumb mysql mistakes I'm making; in this case, the query is simply "SELECT COUNT(*) FROM sensorevents WHERE device=? AND timestamp >= ? AND timestamp < ?". mysqldump shows a btree index on the timestamp. There are about 9 million entries in the table. Queries where the start and end timestamps are separated by a day take ~1 second to complete; I'm feeling like this is way way too long. Any help appreciated! – Hamitic

If you restore the dump on another machine with regular rotational disks, do you still observe slow query response time? If you're set on relational db, why not sqllite? – Josejosee

Answer 1

InfluxDB should be fine with 1 GB RAM at that volume. Embedded sensors and low-power devices like Raspberry Pi's are definitely a core use case, although we haven't done much testing with the latest betas beyond compiling on ARM.

InfluxDB 0.9.0 was just released, and 0.9.x should be available in our Hosted environment in a few weeks. The low end instances have 1 GB RAM and 1 CPU equivalent, so they are a reasonable proxy for your Pi performance, and the free trial lasts two weeks.

If you have more specific questions, please reach out to us at [email protected] or [email protected] and we'll see hwo we can help.

Studner

Be aware that influxdb does not support 32bit platforms well... I've just ran into this problem and influx is not willing to support 32bit with larger database... github.com/influxdata/influxdb/issues/10486 – Pulverable

Answer 2

Try VictoriaMetrics. It should run on systems with low RAM such as Raspberry Pi. See these instructions on how to build it for ARM.

VictoriaMetrics has the following additional benefits for small systems:

• It is easy to configure and maintain since it has zero external dependencies and all the configuration is done via a few command-line flags.
• It is optimized for low CPU usage and low persistent storage IO usage.
• It compresses data well, so it uses small amounts of persistent storage space comparing to other solutions.

Deandre

Answer 3

Did you try with OpenTSDB. We are using OpenTSDB for almost 150 houses to collect smart meter data where data is collected every 10 minutes. i.e is a lot of data points in one day. But we haven't tested it in Raspberry pi. For Raspberry pi OpenTSDB might be quite heavy since it needs to run webserver, HBase and Java. Just for suggestions. You can use Raspberry pi as collecting hub for smart home and send the data from Raspberry pi to server and store all the points in the server. Later in the server you can do whatever you want like aggregation, or performing statistical analysis etc. And then you can send results back to the smart hub.

Yaws

The raspberry pi is the server :). There may not be an external network connection. Thanks for your answer! – Hamitic

Answer 4

ATSD supports ARM architecture and can be installed on a Raspberry Pi 2 to store sensor data. Currently, Ubuntu or Debian OS is required. Be sure that the device has at least 1 GB of RAM and an SD card with high write speed (60mb/s or more). The size of the SD card depends on how much data you want to store and for how long, we recommend at least 16GB, you should plan ahead. Backup batter power is also recommended, to protect against crashes and ungraceful shutdowns.

Here you can find an in-depth guide on setting up a temperature/humidity sensor paired with an Arduino device. Using the guide you will be able to stream the sensor data into ATSD using MQTT or TCP protocol. Open-source sketches are included.

Burchell