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
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
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.
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
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