Hacker News Reader

Comment by rogers18445 | Hacker News Reader

If you open multiple connections to an sqlite db from a single app you are a bad developer, full stop.

We have server app on a single beefy server servicing ~100,000 simultaneous users with avg user action per second of 0.2 (~20k actions per second) requiring some ~60k sqlite writes per second.

A single thread handles that with message passing with little load. We estimate we can handle 5-10 times as much without changing anything. That's with SSD's and standard OS caching. With either an in-memory db or using zstd compressed ramdisks the limits are ridiculous.

View on HN

This is pretty much how we've been running in production with several different banks over the last half decade.

1 connection per database is critical. WAL is important. Not double-locking helps (most builds of SQLite serialize all writes internally).

You do all of these things correctly at the same time, then you can easily handle tens of thousands of transactions per second.

Additionally, we also do a per session database concept where data is scoped as specifically as possible. Using just 1 sqlite database for the whole application would be a mistake imo.

Synchronous replication is our next step, but we might build something in-house for this.

Are you aware of litestream? It's become quite useful in the last year or two, and recently provides synchronous replication. Worth checking it out (as well as rqlite and dqlite and bedrock) before starting your own project.

Hi, thanks for sharing your experiences. What's "Not double-locking"? Thanks.

With either an in-memory db or using zstd compressed ramdisks the limits are ridiculous.

Who needs durability anyway, right?

There are workloads where some data loss is acceptable, like analytics.

But more seriously if you want the best of both worlds (in-memory speeds and durability) there are NVDIMMs.

SQL Server can take advantage of them: https://docs.microsoft.com/en-us/sql/relational-databases/pe...

I imagine with some effort SQLite could as well.

The problem is (I've seen it) staff turning off or disabling WAL[1] because it makes things faster, or as an unintended consequence of doing something else. Great if 'some data loss' being possible is understood in advance. Often, it isn't.

[1] example: not quite WAL, but disabling full logging, so it was then on simple logging, on MSSQL as part of a log shrink process. On a cient site. Who did 3-hourly log backups.

Litestream can ensure a pretty decent level of durability, for a ramdisk anyway.

Suitability depends on what the requirements are.

Do you happen to have a blog post laying out the details? Would love to learn more about what you're doing!

By build a c/s architecture on top of SQLite, we can get almost 100K writes per second, including the object to JSON, JSON to SQL parsing process:

https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Perf...

Can you elaborate on why you think it's bad to open multiple connections? How would you allow read access while a writer is active without multiple connections?

> If you open multiple connections to an sqlite db from a single app you are a bad developer, full stop.

Even if you are taking advantage of the built in WAL feature?

Reply on news.ycombinator.com