node-red-node-mysql Slow performance

[Garry Hayne]

Hi,

I am using node-red-node-mysql to display about 8000 database rows (Timestamp, Value) in a chart node.

While fetching the data node red slows down almost to a stop and the dashboard tab takes ages to load.

I suspect that the node uses a blocking algorithm. Has anyone else experienced this behaviour?

Regards, Garry

[Colin Law]

Why do you need 8000 points in a chart? Do you have a display 8000
pixels wide? All those points have to be sent to the browser and then
converted into points on the chart using javascript.

Colin

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to node-red+u...@googlegroups.com.
> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit
> https://groups.google.com/d/msgid/node-red/8e769a5d-5204-47c3-8c33-c67d0bb3ff05%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Garry Hayne]

Colin,

you are of course right, I will have to aggregate / limit the data, I overlooked the browser side of things!

Garry

[steve rickus]

I agree with Colin -- let the database do what it does best, and aggregate your points into larger chunks of time within your query...

[Luis GCU]

I have similar problem with  raspberry pi , i have  a mysql database with 1500 row of data ( that is growing) coming   from my real-time GPS  tracker experiment, when I tried to pull all data out of the database, raspberry almost crash  cpu is at 110%, i made same test on a laptop with ubuntu  OS  and no problem at all.. seems raspberry is not enough to process all that data.

[Garry Hayne]

Which Raspberry Pi are you using?

[Colin Law]

What are you doing with the 1500 rows, and what do you want to do with them?
Are you pulling them all into memory at once?

Obviously the pi has much less memory, probably slower 'disc' and
probably much less processor. So it depends on what you want to do
with the data whether you can do it with the pi. Is the database on
the pi or remote?

Colin

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to node-red+u...@googlegroups.com.
> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/node-red/ec3205af-8a70-4f4d-be1e-6581482e3931%40googlegroups.com.

[Julian Knight]

Agreed, don't forget that you can create summary tables. That will be more efficient for limited devices.

[Luis GCU]

I am just trying  to mess as much as I could with node-red and MySQL, I am  very new in all that, so I am pretty sure I  am not doing everything  optimal and efficient.

 I use the database to store gPS tracking information, for now I just learned how to pull all the data out.. I am in the process to learn how to pull out data base on range of dates, and not the entire database.

one question what do you guys recommend to be more convenient  to be used for my tests  on raspberry pi Mysql or SQlite? 

thanks guys, 

[Julian Knight]

Running MySQL on the Pi is fairly heavy-weight. SQLite is certainly easier on resources as long as you don't store too much data.

Either way, thinking about sensible data densities to display is useful. I use InfluxDB for example with sensors sending around every 50sec. That is OK for a few days but mounts up very quickly. So for longer term displays, I use the built-in features in InfluxDB to create a summary using averaged hourly data which I can keep virtually indefinitely and to trim the 1min data to a week.

You should be able to do similar processing using either MySQL or SQLite though you will need some clever SQL.

Also, don't forget that you can tweak the settings for MySQL to reduce its resource usage.

[Cor Bosman]

Do you show these influxdb graphs in the dashboard? If so, could you share a template?

[Julian Knight]

Hi, no I use Grafana. It is much more efficient if you need lots of details charts. I have a couple of sets for environment sensors (short-term and long-term) and another for monitoring the Pi.

Grafana directly supports InfluxDB so it is really easy to create charts.

[Colin Law]

+1 for Influx and Grafana. Well worth the learning curve.

Colin

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to node-red+u...@googlegroups.com.
> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/node-red/af555fe0-088f-49b2-8853-dd3a4330de88%40googlegroups.com.

[Cor Bosman]

We use influxdb/grafana heavily at work. I was more wondering if you somehow integrated these graphs with the dashboard, and if not, how you view them. You just have separate bookmarks in your browser? No actually integration in any way?  Iframes?

[Julian Knight]

I have a page that is the default for the Pi that has links. I also have links in the Dashboard. My Grafana dashboards have links back again.

In truth though, I normally use bookmarks.

[Dave C-J]

If your grafana pages allow embedding  then you could try the link section in the bottom right of the dashboard edit panel

[Cor Bosman]

Node-red is like a present that keeps on giving. Never knew that existed! Thanks for the tip.  

[Julian Knight]

I didn't either! But sadly not working for me. It is fine in a new tab but won't let me use an iFrame.

[Dave C-J]

That's a shame - some websites do block that.  (in order to stop themselves getting wrapped up and ripped off by others). Shame but there it is.

[Julian Knight]

Found some documentation that suggests it should be possible using the sharing link but it doesn't seem to work for me.

Ah, not so fast! It does work with IE11 but only after telling IE to allow all content. Realised that I'm using https for NR but not for Grafana - Chrome doesn't like that. 

So it appears that, if you use a sharing link for a dashboard and have everything on http or everything on https, you should be OK.

[Dave C-J]

Aha, good tip that.

[mark hubrich]

I noticed the delay node displays a 1000 msg buffer alert if over 1000 messages. What I'm doing is loading different query results to redis topics then subscribing to the various topics for different things.

Question is... Is it possible to construct some of the query topic from values created from dashboard input? For example a limit value. Like if I only want to query 100 results without editing the node text manually?

Thanks
Mark

[Garry Hayne]

I'm working on it with the date picker, I'll let you know.

Garry

--
http://nodered.org

Join us on Slack to continue the conversation: http://nodered.org/slack
---

You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/8vi5OcTUdgw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send an email to node...@googlegroups.com.

Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/3ca425b0-df0a-40be-8500-3cb21b8e6eb0%40googlegroups.com.