Problem on generate graphs from values stored in the database using multiple data points in a single graph

Hi all,

I’m a newbie in node red!
I would like to show dynamically multiple data points in a single chart.

I found this great video sample:
https://www.youtube.com/watch?v=ccKspiI8FRw&t=978s

Flow sample at:
https://flows.nodered.org/flow/36f2de38ed4bb62cf3a5e8fdc76d779e

All function well but, sometimes, the showing of multiple data points in chart doesn’t function as expected.

The problem is that, sometimes, some data points are missing or exceeding as expected.
Using debug node I note that function node “SQL”, that produce the multiple “SELECT” for database “DB” node, works always well (if operator select 3 search key function produce 3 “SELECT” in an array and so on).

After “DB” node there is a “Edit Join” node that should combine each msg.payload produced by “DB” node to create an array that will be elaborated by a “Prep” function node that feed data in “Chart” node.

Putting a debug node after the “Edit Join” node it seems that sometimes (not always) are missing or exceeding some elements in array (for example from “DB” node exit 4 msg.payload but after “Edit Join” node only 2 or 3 are produced and so “Chart” node doesn’t show the correct data.

My opinion is that, for strange reasons, “Edit Join” node starts elaboration before that “DB” node has finished to produce all the data.

In fact, if for example the operator selects 5 search keys, observing the debugging I note that sometimes first 3 Payload come out from the "DB" then the array Payload exits from the "Join" and then the last 2 Payloads come out from the “DB” and so are no longer considered by the "Join".

Join

I also try to configure Join node using "Send the message after a timeout following the first message" but without results (indeed it seems that it is worse...)

Why in your opinion?

The function node before the sqlite node builds an array of select messages to send to the sqlite node and it sets msg.complete on the last one. The sqlite node should send one message for each one it receives and the last message should still have complete set. The join node should join those together into an array and use the msg.complete to say that it should pass the result on, so the join node should not send anything till it receives the last one. However, if you put debug nodes on the input and output of the join node you will not necessarily see what you expect in the debug log, the message from the output may appear before the last one from the input just because of the way the messaging works in node red. The thing to check is whether there are the right number of elements in the array coming out of the join node. It should be the same length as the number of messages that went in. Also if you select Show Complete Message in the debug node going into the join node you can check that msg.complete is not set except on the last one out of the sqlite node.

Below the sequence in debug windows when system doesn’t work as aspected:

7 select messages from “DB” node and only 4 elements in array after the “JOIN” node. I don’t understand why…

This behavior is not always reproducible because other times the exact same sequence works (7 messages and 7 elements in array)

28/10/2018, 15:12:15node: 33225ce8.d8a25cSELECT * FROM sensor_data WHERE device='Estrusore' AND sensor='Potenza' AND epoch >= 1540649523235 AND epoch <= 1540735923235 : msg.payload : array[6916]
[ object , object , object , object , object , object , object , object , object , object … ]

28/10/2018, 15:12:16node: 33225ce8.d8a25cSELECT * FROM sensor_data WHERE device='Estrusore' AND sensor='Temp 3' AND epoch >= 1540649523235 AND epoch <= 1540735923235 : msg.payload : array[6916]
[ object , object , object , object , object , object , object , object , object , object … ]

28/10/2018, 15:12:17node: 33225ce8.d8a25cSELECT * FROM sensor_data WHERE device='Mulino' AND sensor='Potenza' AND epoch >= 1540649523235 AND epoch <= 1540735923235 : msg.payload : array[6916]
[ object , object , object , object , object , object , object , object , object , object … ]

28/10/2018, 15:12:18node: 34ce65be.382f22SELECT * FROM sensor_data WHERE device='Mulino' AND sensor='Tensione' AND epoch >= 1540649523235 AND epoch <= 1540735923235 : msg : Object
{ topic: "SELECT * FROM sensor_data WHER…", _msgid: "b4c425d4.92b6a8",payload: array[4] }

28/10/2018, 15:12:19node: 33225ce8.d8a25cSELECT * FROM sensor_data WHERE device='Mulino' AND sensor='Tensione' AND epoch >= 1540649523235 AND epoch <= 1540735923235 : msg.payload : array[6916]
[ object , object , object , object , object , object , object , object , object , object … ]

28/10/2018, 15:12:20node: 33225ce8.d8a25cSELECT * FROM sensor_data WHERE device='Estrusore' AND sensor='Tensione' AND epoch >= 1540649523235 AND epoch <= 1540735923235 : msg.payload : array[6916]
[ object , object , object , object , object , object , object , object , object , object … ]

28/10/2018, 15:12:21node: 33225ce8.d8a25cSELECT * FROM sensor_data WHERE device='Estrusore' AND sensor='Temp 2' AND epoch >= 1540649523235 AND epoch <= 1540735923235 : msg.payload : array[6915]
[ object , object , object , object , object , object , object , object , object , object … ]

28/10/2018, 15:12:22node: 33225ce8.d8a25cSELECT * FROM sensor_data WHERE device='Estrusore' AND sensor='Temp 1' AND epoch >= 1540649523235 AND epoch <= 1540735923235 : msg.payload : array[6916]
[ object , object , object , object , object , object , object , object , object , object … ]

Which of the messages going in has got msg.complete set? As I said you will have to set the debug node to Show Complete Message. Also check that you have not still got the timeout in the join node.
Are you really going to put 50,000 points on a chart? I hope you have a supercomputer running the browser and a gigabit ethernet connection.

Also tell us what operating system node-red is running under and post what you see in the node red log when you restart node-red. That will tell us the versions of everything.
If you are wanting to handle such large quantities of data then I strongly suggest looking at Influxdb for the realtime data with Grafana for the charting.

See below my configuration:

[{"id":"33225ce8.d8a25c","type":"debug","z":"2f1e9c7a.421674","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":440,"y":960,"wires":}]

All of 2 debug nodes show the complete messages.
The points in chart are 6000 (more or less) not 50000.

The operating system in "NodeRed PI Website V0.19.4" (I hope this is the data you ask me...)

How I can check the timeout of Join node?

Sometimes system work and sometime doesn't' work (I can not understand why...)

Thanks for all!

Please try and answer all the questions. If you don't understand then ask.
You haven't said which of the messages going in has got msg.complete set? Since you are showing the complete message object then you can look in there to see which one (going into the join) has msg.complete set.

How did you install node red on the pi? Did you run the recommended install/upgrade method from the docs?
https://nodered.org/docs/hardware/raspberrypi
If not then I recommend that you do so, that will make sure you have the latest node-red along with recommended versions of node.js and npm.

I asked for the contents of the node red log that you see when you restart node red. How you can see that depends on how you installed node red. If you used the recommended method then open a terminal and run
node-red-log
then in another terminal restart node-red, and copy/paste here what appears in the first window, starting with the welcome to node red line, or something like that anyway. You can copy from the terminal using Ctrl-Shift-C

As to the timeout setting in the join node, you said you had tried the 'send message after timeout' setting in an earlier post, I just wanted to make sure you have cleared that (and have not got any of the other 'Send the message' fields filled in, so that it will only send on receiving a message with msg.complete set.

The debug nodes say there are 6912 points for each line. There are seven lines. 7*6912 = 43344, ok I was exaggerating. The point is still valid though. I guess your chart on screen is a few hundred pixels across. If you have, for each line, 6912 points then that means that if the chart is 500 pixels wide you have about 14 values to plot for each pixel across the chart, for each line. Also in terms of raw data to be manipulated in the browser, each point is (I guess) a timestamp and a value, at 8 bytes each, so that is around a megabyte of data to be passed to the browser when you open the page, and then the browser has to do all the maths to work out where to plot the points. Have you got the chart displaying?