# Confused about MQTT \> InfluxDB

**URL:** <https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365>\
**Category:** General\
**Tags:** database\
**Created:** [5 October 2019 22:36 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365 "2019-10-05T22:36:47Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![clickworkorange](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/clickworkorange/32/12109_2.png) [@clickworkorange](https://discourse.nodered.org/u/clickworkorange)\
**Post date:** [5 October 2019 22:36 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/1 "2019-10-05T22:36:47Z")

</div>

I'm new to InfluxDB, but it seems to me there's a bit of a conflict between Node-RED/MQTT's messaging structure and InfluxDB's "measurement" concept in that the former wants to be atomic while the latter expects multiple (related) values in the same message. Take as an example, some basic system parameters, such as load average, memory usage and disk usage. In Node-RED these are typically treated as separate messages, and MQTT recommends they are separate topics. But when I pass these values to InfluxDB I instincively want to put them in the same "table" (or "measurement"), under different "tags" and "keys". This part of my MQTT topic tree looks like this:

```auto
sentinel/nodered/status
sentinel/system/loadaverage
sentinel/system/memusage
sentinel/system/uptime
sentinel/system/diskusage
sentinel/system/temperature
sentinel/system/fan 

```

The "measurement" would here be "sentinel", the "tags" "part"="system", and the "key" "temperature" (for example). But this doesn't seem to be a good plan - the messages arrive at different times so have to be inserted individually, resulting in a very sparse looking database, since each "row" will always have all "columns" ("keys").

```auto
> select * from sentinel
name: sentinel
time fan loadaverage memusage part status temperature
---- --- ----------- -------- ---- ------ -----------
1568071466000000000 nodered deployed 
1568071525000000000 system 29.9
1568071525000000000 0.42 system           
1568071525000000000 94.0 system           
1568071525000000000 0 system           

```

Am I going down the wrong path here?

---

<div class="post-metadata">

**Author:** ![cymplecy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cymplecy/32/2773_2.png) [@cymplecy](https://discourse.nodered.org/u/cymplecy)\
**Post date:** [6 October 2019 08:38 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/2 "2019-10-06T08:38:53Z")

</div>

You could just feed all your MQTT feeds into one function node that updates a node context for each message and then writes all the node context values to the database each time

That would populate your database fully

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [6 October 2019 08:52 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/3 "2019-10-06T08:52:44Z")

</div>

This is how my weather data is "assembled" in a change node feeding the InfluxDB node.

{  
"outsideTemp":outsideTemp,  
"insideTemp":insideTemp,  
"seaPressure":sea,  
"backLux":lux,  
"outsideTempFront":$globalContext("PortTemp")  
}

Where outsideTemp, insideTemp, sea, lux are message properties.

The InfluxDB nodes documentation has more complex examples:

> **[node-red-contrib-influxdb](https://flows.nodered.org/node/node-red-contrib-influxdb)**
>
> Node-RED nodes to save and query data from an influxdb time series database

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [6 October 2019 10:09 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/4 "2019-10-06T10:09:36Z")

</div>

No, this is common for a timeseries db, some of my tables are far more sparse.

It doesn't really matter since you will generally be summarising by a timespan (`GROUP BY time()`) so everything comes out nicely.

---

<div class="post-metadata">

**Author:** ![clickworkorange](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/clickworkorange/32/12109_2.png) [@clickworkorange](https://discourse.nodered.org/u/clickworkorange)\
**Post date:** [6 October 2019 13:22 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/5 "2019-10-06T13:22:33Z")

</div>

The alternative, as far as I can tell, would be to have one "measurement" (table) for each parameter. Then every table would be 100% populated, and no rows would contain any gaps. But that's probably a bad idea?

**Edit:** Sorry @cymplecy, but I can't see myself going down that route; too fiddly and fragile (one message being late, or missing, would prevent others from being stored), and it breaks the sequence of events.

---

<div class="post-metadata">

**Author:** ![cymplecy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cymplecy/32/2773_2.png) [@cymplecy](https://discourse.nodered.org/u/cymplecy)\
**Post date:** [6 October 2019 14:00 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/6 "2019-10-06T14:00:44Z")

</div>

You've not understood my suggestion 🙂  
I'm saying write all the fields when you receive an update on any of them

---

<div class="post-metadata">

**Author:** ![moebius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/moebius/32/2872_2.png) [@moebius](https://discourse.nodered.org/u/moebius)\
**Post date:** [6 October 2019 14:01 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/7 "2019-10-06T14:01:39Z")

</div>

For this kind of data you could take a look at telegraf:  
[https://www.influxdata.com/time-series-platform/telegraf/](https://www.influxdata.com/time-series-platform/telegraf/)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 October 2019 14:06 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/8 "2019-10-06T14:06:57Z")

</div>

I have always assumed that since this is exactly the sort of data that influx is intended for that it is optimised to minimise such overheads.

---

<div class="post-metadata">

**Author:** ![clickworkorange](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/clickworkorange/32/12109_2.png) [@clickworkorange](https://discourse.nodered.org/u/clickworkorange)\
**Post date:** [6 October 2019 15:06 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/9 "2019-10-06T15:06:41Z")

</div>

> [@cymplecy](#):
>
> You've not understood my suggestion 🙂  
> I'm saying write all the fields when you receive an update on any of them

Ah, that makes a lot more sense, thank you! Still not sure I _like_ it though... The way I see it, an event _is a change_, and that's all you should need to store...

---

<div class="post-metadata">

**Author:** ![clickworkorange](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/clickworkorange/32/12109_2.png) [@clickworkorange](https://discourse.nodered.org/u/clickworkorange)\
**Post date:** [6 October 2019 15:30 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/10 "2019-10-06T15:30:59Z")

</div>

> [@Colin](#):
>
> I have always assumed that since this is exactly the sort of data that influx is intended for that it is optimised to minimise such overheads.

Yes, but that doesn't mean there aren't more or less efficient ways to design your schema... For example, I have no need for ms precision, so all my timestamps are rounded to the nearest second. This supposedly has a marked impact on InfluxDB performance:

> #### [Does the precision of the timestamp matter?](https://docs.influxdata.com/influxdb/v1.3/troubleshooting/frequently-asked-questions/#does-the-precision-of-the-timestamp-matter)
> 
> Yes. To maximize performance we recommend using the coarsest possible timestamp precision when writing data to InfluxDB.

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [6 October 2019 15:34 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/11 "2019-10-06T15:34:29Z")

</div>

Your sentinel/system/ messages are all arriving at the same time. If you subscribed to sentinel/system/# and fed the results into a join node set to wait for 6 messages and put them in an array you could insert them all at once. No database redundancy (null fields).

---

<div class="post-metadata">

**Author:** ![clickworkorange](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/clickworkorange/32/12109_2.png) [@clickworkorange](https://discourse.nodered.org/u/clickworkorange)\
**Post date:** [6 October 2019 15:41 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/12 "2019-10-06T15:41:48Z")

</div>

> [@ghayne](#):
>
> Your sentinel/system/ messages are all arriving at the same time.

Well spotted! Unfortunately the timestamps are misleading in my example; they are the same because I manually triggered a read of all values, just to see how it would look in the DB. I considered editing my sample before posting but forgot - apologies for any confusion caused. In practice these are all read by individual timers, some once a minute, some every ten seconds, yet others every five minutes. So the values will typically arrive at least several seconds apart. Furthermore, I have a rate limiting node on my MQTT output, set to only pass one message every 250ms. Just because.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 October 2019 15:54 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/13 "2019-10-06T15:54:06Z")

</div>

> [@ghayne](#):
>
> f you subscribed to sentinel/system/# and fed the results into a join node set to wait for 6 messages and put them in an array you could insert them all at once

Or to cope with the fact that some may be missing set the join node to send after 30 seconds (or whatever) so that it will join them into 30 second chunks.

---

<div class="post-metadata">

**Author:** ![clickworkorange](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/clickworkorange/32/12109_2.png) [@clickworkorange](https://discourse.nodered.org/u/clickworkorange)\
**Post date:** [6 October 2019 16:00 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/14 "2019-10-06T16:00:36Z")

</div>

> [@Colin](#):
>
> > f you subscribed to sentinel/system/# and fed the results into a join node set to wait for 6 messages and put them in an array you could insert them all at once
> 
> Or to cope with the fact that some may be missing set the join node to send after 30 seconds (or whatever) so that it will join them into 30 second chunks.

The concept is growing on me! 🙂

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [6 October 2019 16:32 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/15 "2019-10-06T16:32:01Z")

</div>

I did a lot of Database design using MSSQL Server many moons ago, so the thought of null fields is a painful experience for me 🙂

---

<div class="post-metadata">

**Author:** ![clickworkorange](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/clickworkorange/32/12109_2.png) [@clickworkorange](https://discourse.nodered.org/u/clickworkorange)\
**Post date:** [6 October 2019 16:49 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/16 "2019-10-06T16:49:48Z")

</div>

Same here! I instinctively disliked what `select * from sentinel` produced. But maybe my instincts are wrong in this case 🙂

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [6 October 2019 17:14 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/17 "2019-10-06T17:14:34Z")

</div>

Although it will only be a problem if you need something like SELECT \* FROM sentinel WHERE fan = 0 AND temperature \> 80, which means your fan is broken but you'll never know 🙂

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [6 October 2019 18:04 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/18 "2019-10-06T18:04:12Z")

</div>

> [@clickworkorange](#):
>
> Still not sure I _like_ it though

I understand what Simon is saying and it would make sense for a SQL db but it really doesn't for a timeseries db in my view.

> [@clickworkorange](#):
>
> This supposedly has a marked impact on InfluxDB performance

Now that is interesting and I'd missed that. I may try tweaking that on my system.

> [@ghayne](#):
>
> No database redundancy (null fields)

But at the cost of quite a bit of processing on the Node-RED side (to ensure that you had all fields together) or quite a lot of redundancy (writing more data than you need to as per Simon's suggestion).

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [6 October 2019 18:14 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/19 "2019-10-06T18:14:36Z")

</div>

> [@TotallyInformation](#):
>
> I understand what Simon is saying and it would make sense for a SQL db but it really doesn't for a timeseries db in my view.

That is a very relevant comment InfluxDB is not really comparable to pure SQL databases.

---

<div class="post-metadata">

**Author:** ![cymplecy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cymplecy/32/2773_2.png) [@cymplecy](https://discourse.nodered.org/u/cymplecy)\
**Post date:** [6 October 2019 18:27 UTC](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365/20 "2019-10-06T18:27:56Z")

</div>

I'm off to read up on these influx databases....

[Next page](https://discourse.nodered.org/t/confused-about-mqtt-influxdb/16365.md?page=2)