Use WASM shared memory?

[infogulch]

As of two months ago, wazero has symbols that mention shared memory. Could this be used to lift some of the caveats mentioned in the readme such as allowing locking mode NORMAL and connection pooling?

[infogulch]

I'm quite satisfied to get issue number 69, nice! 😄

Sorry, did you intend to mark this discussion "Closed as resolved"?

[ncruces]

No, I'm sorry.

I intended to start a discussion of the possibilities, instead of an issue that tracks progress… or lack thereof.

I've had WAL as a goal from the beginning, started and deleted a couple of design docs, jumped at every new avenue, including this one (there's a threads branch).

I also looked into shared cache as a mitigation.

I honestly don't think it's feasible with the current state of affairs, but I'm open to suggestions.

I want to keep this relatively simple to maintain, and in particular, I want to remain able update to new SQLite versions in less than a day's work.

[infogulch]

No problem, I was just a little confused by the misclick. Perhaps I could have been more patient. 😄

I can appreciate your goal of designing this library to be simple enough to keep up to date easily.

[ncruces]

That's a great question, with a long answer.

The simplest answer to your question is: yes, kinda. It could certainly help; but with its own set of caveats.

I explicitly mention WAL in the README because it's considered best practice to use it, and as such, I'd really like to support it, to as full an extent as possible.

As the README states, I created a from scratch VFS which has a bunch of advantages and compromises.

So, in the current architecture, each SQLite connection lives in its own little sandbox, completely isolated from other connections. Through the VFS layer, Go acts like an OS kernel, managing these OS “processes.” There are no threads, no locks, no shared cache, etc. It's a good model.

Unfortunately, SQLite expects to be able to share memory between these “processes” for the WAL index (just a few pages) which we can't do.

With WASM shared memory another model is possible. We create a big (4GB) shared memory, and we run all our SQLite connections on it. But, if we want to have any concurrency, we need threads, locks, etc.

At the time of this writing, this is possible to compile with wasi-sdk, and to run with wazero; go-re2 uses this model.

But… it's complicated. The ABI to setup threads is far from obvious or stable. wazero APIs, are likewise, experimental. Moving to this model would for sure involve API redesign for this package. And support for platforms other than 64-bit Linux/Windows/macOS is dubious at best.

Finally, this still won't allow our SQLite connections to share memory with SQLite connections from other processes, like Litestream. It's also an open question how to lock the database to ensure other processes couldn't access it while we have multiple connections open (it's a change to the locking protocol that needs a design).

I've also considered other options. I could create a VFS where each connection thinks it's in memory journal mode, but then I send transactions to the WAL. Or I could look into the libSQL virtual WAL.

All solutions are more work than I want to take in at the moment, and ultimately, maintain.

[ncruces]

That's not part of the main branch of SQLite and will not be supported, unless (maybe) you build SQLite yourself.

[infogulch]

Yes I was mistaken. I wrote a new benchmark to try to bench WAL mode:

infogulch/go-sqlite-bench@master...bench-wal

./build-all.sh
./run-all.sh -simple=false -complex=false -many=false -large=false -concurrent=false -wal=true

It should read about 3x as much as it writes, and write across 1/4/16 goroutines.

It fails to run on several of the implementations, usually with a sqlite_busy error (the benchmark may need some tweaks). The benchmark for the wal branch of ncruces/go-sqlite3 appears to fail to read back rows after committing them, indicating corruption:

6_wal/1  - insert_query - ncruces    -      11124
6_wal/1  - dbsize       - ncruces    -   56573952
panic: must be equal but was different: 25000 != 1040

goroutine 99 [running]:
github.com/cvilsmeier/go-sqlite-bench/app.MustBeEqual(...)
        go-sqlite-bench/app/util.go:23
github.com/cvilsmeier/go-sqlite-bench/app.benchWal.func2.1(0xc001712000?)
        go-sqlite-bench/app/app.go:490 +0x568
github.com/cvilsmeier/go-sqlite-bench/app.benchWal.func2()
        go-sqlite-bench/app/app.go:505 +0x464
created by github.com/cvilsmeier/go-sqlite-bench/app.benchWal in goroutine 1
        go-sqlite-bench/app/app.go:475 +0x545
Sun Mar 31 14:49:18 CDT 2024

[ncruces]

The fact that mptest is failing, means the implementation is buggy. I'm not discarding the idea outright, but it's not a great sign. It may still be the case that I just messed up the locking, not the shared memory bits. Very few VFSes implement WAL, and it's very underspecified.

[ncruces]

Latest commit consistently passes mptest. This is good.
Must consider portability now.

[infogulch]

Great! With the latest commit, my benchmark doesn't panic or give invalid results anymore.

6_wal/1  - insert_query - ncruces    -      10245
6_wal/1  - dbsize       - ncruces    -   56573952
6_wal/2  - insert_query - ncruces    -       7493
6_wal/2  - dbsize       - ncruces    -   58912768
6_wal/4  - insert_query - ncruces    -       7574
6_wal/4  - dbsize       - ncruces    -   58814464
6_wal/8  - insert_query - ncruces    -       8066
6_wal/8  - dbsize       - ncruces    -   59703296
6_wal/16 - insert_query - ncruces    -       8544
6_wal/16 - dbsize       - ncruces    -   58777600

[ncruces]

This is being worked on #71.