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

121–130 of 189 posts

Re: SQLite concurrency and why you should care about it

#121

Post by georgemcbay »

izacus wrote: post #115
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?

> 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 unreasonable app slowdown because they didn't buy a good enough device to run it on, especially when they've previously experienced it running just fine.

If all their apps feel like crap on that tablet, sure, that might fly... but if its only your app (or only a small set of apps that use SQLite in the same way the OP's company did) that feels like crap after a while, that's effectively a you problem (to solve) even if its not really a you problem.

In any case, its an interesting data point and could be very useful information to others who run into similar issues.

Re: SQLite concurrency and why you should care about it

#122

Post by throwaway894345 »

thayne wrote: post #113
throwaway894345 wrote: post #84

Earlier quoted context omitted.

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

I’m a distributed systems architect. I design, build, and operate distributed systems.

> Distributed systems have many failure modes that you don't have to worry about in non-distributed systems.

Yes, but as previously mentioned, those failure modes are handled by abiding a few simple principles. It’s also worth noting that multiprocess or multithreaded software have many of the same failure modes, including the one discussed in this post. Architecting systems as though they are distributed largely takes care of those failure modes as well, making even single-node software like Jellyfin more robust.

> 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.

Generally I just target storage interfaces that can be easily distributed—things like Postgres (or maybe dqlite?) for SQL databases or an object storage API instead of a filesystem API. If you build a system like it could be distributed one day, you’ll end up with a simpler, more modular system even if you never scale to more than one node (maybe you just want to take advantage of parallelism on your single node, as was the case in this blog post).

Re: SQLite concurrency and why you should care about it

#123

Post by throwaway894345 »

ants_everywhere wrote: post #116
throwaway894345 wrote: post #52

Earlier quoted context omitted.

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.

Yeah, honestly I’m kind of thinking about a media server architecture that has a stateless media server that vends links to pre-transcoded media in object storage (which video players would source from), since pretty much anything can handle mp4/h264/acc video. Maybe in the future I could add on some on-the-fly transcoding (which would happen on a dedicated cluster, reading and writing to object storage), but that seems like a pretty big undertaking.

Re: SQLite concurrency and why you should care about it

#124

Post by EionRobb »

izacus wrote: post #115
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?

We had that thought too. I'll have to try dig out what the tablets were to find out exactly what type - this would have been 3 or 4 years ago now. We sort of ruled that out because:

The other workaround to get a speed boost was the user to uninstall and reinstall the app (and then wait for all the data to download again) but that didn't fly because the users would delete before they'd synced off all their data and then data would go missing.

This was all despite having VACUUM running whenever the app started.

Whether it was bad flash or no, we still had to try resolve it as the client wouldn't buy new hardware until we could prove that we had the knowledge to make the problem go away first :/

Re: SQLite concurrency and why you should care about it

#125

Post by EionRobb »

izacus wrote: post #115
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?

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

Re: SQLite concurrency and why you should care about it

#126

Post by axitanull »

DANmode wrote: post #102
didip wrote: post #98

Earlier quoted context omitted.

This is fascinating. What would be the solution for this? You can’t ask users to defrag.

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?

Re: SQLite concurrency and why you should care about it

#127

Post by teddyh »

mceachen wrote: post #93
Multicomp wrote: post #92

Earlier quoted context omitted.

Would the SQLite vacuum function help with that?

You can VACUUM INTO, ~~but standard vacuum won’t rewrite the whole db~~ (vacuum rewrites the whole db) https://sqlite.org/lang_vacuum.html (Edit: if multiple processes are concurrently reading and writing, and one process vacuums, verify that the right things happen: specifically, that concurrent writes from other processes during a vacuum don’t get erased by the other processes’ vacuum. You may need an external advi…

> You can VACUUM INTO, ~~but standard vacuum won’t rewrite the whole db~~ (vacuum rewrites the whole db)

HN does not support whatever markup you are trying to use. You have to use Unicode:

“You can VACUUM INTO, b̶u̶t̶ ̶s̶t̶a̶n̶d̶a̶r̶d̶ ̶v̶a̶c̶u̶u̶m̶ ̶w̶o̶n̶’̶t̶ ̶r̶e̶w̶r̶i̶t̶e̶ ̶t̶h̶e̶ ̶w̶h̶o̶l̶e̶ ̶d̶b̶ (vacuum rewrites the whole db)”

Re: SQLite concurrency and why you should care about it

#128

Post by simscitizen »

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…

Copying the file likely forces the creation of a new one with no or lower filesystem fragmentation (e.g. a 1MB file probably gets assigned to 1MB of consecutive FS blocks). Then those FS blocks likely get assigned to flash dies in a way that makes sense (i.e. the FS blocks are evenly distributed across flash dies). This can improve I/O perf by some constant factor. See https://www.usenix.org/system/files/fast24-jun.pdf for instance for more explanation.

I would say that the much more common degradation is caused by write amplification due to a nearly full flash drive (or a flash drive that appears nearly full to the FTL because the system doesn't implement some TRIM-like mechanism to tell the FTL about free blocks). This generally leads to systemwide slowdown though rather than slowdown accessing just one particular file.

This was especially prevalent on some older Android devices which didn't bother to implement TRIM or an equivalent feature (which even affected the Google devices, like the Nexus 7).

Re: SQLite concurrency and why you should care about it

#129

Post by heavyset_go »

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.

I run Jellyfin in a multi-arch cluster because I hate myself, and this would force me to think about where Jellyfin/Postgres is deployed because Postgres databases aren't portable.

I already had to do that for my authoritative PG deployment, and my media manager shouldn't require a full RDBMS.

Using SQLite for Jellyfin has made running it wherever really, really easy, same thing with doing backups and lazy black box debugging.

Re: SQLite concurrency and why you should care about it

#130

Post by heavyset_go »

throwaway894345 wrote: post #59
thayne wrote: post #53

Earlier quoted context omitted.

Using postgres would make it significantly more complicated for Jellyfin users to install and set up Jellyfin. And then users would need to worry about migrating the databases when PostgreSQL has a major version upgrade. An embedded database like sqlite is a much better fit for something like Jellyfin.

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…

Jellyfin isn't a Netflix replacement, it's a desktop application that's a web app by necessity. Treat it like a desktop app and you won't have these issues.
Post reply on HN