5 ms·
In my experience most SQLite writes take less than 1ms. So if your app is handling less than a thousand writes per second you will probably be OK! A common pat
by simonw 5y ago
In my experience most SQLite writes take less than 1ms. So if your app is handling less than a thousand writes per second you will probably be OK!
A common pattern (which I've implemented myself in Datasette) is to put your writes in an in-memory queue and apply them using a single dedicated connection.
- rogers18445 5y agoIf 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 5y agoDo you happen to have a blog post laying out the details? Would love to learn more about what you're doing!
- tremon 5y agoWith either an in-memory db or using zstd compressed ramdisks the limits are ridiculous. Who needs durability anyway, right?
- jaytaylor 5y agoLitestream can ensure a pretty decent level of durability, for a ramdisk anyway. Suitability depends on what the requirements are.
- taspeotis 5y 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 5y 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 5y 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 5y 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 5y 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...
- kqr 5y agoQuick queuing theory recap: independent requests arriving at ~1000/s and each taking ~1 ms will, in the presence of any variability at all, be guaranteed to lead to an infinite backlog of unserved requests.
- rco8786 5y agoI’m not an expert on queue theory but this little “tidbit” has always bothered me. As far as I can tell, it is true IFF your variability causes the average wpos to go up, which negates the entire premise of “requests arriving at 1000/s” If you’re averaging 1000/s and you get 1010 req in one sec, yes this leads to an infinite queue - but your average is now above 1000/s! Then if a few seconds later you get 990 requests, the queue catches up (because your avg is back to 1000) IOW - it assumes only positive variability and no negative variability which does not mimic real world systems in my experience. It’s an academic exercise that’s interesting to think about, but has no practical application. Else we would see infinite queue backups every time a system received a burst of traffic beyond their maximum throughput in any given time period. What am I missing?
- hansvm 5y agoJust code up a 5-line simulation and see what happens? You do actually expect the server to eventually recover from all load spikes, but that dead time means cumulatively you'll typically have queued more requests than you've handled at long enough time scales. On average that surplus is infinite, despite it sometimes being as low as 0. It's not just academic, but it's not surprising that doesn't echo your real-world experience either. We don't usually run systems anywhere near capacity, and even when you do you'll still expect the queue to be able to handle the load eventually.
- antifa 5y agoI tried sqlite once and had a lot of errors with just 2 writers. Instantly no errors after switching to postgres.