Awesome tutorial on MQTT Node Red
-
Right now I'm mostly interested in it for:
- dumping everything into a mysql database, so everything gets logged forever (storage is cheap). Then somehow graphing that at much finer granularitry than the 5 minute intervals that domoticz offers.
- dashboard capabilities.
Haven't done it yet, but it looks like it might be easier than the alternatives.
-
Right now I'm mostly interested in it for:
- dumping everything into a mysql database, so everything gets logged forever (storage is cheap). Then somehow graphing that at much finer granularitry than the 5 minute intervals that domoticz offers.
- dashboard capabilities.
Haven't done it yet, but it looks like it might be easier than the alternatives.
-
@nca78 said in Awesome tutorial on MQTT Node Red:
@neverdie any plan to include Tensor Flow in the mix to get a really smart home ?
Good idea! That would be the ultimate. :)
-
Right now I'm mostly interested in it for:
- dumping everything into a mysql database, so everything gets logged forever (storage is cheap). Then somehow graphing that at much finer granularitry than the 5 minute intervals that domoticz offers.
- dashboard capabilities.
Haven't done it yet, but it looks like it might be easier than the alternatives.
@neverdie said in Awesome tutorial on MQTT Node Red:
- dumping everything into a mysql database, so everything gets logged forever (storage is cheap).
Skip the relational databases and pick a time series database like influxdb to store your data.
You'll thank me in the end!http://yveaux.blogspot.nl
-
@neverdie said in Awesome tutorial on MQTT Node Red:
- dumping everything into a mysql database, so everything gets logged forever (storage is cheap).
Skip the relational databases and pick a time series database like influxdb to store your data.
You'll thank me in the end! -
@gohan
it's about scale, resources etc. I'm sure there are lot of explanations on google.
Shortly because time series database are more efficient to process metrics, and we use lot of metrics with our HA.)
Time series database can process faster big amount of timed data. data indexed by time, these database finally are mostly used for INSERT queries. as the metrics doesn't need to be updated.Relational database are better fit for transactions. more indexes, better for UPDATE queries etc.
Relational databases can show their limits for graphing, dashboards, with big amount of data. (Example monitoring servers, sensors etc each seconds can represents lot of data after a year). Their SQL can looks a bit more complex for some people (JOIN etc).I would use relational database for managing collection of devices, or CRM applications for example (mysql etc). And I would use time serie database (influxdb) for graphing, dashboards of metrics.
I'm former IT dev with relational databases (and I don't work in this field anymore), so not expert on time serie database. I started using influxdb for my HA. I remember first time I read about it, I thought "wowow what's that! Data will look messy" LOL But that's because I was still thinking relational database in my head, and not metrics..
That's just my point of view on this, I think it makes sense, maybe I'm wrong :)@NeverDie you should try nodered/influxdb/grafana for your metrics. Domoticz or others for supervision ;)
-
Any favorite arduino MQTT libraries to enable an ESP8266 to send MQTT directly to Mosquitto on the Pi? I was going to let ESP-LINK handle it,
but it appears that it can publish to only one topic,and so it seems cleaner to send to the proper topic in the first place rather than re-process it later. -
Correction: I guess ESP-LINK can do it after all: https://github.com/jeelabs/el-client
-
And I'm glad it is so, because I already have a shield for that:
https://www.openhardware.io/view/491/PA-LNA-nRF52832-ESP-LINK-Shield-for-Wemos-D1-Mini-ESP8266 :) -
@gohan
it's about scale, resources etc. I'm sure there are lot of explanations on google.
Shortly because time series database are more efficient to process metrics, and we use lot of metrics with our HA.)
Time series database can process faster big amount of timed data. data indexed by time, these database finally are mostly used for INSERT queries. as the metrics doesn't need to be updated.Relational database are better fit for transactions. more indexes, better for UPDATE queries etc.
Relational databases can show their limits for graphing, dashboards, with big amount of data. (Example monitoring servers, sensors etc each seconds can represents lot of data after a year). Their SQL can looks a bit more complex for some people (JOIN etc).I would use relational database for managing collection of devices, or CRM applications for example (mysql etc). And I would use time serie database (influxdb) for graphing, dashboards of metrics.
I'm former IT dev with relational databases (and I don't work in this field anymore), so not expert on time serie database. I started using influxdb for my HA. I remember first time I read about it, I thought "wowow what's that! Data will look messy" LOL But that's because I was still thinking relational database in my head, and not metrics..
That's just my point of view on this, I think it makes sense, maybe I'm wrong :)@NeverDie you should try nodered/influxdb/grafana for your metrics. Domoticz or others for supervision ;)
@scalz I am from IT too and I work with databases in the past and I saw you can do many things with a SQL database, you can optimize it for different tasks. Of course the db on its own doesn't have dashboards and reports but you need an external tool for them. I agree that if you have a raspberry you would prefer a lightweight database.
-
@gohan
I know. and I also remember seeing some crazy slow sql queries, needing optimizations, sometimes not without pain etc.
but with internet, iot.. datas increase each year. I think nosql databases have been created to be more flexible in this case.I'm "old school" on this too, so I still like the relational schema, sql.. but Imho it's better to choose the db regarding the usecase. Each (nosql and sql db) have their pros and cons.
I also use influxdb/grafana couple because of the points you mentioned, and it can make very nice graph.Looks like since a few years, there is a nosql fashion, which won't stop with Big Data :)
That would be fun to see a benchmark, with thousands of datas, read/writes, of metrics, for the same task, influxdb vs mysql. (I think I know which one would win for simple metrics..)
Old docs about influxdb (you can find a lot more docs about sql vs nosql, their usecase, and admins choice)
-
@scalz I am from IT too and I work with databases in the past and I saw you can do many things with a SQL database, you can optimize it for different tasks. Of course the db on its own doesn't have dashboards and reports but you need an external tool for them. I agree that if you have a raspberry you would prefer a lightweight database.
@gohan You probably want to graph your data. Especially when combining different datasets, sampled asynchronous at non equidistant intervals you will have a lot of trouble plotting it when it is stored in a standard relational database like MySQL.
Time series databases are perfectly fit for sensor data from different sources, like your MySensors network.
Tools like Grafana and Influxdb make the perfect combination for this! -
The above motivated me to put together this upgraded board for nRF52832 and ESP-LINK. This particular fork is not perfected, but it is better than the board I previously posted in that the nRF52832 can be programmed from the top, without having to unplug it from the Wemos:
-
Connection to Mosquitto on the Pi can be configured through a webpage presented by ESP-LINK from the ESP8266 :
-
As I said before I never used influxdb and grafana, but it is in my to do list so I am just asking some questions as I "grew up" working most of the times on SQL server
@gohan said in Awesome tutorial on MQTT Node Red:
As I said before I never used influxdb and grafana, but it is in my to do list so I am just asking some questions as I "grew up" working most of the times on SQL server
I understand. I also grew up with relational databases and did my first iot steps with them too. Then I found influxdb and never want to go back!
Just sharing my enthusiasm and hoping to spare you time by making the switch now ;-)
-
Guys,
How are you getting your data into the influxdb and grafana? Are you using Node Red, Domoticz plug-ins, or some other method? -
on my side, I'm injecting data from node-red to influxdb, grafana is connected to influxdb. my controller is also on the same rpi (v3, with ssd). I'm planning to add some grafana graph to my controller dashboard (because I prefer my controller for dashboards).
