Maximum Insert Speed for mySQL through network?

My insert speed is 200Hz through local network to a mySQL server. I found out the delay is about 30 seconds.... It seems a lot as the whole things is through Wifi and there are only 4 computers. It shouldn't be the network problem.
When I look at the topic to the MYSQL block and also the OK response from mySQL, there is a 30 seconds delay. Is it reasonable?

Worst case is that I need repack all 200 data and sends them through per second, but I need a time stamp. Does Node-red have ms resolution? Thx

I don't understand what you mean by

In particular you then go on to say that there is a delay of 30 seconds. Can you clarify exactly what you mean?

and a few other questions pop to mind:

  1. how many rows are you trying to insert at one time?
  2. how large is a single row?
  3. what machine and os is the mysql server running on?
  4. what else is running on te server?
  5. what maching and s is NR running on?
  6. what else is running on the NR machine?

Hi,

At the input to the MySQL, I look at the topic and see the values change almost right away when I change my input. However, when I look at the OK response from the MYSQL, it has 30 seconds delay. Thx

「Colin via Node-RED Forum nodered@discoursemail.com」在 2019年8月30日 週五,下午6:44 寫道:

Hi,

  1. One row each time.

  2. Just several items.

I have 4 tables with several columns in the MySQL. 3 x 200 Hz insert at local server.

1x 200Hz insert from another computer through local network. I believe the network traffic is fine.

I am running Win 10 on i5 computer. Just running node red and MySQL only.

Thx

「Paul via Node-RED Forum nodered@discoursemail.com」在 2019年8月30日 週五,下午6:53 寫道:

Put a debug node set to show the complete message, showing what is going into the mysql node and another showing what is coming out. Do a screenshot of the output and paste it here. Or copy/paste the debug window text output.


What is the good practice to check why the node-red crash? I did a simple test and 200Hz write to mySQL should be fine. There is something wrong with my code I think. When I run my code, except the mySQL insert is slow, other things are fine. But then, after about 10 mins, the node-red crash. How to see the memory usage or leak? I don't see any error message during that 10 mins though.
Also, how to auto restart the node-red in Window 10? Thx

And I have removed the MQTT and let the node-red connect directly to the mySQL server. Same problem.

If you are sending 200 messages a second and its taking each message 30 seconds to complete I'm not surprised you are running out of memory!

Your error message would also suggest that you are running the mosca MQTT broker?
Are you using node-red-contrib-mqtt-broker?
In which case I'd suggest using a separate MQTT broker instead (eg Mosquito) to remove running an MQTT broker in the single threaded Node-RED app.

You also would appear to be getting error in your SQL statement / data, perhaps its worth "thinning" the data to check to see if you are actually getting tend at a you think you are getting.

In your screen shot I see some mysql insert errors.
Maybe handeling of these errors slows down mysql.

Hi, I think those errors is from my serial port when I start the system. Sometimes, a “?” shows when the Arduino starts. It is only at row 1. So, I didn’t pay much attention to it. Also, how to capture the details log of that error? I have checked the database and I don’t see that entry, guess that row error did not insert into the MySQL.Thx

「Evert via Node-RED Forum nodered@discoursemail.com」在 2019年8月31日 週六,上午4:33 寫道:

One of those errors visible in the last literal screen photo show a SQL error for wrong integer value for the timestamp column. Instead it tries to insert NaN: not a number. Start with that one, and look for more errors like it, it might speed up a lot.

What is a good way to capture this error? I can not see that entry in mySQL. And in Node Red, if I try to read the output OK packet from SQL, it runs too run and I can not capture it. Can I log the error and all debug message? Thx


The first item is my timestamp in us. See the big gap between insert and OK packet response from SQL? 5376356 us - 34865184 us. That 2 sec grew to -111455220+640499192 us = 530 sec after about 10 minutes SQL_insert_aftersometime|690x388 And for the first row issue, I thank it is because when the node-red starts, it does not sync with arduino, so, only after /n is sent, there may be a NaN.

Here is the topic I created.
newMsg.topic = "INSERT INTO centre_table (Time_Stamp, Position, Relay1, Relay2, Relay3, Relay4, Relay5, Current, DistanceLeft, TemperatureLeft, DistanceRight, TemperatureRight, RealTime) VALUES ('" +
context.data.TimeStamp +"'," + "'"+
context.data.Position + "'," + "'"+
context.data.Relay0 + "',"+ "'"+
context.data.Relay1 + "',"+ "'"+
context.data.Relay2 + "',"+ "'" +
context.data.Relay3 + "',"+ "'" +
context.data.Relay4 + "',"+ "'" +
context.data.Hall + "',"+ "'" +
context.data.distance + "',"+ "'" +
context.data.temperature + "',"+ "'" +
context.data.distance1 + "',"+ "'" +
context.data.temperature1 + "',"+
"NOW(6));";
context.data = null;
return newMsg;

Without seeing your flow it’s hard to understand .

Why are you using context?
Are you trying to retrieve data stored in context, if so have you read the syntax in the storing data section of the docs page “writing functions”
How have you got your context store configured?

You can set the debug to also write to the console log, so I would configure one to capture the flow of data you also send to the mysql node

Start by slowing the data rate right down to something like one message every 10 seconds and see what the delays are.

Disable the debug output and try the catch node.

I have isolated the flow and did the checking. If the Insert is at 15ms rate, it is alright. But when it goes up to 5ms which is the required 200Hz, the delay accumulates...
I am thinking how to repack the topic to send 200 data at 1Hz...To make an array of 200? Any solution? Thx

Below is the code to make the topic for INSERTION

var AllData;
var newMsg = {};
var Sensor = {
TimeStamp: 0,
Ax: 0, Ay: 0, Az: 0,
Ax1: 0, Ay1: 0, Az1: 0,
ButtonState: 0,
Time: new Date().toString()
};

context.data = context.data || {};

switch(msg.parts.index){
case 0:
context.data.TimeStamp = parseFloat(msg.payload);
msg = null;
break;
case 1:
context.data.Ax = parseFloat(msg.payload);
msg = null;
break;
case 2:
context.data.Ay = parseFloat(msg.payload);
msg = null;
break;
case 3:
context.data.Az= parseFloat(msg.payload);
msg = null;
break;
case 4:
context.data.Ax1 = parseFloat(msg.payload);
msg = null;
break;
case 5:
context.data.Ay1 = parseFloat(msg.payload);
msg = null;
break;
case 6:
context.data.Az1 = parseFloat(msg.payload);
msg = null;
break;
case 7:
context.data.ButtonState = parseFloat(msg.payload);
AllData = 1;
msg = null;
break;
default:
msg = null;
break;
}
if(AllData){
AllData = 0;
var time = Date();
context.data.Time = time.toString();
Sensor = context.data;
newMsg = { payload: context.data,
topic: 'LiftCar' };
newMsg.topic = "INSERT INTO liftcar_table (Time_Stamp, AcX, AcY, AcZ, AcX1, AcY1, AcZ1, IR, RealTime) VALUES ('" +
context.data.TimeStamp +"'," + "'"+
context.data.Ax + "'," + "'"+
context.data.Ay + "',"+ "'"+
context.data.Az + "',"+ "'"+
context.data.Ax1 + "'," + "'"+
context.data.Ay1 + "',"+ "'"+
context.data.Az1 + "',"+ "'"+
context.data.ButtonState + "',"+
"NOW(6));";
context.data = null;
return newMsg;
}

This looks kind of intense. Every 5ms, 200 inserts while manipulating/creating an object for each insert.

You could improve performance by removing the context and skip creating an object while you already receive an array that you can use.

Can you not use a join node to create complete object instead ?

1 Like

use faster machines...

2 Likes