Trying to find Node backend framework with worker_threads / cluster / fork

Using backend server with concurrent SQLite writes of 10k rows each, and the server dies from run out of memory heap.

<--- Last few GCs --->

[55944:0x10287b000]   462589 ms: Mark-sweep 1355.3 (1437.4) -> 1355.2 (1437.4) MB, 193.9 / 0.0 ms  (average mu = 0.073, current mu = 0.000) allocation failure scavenge might not succeed
[55944:0x10287b000]   462804 ms: Mark-sweep 1355.3 (1437.4) -> 1355.3 (1437.4) MB, 212.5 / 0.1 ms  (average mu = 0.043, current mu = 0.009) allocation failure scavenge might not succeed


<--- JS stacktrace --->

==== JS stack trace =========================================

Security context: 0x26587a5ad149 <JSObject>
    0: builtin exit frame: stringify(this=0x26587a5a17c9 <Object map = 0x265854a842a9>,0x2658822026f1 <undefined>,0x26587eedb049 <JSFunction (sfi = 0x2658571db181)>,0x2658db57f741 <JSArray[70030]>,0x26587a5a17c9 <Object map = 0x265854a842a9>)

    1: arguments adaptor frame: 2->3
    2: removeNull [0x2658b9a13439] [/Users/patarapolw/projects/rep2recall/packages/e-server/lib/db/util.js:~26] [pc=0...

FATAL ERROR: Ineffective mark-compacts near heap limit Allocation failed - JavaScript heap out of memory
 1: 0x100d77bad node::Abort() (.cold.1) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
 2: 0x10003ae83 node_module_register [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
 3: 0x10003b044 node::FatalTryCatch::~FatalTryCatch() [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
 4: 0x1001aa117 v8::Utils::ReportOOMFailure(v8::internal::Isolate*, char const*, bool) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
 5: 0x1001aa0b1 v8::internal::V8::FatalProcessOutOfMemory(v8::internal::Isolate*, char const*, bool) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
 6: 0x1005994c2 v8::internal::Heap::FatalProcessOutOfMemory(char const*) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
 7: 0x10059bbf3 v8::internal::Heap::CheckIneffectiveMarkCompact(unsigned long, double) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
 8: 0x100597b6e v8::internal::Heap::PerformGarbageCollection(v8::internal::GarbageCollector, v8::GCCallbackFlags) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
 9: 0x1005959ed v8::internal::Heap::CollectGarbage(v8::internal::AllocationSpace, v8::internal::GarbageCollectionReason, v8::GCCallbackFlags) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
10: 0x1005a2c3f v8::internal::Heap::AllocateRawWithLigthRetry(int, v8::internal::AllocationSpace, v8::internal::AllocationAlignment) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
11: 0x1005a2c7f v8::internal::Heap::AllocateRawWithRetryOrFail(int, v8::internal::AllocationSpace, v8::internal::AllocationAlignment) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
12: 0x1005748c3 v8::internal::Factory::NewRawTwoByteString(int, v8::internal::PretenureFlag) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
13: 0x10089c15b v8::internal::IncrementalStringBuilder::Extend() [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
14: 0x100673ce4 v8::internal::JsonStringifier::SerializeString(v8::internal::Handle<v8::internal::String>) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
15: 0x100677222 v8::internal::JsonStringifier::Result v8::internal::JsonStringifier::Serialize_<true>(v8::internal::Handle<v8::internal::Object>, bool, v8::internal::Handle<v8::internal::Object>) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
16: 0x100676652 v8::internal::JsonStringifier::Result v8::internal::JsonStringifier::Serialize_<false>(v8::internal::Handle<v8::internal::Object>, bool, v8::internal::Handle<v8::internal::Object>) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
17: 0x100674716 v8::internal::JsonStringifier::SerializeArrayLikeSlow(v8::internal::Handle<v8::internal::JSReceiver>, unsigned int, unsigned int) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
18: 0x1006755f5 v8::internal::JsonStringifier::Result v8::internal::JsonStringifier::Serialize_<false>(v8::internal::Handle<v8::internal::Object>, bool, v8::internal::Handle<v8::internal::Object>) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
19: 0x10067209f v8::internal::JsonStringifier::Stringify(v8::internal::Handle<v8::internal::Object>, v8::internal::Handle<v8::internal::Object>, v8::internal::Handle<v8::internal::Object>) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
20: 0x1002709ec v8::internal::Builtin_Impl_JsonStringify(v8::internal::BuiltinArguments, v8::internal::Isolate*) [/Users/patarapolw/.nvm/versions/node/v10.20.1/bin/node]
21: 0x61284adbf9d 
22: 0x61284a8a5c3 
23: 0x61284f746db 
24: 0x61284a918d5 
25: 0x61284a8a5c3 
26: 0x61284a918d5 
27: 0x61284a918d5 
28: 0x61284a8a5c3 
29: 0x61284a918d5 
30: 0x61284a8a5c3 

Comments

[simonhaisz]

Not sure what the references to multi-process/thread in the title, but you are running out of memory while trying to build a big string from JSON.stringify. I have seen that before when you have large arrays. I have no idea how you are writing these 10K records to the DB and I don't know the size of each object in the array, so it's difficult to give advice. You are blowing the memory limit at ~1.4GB.

Maybe try going down to 1K or even 100 records at a time to see if that fixes the issue.

[Pacharapol Withayasakpunt]

I fixed with worker_threads or threads.js, but some threads die due to SQLite write lock (even if I enable WAL mode.)

Maybe try going down to 1K or even 100 records at a time to see if that fixes the issue.

I am using a web server with POST request / WebSockets, so I assume that I cannot really control concurrency???

[simonhaisz]

From the node docs

Workers (threads) are useful for performing CPU-intensive JavaScript operations. They will not help much with I/O-intensive work. Node.js’s built-in asynchronous I/O operations are more efficient than Workers can be.

If each thread is writing then they will block each other, regardless of what mode you use. I don't know why you started using worker threads, maybe you have some expensive request handling before writing to the DB, but they are not going to help you write to the DB faster.

[Pacharapol Withayasakpunt]

maybe you have some expensive request handling before writing to the DB,

Thanks, I will try to extract the said steps.

[simonhaisz]

The good news is that you clearly have an easy method of testing this. Great! I suggest you use that to better understand you system. My recommendation would be to remove all of the worker thread stuff and then run your test against the system while under a profiler. That should give you the truth about where the work is actually being done in your system. Only then should you then look at applying techniques, whether worker threads or otherwise, to improve performance.

[Pacharapol Withayasakpunt]

The real issue is PK/FK/UNIQUE. How do I enforce that? My original solution was to MIX READ/WRITE's, but it is probably a bad idea. How about?

• UUID PK/FK vs INT PK, but SELECT MAX(PK) + 1
• UNIQUE with new Set() / new Map(), where keys are JSON.stringify(Array)
• BEGIN TRANSACTION -- COMMIT + Write only, no reads at the time of writing.
• Do I need to PRAGMA foreign_keys=off temporarily? Or maybe I don't need to do that?

and then run your test against the system while under a profiler.

Not really sure how do I do this in Node.js

whether worker threads or otherwise, to improve performance.

better-sqlite3 is sync by default. Not really sure if this is a good or bad idea. The only way to go async is to use Worker or Fork.

[simonhaisz]

Key uniqueness is an entirely different problem. I don't use SQL DBs that much so I cannot offer advice on the technical options you specify. Though I expect that before anyone else could you would need to share more of the use-case about what you are actually trying to accomplish.

When trying to manage key uniqueness there are three fundamental approaches you can take.

1. Assume the data is already unique and do nothing apart from throwing errors when this assumption is incorrect.
2. It is the writers responsibility to ensure uniqueness. This generally involves querying the DB to find out about the existing keys but doesn't have to. If you have access to all of the data outside of the DB before it is inserted you can create unique keys based upon that before inserting.
3. Define the DB schema so that uniqueness is automatic. Auto incrementing numbers or GUIDs work here.

From the sound of it I'm assuming that your request handler is not just inserting thousands of records but is inserting records into multiple tables, hence your reference to foreign keys. So the crux of the problem isn't necessarily key uniquness but in finding the keys of the records you just inserted into order to refer to them in other records you are inserting. If you got with the above option #3 then you have to query the DB to find the values of the keys that the DB determined for the inserted records. If you go with option #2 you can create the key values yourself before insert so you do not need to query afterwards, but that means you are responsible for keeping them unique. GUIDs are the simplest approach, not sure why you don't like that one. If you went with that your system would already be working.