Influx retention policy and remote storage location

[bkenobi]

I am working on a flow that takes data from MQTT (mostly rtl_433 sensors) and converts the inputs to meaningful data that then is output to an Influx database. The tutorials I've read have gotten me started and after a couple questions here, I have most of the basic functionality to at least store appropriate data.

My next goal is to figure out retention policies such that I store the high fidelity data for a short time and then down sample to hourly averages for long term storage. I am currently working on the RPi onboard SD while learning, but I believe a better approach would be to have the high fidelity data stored on a USB flash drive and the long term data stored on my RAID server.

I have been reviewing a few tutorials, but not having experience managing databases, I'm not sure if I am on the right track. The following tutorial seems excellent when discussing setting up RP on the Influxdb side:
https://docs.influxdata.com/influxdb/v1.8/guides/downsample_and_retain/

I don't know if what I am describing requires a separate database set up on the Windows server or if it can be handled by the RPi alone via SAMBA or similar. If someone could post a tutorial link or a high level recommendation on how to set things up I can continue my research.

I'm only 1 week in on NR, InfluxDB, and Grafana. These tools are absolutely amazing though!

[TotallyInformation]

@bkenobi

I believe a better approach would be to have the high fidelity data stored on a USB flash drive

Be careful there, not all flash drives have wear levelling (indeed, not all SD cards do either). In fact I would say that it is better to use a decent quality SD Card. Flash drives, especially cheap ones are notoriously unreliable.

@bkenobi

I have been reviewing a few tutorials

There is a thread from a month or so back where I gave some detailed examples of what you are trying to do for the retention policy side.

@bkenobi

I don't know if what I am describing requires a separate database set up on the Windows server or if it can be handled by the RPi alone via SAMBA or similar.

I would avoid using SAMBA for this if you can. It isn't really designed for this kind of work. Since InfluxDB uses HTTP links, I would stick with that even though it requires you to run InfluxDB on the Windows server as well as the Pi.

@bkenobi

I'm only 1 week in on NR, InfluxDB, and Grafana

Honestly, I would keep it simple for now. Even a modest Pi can cope with a lot of data in InfluxDB. My Pi3 has 296MiB of data in it at the moment, several years of environmental and system performance data.

[Colin]

@bkenobi

have the high fidelity data stored on a USB flash drive and the long term data stored on my RAID server.

I am not sure whether the usual downsampling technique will allow the downsampled data to go to a different system. Normally it is all within one influxdb server. If you don't want it on the SD card then you could put a USB drive (hard or SSD) on the Pi and put it all there.

Since you are starting from scratch you might want to consider going straight to Influx 2.0 as that is the way Influx is going. The node-red influx nodes support Influx 2.0 now. A problem might be that you will have more difficulty finding anyone that will help. Several of us would be learning with you I think. The advantage of starting with 2.0 is that if you go for 1.8 you will likely have to migrate the data at some point which may not be trivial.

[bkenobi]

@TotallyInformation assume you mean this thread:

Need more detailed information on influxdb

Hi, I want to runn all my sensor values and things into an influxdb. I have looked into the documentation of node-red-contrib-influxdb and that left many questiuons open: I want to automatically retain all measurements for 3 months, retain an hourly average/median for 3 more months and Store Weekly averages indefinitely. What would I have to do to make this as easy as possible? Would you rather run influxdb on you raspberry or an old laptop? Would you rather create "tables" per measured val…

I was thinking that it would be better to put the high fidelity/short term data on a local USB. The primary reason for local rather than on the server is that the server being RAID5 with 4 conventional disc drives is the startup time. If I write to the the server once a day, it's no problem since the time it takes to spin each up in series isn't a big issue. But if I write every bit of sensor data to the server it will keep the drives spinning all the time and cause earlier failure. My intent was to have the current day/week stored locally and then uploaded to the server for long term storage. Keeping it local would be fine, but a backup would be preferred. If I hammer a USB of any kind, the media will eventually fail. If I lose the high fidelity data I'm out a day or week which is not the end of the world. If I lose the full set, I'd be starting over.

@Colin I looked at a couple descriptions of Influx 2 and it appears it's still considered Alpha though it is available to anyone. I don't have a problem being an early adopter so long as it works, but without much database background, that may be a bit of a hurdle if there's no support on this or their own forum. I'll do some more reading.

[TotallyInformation]

@bkenobi

cause earlier failure

Urm! I think you will find that server disks are designed for that A lightly loaded server disk will probably keep going for well over a decade. My NAS drives (WD Red) have done 7 years 24x7 and they show no signs of wear.

@bkenobi

If I hammer a USB of any kind, the media will eventually fail. If I lose the high fidelity data I'm out a day or week which is not the end of the world. If I lose the full set, I'd be starting over.

You are certainly over-thinking things. Server drives are designed to be always on. USB Sticks are designed for occasional use. But either way, my SD Cards have lasted for years and get constantly hammered. We have covered this many times in the forum.

[Colin]

@bkenobi

If I lose the full set, I'd be starting over.

However you store it, if it is important data, then you must have a routine backup schedule. Whatever device you use it may fail at any time without warning.

[bkenobi]

@Colin I've been thinking about your comment and realized that the best approach is probably to have the database stored local to the RPi on some form of media and then to back it up to a server for safe keeping. If the RPi storage goes bad, I'd restore the whole database from the backed up location. This would probably be easier and within the scope of how Influx is designed anyway. I will have to consider what medium to use for storage further. I wasn't suggesting that the USB drive would be better/worse than the RPi SD card so much as I was suggesting that if the SD were used and it failed the whole setup is down whereas if I use an external device (USB stick, USB->SD adapter, SSD, etc) I would only lose the data on that device. That's kind of a wash though since I intend on using rpi-clone regularly/weekly anyway.