Hi, I am observing an odd thing that I wanted to highlight.
I'll briefly highlight my setup.
I have multiple flows that use the MYSQL node as follows (in similar ways). Cron Plus Timer (30m interval) --> HTTP Request (returns array) --> function --> split --> delay (ratelimit 2/s) --> function --> MySQL
My observation is that on some days, I see MySQL too many connection errors. That led me to check how many connections were currently established to the database by using MySQL SHOW PROCESSLIST statement. This shows around 60 connections, out of which 50 are sleeping for more than 600 seconds. These disappear for some time if I restart nodered.
I tried to run the following query through NodeRed, and that returns the number 55.
show status where `variable_name` = 'Threads_connected';
My assumption is that the MySQL node is creating and holding connections open for some reason when it shouldn't.
Related to Colin's questioning above, what are you using to connect and handle your SQL querying? Does it handle automatic disconnection upon query completion? There are some options to fix your problem. But what you have needs to be identified first.
Sorry, I should have been more accurate with the figures considering the problem we are describing.
I don't have the flow with 5000 items running at 60-minute intervals; that specific flow runs only once a day at night. Just to remove that from the consideration, I can confirm that currently, we do not have any self-overlapping flow executions.
This problem was mostly only related to the connectionLimit in my case.
Edit: It also seems like the number of connections on SLEEP slowly ramps up to 20 once the flows are running. Just an observation, not an issue.
Edit 2: SHOW VARIABLES LIKE "max_connections"; shows 1000 -- I am looking into why 60 connections would cause MySQL to report too many connections error then.