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

131–140 of 189 posts

Re: SQLite concurrency and why you should care about it

#131

Post by npodbielski »

kccqzy wrote: post #87
ricardobeat wrote: post #29

Articles like this leave me with an uneasy feeling that the “solutions” are just blind workarounds - more debugging/research should be able to expose exactly what the problem is, now that would be something worth sharing.

Articles like this give me the feeling that the author did a little bit of research and shared a suboptimal solution, and was hoping that experts on HN would present better solutions. Wasn't there a saying about how the best way to get correct answers is to post not just the question but the wrong answers to it?

If something is stupid but it works then it is not stupid. If this will help them find a solution then sure why not. Though I am wondering if this would not be easier to just use postgress and focus on features instead.

Re: SQLite concurrency and why you should care about it

#132

Post by npodbielski »

abound wrote: post #48
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.

Jellyfin is a self-hostable media server. If they "used Postgres", that means anyone who runs it needs Postgres. I think SQLite is the better choice for this kind of application, if one is going to choose a single database instead of some pluggable layer

What is the problem to bundle postgress db engine in the docker server? If you want to install it from package, they can have postgress dB as an option with the warning somewhere that it is 'recomended'. I am sure that if you are able to slefhost stuff you are able to install postgress too.

Re: SQLite concurrency and why you should care about it

#133

Post by npodbielski »

nick_ wrote: post #107
petters wrote: post #88

Earlier quoted context omitted.

Jellyfin is mostly for a single household, right? Sqlite should be much more than sufficient for Jellyfin (if used correctly). Unfortunately, reading this article you get the impression that they are not using it optimally

Agreed. How can a media file sharing app possibly saturate Sqlite's write limit? I would use an app-level global lock on all writes to Sqlite.

Probably during scanning libraries? They read hundreds of files and for each of them look for metadata in the internet like discogs and similar. So sure if implemented as async in c# you could run into this issue.

Re: SQLite concurrency and why you should care about it

#134

Post by andersmurphy »

summarity wrote: post #8
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…

I've always tried to avoid situations that could lead to SQLITE_BUSY. SQLITE_BUSY is an architecture smell. For standard SQLite in WAL, I usually structure an app with a read "connection" pool, and a single-entry write connection pool. Making the application aware of who _actually_ holds the write lock gives you the ability to proactively design access patterns, not try to react in the moment, and to get observabilit…

Yes! This is the way.

Honestly, its the key to getting the most out of sqlite. It also allows for transaction batching and various other forms if batching that can massively improve write throughput.

Re: SQLite concurrency and why you should care about it

#135

Post by ErroneousBosh »

EionRobb wrote: post #90

One of the biggest contributors I've had in the past for SQLite blocking was disk fragmentation. We had some old Android tablets using our app 8 hours a day for 3-4 years. They'd complain if locking errors and slowness but every time they'd copy their data to send to us, we couldn't replicate, even on the same hardware. It wasn't until we bought one user a new device and got them to send us the old one that we could…

> One of the biggest contributors I've had in the past for SQLite blocking was disk fragmentation.

Is that even still a thing? I thought modern filesystems like ext4 were supposed to be largely immune to that.

Re: SQLite concurrency and why you should care about it

#136

Post by andersmurphy »

stefanos82 wrote: post #11

When hctree [1] becomes stable in SQLite, it will be the only database I will be using lol! I presume the `hc` part in project's code name should be High Concurrency. [1] https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html

Thing is if you design your app to have a single writer you can probably get higher throughput than multiple writers in concurrency mode.

Re: SQLite concurrency and why you should care about it

#137

Post by andersmurphy »

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.

Sqlite is fine you need to read the extensive documentation though to get the most out of it. It also has terrible defaults.

I think the author od this article missed sqlite_busy.

Once you do have it set up correctly, are handling a single writer at the application level and have litestream set up your off to the races assuming your app can scale on a single box (it most likely can).

Re: SQLite concurrency and why you should care about it

#138

Post by DANmode »

axitanull wrote: post #126
DANmode wrote: post #102

Earlier quoted context omitted.

Perform the file operation, after zipping the existing db as a backup, and leaving the original where it sits. Success, performance increase. Failure, no change.

Forgive my lack of knowledge, but how is simply zipping the original file would "defrag" the file? Shouldn't the file be moved into different disk fragment first, for that to happen?

That's "the file operation" :]

Again: zip is a backup

Re: SQLite concurrency and why you should care about it

#139

Post by apitman »

simonw wrote: post #18
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…

Yeah I read the OP and my first instinct was that this is SQLITE_BUSY. I've been collecting posts about that here: https://simonwillison.net/tags/sqlite-busy/

The necessity of this sort of tribal knowledge kills a lot of the simplicity of sqlite for me. Honestly it seems to have a lot of footguns. I've tried to understand proper concurrent use of sqlite with Golang about 5 times and never come away feeling like I actually get it.

Re: SQLite concurrency and why you should care about it

#140

Post by apitman »

kccqzy wrote: post #87
ricardobeat wrote: post #29

Articles like this leave me with an uneasy feeling that the “solutions” are just blind workarounds - more debugging/research should be able to expose exactly what the problem is, now that would be something worth sharing.

Articles like this give me the feeling that the author did a little bit of research and shared a suboptimal solution, and was hoping that experts on HN would present better solutions. Wasn't there a saying about how the best way to get correct answers is to post not just the question but the wrong answers to it?

Cunningham's Law
Post reply on HN