Node-red unresponsive while sqlite node is working

[sonntam]

I'm using the sqlite-node extensively to store and retrieve sensor data. So far I have around 20 different sensors (temperature, humidity, co2-concentration, air pressure, open/closed status,...) that push data into a sqlite3 database. The database is stored on a fast ssd and currently has around 260 MBytes of size (data has been collected for the past 2 years).

Every 30 minutes I update my dashboard charts with the collected data from the sqlite3 database, presenting the last 18 hours of data. It takes around 600ms for each data series to be retrieved via the sqlite node. In total around 40 seconds of cpu time is needed on one core to retrieve all data I want to display.

Now here is my problem: While the sqlite node is working the node-red flows are completely locked up and I see that the node-red process is busy 100% on one of the 4 cpu cores. During the time of the data retrieval I can't open dashboard or the flow editor.

Is there anything I can do to keep node-red responsive while the sqlite node is busy? The only idea I have right now is using the exec node and manually using the sqlite3 cli program. This way a separate process would be spawned that can run on another core/thread.

Is there any other more convenient way?

I'm using Node-Red 2.2.2 with the node-red-node-sqlite 1.0.3 node.

[sonntam]

Well,... sometimes I can't stop tinkering. I implemented a custom exec async node that uses node.js childProcess and stream libraries to start a process and pipe text to its stdin.

This custom node is used in a custom sqlite read async node to spawn a niced (to decrease process priority) sqlite3 cli process which opens a sqlite db with the -readonly -json flags.

This works quite nicely. Note that you have to have the nice and sqlite3 programs on your path in order for this to work. The sqlite file path cannot contain ~ as the path is put in double quotes, but you can use the $HOME env variable.

Gist for custom sqlite read async node.

[sonntam]

The aforementioned exec async node with the ability to pipe in stdin to a process has proven (at least in my case) to be quite useful. I might consider writing up a proposal to extend the core "exec" node.