Xampp node-red-node-mysql increased resources use

Hi all,

I am new in node-red and try to use it as a SCADA tool.
I am reading from PLCs and based on options I am writing into database (XAMPP).
The problem is that the process "mysqld.exe" starts to use lots of pc resources when doing a data manipulation like in the next picture


Memory keeps still at 984.8MB and CPU goes from 0% (when not querying) to 76.1% (when querying).
I am also attaching one of my flows

I also noticed that even a simple query from localhost/phpmyadmin takes longer now...

Do you know what I am doing wrong here?
Can someone help me? I can offer more info, if you ask for and also tell me how to do it :slight_smile:

I am using node v16.14.0, npm v8.5.4 and node-red-node-mysql v1.0.1

Thanks for trying to help me!

What query are you running from node red, and how often? If it is a select query, how many records does it return?

This is a common problem with relational databases when the table has lots of data and incorrect indexes for the query.

If you dont quite understand what that means, then look into using a mysql profiler to analyse your query. It will suggest what indexes are necessary (and some even update the table for you)

Lastly, it looks like you are running x86 (32bit) mysql instead of x64 (64 bit) mysql?

Thanks @Colin for your question.

In the above flow, I have the following:

  1. query of 20 records - every 20 seconds
  2. query based on criteria => unknown number of records (maximum 100)
    2.1 every record from 2. is being updated and for every record also an 2 inserts are done
  3. insert query
    2., 2.1, 3. are done based on PLC variable changes
    In my php scripts after a query I close the connection. I do not know if node-red-node-mysql does that or if this is one of the problems...

@Steve-Mcl thanks for trying to help.
Below you can see my table


How can I improve it more?
Regarding the version of xampp, I will try to upgrade it soon.
Why does it work slower with node-red? If I turn off node-red on that server, it runs smooth and it is also a server for my other projects that manipulate lots of data? My other projects are made in vue and row php, javascript (web based apps).
Thanks!

Impossible to tell from a screenshot. I would need a lot of detail and access to your systems. Indexing is not a simple subject. You need to identify which columns in the table are used in the query WHERE filter & determine if they are suitable for indexing. I'm afraid you will need to read up on indexing and do some learning.

As for why node-red might be slower - it is possible something is happening that you dont realise. for example, how often are the SQL nodes being triggered? Use a counter node before each SQL node to see what is going on. You might be hammering the database (a counter node could be a simple function node or a contrib node - search for message counter on the forum)

Also, have you checked every query going to / coming from database to node-red - are you 100% certain you are LIMITing the row count? If not, node-red might be requesting ALL ROWS every 20 secs

@Steve-Mcl,
I always create queries to limit the number of returned rows.
image
I think somewhere node-red is not closing my connections, because memory used is increasing as CPU is fluctuating.
Do you know a way of making node-red-node-mysql close connection after each query? :slight_smile:
I see that if I close node-red it keeps the memory occupied. If I restart the machine and run my other projects, that use a lot of queries and are being used by more then 50 persons at once, I have no problem with the resources. That is why I think there is a setting I do not understand in node-red-node-mysql :frowning:

I suspect you are looking for a golden bullet / quick fix.

The latest node-red-node-mysql package uses connection pools. It is defaulted to 50 (which I think a bit high TBH) See here: node-red-nodes/68-mysql.js at fb9c90f68cdf6f927360544793eacea081c1fd30 · node-red/node-red-nodes · GitHub

Before going off and adjusting this (or any other settings) there are a number of things to understand first...

  1. How often are the mySQL nodes being hit. Please dont say "every 20s" - add counters BEFORE EVERY mySql node and measure it. You might have a bug!
  2. Are you using latest versions of
    1. nodejs
    2. node-red
    3. node-red-node-mysql

@Steve-Mcl ,

To be honest, I am pretty stressed out because of this, so you may say I am looking for a quick fix :smiley:
Regarding the versions:


I also implemented a query counter as you said. In 1min and 29 secs, it did 22 queries.
Could you tell me how to access the code generated by the visual programming? :smiley:
I would take a look there to try understand more

I see you are sorting on timestamp. Make sure that you have an index on that field, otherwise it has to sort the full database at every query.

That is a query every 4 seconds, not every 20 seconds.

All that did was check versions of a package in the windows system folder is up to date.
image



To understand the versions...

For Nodejs version - in a command window enter...

node -v

For node-red and mysql versions, use the node-red editor --> manage pallete
image

image

@Colin thanks for getting back.
I will add index on that timestamp.
About number of queries, I had 3 types that run: 1 that runs every 20 secs, and 2 that run based on variables changes. It can run 3 at once, if they synchronize :smiley:
After adding indexes to the tables, the memory used is lower, to around 550MB. CPU continues to fluctuate based on when and how many queries are triggered.
Thanks @Colin & @Steve-Mcl for helping me with questions and suggestions.
I remain opened to your future help on this and/or other matters :slight_smile:

Here they are: I am using node v16.14.0, npm v8.5.4 and node-red-node-mysql v1.0.1 :smiley: