Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite concurrency and why you should care about it

jellyfin.org

Post reply on HN
Print view View original

31–40 of 189 posts

Re: SQLite concurrency and why you should care about it

#31

Post by sethev »

dv35z wrote: post #14

Curious if anyone has strategies on how to perform parallel writes to an SQLite database using Python's `multiprocessing` Pool. I am using it to loop through a database of 11,000 words, hit an HTTP API for each (ChatGPT) and generate example sentences for the word. I would love to be able to asynchronously launch these API calls and have them come back and update the database row when ready, but not sure how to handl…

Have you tried it?

What you're describing sounds like it would work fine to me. The blog post is misleading imho - it implies that SQLite doesn't handle concurrency at all. In reality, you can perform a bunch of writes in parallel and SQLite will handle running them one after the other internally. This works across applications and processes, you just need to use SQLite to interact with the database. The blog post is also misleading when it implies that the application has to manage access to the database file in some way.

Yes, it's correct that only one of those writes will execute at a time but it's not like you have to account for that in your code, especially in a batch-style process like you're describing. In your Python code, you'll just update a row and it will look like that happens concurrently with other updates.

I'll bet that your call to ChatGPT will take far longer than updating the row, even accounting for time when the write is waiting for its turn in SQLite.

Use WAL-mode for the best performance (and to reduce SQLITE_BUSY errors).

Re: SQLite concurrency and why you should care about it

#32

Post by mikeocool »

mickeyp wrote: post #17
tejinderss wrote: post #13

Earlier quoted context omitted.

Do you know any good default PRAGMAs that one should enable?

These are my PRAGMAs and not your PRAGMAs. Be very careful about blindly copying something that may or may not match your needs. PRAGMA foreign_keys=ON PRAGMA recursive_triggers=ON PRAGMA journal_mode=WAL PRAGMA busy_timeout=30000 PRAGMA synchronous=NORMAL PRAGMA cache_size=10000 PRAGMA temp_store=MEMORY PRAGMA wal_autocheckpoint=1000 PRAGMA optimize Note that I do not use auto_vacuum for DELETEs are uncommon in my w…

Using strict tables is also a good thing to do, if you value your sanity.

Re: SQLite concurrency and why you should care about it

#33

Post by Leherenn »

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.

Re: SQLite concurrency and why you should care about it

#34

Post by asa400 »

Leherenn wrote: post #33

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 pa…

Are you running vacuums at all? auto_vacuum enabled at all?

https://sqlite.org/lang_vacuum.html

Re: SQLite concurrency and why you should care about it

#35

Post by kachapopopow »

Leherenn wrote: post #33

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 pa…

sounds like normal behavior of adjusting buffers to better fit the usecase, not sure if it applies to sqlite or if sqlite even implements dynamic buffers.

Re: SQLite concurrency and why you should care about it

#36

Post by eduction »

mangecoeur wrote: post #28

Sqlite is a great bit of technology but sometimes I read articles like this and think, maybe they should have used postgres. I you don’t specifically need the “one file portability” aspect of sqlite, or its not embedded (in which case you shouldn’t have concurrency issues), Postgres is easy to get running and solves these problems.

100%. I specifically clicked for the “why you should care” and was disappointed I could not find it.

I certainly don’t mind if someone is pushing the limits of what SQLite is designed for but personally I’d just rather invest the (rather small) overhead of setting up a db server if I need a lot of concurrency.

Re: SQLite concurrency and why you should care about it

#37

Post by porridgeraisin »

asa400 wrote: post #34
Leherenn wrote: post #33

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 pa…

Are you running vacuums at all? auto_vacuum enabled at all? https://sqlite.org/lang_vacuum.html

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

Re: SQLite concurrency and why you should care about it

#38

Post by porridgeraisin »

mickeyp wrote: post #17
tejinderss wrote: post #13

Earlier quoted context omitted.

Do you know any good default PRAGMAs that one should enable?

These are my PRAGMAs and not your PRAGMAs. Be very careful about blindly copying something that may or may not match your needs. PRAGMA foreign_keys=ON PRAGMA recursive_triggers=ON PRAGMA journal_mode=WAL PRAGMA busy_timeout=30000 PRAGMA synchronous=NORMAL PRAGMA cache_size=10000 PRAGMA temp_store=MEMORY PRAGMA wal_autocheckpoint=1000 PRAGMA optimize Note that I do not use auto_vacuum for DELETEs are uncommon in my w…

You should pragna optimize before TX end, not at tx start.

Except for long lived connections where you do it periodically.

https://www.sqlite.org/lang_analyze.html#periodically_run_pr...

Re: SQLite concurrency and why you should care about it

#39

Post by kijin »

asa400 wrote: post #2

In SQLite, transactions by default start in “deferred” mode. This means they do not take a write lock until they attempt to perform a write. You get SQLITE_BUSY when transaction #1 starts in read mode, transaction #2 starts in write mode, and then transaction #1 attempts to upgrade from read to write mode while transaction #2 still holds the write lock. The fix is to set a busy_timeout and to begin any transaction th…

Wouldn't that "fix" make the problem worse on the whole, by making transactions hold onto write locks longer than necessary? (Not trying to disagree, just curious about potential downsides.)

Re: SQLite concurrency and why you should care about it

#40

Post by mickeyp »

hruk wrote: post #22
mickeyp wrote: post #15

Earlier quoted context omitted.

Edit: disregard. I read it as he'd done it and had contention problems. You can't. You have a single writer - it's one of the many reasons sqlite is terrible for serious work. You'll need a multiprocessing Queue and a writer that picks off sentences one by one and commits it.

This is just untrue - the naive implementation (make the API call, write a single row to the db) will work fine, as transactions are quite fast on modern hardware. What do you consider "serious" work? We've served a SaaS product from SQLite (roughly 300-500 queries per second at peak) for several years without much pain. Plus, it's not like PG and MySQL are pain-free, either - they all have their quirks.

Edit: disregard. I read it as he'd done it and had contention problems.

I mean it's not if he's got lock contention from BUSY signals, now is it, as he implies. Much of his issues will stem from transactions blocking each other; maybe they are long-lived, maybe they are not. And those 3-500 queries --- are they writes or reads? Because reads is not a problem.

Post reply on HN