[REQUEST] Memory with writeback

[the-hotmann]

For performance reasons I would like to ask, if this is possible?

Opening an existing SQLite3 file and writing it into Memory. But everytime I write or read everything, I would like it to happen in memory (so way quicker). But after it is written an async task should write it back.

Currently I have tried it likle this:

db, err = sql.Open("sqlite3", "file:../data/tickets.db?mode=memory&cache=shared&_journal_mode=MEMORY")

But everytime I read/write it is way slower then other SQLite3 implementations - like in GORM, Python3 ...
Here a write and read takes about 1,5ms, but for these others it actually is about 0,05ms which for me is a big difference.

So I would like to know what I migh be doing wrong., Are any of these settings (mode=memory&cache=shared&_journal_mode=MEMORY) not beneficial for performance?
Even when I have the mode=memory set, it does not seem to be actually as fast as it should be if it would have been in memory. And it does not work, when I want it to load a DB from SSD into memory. I already have a DB, and this should be copied over into memory - I dont want to have a fresh DB everytime, as this is what is happening as soon as I use mode=memory.

Would be happy to get any feedback and I am open for an discussion.

[rittneje]

So I would like to know what I migh be doing wrong., Are any of these settings (mode=memory&cache=shared&_journal_mode=MEMORY) not beneficial for performance?

Instead of mode=memory you should use vfs=memdb. (Note that you MUST prefix your pseudo-filename with a slash or you will face issues with connection pooling.) Do not use cache=shared. Consider using _journal_mode=wal if reads will be concurrent with writes.

Also consider bounding the connection pool that database/sql implements.

• https://pkg.go.dev/database/sql#DB.SetMaxOpenConns
• https://pkg.go.dev/database/sql#DB.SetMaxIdleConns
• https://pkg.go.dev/database/sql#DB.SetConnMaxLifetime
• https://pkg.go.dev/database/sql#DB.SetConnMaxIdleTime

Generally I recommend making two pools - one for read/write transactions that only allows one connection, and one for read-only transactions that allows multiple connections. This is because SQLite does not support multiple concurrent writers, so allowing multiple writer connections is usually counter-productive.

I already have a DB, and this should be copied over into memory - I dont want to have a fresh DB everytime, as this is what is happening as soon as I use mode=memory.

For now, your best bet is to use the backup API to copy the real database into your in-memory database. This is the safest option if the real database is written to by other processes. Note that you will need to leverage an escape hatch to call this method.

[otoolep]

@rittneje gave me this very useful advice before, and it's become how rqlite sets up its connections. It's worked well. See:

https://github.com/rqlite/rqlite/blob/master/db/db.go#L87