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
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?
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
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...
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.
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?
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
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?
I would take a look there to try understand more
@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
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
