←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
353 points HunOL | 1 comments | 01 Nov 25 12:59 UTC | HN request time: 0s | source
Show context
Leherenn ◴[01 Nov 25 15:23 UTC] No.45782370[source]▶
A bit off topic, but there seems to be quite a few SQLite experts here.

We're having troubles with memory usage when using SQLite in-memory DBs with "a lot" of inserts and deletes. Like maybe inserting up to a 100k rows in 5 minutes, deleting them all after 5 minutes, and doing this for days on end. We see memory usage slowly creeping up over hours/days when doing that.

Any settings that would help with that? It's particularly bad on macOS, we've had instances where we reached 1GB of memory usage according to Activity Monitor after a week or so.

replies(4): >>45782392 #>>45782400 #>>45783445 #>>45783464 #
asa400 ◴[01 Nov 25 15:25 UTC] No.45782392[source]▶
Are you running vacuums at all? auto_vacuum enabled at all?

https://sqlite.org/lang_vacuum.html

replies(1): >>45782475 #
porridgeraisin ◴[01 Nov 25 15:33 UTC] No.45782475[source]▶
In memory DBs don't have anything to vacuum.

However... what you (and OP) are looking for might be pragma shrink_memory [1].

[1] https://sqlite.org/pragma.html#pragma_shrink_memory

replies(1): >>45782776 #
1. asa400 ◴[01 Nov 25 16:08 UTC] No.45782776[source]▶
Ah, you're correct. I read too fast and missed that it was in-memory databases specifically!