5 ms·
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 ~1
by rogers18445 4y ago
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.
- codethief 4y agoDo you happen to have a blog post laying out the details? Would love to learn more about what you're doing!
- tremon 4y agoWith either an in-memory db or using zstd compressed ramdisks the limits are ridiculous. Who needs durability anyway, right?
- jaytaylor 4y agoLitestream can ensure a pretty decent level of durability, for a ramdisk anyway. Suitability depends on what the requirements are.
- taspeotis 4y agoThere 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/performance/configuring-storage-spaces-with-a-nvdimm-n-write-back-cache?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... I imagine with some effort SQLite could as well.
- zasdffaa 4y agoThe 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.
- bob1029 4y agoThis 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.
- beagle3 4y agoAre 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.
- edwinyzh 4y agoHi, thanks for sharing your experiences. What's "Not double-locking"? Thanks.
- warmwaffles 4y ago> 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?
- meribold 4y agoCan 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?
- edwinyzh 4y agoBy 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-Performance https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Perf...