5 ms·
One write, infinite read. Per file (which matters, since you can have many files). Writes can wait for one another. So you can still handle multiple writers, b
by slver 6y ago
One write, infinite read. Per file (which matters, since you can have many files).
Writes can wait for one another. So you can still handle multiple writers, but serialized.
BTW, most people vastly overestimate their concurrency and write frequency needs. For example something like a CMS is 99.999% reads and 0.001% writes (yes, I pulled this out of my behind, but it's close).
- unixhero 6y agoInteresting, thank you!
- cosmotic 6y agoCMS would be low writes assuming no tracking or authentication audit trails or maybe buffered copies of all those.
- slver 6y agoI wouldn't store web logs in an SQLite DB, but I wouldn't do that in any RDBMS typically. Easiest solution is a flat file, which you can import and analyze offsite (in SQLite, or Excel, or whatever :-).
- mrweasel 6y agoYou have a design where logs are added to a queue (something like ZMQ perhaps) and have a process/thread writing the logs to a separate SQLite database. I'm not saying it's a good idea, especially for logging, where a flat file might be better, but it is possible.
- slver 6y agoI was gonna say a queue, but I didn't want to get too fancy, but you're absolutely right. If you can use an in-memory/socket queue, even a basic one, which serializes the writes to a dedicated SQLite file, SQLite becomes as performant as any database (you're limited by disk I/O).
- nikisweeting 6y agoIt does not handle multiple writers well even in serialized mode with WAL turned on, most systems will throw "database locked" errors. It's up to the developer to coordinate between threads to prevent concurrent writes from getting blocked.
- saberience 6y agoHow does it handle infinite reads? For example: I used to work with a Couchbase cluster of 6 nodes, billions of documents (> 1TB of data) and doing roughly 25k-50k reads per second and every read was returned in < 1ms. Are you telling me we could have used SQLite on a single instance and it would have also have an average end to end latency of < 1ms. I.e. from App server to DB and back was < 1ms? For 25k+ OPS/sec?
- slver 6y agoObviously a tiny embedded library that reads off a file on disk won't replace a "Couchbase cluster of 6 nodes etc. etc." But SQLite has no hard limits on number of readers. There are no sockets, or Unix pipes. It reads off file cache in RAM, or if your data is bigger, it reads of disk. Like you would from a file. How'd you answer how many reads you can do from RAM? Kind of... depends on everything else, doesn't it? I'd say it can have comparable performance to a single MySQL instance for simple read queries. If that helps.
- yread 6y ago50k reads per second? And you need how many servers for that? Sqlite can't do infinite reads but pretty close https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...