←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
353 points HunOL | 2 comments | 01 Nov 25 12:59 UTC | HN request time: 0.447s | source
Show context
EionRobb ◴[01 Nov 25 19:57 UTC] No.45784808[source]▶
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 check it out. We thought maybe the ssd had worn out over the few years of continual use but installing a dev copy of our app was super fast. In the end what did work was to "defrag" the db file by copying it to a new location, deleting the original, then moving it back to the same name. Boom, no more "unable to open database" errors, no more slow downs.

I tried this on Jellyfin dbs a few months ago after running it for years and then suddenly running into performance issues, it made a big difference there too.

replies(6): >>45784954 #>>45785309 #>>45786705 #>>45787158 #>>45787976 #>>45788856 #
izacus ◴[02 Nov 25 00:06 UTC] No.45786705[source]▶
That's much more likely flash degradation than actual fragmentation. Did you use cheap tablets with eMMC storage?
replies(3): >>45787609 #>>45787882 #>>45787893 #
1. georgemcbay ◴[02 Nov 25 03:16 UTC] No.45787609[source]▶
> 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.

replies(1): >>45789074 #
2. izacus ◴[02 Nov 25 09:44 UTC] No.45789074[source]▶
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.