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

111–120 of 189 posts

Re: SQLite concurrency and why you should care about it

#111

Post by reddalo »

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

They're actually planning on migrating to Postgres in a future release:

>[...] it also opens up new possibilities - not officially yet, but soon - for running Jellyfin backed by "real" database systems like PostgreSQL, providing new options for redundancy, load-balancing, and easier maintenance and administration. The future looks very bright!

https://jellyfin.org/posts/jellyfin-release-10.11.0/

Re: SQLite concurrency and why you should care about it

#112

Post by Yodel0914 »

tombert wrote: post #89

Does this mean I can finally load-balance with multiple Jellyfin instances? A million years ago, back when I still used Emby, I was annoyed that I couldn't use it across multiple in Docker Swarm due to locking of SQLite. It really annoyed me, enough to where I started (but never completed) a driver to change the DB to postgres [1]. I ended up moving everything over to a single server, which is mostly fine unless I ha…

Jellyfin have just gone through a massive refactor and pulled all their data access code into EFCore. This opens the path for supporting different RBDMSs which think is next on their list.

Re: SQLite concurrency and why you should care about it

#113

Post by thayne »

throwaway894345 wrote: post #84
thayne wrote: post #78

Earlier quoted context omitted.

Is running multiple nodes a typical way to run Jellyfin through? I would expect that most Jellyfin users only run a single instance at a time.

Yes, but you have to go out of your way when writing software to make it so the software can only run on one node at a time. Or rather, well-architected software should require minimal, isolated edits to run in a distributed configuration (for example, replacing SQLite with a distributed SQLite).

That's just not true. Distributed software is much more complicated and difficult than non-distributed software. Distributed systems have many failure modes that you don't have to worry about in non-distributed systems.

Now maybe you could have an abstraction layer over your storage layer that supports multiple data stores, including a distributed one. But that comes with tradeoffs, like being limited to the least common denominator of features of the data stores, and having to implement the abstraction layer for multiple data stores.

Re: SQLite concurrency and why you should care about it

#114

Post by rpcope1 »

Do these guys really not understand that WAL is still single writer multi reader? You could do concurrent (but not parallel) write DML in both the normal and WAL journaling models. WAL alleviates read transactions being blocked by writers but you still have to lock it down to a single writer. It would be nice if SQLite3 had full blown MVCC, but it still works if you understand it.

Re: SQLite concurrency and why you should care about it

#115

Post by izacus »

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…

That's much more likely flash degradation than actual fragmentation. Did you use cheap tablets with eMMC storage?

Re: SQLite concurrency and why you should care about it

#116

Post by ants_everywhere »

throwaway894345 wrote: post #52
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.

As a user of Jellyfin, I’m very sad that it doesn’t just use Postgres. I basically have to run an NFS system just for Jellyfin so that its data can be available to it no matter which node it gets scheduled on and also that there are never multiple instances running at the same time, even during deployments (e.g., I need to take care that deployments completely stop the first Jellyfin instance before starting the subs…

I have the same experience. SQLite has been a source of most Jellyfin problems, and Jellyfin has more problems than the rest of the ~ 150 containers I run regularly.

A stateless design where a stateless jellyfin server talks to a postgres database would be simpler and more robust.

Re: SQLite concurrency and why you should care about it

#117

Post by [deleted] »

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?

[deleted]

Re: SQLite concurrency and why you should care about it

#118

Post by [deleted] »

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…

[deleted]

Re: SQLite concurrency and why you should care about it

#119

Post by zeroq »

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 has so many small benefits for tiny projects it can't be easily replaced.

It's like saying "oh, you want to visit Austrian country side next month and you're asking for advice for best tent? How about you build a cabin instead?".

Re: SQLite concurrency and why you should care about it

#120

Post by liuliu »

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…

Note that busy_timeout is not applicable to SQLite in this case (the SQLITE_BUSY issued immediately, no wait in this case).

Also this is because WAL mode (and I believe only for WAL mode, since there is really no concurrent reads in the other mode).

The reason is because pages in WAL mode appended to a single log file. Hence, if you read something inside a BEGIN transaction, later wants to mutate something else, there could be another page already appended and potentially interfere with the strict serializable guarantee for WAL mode. Hence, SQLite has to fail at the point of lock upgrade.

Immediate mode solves this problem because at BEGIN time (or more correctly, at the time of first read in that transaction), a write lock is acquired hence no page can be appended between read -> write, unlike in the deferred mode.

Post reply on HN