MysqlDBNode reuses the same connection for all access


#1

We have a node-red flow with multiple API endpoints GET /api/this, GET /api/that etc. that all access the database. Most often simple "SELECT something FROM table"

We observed that if one of those endpoints did a query that took a bit longer (seconds) - all other endpoints accessing the MySQL would hang until that slow request finished.

Looking at the mysql SHOW PROCESSLIST it shows only a single connection from node-red

+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
| Id  | User        | Host            | db   | Command | Time | State                    | Info             | Progress |
+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
|   9 | nodered     | localhost:37780 | te   | Sleep   |   18 |                          | NULL             |    0.000 |
+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+

And when the slow query is running that connection is then serving that query.

Looking at the node-red-node-mysql I looks like a mysql connection pool with allowed 25 connections is setup - but then only one connection is collected and used in perpetuity

I tried patching the node_modules/ installed version of node-red-node-mysql/68-mysql.js to use the pool to do query instead of connection as the mysqljs/mysql documentation mentions pool.query as a short hand for getConnection, query, release:


diff --git a/storage/mysql/68-mysql.js b/storage/mysql/68-mysql.js
index a3362fa..0628f7f 100644
--- a/storage/mysql/68-mysql.js
+++ b/storage/mysql/68-mysql.js
@@ -121,7 +121,7 @@ module.exports = function(RED) {
                     if (typeof msg.topic === 'string') {
                         //console.log("query:",msg.topic);
                         var bind = Array.isArray(msg.payload) ? msg.payload : [];
-                        node.mydbConfig.connection.query(msg.topic, bind, function(err, rows) {
+                        node.mydbConfig.pool.query(msg.topic, bind, function(err, rows) {
                             if (err) {
                                 node.error(err,msg);
                                 node.status({fill:"red",shape:"ring",text:"Error"});

Running with that I see multiple connections, but not neccesarily all 25. I figure it's smart about only allocation new when needed.

+-----+-------------+--------------------+------+---------+------+--------------------------+------------------+----------+
| Id  | User        | Host               | db   | Command | Time | State                    | Info             | Progress |
+-----+-------------+--------------------+------+---------+------+--------------------------+------------------+----------+
|  94 | nodered     | xxx.xx.xx.xx:48062 | beta | Sleep   |  199 |                          | NULL             |    0.000 |
|  95 | nodered     | xxx.xx.xx.xx:48064 | beta | Sleep   |   48 |                          | NULL             |    0.000 |
|  99 | nodered     | xxx.xx.xx.xx:48164 | beta | Sleep   |   18 |                          | NULL             |    0.000 |
| 100 | nodered     | xxx.xx.xx.xx:48200 | beta | Sleep   |   78 |                          | NULL             |    0.000 |
+-----+-------------+--------------------+------+---------+------+--------------------------+------------------+----------+

And it alleviates the hanging API endpoints when running concurrently with slow queries.

For now I'll run with patch-package but would you consider accepting this patch?


#2

A PR would be welcome.


#3

Cool! https://github.com/node-red/node-red-nodes/pull/504