MYSQL2 - Dynamic info - Open or Closed Sessions?

[allacmc]

I've recently been on a mission to make a system where the implementation of the system won't be mine.

It will be a third-party team.

So I need to make a system that will be configured by someone else.

In this case, I need to manually configure the database information directly through the user interface, such as URL, username and password.

after much searching I found node-red-contrib-mysql2 which works very well.

However, there is a problem with the setup of information that is necessary for each SQL inject.

This slows down the system.

Does anyone have a solution?

Do you know of any way around this?

This node: node-red-contrib-mysql2

because it is necessary again to pass the setup information

[allacmc]

@Steve-Mcl Any suggestions in that regard?

[paulkeates]

Hi,

A guy on my team wrote the MYSQL2 node - so thanks for the positive feedback!

I've not experienced any slowdowns using MYSQL2. Maybe a few more details would help with 'slowdown'?

you can actually put the yellow 'set msg.topic' node before the Function node and drop the additional timestamp node.

Then simply clicking the Inject node (or ANYTHING that activates the flow) will run the query.

Cheers,

Paul

[allacmc]

My question is the following:

Every time you run a query, it redoes the connection, isn't that inefficient?

Wouldn't it be ideal to leave it connected and inject the queries later or at any time?

The node worked perfectly... I have no questions as to how it works.

My question is about the efficiency of the execution.

[dceejay]

The alternative node-red-node-mysql uses a pool of connections and does keep the connection open.

[allacmc]

I imagine that the default node-red-node-mysql only keeps 1 connection open.

And it runs the pool only when requested.

But those of you who are more experienced could tell me what would actually be more efficient?

Can I then use the node-red-contrib-mysql2 written by the colleague that I will have a good efficiency in the execution?

What do you think from the point of view of better performance?

[dynamicdave]

This is an interesting thread as this week I received an email from my ISP informing me I had a large number of sleeping mysql connections (and could I take steps to reduce the number).
I'm using 'node-red-node-mysql' which seems to keep each connection open after the query has been executed (so I can see how that would annoy my ISP when the number becomes significant).

This morning I installed mysql (mariadb) on one of my RPi-4B home-servers and created a local and some remote users.
By using SHOW PROCESSLIST; (when logged in to mysql) I can see that 'mysql' leaves each connection open after it has executed the query, and can see why this might annoy my ISP, as in my case I suspect the number was quite high.

The 'mysql2' node closes a connection after execution and never appears on the Processlist.
I've actually run a flow all morning, that pings devices around my home, and then performs a series of INSERT queries every 90 seconds (to my local server) and not encountered any problems.

I guess I'll replace the 'mysql' nodes with 'mysql2' nodes (in all my flows) in order to keep my ISP happy?

[allacmc]

But I wonder the following:

If you are going to develop an application where every time you need to insert a record in an external database, such as capturing data from telemetry, where there are thousands of records, imagine and this opens and does it close all the time of the connection?

For sure the performance will not be the same...

Even more so in professional applications

I'm developing an application that will add at least 10,000 records in about 5 minutes.

I can't plan something that will open and close the connection every time I insert.

If I choose to open and close the connection all the time, it will consume a lot of machine resources

what do you think ?

@dceejay @Steve-Mcl @TotallyInformation @paulkeates

Is there a way to make a flow where all requests are sent to only one connection that is already open?

[Colin]

As described above, the standard node keeps it open.

[allacmc]

I still have doubts about which one would be more efficient.

Do you have an opinion on this matter?

@dceejay @knolleary @Steve-Mcl

[Steve-Mcl]

@allacmc

I'm developing an application that will add at least 10,000 records in about 5 minutes.

is that 10000 records EVERY 5 minutes? You do realise the database have 2,880,000 new records per day, 1,051,200,000 new records per year (1,051,200,000 = one billion fifty-one million two hundred thousand)

@allacmc

Is there a way to make a flow where all requests are sent to only one connection that is already open?

The node-red-node-mysql node uses a connection pool (as do many / most applications).

Database connection pooling is a way to reduce the cost of opening and closing connections by maintaining a “pool” of open connections that can be passed from database operation to database operation as needed

PS, would you mind not tagging please directly (we are all volunteers here and respond when we can) - thanks.

[allacmc]

@Steve-Mcl

is that 10000 records EVERY 5 minutes? You do realise the database have 2,880,000 new records per day, 1,051,200,000 new records per year (1,051,200,000 = one billion fifty-one million two hundred thousand)

This application is not used 24 hours a day. It will be used occasionally.

Maybe 4 to 10 times a day.

[allacmc]

@Steve-Mcl

PS, would you mind not tagging please directly (we are all volunteers here and respond when we can) - thanks.

At no point would I want you to feel pressured.

I tagged you because I know you're pretty experienced. And I would like to count on your excellent and friendly help.

[Steve-Mcl]

No worries, its not really considered great netiquette - I hope you understand.