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?
