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

41–50 of 189 posts

Re: SQLite concurrency and why you should care about it

#41

Post by sethev »

mickeyp wrote: post #9
summarity wrote: post #8

Earlier quoted context omitted.

I've always tried to avoid situations that could lead to SQLITE_BUSY. SQLITE_BUSY is an architecture smell. For standard SQLite in WAL, I usually structure an app with a read "connection" pool, and a single-entry write connection pool. Making the application aware of who _actually_ holds the write lock gives you the ability to proactively design access patterns, not try to react in the moment, and to get observabilit…

I mean, you're not wrong, and that is one way to solve it, but the whole point of a sensibly-designed WAL -- never mind database engine -- is that you do not need to commit to some sort of actor model to get your db to serialise writes.

These are performance optimizations. SQLite does serialize writes. Avoiding concurrent writes to begin with just avoids some overhead on locking.

Re: SQLite concurrency and why you should care about it

#42

Post by mickeyp »

sethev wrote: post #41
mickeyp wrote: post #9

Earlier quoted context omitted.

I mean, you're not wrong, and that is one way to solve it, but the whole point of a sensibly-designed WAL -- never mind database engine -- is that you do not need to commit to some sort of actor model to get your db to serialise writes.

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 make sqlite not suck at writes that is the problem.

Re: SQLite concurrency and why you should care about it

#43

Post by ncruces »

sethev wrote: post #41
mickeyp wrote: post #9

Earlier quoted context omitted.

I mean, you're not wrong, and that is one way to solve it, but the whole point of a sensibly-designed WAL -- never mind database engine -- is that you do not need to commit to some sort of actor model to get your db to serialise writes.

These are performance optimizations. SQLite does serialize writes. Avoiding concurrent writes to begin with just avoids some overhead on locking.

SQLite, for the most part, uses polling locks. That means it checks if a lock is available to be taken, and if it's not, it sleeps for a bit, then checks again, until this times out.

This becomes increasingly inefficient as contention increases, as you can easily get into a situation where everyone is sleeping, waiting for others, for a few milliseconds.

Ensuring all, or most, writes are serialized, improves this.

Re: SQLite concurrency and why you should care about it

#44

Post by hruk »

mickeyp wrote: post #40
hruk wrote: post #22

Earlier quoted context omitted.

This is just untrue - the naive implementation (make the API call, write a single row to the db) will work fine, as transactions are quite fast on modern hardware. What do you consider "serious" work? We've served a SaaS product from SQLite (roughly 300-500 queries per second at peak) for several years without much pain. Plus, it's not like PG and MySQL are pain-free, either - they all have their quirks.

Edit: disregard. I read it as he'd done it and had contention problems. I mean it's not if he's got lock contention from BUSY signals, now is it, as he implies. Much of his issues will stem from transactions blocking each other; maybe they are long-lived, maybe they are not. And those 3-500 queries --- are they writes or reads? Because reads is not a problem.

Roughly 80/20 read to write. On the instance's gp3 EBS volume (which is pretty slow), we've pushed ~700 write transactions per second without much problem.

Re: SQLite concurrency and why you should care about it

#45

Post by asa400 »

porridgeraisin wrote: post #37
asa400 wrote: post #34

Earlier quoted context omitted.

Are you running vacuums at all? auto_vacuum enabled at all? https://sqlite.org/lang_vacuum.html

In memory DBs don't have anything to vacuum. However... what you (and OP) are looking for might be pragma shrink_memory [1]. [1] https://sqlite.org/pragma.html#pragma_shrink_memory

Ah, you're correct. I read too fast and missed that it was in-memory databases specifically!

Re: SQLite concurrency and why you should care about it

#46

Post by e2le »

tejinderss wrote: post #13
mickeyp wrote: post #5

SQLite is a cracking database -- I love it -- that is let down by its awful defaults in service of 'backwards compatibility.' You need a brace of PRAGMAs to get it to behave reasonably sanely if you do anything serious with it.

Do you know any good default PRAGMAs that one should enable?

Although not what you asked for, the SQLite authors maintain a list of recommended compilation options that should be used where applicable.

https://sqlite.org/compile.html#recommended_compile_time_opt...

Re: SQLite concurrency and why you should care about it

#47

Post by mickeyp »

hruk wrote: post #44
mickeyp wrote: post #40

Earlier quoted context omitted.

Edit: disregard. I read it as he'd done it and had contention problems. I mean it's not if he's got lock contention from BUSY signals, now is it, as he implies. Much of his issues will stem from transactions blocking each other; maybe they are long-lived, maybe they are not. And those 3-500 queries --- are they writes or reads? Because reads is not a problem.

Roughly 80/20 read to write. On the instance's gp3 EBS volume (which is pretty slow), we've pushed ~700 write transactions per second without much problem.

For small oltp workloads the locking is not going to be a problem. But stuff that holds the write lock for some measurable fraction of a second even will gum things up real fast. Transactions that need it for many seconds? You'll quickly be dead in the water.

Re: SQLite concurrency and why you should care about it

#48

Post by abound »

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.

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

Re: SQLite concurrency and why you should care about it

#49

Post by sethev »

mickeyp wrote: post #42
sethev wrote: post #41

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

This is just FUD. The reason SQLite does locking to begin with is to avoid data corruption. Almost every statement this blog post makes about concurrency in SQLite is wrong, so it's little surprise that their application doesn't do what they expect.

>Who knows when those writes you scheduled really get written

When a commit completes for a transaction, that transaction has been durably written. No mystery. That's true whether you decide to restrict writes to a single thread in your application or not.

Re: SQLite concurrency and why you should care about it

#50

Post by yellow_lead »

porridgeraisin wrote: post #19

> So an application that wants to use SQLite as its database needs to be the only one accessing it. No. It uses OS level locks. fcntl(). You can access it from how many ever processes. The only rule is, single writer (at a time). > When another part of the application wants to read data, it reads from the actual database, then scans the WAL for modifications and applies them on the fly. Also wrong. WAL does not conta…

C# devs*
Post reply on HN