Energy monitoring using Raspberry Pi, looking for advice regarding data storage

[JeanF]

Hello,

I would be glad to hear your suggestions about data storage with a raspberry pi, either locally or on a server/some sort of cloud 

I would like to log data from two sources, both having serial ports, and to store them somewhere with timestamps. I'd like to be able to read the data later on via remote access. Because that's what I have on hand and because the elegance of it is not hugely important (it's a one-off, for hobby purposes), I plan to use a Raspberry Pi in addition to two UART to USB boards, and connect the RasPi to the router in the house.

There is not much to store, only a handful of values (voltage, current, active power, apparent power, measured at one single place for the whole house) every few seconds. This will have to last only for the winter so even if I go overkill and log every second for 4 months, we are talking about a few hundreds of megabytes at most, if I didn't completely screw up my estimation. 

I don't really need fancy displays or web interfaces, I would just need to come back after the winter to analyze data (Excel would be fine). I heard that logging directly to SD card could have reliability issues with the SD card wearing out, and that making the card read-only could help. What do you think ?

I saw some blog posts with guys doing it on their own in PHP, storing data in a MySQL database, but sadly I think I don't have the experience required to dwelve into that... I heard (in no particular order) about EmonCMS, Node Red, InfluxDB, ThingSpeak, Domoticz, Jeedom ; but I don't have any experience with any and I am a little bit confused.

What would be, in your opinion, the easiest way to store the values ? Any advice or any suggestion will be appreciated. Thank you !

PS : the house may encounter rare power outages. There will be a small UPS for the Raspberry Pi, but not for the router; if I choose to store values on a server over the internet, there must be some sort of buffer to keep the values and to upload them later.

[JeanF]

A little bit more background about this project, for those interested ; it is very optional :

At my parents' house, they have electric heating (The french government heavily promoted it in the 70's to justify investments in nuclear power). They live in a small, yet quite touristic place in the seaside. In summer, no problem ; but during the winter, the local substation is struggling to cope with all the people turning on the heaters for the holidays in their vacation homes.

As you all know, P is proportional to V squared so it doesn't take much of a voltage drop to drastically reduce the heating capabilities in the house. Add that to a poor insulation job, and all in all the temperature can quickly become uncomfortable (in addition to very dim lighting...). The national energy provider is required to beef up his wiring or tweak the transformer taps if I can prove that the supplied voltage goes below 230V -10% tolerance = 207 volts. If I call and tell them the problem, they will (after a fair bit of negotiations) send someone to check the voltage at the "Place of Delivery". But they are smart and will come on a warm sunny day when the voltage is perfectly fine ; that way they can get away without doing anything (and they will charge me for the trip).

So the idea is to log the network voltage during the winter and then see if (and when) voltage drops occur. That way I will know if there is a real problem or if my parents are just getting sensitive to the cold  and if I have to negotiate with the power company, I will know when to make them come and check.

[georges80]

A simple/cheap serial (uart) logger is something like the Openlog design - lots on ebay/china units etc.

Logs to a uSD card and can handle FAT32 so gigabyte sized files. Pretty cheap, $12 US on prime and in the $5 range for China ebay. Super easy to use. It takes care of creating the filename and writing/logging data. Just edit the config.txt to set baud rate etc. Easy.

https://github.com/sparkfun/OpenLog

Flash doesn't wear out unless you are rewriting a LOT of data to the same page, for a logger this isn't going to be an issue.

cheers,
george.

[hamster_nz]

If you have internet access, and you want a low fuss cloud data solution, have a look at ThingSpeak - here is one of my feeds: https://thingspeak.com/channels/354869

All you will need to do on the Pi is have the program monitoring the serial data perform a web request - e.g run a system command to run "curl https://api.thingspeak.com/update?api_key=[your key]&field1=[your value]&field2=[your value]".

Free accounts are limited to one message every 15 seconds. I would suggest that you log "min", "average" and "max" values for each minute. And also add a DHT11 sensor so you can do temperature and humidity too :-)

PM me if you want a hand.

[capt bullshot]

Yes, SD cards can be quite unreliable, especially when data gets written often to them.
You could connect an external SSD with an USB to SATA converter (or just an USB HDD) to the Raspberry and use this to store the data.

For the data storage software, take a look at RRDtool: https://oss.oetiker.ch/rrdtool/

[hamster_nz]

Some advice I have been given about using SD cards:

- Allocate a really big file (e.g. 1GB)

- Write it once, with non-zero data using a large block size.

- Then buffer your data till you are writing in 1kB or larger chunks.

This causes all the blocks to be allocated - avoid lots of small metadata updates as new blocks are added to the file. It also avoids most of the re-writes of blocks that occur if you open the file, write a line, then close the file.

[HoracioDos]

Hello.
You can sync files or folders to another machine with rsync. If you don't want to send data to the cloud but you still want to have a dashboard. I would install influxdb (time series database) and grafana (Data Visualization & Monitoring).
You can send data to influxdb with curl or use your own script (python or other). Then you can create professional dashboards with grafana. (Everything on premises)

[NivagSwerdna]

I have used https://openenergymonitor.org/ which is essentially a RPi, MQTT, Mosquitto and some back end stuff.

[JeanF]

Thank you for all your comments and suggestions, I really appreciate it

georges80, I didn't know this openlog module existed. It seems to be a big thing for quadcopter users. I like the concept and will keep it in mind for future applications. In this particular case though, I have the impression that it may not be the best choice, because:
    - my two data sources work at two different baud rates (which I cannot change), so I would need two Openlogs and manually merge the two files afterwards
    - the data coming in is not timestamped so I would need some sort of microcontroller + RTC to add it to the stream. This kind of moves the whole thing away from its initial simplicity...
    - one of the two sources (a Chinese voltmeter module, PZEM-004T) doesn't spit out data by default, I have to send a command each time I want to receive a value. I don't think I can easily do that with an Openlog module without modifying the firmware, which... well... I'd rather avoid
Nevertheless, thank you!

hamster_nz, capt bullshot, HoracioDos, thank you very much for your tips, I'll look into that in more detail.

NivagSwerdna, great! How did you like it?

[ljwinkler]

Hi,

I'm currently working on a wireless weather station (https://www.eevblog.com/forum/projects/solar-powered-wireless-weather-station/) where I use RPi as a storage.
In a nutshell: I have a bunch of sensors with RFM69W that transmit data into a single receiver (Arduino) that sends data over USB (USB2serial adapter).
On RPi I have a python script running that opens the serial port (it checks PID/UID to connect to the right tty in a case of a reboot, USB change, etc.), validates the data and inserts it into a MySQL database.

In your case you'd just need to open two different connections (you can specify baud rate independently).

Right now I'm using a client on my desktop to read data from the database, I have created few SQL views that give me the most recent data per sensor (temp, humidity, battery level...), data from midnight until now, historic data, etc.

Speaking about reading the data... how are you going to present it?
Are you going to just dump the numbers into an Excel and draw a graph of voltage in a time?

This is something I'm struggling right now
I have the data but have no idea how to present it   I thought about creating a single webpage with current temperatures and then maybe few more pages with some comparisons, charts, etc...  but I'd like to have this to be a fancy dynamic dashboard, not just fixed and hardcoded date ranges.

[NivagSwerdna]

NivagSwerdna, great! How did you like it?

It worked.  They have recently changed to a charging model for their hosted solution but the software is opensource so you can run your own instance if you don't want to pay.  I used the hardware as a backbone for publishing of temperature from many sensors.  Works great

[mariush]

You could use one of those FRAM memory chips to "cache"... store a bunch of measurements, they have very high endurance and all that good stuff.

For example, get a 2mbit or 4 mbit 8pin memory chip and store and you have 256-512 KB of "safe" memory.

Here's a suggestion link: https://www.digikey.com/product-detail/en/fujitsu-electronics-america-inc/MB85RS2MTAPH-G-JNE2/865-1285-ND/7604212

Assuming your data is something like 64-128 bytes per measurement and you make a measurement every 5 seconds, then you have 12 measurements per minute and  720 measurements per hour ... at 128 bytes per measurement that's 92160 bytes for one hour or less than 100 KB ... you could log 2h+ on a chip.  or if you use 30m chunks of time, then at 128 bytes per measurement you can squeeze ~ 2.5 hours worth of logging before you have to overwrite stuff.

(optionally) Your measurements are most likely going to be highly compressible, since the values won't fluctuate so much, so once an hour you could use zlib or zip or whatever to quickly compress those ~ 100KB of data (30m or 1h worth of data) into something smaller and dump it to some media ... for example save it to 2018/12/25/20181225_1400.csv.zip or whichever way you like it.
you could plug two 2 usb sticks and copy each file to both usb sticks (for redundancy) and if you want you could also upload this <100 KB to a remote website or to your local network.

You could also use a SD card and since you're only going to write once every 30m or 1h, i guess you won't have to worry so much about endurance, and the SD card will probably also be happier if you write to it in 512 byte or 4 KB chunks (basically store big chunks at a time instead of a few hundred bytes every few seconds)

if you're really paranoid, or if you want it to be fully offline, you could get this compressed data (the data packet with one hour worth of measurements), encode it in base64 or something and then send it to a dot matrix printer and have it printed on those long continuous computer paper (~2400 sheets per box ...  assuming one page per hour, that's 100 days worth of logging)
If needed you can then simply scan back the paper into a computer and use some OCR software to convert back to data.
I was thinking thermal printer since they're cheaper and you don't have to worry about ink but I'm not so sure about long spools of thermal paper and how much you can fit on a "receipt" width style paper 


When you get the data, you can simply extract all the CSV files and you mass import them into a database (mysql for example) or you can join all csv files in a big csv file and import that into excel or libreoffice calc or whatever

I'd stick to CSV files or TSV (tab separated values) since they're humanly readable, easy to compress, safe to use padding to have each record of equal length if you so desire

[JeanF]

ljwinkler, thank you for your tips (good job with the fixed USB peripheral name, I plan to use this trick as well), that's a nice project you have here. Concerning data visualization, my needs are pretty basic I was indeed planning to dump a CSV file into Excel, because as I mentioned the primary goal is just to check if (and when) the voltage goes under a predefined value, and when that happens I may check the other values, such as instantaneous current, in order to estimate whether the voltage drop is primarily caused by a heavy load in my parents' house or in other houses around.
I don't know have experience in creating nice dashboards. HoracioDos suggested Grafana and it's not the first time I hear about it, I also saw it being used here. But I don't know if it could fulfil your needs about interactivity and not-hardcoded ranges, I haven't tried it. Also this guy used PHP/SQLite and Google Charts to display his curves (in French)

I tried Thingspeak, it is very easy to use indeed. I just created an account, used curl to send test values and that's it. At first glance, the included visualisations seem pretty basic but I'm not too worried as there is a CSV export button available.

However, I'm curious to see what you finally choose to use for visualisation because in version 2 of this project I may need that as well. Currently, my parents have a Geo Minim+ energy "monitor" (just a simple LCD display really) to help them manage their electricity consumption (mainly because there are more than 17 kW of heaters in the house and they have a 12 kVA contract, so obviously they can't all be turned on AND at full throttle at the same time), but this meter is not accurate because it only monitors current; not voltage nor power factor. So the readings are a fair bit off, particularly with large voltage swings. If I manage to get a Raspberry Pi to grab all the real values from the official energy meter, I could as well build another little device to display them instead of the Minim+. I was thinking maybe an old Android phone connected to wifi and displaying a webpage hosted on the Raspberry Pi. Otherwise, an Arduino or another Raspi Zero with a small Oled display could also work. All of that isn't going to happen soon, because my HTML skills are near zero for now.

If anyone has suggestions for that matter, I am interested as well!


mariush, thank you very much for this detailed post. I really appreciate your suggestions. I don't have any experience with that kind of external memory chips. I assume they could be useful additions to standalone microcontroller projects as well. In the rest of your post you talk about USB ports, SD cards etc so I assume that the context of your reply is: using a Raspberry Pi to run the show, and not going completely custom with any µC and doing all the firmware myself. Am I correct? But then, what is the advantage of using one of those FRAM chips instead of the onboard RAM, before sending to USB or to a remote server?

Thank you for your idea of printing, I won't go into this level of redundancy but it is a fascinating idea, I've never heard of something like that. I will also see if I can find any data concerning the reliability of USB sticks vs. SD cards. Anyway, nice addition to have 2 of them. I will also keep the idea of data compression if needed.

Again, thank you everyone for your support
cheers

[mariush]

I was thinking things from a reliability point of view and as means to reduce the number of writes to media which wears out ...

If you don't have a UPS or something that would hold your pi up for a significant amount of time, then whatever you have in your pi's ram would be lost on power failure. That FRAM memory has a very high endurance and the writes complete practically instantly, while your pi may cache writes to the flash media so if you take one measurement every second you may lose a measurement or two.

If there's a power loss, at next reboot your code can simply scan the fram chip and determine the last logged entry and resume storing measurements from that point and once you have enough measurements saved, you can get them and zip them and save them to flash or some removable media or send them through network somewhere and then reset the fram chip (just erase the data)

if you don't care about small power failures or if you can get a tiny UPS to keep your pi running for a few hours (or basically a 12v lead acid battery that's always charging from a mains charger would have enough charge to keep your pi happy for probably even a day) , then yes you could just hold the measurements in the pi's ram.
 
I have to admit i was also thinking how i would be doing this using a cheaper microcontroller with less ram available, and using something like miniz (zlib ,deflate algorithms) to compress data before you send to a SD card... forgetting you actually have the pi.

[sokoloff]

Currently, my parents have a Geo Minim+ energy "monitor" (just a simple LCD display really) to help them manage their electricity consumption (mainly because there are more than 17 kW of heaters in the house and they have a 12 kVA contract, so obviously they can't all be turned on AND at full throttle at the same time), but this meter is not accurate because it only monitors current; not voltage nor power factor. So the readings are a fair bit off, particularly with large voltage swings.

If the heaters are resistance heaters, you can model them as having a power factor of 1. I would be surprised if your voltage fluctuated more than 10% total swing, so you should be able to get "engineering close" by just monitoring current if that means the difference between successfully completing the project versus not.

[ljwinkler]

JeanF, here is the python code responsible for detecting the port (the USB2Serial adapter I use has ID of 2341):

Code: [Select]

import serial
import serial.tools.list_ports

while True:
  ports = list(serial.tools.list_ports.comports())
  device = ""
  for p in ports:
    if "2341" in p.hwid:
      device=p.device
  try:
    arduino = serial.Serial(device, 115200)
  except:
    print "Failed to connect to",device

  try:
    while True:
      data = arduino.readline()
      data = data.replace("\r\n","")
      pieces = data.split(",")
      ...

You can use the below to play with the devices:

Code: [Select]

import serial.tools.list_ports
ports = list(serial.tools.list_ports.comports())
for p in ports:
    print p.device, "- hwid:", p.hwid, "-", p.description
    if "0403" in p.hwid:
       port=p.device
       print "This is an Arduino!"
    else:
       print "This is not :)"

Thanks for the info about dashboards, my goal is to have something that is generated locally. I plan to use this station for life without worrying that some company will go bust and my dashboard will stop working

For your energy meter webserver - good idea however having an android phone displaying a page looks like an overkill   Ok, it can be cheap (if you have a spare phone) as newer RPis have WiFi on board, they can act as an access point so the phone can connect to it directly. Ideally you could have an LCD screen (even the official RPi 7") or any other HDMI display to display the page. You can have RPi booting into a desktop environment and displaying its own page in chromium browser.

[GeorgeOfTheJungle]

Save it locally but also copy on a google spreadsheet, there's a REST API:

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate

[mjkuwp]

From AliExpress (china) you can get a power measuring board with serial connection).  It is described as "Free shipping Digital Display AC Voltmeter Ammeter Power Meter Energy Meter Power Meter Serial Communication 100A" and is $26 USD.

https://www.aliexpress.com/item/Free-shipping-Digital-Display-AC-Voltmeter-Ammeter-Power-Meter-Energy-Meter-Power-Meter-Serial-Communication-100A/32855637078.html?spm=2114.search0104.3.15.11493482oDc9bI&ws_ab_test=searchweb0_0,searchweb201602_4_10065_10068_10130_10890_318_10547_319_10546_10548_317_10545_10696_450_10084_10083_10618_452_535_534_10307_533_532_204_10059_10884_323_325_10887_100031_320_321_322_5727818_10103_5727718_448_449,searchweb201603_45,ppcSwitch_0&algo_expid=c60126c5-1223-40aa-a30f-02f3d8987393-2&algo_pvid=c60126c5-1223-40aa-a30f-02f3d8987393

If you go the Raspberry Pi route you could consider writing to USB based flash drives and if you can write to one, two is just as easy so that you have redundancy.  I personally have projects like this and they log to my own webhost account using a php script as the endpoint.  I used to use services but got tired of them adjusting their business models/ownerships/api designs.

Obviously you would want to have the code automatically start upon booting the pi so that it recovers from power outage.

I suggest doing two methods - the first is to send data to a cloud service or your own webhost and the second is to log to a usb flash drive for the backup. 

It is also possible to buy commercial power monitors that would log every instance of a sag in voltage.

The very simplest Idea I can think of is to purchase a multimeter (DMM)  that has a usb based serial connection and log using that, however this would be pretty $$ and also you would have to hack the battery input to give constant power supply to the meter.

[JeanF]

Dear all, thank you very much for your responses.

mariush
thank you for this additional information I will look into miniz. There will be a UPS for the Raspberry Pi + the voltmeter module. I bought several USB powerbanks and tested them all, capacity, hi/lo cut-off voltages, general build quality etc, and I chose one of them which is pretty decent in all regards. Obviously, its output boost converter is powered on at all times, regardless of the 5v input (from the wall adapter) being present or not. Not all of them have this feature. All in all, it looks like a pretty good choice for a small 5v UPS. I read contradictory opinions about keeping Lithium cells fully charged 24/7, but I couldn't find any definitive conclusion so I'll test it myself. And there are commercial units (small UPSes for routers, CCTV cameras etc) that also use 18650 cells so time will tell . If it fails I will revert to lead-acid.

sokoloff
I agree about "engineering close" being enough. There are two distinct goals in this project:
    - checking and logging voltage drops during the winter, and let the power company know for them to take action
    - if and only if the first goal is achieved, maybe add the functionality to somehow display the precise values grabbed from the official power meter; thus upgrading the present system which is using a Geo Minim+ power monitor. That would give similar functionality but better accuracy.
But you are right, I must keep track of what's really important. As always the risk is to try to add so many bells and whistles that the project ends up never finished... Concerning the power factor, you are right. Voltage swings are (by far) the biggest source of error. I have measured less than 190V instead of the nominal 230V from time to time, but a single measurement is less fun than a graph over time, hence the idea of this whole thing

ljwinkler
thank you for your code, it will be put to good use, I will let you know when I succeed BTW in my case yes I have several old phones so using one as a display could work (and probably be cheaper than getting another RPi + external screen) but yeah it's surely a bit overkill

GeorgeOfTheJungle
thank you for the tip about Google Spreadsheets, for sure I wouldn't have thought about that 

mjkuwp
I initially planned to include this in my first post, but it was long enough so I removed it. Anyway, this is the exact module I purchased  . It can be found on multiple places when searching "PZEM-004T". I got mine on BangGood. There is also the -004 version (without T) which has 4 seven segments display as well. Due to higher current needs for the segments, the PSU stage is a bit different and so is the board layout, but it is very similar. Yes a cheap multimeter with serial out (UNI-T UT61D has one for example) could work but would take more space.

[kripton2035]

the same module, with some displays and serial link, and cheaper .
https://www.aliexpress.com/item/AC80-260V-100A-Power-Meter-Monitor-Voltmeter-Ammeter-Energy-Meter-With-Serial-Communication-Free-Shipping-12002352/32430076105.html

[JVR]

Something to keep in mind, if you want to use this data w.r.t the government, is calibration. If they want to be dicks about it, they will tell you that your measurement equipment is uncalibrated, and therefore useless.

If you want to have legitimate data, buy a DMM with a cal certificate, and read the measurements off it. Also, the point of the house that you will be measuring will impact the measurement, but I assume you want to measure at the point of supply.

[JeanF]

Yes, you are right. They will indeed tell me that they don't trust my measurements. Usually, they send someone to your house to install a logger themselves; if they can confirm the problem, they are committed to correcting it, and if they can't, then you have to pay a penalty. The sole goal of this project is to be better informed myself about what's really going on.

Whether they would trust me if I had a calibrated meter is another story. They would probably insist to send a tech anyway; they are notorious for being fussy about the procedure. I can't really blame them for that, it must be a challenge to manage ~30M customers anyway...

Yes, I will be measuring right at the point of supply indeed

As a general question, what are the options to get a cal certificate? (I've never done it). I know that the usual big distributors do it, but I've no idea about the price (often they say "contact us with your enquiry...", I won't waste their time with that at this point, as I'm not really interested). Are there other ways?

[cdev]

I just wanted to say that you should also monitor temperature in the home since it is important. Even if the temperature in that area rarely if ever goes below freezing once in a very great while it may and then you will want to prevent your pipes from freezing, that is very important. (It even freezes solid in San Francisco once in a great while, thats a very maritime climate.)

Climate is less predictable now so since you are setting up this logging capability, best to have it have all the basics since its not very difficult once you have built it. Make sure you can also text message yourself and email yourself if any of the parameters are not within limits.

Also, you should save your data to a real hard drive as well as flash because flash wears out if you do many writes. You can store your writes and make some period's log writes all at once at say, (insert appropriate interval) to reduce the total number of writes. 

Don't rely on the network being there to save your data. Also, you should have a battery backup large enough to last several days.