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

141–150 of 189 posts

Re: SQLite concurrency and why you should care about it

#141

Post by apitman »

npodbielski wrote: post #132
abound wrote: post #48

Earlier quoted context omitted.

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.

Jellyfin is one of the very few selfhosted apps that can be run as a simple GUI app on Windows. As an advocate for making selfhosting accessible to less technical people, I'm glad they're using sqlite and also that they don't require docker.

Re: SQLite concurrency and why you should care about it

#142

Post by apitman »

tombert wrote: post #105
abound wrote: post #48

Earlier quoted context omitted.

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

I share my Jellyfin with about a dozen people, and it's not weird to have several people streaming at the same time. I have a two gigabit connection so bandwidth isn't generally an issue, but I've had issues when three people all streaming a VC-1 encoded video to H264 in software. This is something that I think I could fairly easily ameliorate if I could simply load-balance the application server by user, but histori…

Why not encode to H264 or another codec more widely supported by clients? Storage is cheap.

Re: SQLite concurrency and why you should care about it

#143

Post by izacus »

georgemcbay wrote: post #121
izacus wrote: post #115

Earlier quoted context omitted.

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

> That's much more likely flash degradation than actual fragmentation. Did you use cheap tablets with eMMC storage? My understanding of the parent reply's situation is that this was happening on the tablets of their users, so it kinda doesn't matter that it can be avoided by not using cheap tablets. Most apps aren't in a position to tell their users that they are on their own when they run into what feels like an unr…

I don't quite understand what you're arguing here.

I'm merely saying that the root cause was misidentified - the performance degradation didn't happen due to fragmentation, but because the flash storage was degraded to the point where the write performance dropped significantly. This happens faster for eMMC vs. SSD-style storage.

Copying the DB file moved the data to different storage blocks which is why it (temporarily again) improved performance.

Re: SQLite concurrency and why you should care about it

#144

Post by izacus »

EionRobb wrote: post #125
izacus wrote: post #115

Earlier quoted context omitted.

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

Ah yup eMMC https://www.gsmarena.com/samsung_galaxy_tab_active2-8897.php

Yeah, then copying the files most likely moved them to different part of storage and it improved performance again.

Sadly that's a common plague for cheaper Android hardware - after enough writes the flash performance drops off a cliff making those devices essentially unusable :/

(More expensive hardware - including Apples - tends to have UFS type storage which lasts longer.)

Re: SQLite concurrency and why you should care about it

#145

Post by apitman »

reddalo wrote: post #111
abound wrote: post #48

Earlier quoted context omitted.

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/

I hope they keep sqlite as a first class citizen.

Re: SQLite concurrency and why you should care about it

#146

Post by apitman »

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…

You're from the current generation of selfhosters, which culturally is very similar to kit car builders. The next generation of selfhosters/indiehosters just want a car to get from point A to point B. Sqlite is better for those people.

Re: SQLite concurrency and why you should care about it

#147

Post by BinaryIgor »

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…

Also worth mentioning - it happens more often when you set journal_mode=WAL, which is not a default.

The default is DELETE mode, where the rollback journal is deleted at the conclusion of each transaction. What's more - in this mode (not-WAL), readers can coexist, but they do block the writer (which is always one) and the writer block readers - concurrency is highly limited.

In WAL mode - which pretty much always you should set - there's also at most one writer, but writer can coexist with readers.

Re: SQLite concurrency and why you should care about it

#148

Post by BinaryIgor »

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/

Exactly - it's also strange that they didn't find a simple solution of setting PRAGMA busy_timeout=N; if you google/search SQLITE_BUSY: database is locked there are plenty of solid results describing the problem and solution

Re: SQLite concurrency and why you should care about it

#149

Post by BinaryIgor »

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

Indeed. Everyone who uses sqlite will get burnt by this one day and spend a lot of time chasing down errant write-upgraded transactions that cling on for a little bit longer than intended.

SQLite has its quirks, but in this particular case all you need is set PRAGMA busy_timeout= and the problem is solved; and if you google it, it's widely known issue with described (this) solution.

It's just weird that it's set to 0 by default rather than something resonable like 3000 or 5000 ms.

Re: SQLite concurrency and why you should care about it

#150

Post by xorcist »

npodbielski wrote: post #132
abound wrote: post #48

Earlier quoted context omitted.

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.

A database is never hard to install, but it can be tricky to operate.

You have to at least have at least a slight idea about the specifics, from different types of vacuum to how it behaves in low memory conditions. The idea that docker has something to do this is a misdirection at best.

And if you think sqlite has many knobs and special modes, wait until you hear about Postgres.

Post reply on HN