Awesome tutorial on MQTT Node Red
-
@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).
-
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).
@scalz said in Awesome tutorial on MQTT Node Red:
I prefer my controller for dashboards
Why? Also, which controller are you using?
-
@NeverDie
with nodered, you can also do some UI with nodered-contrib-ui. and use grafana for graphing for example. Some people do that. without any additional controller like domoticz. for simplicity I think, nodered as standalone controller.I'm using Jeedom. It's french opensource sw. There is multilanguage but not sure if it's very complete unfortunately.. never tried to change the language.
It provides a nice and easy way to design custom designs for nice UI, without code, else it's not very hard to add some piece of html/css too .
If I wanted to do the same, with nodered as standalone controller and some external package for ui, or from scratch, I would have to reinvent the wheel to get the same UI (no time).
Of course I'm not using an additional controller just for that, but also for plugins :) (again no wheel to reinvent)I should have said UI instead of dashboard. what I meant is I just want to have access to everything from one place (UI) and not struggle between urls.
note: you'll need linux socat command. for serial->nodered->domoticz
there is a link in the forum, in nodered section I think -
If you have ethernet or mqtt gateway it is easier to connect more controllers at the same time :-)
@gohan said in Awesome tutorial on MQTT Node Red:
If you have ethernet or mqtt gateway it is easier to connect more controllers at the same time :-)
Yeah, I'm assuming that if each gateway is connected to the same MQTT broker, and the same with each controller (if there's more than one, that is), then you can mix and match however you want. In theory anyway. I haven't done it yet, so maybe there are gotcha's along the way.
-
Now that @nca78 has brought it up, has anyone here looked into somehow integrating TensorFlow, or something like it, into their home automation? Other than replicating the Nest thermostat, what else might it do that would be useful?
https://www.tensorflow.org/
