shared in memory db: :memory:+shared cache OR memdb VFS?
(1.1) By andse-t610 on 2021-07-06 14:20:17 edited from 1.0 [link] [source]
Hello! I want to have in-memory db shared between multiple db connections.
There was one way before release 3.36.0 that is described in the in-memory db docs. That is shared cache:
or as i understand the same
sqlite 3.36.0 release notes says:
The "memdb" VFS now allows the same in-memory database to be shared among multiple database connections in the same process as long as the database name begins with "/".
So now I can use
This way currently is not described in the docs (even nothing is found on request "memdb").
Which should I use? Can someone describe advantages and disadvantages of each variant?
And one question I had when I saw the 3.36 announcement regarding MemDB VFS,
was whether WAL mode was supported in the new mode, for MVCC.
All readers/writers are in the same process, so having shared-memory for WAL
downgrades to plain memory then, just with some synchronization, no?
If that alternative is really not documented, or is too difficult to find, the docs should be improved. (And, in that case, thanks for the report!)
To me, looking at the code, it appears that the memdb specification variations all bottom-out in the common sqlite_open*() implementation with SQLITE_OPEN_SHAREDCACHE passed in among the flags. (I would be shocked if those alternative methods of getting the shared effect for an in-memory DB did not bottom out to the same implementation. It would represent a much better optimization opportunity than many others I've seen exploited.)
I will see to getting this documented (made easier to find.) In the meanwhile, I no reason not to favor the documented approach beyond the possibility of saving a few bytes of data storage.
Then no WAL-mode and no MVCC then, I guess :(
Too bad. I thought going for the VFS approach was precisely to gain WAL-mode
for shared in-memory DBs, thus I don't quite understand why a separate approach
was added, if it ends up going through the same code in the end.
There must be something I'm missing. Looking forward to the new doc.
(7) By andse-t610 on 2021-07-06 15:10:04 in reply to 5 [link] [source]
Unfortunately no wal:
PRAGMA jorunal_mode=wal; PRAGMA journal_mode
memory for each in-memory variant.
(6) By andse-t610 on 2021-07-06 15:06:59 in reply to 3 [link] [source]
[a] commit says
This provides a way for threads to share an in-memory database without the use of shared-cache mode
where is the truth?
Regarding my statement:
To me, looking at the code, it appears that the memdb specification variations all bottom-out in the common sqlite_open*() implementation with SQLITE_OPEN_SHAREDCACHE passed in among the flags.
I am going to document what these open options do, after a much more careful study of the code to see what combinations of flags reach the underlying open(). That study may induce a walk-back of the above. Or not.
In my opinion, a shared in-memory store and a shared-cache mode are largely redundant. If the performance advantages all favor one or the other, one should vanish as a needless pitfall. And if they trade against each other, that should also be documented.
(9) By andse-t610 on 2021-07-06 15:47:25 in reply to 8 [link] [source]
I will wait for the documentation update. Thank you!
Well, the point is that shared-cache is frowned upon, and kinda deprecated,
except on very low resources devices, which is precisely not the case of wanting
to use it on large multi-core desktop machines. Except shared-cache was the only
known way to have separate SQLite connections sharing the same in-memory DB within
the bounds of a single process, allowing concurrent access to the same DB from several
threads. Until MemDB VFS. But then you say they end up being the same code... that's confusing.
Shared-cache implies normal Journal mode, with readers blocking writers, and the writer
blocking readers. WAL not supported. While nothing prevents in theory a VFS from supporting
WAL-mode, as long as that VFS supports SHM, which it can if that VFS is in-memory.
Thus my question whether MemDB VFS supports WAL-mode for true MVCC.
Hopefully everything will be clearer soon. --DD