[dead]
SQLite concurrency and why you should care about it
101–110 of 189 posts
Re: SQLite concurrency and why you should care about it
#102One 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…
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.
Re: SQLite concurrency and why you should care about it
#103I am a little confused, but maybe I am missing some context? Wouldn't using a proper database be a lot easier than all of this transaction hacking? I mean, is Postgres that hard to use?
Re: SQLite concurrency and why you should care about it
#104Earlier quoted context omitted.
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. This is not true. From the link you posted: > The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file.
Ugh, you’re totally right.
I always get optimize and vacuum mixed up.
Re: SQLite concurrency and why you should care about it
#105Sqlite 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
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 historically (with Emby), I've not been able to do that due to SQLite locking not allowing me to run multiple instances pointing to the same config instance.
There's almost certainly ways to do this correctly with SQLite but if they allowed for using almost literally any other database this would be a total non-issue.
ETA:
For clarification if anyone is reading this, all this media LEGALLY OBTAINED with PERMISSION FROM THE COPYRIGHT HOLDER(S).
Re: SQLite concurrency and why you should care about it
#106Earlier quoted context omitted.
These are performance optimizations. SQLite does serialize writes. Avoiding concurrent writes to begin with just avoids some overhead on locking.
"performance optimisation" --- yeees, well, if you don't care about data integrity between your reads and writes. Who knows when those writes you scheduled really get written. And what of rollbacks due to constraint violations? There's we co-locate transactions with code: they are intertwined. But yes, a queue-writer is fine for a wide range of tasks, but not everything. It's that we need to contort our software to m…
> Who knows when those writes you scheduled really get written
I await the write to complete before my next read in my application logic, same as any other bit of code that interacts with a database or does other IO. Just because another thread handles interacting with the writer connection, doesn't mean my logic thread just walks away pretending the write finished successfully in 0ms.
Re: SQLite concurrency and why you should care about it
#107Sqlite 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 mostly for a single household, right? Sqlite should be much more than sufficient for Jellyfin (if used correctly). Unfortunately, reading this article you get the impression that they are not using it optimally
Agreed. How can a media file sharing app possibly saturate Sqlite's write limit? I would use an app-level global lock on all writes to Sqlite.
Re: SQLite concurrency and why you should care about it
#108Earlier quoted context omitted.
DuckDB is similar as an in process SQL database, but lacking btree-style ordered indexes makes it a poor performer in key lookups and order-by / range scans if your table is any size larger than trivial. It’s the classic OLAP (DuckDB) vs OLTP (SQLite) trade off between the two. DuckDB is very good at many things but most applications that need a traditional SQL DB will probably not perform well if you swap it over to…
Duckdb has optional adaptive radix tree indexing ( https://duckdb.org/docs/stable/sql/indexes.html )
Oops, I stand corrected!
What I remember about our evaluation of DuckDB in 2024 concluded that (1) the major limitations were lack of range-scan and index-lookup performance (maybe w/ joins? or update where?), and (2) the DuckDB Node.js module segfaulted too much. Perhaps the engineers somehow missed the ART index it could also be the restriction that data fit in memory to create an index on it (our test dataset was about 50gb)
Re: SQLite concurrency and why you should care about it
#109In 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/
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. This is a low traffic site but we have a serious reliability problem when load increases: the ATTACH calls occasionally fail with "database is locked". I don't know if this is because the ATTACH fails immediately without respecting the normal 5 second database timeout or what. To be honest I haven't implemented connection pooling yet because I want to understand what exactly causes this problem.
Re: SQLite concurrency and why you should care about it
#110Earlier 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…
Yeah, I'm sure those twelve people love watching your vacation clips all the time ;)