Using SQLITE_OPEN_READONLY to allow read concurrency in some VFSs?

[my-lalex]

Hello guys,

After reading a very interesting post #246 (but too high-level - or low-level? - for me) I would have a question

I've created a SQLite in-browser client using wa-sqlite, with read concurrency as the main goal (as I can have a heavy query running on one "thread" while having a lot of fast queries at the same time).... it consist in a pool of workers, allowing only one concurrent write: it's quite efficient, but not enough yet.
The only VFS allowing that for now is OPFSPermutedVFS, but with a large database (~3.5Gb database, and it's gonna grow), it has its cons:

• the memory footprint is quite large in workers (~150Mb by worker)
• the initialisation time is ~10s by worker. As I use a SharedArrayBuffer lock to open database, with 3 workers it's ~30s before being able to use the pool. The UX is bad at startup because of that...

Currently, the "writer worker" is assigned at "query-time" using a acquire/release classic flow, looking for available workers and passing the writer along to a writer request queue, then setting it back to "reader" state when no writing request are in the queue
But it wouldn't really be a problem for me to designate a worker at startup dedicated to writing, and I could live with a writer and 2 or 3 readers
It wouldn't be a problem for me either not being able to write while reading, as I can control the UI flow to prevent this...

The main question is actually: would it be possible to use another VFS from wa-sqlite (with or withou modification) to do that (I think about OPFSCoopSyncVFS in sync mode) by using SQLITE_OPEN_READONLY flag on the reader threads? I'm not skilled enough about VFSs and SQLite locks to determine that, and before refactoring the whole request queue engine, I'd like to hear about your advices 😉

[rhashimoto]

@my-lalex You can only get real concurrency with an OPFS VFS in a browser that supports the mode: 'readwrite-unsafe' option to createSyncAccessHandle(), which allows multiple open access handles on an OPFS file. As of now that would be only Chromium-based browsers.

OPFSAdaptiveVFS will use that feature if it is available. You will also need to enable shared reads when instantiating the VFS, like this (because the default lockPolicy is 'exclusive'):

const vfs = new OPFSAdaptiveVFS('my-vfs', module, { lockPolicy: 'shared' });

That's probably the easiest thing to try. That should definitely be faster to start up. I'm not sure how query performance will compare on your workload.

[my-lalex]

Hi @rhashimoto , thanks for your kind answer.

This is actually the perfect solution for me: it's blazing fast at start and read concurrency is there!
And as I can put a Chromium based browser as a prerequisite, it will be OK, hoping that other browser will support this readwrite-unsafe option soon for a more universal code....

For my own culture, could you explain to me what's preventing OPFSCoopSyncVFS to use the readwrite-unsafe option? A sync VFS would be even faster I imagine. I think all this stuff is so interesting but I don't have enough time to go deep in this...

One more thing: I'd like to say how incredible is your work, I'm really amazed by what you did with wa-sqlite!

Thanks again

[rhashimoto]

And as I can put a Chromium based browser as a prerequisite, it will be OK, hoping that other browser will support this readwrite-unsafe option soon for a more universal code....

Note that on Chromium you can also use the JSPI build (instead of the Asyncify build). Actually wide support of JSPI appears to be much closer than readwrite-unsafe.

For my own culture, could you explain to me what's preventing OPFSCoopSyncVFS to use the readwrite-unsafe option?

There is no real technical reason. OPFSCoopSyncVFS started out as modifying AccessHandlePoolVFS to support multiple connections, and it made more sense to have it work on all browsers because AccessHandlePoolVFS does. I didn't want to add readwrite-unsafe at the time because (1) it was an extra complication and just making it work was already a challenge, (2) there were already examples (OPFSAdaptiveVFS and OPFSPermutedVFS) showing the usefulness of readwrite-unsafe, and (3) it wasn't obvious that anyone would really use a VFS based on the retry hack (and it is definitely a hack).

You may want to keep an eye on the Discussions here for future developments and ideas.

[my-lalex]

You may want to keep an eye on the Discussions here for future developments and ideas.

I definitely will, this project is awesome and you sharing some much about your experimentations is a chance for the community.

Thanks again