MySQL - Too many connections

General
1

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.

2

Seems related to mysqlConnectionLimit

I will try setting it to 20 and check back in a few hours.

3

How many elements are there in the array that you split?

Also which versions of node red, node.js and the mysql node are you using?

4

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.

5

There are multiple flows with repeating scheduled at 30-60 minute intervals.
Some lists have 500 items, while others have 5000.

We have a delay (rate limit) node of 2/second between the array of items and MySQL node so we are not triggering 100s of queries at a single point at least.

I believe NodeRed's MySQL node was working as intended and consuming a pool of 50 connections (as per settings) and keeping them alive for re-use later.

After I added connectionLimit: 20 to ~/.node-red/settings.js it fixed the issue ( Now I only see approx 20 connections on sleep in MySQL SHOW PROCESSLIST )

6

If you have just two flows with 5000 items, at 2/sec that would take 5000 seconds which is more than 60 minutes, so it will not keep up with real time.

7

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.

8

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.