Temporary tables

[vladar]

Curious what do you think would be the best way to implement something like a temporary table for sorting.

Imagine we have data in LMDB and want to sort it by some field but we only use the result once and then throw it away (the result is needed for 2-5 seconds at most).

We can load the data in memory and sort it in node heap but this will lead to a big memory spike. Instead, I've been thinking about just using some dupSort key as a temporary table.

The idea is to write there while iterating LMDB cursor with the original data, then do another iteration on values and then remove the dupSort key completely.

Not sure if it makes sense. But if it does - the next question is how to avoid persisting this value to disk - what would be preferable - noSync: true or setting a huge commitDelay? Or maybe I just can use a separate async transaction that returns a promise after the cursor is closed and dupSort key is removed?

[kriszyp]

If you are using this purely to build a table to sort and then immediately removing, you might consider using a child transaction to build the table and then aborting it once you are done instead of committing it.

However, if you need this table to last longer (even 2-5 seconds is a long time to keep a transaction open) for additional interaction, running with noSync does seem like a good idea. Setting a large commitDelay wouldn't accomplish much, that just delays the transaction you want to start (it delays the start of the transaction too). Running noSync in combination with useWritemap seems like it would most ideal if you want to keep the table alive for a little while (I was seeing some issues with that combination on Windows which I have fixed). The combination of noSync and useWritemap means there will basically be no I/O operations involved in a transaction.

[vladar]

Makes sense, thank you

[vladar]

I did a quick and dirty benchmark and looks like it is a viable approach.

Setup: 1,000,000 objects stored in lmdb

Option 1: load all objects in memory and sort in JS, then iterate final array:

load: 10.290s
sort in memory: 7.505s
iterate: 35.616ms
cpu: 171.1% | rss: 1.7 GB (10.0%) | heap: 1.2 GB / 1.2 GB (97.0%) | ext: 1.1 MB | delay: 0 ms | 00:00:19 | loadavg: 0.00, 0.00, 0.00

Done in 20.89s.

Option 2: iterate and put to dupSort db, then iterate dupSort values:

load+sort: 10.270s
iterate: 638.444ms
cpu: 115.3% | rss: 872 MB (5.2%) | heap: 166 MB / 201 MB (82.5%) | ext: 38 MB | delay: 749 ms | 00:00:11 | loadavg: 0.00, 0.00, 0.00

Done in 11.61s.

This is amazing 😲

The caveat is that in the 2nd approach we only have partial data and may need to load full objects again. But in our use case, we need maybe 100 "full" objects after sorting (with limit/offset).

[kriszyp]

Very cool! I was actually meaning to ask which approach you ended up using, cool to see a good use case for the noSync+useWritemap approach (I added some notes to the readme encouraging that combination).