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

181–189 of 189 posts

Re: SQLite concurrency and why you should care about it

#181

Post by thayne »

throwaway894345 wrote: post #168
thayne wrote: post #165

Earlier quoted context omitted.

> just target storage interfaces that can be easily distributed—things like Postgres But as I mentioned above, that makes the system more complicated for people who don't need it to be distributed. Setting up separate db software, configuring the connection, handling separate updates, etc. is a lot more work for most users than Jellyfin just using a local embedded sqlite database. And it would probably make the appli…

> But as I mentioned above, that makes the system more complicated for people who don't need it to be distributed. Setting up separate db software, configuring the connection, handling separate updates, etc. is a lot more work for most users than Jellyfin just using a local embedded sqlite database. You can package a Postgres database with your app just like SQLite. Users should not have to know that they are using P…

> You can package a Postgres database with your app just like SQLite

You technically can. But that is much more difficult to do than including sqlite, and isn't how postgresql was meant to be used. And what happens when you want to upgrade the major version of postgresql? Do you now include two versions of postgresql so that you can convert old databases to the new postgresql format? I certainly wouldn't say it is "just like SQLite".

Re: SQLite concurrency and why you should care about it

#182

Post by npodbielski »

nick_ wrote: post #172
npodbielski wrote: post #133

Earlier quoted context omitted.

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.

Are you hinting at the lack of an `AsyncLock` in .NET?

I did not really inspect their code in this detail. I just did it once few years ago when I was exploring integrating with my oidc provider that use. At that time auth code looked not great with static class handling it it somehow. Anyway I meant that if you scanning a lot of files and do some kind of operations for each of them, and obviously you need to save the result. Depending on how you do it. You could ram sqlite DB with thousands of connection. Which is not great way to do it but it is possible.

Re: SQLite concurrency and why you should care about it

#183

Post by throwaway894345 »

RealStickman_ wrote: post #178
throwaway894345 wrote: post #83

Earlier quoted context omitted.

Yeah, I agree, though making software that can run in a distributed configuration is a matter of following a few basic principles, and would be far less work than what the developers have spent chasing down trying to make SQLite work for their application. The effort required to put an application on Kubernetes is a pretty good indicator of software quality. In other words, I can have a pretty good idea about how dif…

Most of the issues with the database are old sins from Emby. With 10.11 the Jellyfin team finally managed to clean up that mess so they can move forward with a clean implementation. Their blog post on moving to EFCore [1] and version 10.11 release post [2] have more details. [1] https://jellyfin.org/posts/efcore-refactoring [2] https://jellyfin.org/posts/jellyfin-release-10.11.0/

Yes, I agree. I’ve been eagerly awaiting this change for well over a year now.

Re: SQLite concurrency and why you should care about it

#184

Post by throwaway894345 »

thayne wrote: post #181
throwaway894345 wrote: post #168

Earlier quoted context omitted.

> But as I mentioned above, that makes the system more complicated for people who don't need it to be distributed. Setting up separate db software, configuring the connection, handling separate updates, etc. is a lot more work for most users than Jellyfin just using a local embedded sqlite database. You can package a Postgres database with your app just like SQLite. Users should not have to know that they are using P…

> You can package a Postgres database with your app just like SQLite You technically can. But that is much more difficult to do than including sqlite, and isn't how postgresql was meant to be used. And what happens when you want to upgrade the major version of postgresql? Do you now include two versions of postgresql so that you can convert old databases to the new postgresql format? I certainly wouldn't say it is "j…

I can believe the packaging step is more difficult on some platforms, but that’s a one time cost per platform.

Upgrades are more complex because Postgres (for some reason) doesn’t endure backwards compatibility with its disk format like sqlite across major versions (minor versions work fine though). For major version upgrades you ship both binaries and use pg_upgrade or pg_dump/pg_restore. It’s annoying, but it’s a one time cost to automate. It’s not like battling concurrency bugs in sqlite.

Re: SQLite concurrency and why you should care about it

#185

Post by throwaway894345 »

FrinkleFrankle wrote: post #94
throwaway894345 wrote: post #59

Earlier quoted context omitted.

As a Jellyfin user, this hasn’t been my experience. I needed to do a fair bit of work to make sure Jellyfin could access its database no matter which node it was scheduled onto and that no more than one instance ever accessed the database at the same time. Jellyfin by far required more work to setup maintainably than any of the other applications I run, and it is also easily the least reliable application. This isn’t…

Care to share your setup?

Presently I’m running my media directory and sqlite database on NFS (one big single-point-of-failure). My Kubernetes Deployment resource is configured to use the “Replace” rollout strategy (at least I think that’s what it’s called if i’m not misremembering) so there are never two concurrent instances. This means I take downtime during rollouts, but it’s fine for my use case.

One of the more difficult bits (which is not really Jellyfin’s fault) is that the application must run on nodes with access to an adequate GPU to handle any on demand transcode tasks, which requires making the GPU available to them Kubernetes pod and also telling the scheduler which nodes have a GPU and which do not. For that I used node feature discovery along with some intel specific plugin for the GPU (my GPU was an integrated intel GPU).

Re: SQLite concurrency and why you should care about it

#186

Post by benhurmarcel »

sgbeal wrote: post #170
gwking wrote: post #109

Earlier quoted context omitted.

One tidbit that I don't see mentioned here yet is that ATTACH requires a lock. I just went looking for the documentation about this and couldn't find it, especially for WAL mode ( https://www.sqlite.org/lockingv3.html mentions the super-journal, but the WAL docs do not mention ATTACH at all). I have a python web app that creates a DB connection per request (not ideal I know) and immediately attaches 3 auxiliary DBs.…

> I have a python web app that creates a DB connection per request (not ideal I know) FWIW, "one per request per connection is bad" (for SQLite) is FUD, plain and simple. SQLite's own forum software creates one connection per request (it creates a whole forked process per request, for that matter) and we do not have any problems whatsoever with that approach. Connection pools (with SQLite) are a solution looking for…

Where can I read more about this? I use connection pools with SQLite, I’m interested if I can simplify.

Re: SQLite concurrency and why you should care about it

#187

Post by pstuart »

Kinrany wrote: post #75
pstuart wrote: post #66

Earlier quoted context omitted.

The real fork is DuckDB in a way, it has SQLite compatibility and so much more . The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster. https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html As to the…

> Read only databases are a lot easier to deal with regarding locking. "A lot easier" sounds like an understatement. What's there to lock when the data is read only?

> What's there to lock when the data is read only?

That's my point (it was an intentional understatement). Data that is rarely updated can be treated as read only and updates are done offline and swapped in as needed. This is very much a cope for SQLite's limited concurrency but I think it's worthy of consideration for when the situation merits it.

Re: SQLite concurrency and why you should care about it

#188

Post by MayeulC »

tombert wrote: post #173
MayeulC wrote: post #156

Earlier quoted context omitted.

> I've had issues when three people all streaming a VC-1 encoded video to H264 in software. I don't quite get the "in software" part. I assume you mean that the video needs to be transcoded to h.264 on your server for their client to play it. The way I mostly solved this is to ask people to install and use the native app (jellyfin-media-player or Android app) whenever possible, as it is compatible with more codecs. Y…

I mean "in software" in that it's not hardware assisted. I have gotten VAAPI working but it's a bit flaky with some videos for some reason, so I disabled it and just do vanilla ffmpeg. I'll look into the distributed ffmpeg.

Installing backported kernels & mesa versions on my aging Debian version got me much better results with VA-API, for what it's worth (that was an AMD card).

Re: SQLite concurrency and why you should care about it

#189

Post by sgbeal »

benhurmarcel wrote: post #186
sgbeal wrote: post #170

Earlier quoted context omitted.

> I have a python web app that creates a DB connection per request (not ideal I know) FWIW, "one per request per connection is bad" (for SQLite) is FUD, plain and simple. SQLite's own forum software creates one connection per request (it creates a whole forked process per request, for that matter) and we do not have any problems whatsoever with that approach. Connection pools (with SQLite) are a solution looking for…

Where can I read more about this? I use connection pools with SQLite, I’m interested if I can simplify.

> Where can I read more about this?

There's nothing specific to read about it, just plenty of anecdotal evidence. People use connection pools because connecting to _remote_ databases is slow. SQLite _is not remote_. It's _in-process_ and _fast_. Any connection-pool _adds_ to the amount of work needed to get an SQLite instance going.

It's _conceivable_ that pooling _might_ speed it up _just a tad_ for databases with _very large schemas_ because parsing the schema (which is not done at open-time, but when the schema is first needed) can be "slow" (maybe even several whole milliseconds!).

Post reply on HN