3 ms·

I must've been doing something wrong then, I've regularly hit longer locks as soon as the DB grew, to the point that it was not usable for me with multiple proc

I must've been doing something wrong then, I've regularly hit longer locks as soon as the DB grew, to the point that it was not usable for me with multiple processes writing in the same DB because it was constantly locked by one. Having one writer and multiple readers was fine, but even having a dozen writers who update something a few times per second quickly led to the DB becoming the bottleneck. I tried different modes, but saw no difference so I just gave up on using it concurrently.

tptacek 4y ago
I spent a bunch of time wrestling with this last year (and into this one), and the general solution I settled on was to serialize writes through a single writer; multiple reader threads and multiple writer threads (or processes) is not a fun configuration to put SQLite in. There were 500MB-40G databases, probably ~1.5 updates per second at peak load?
- sqlcommando 4y ago
  I think "web app" is too broad a category to have this kind of discussion over.. for a lot of web apps 1.5 updates per second could be considered a reasonable limit. Many people work on web apps that do 100x that and don't consider themselves to be working at any particular level of scale. 1.5K updates/sec is not uncommon either, in different settings -- high-traffic internal web apps can hit that in a single company, and the people working in those contexts don't consider that too special either, not to mention public facing websites like GH where many people start their careers without having seen anything different.
  - tptacek 4y ago
    I'm not benchmarking SQLite, I'm relating the environment in which I was tuning SQLite concurrency, because I feel like it doesn't mean much to say "here's what worked for me" without at least some basic facts about what I was working on. SQLite itself can do tens of thousands of writes per second on commodity hardware without breaking a sweat.

tptacek 4y ago

I spent a bunch of time wrestling with this last year (and into this one), and the general solution I settled on was to serialize writes through a single writer; multiple reader threads and multiple writer threads (or processes) is not a fun configuration to put SQLite in. There were 500MB-40G databases, probably ~1.5 updates per second at peak load?

sqlcommando 4y ago
I think "web app" is too broad a category to have this kind of discussion over.. for a lot of web apps 1.5 updates per second could be considered a reasonable limit. Many people work on web apps that do 100x that and don't consider themselves to be working at any particular level of scale. 1.5K updates/sec is not uncommon either, in different settings -- high-traffic internal web apps can hit that in a single company, and the people working in those contexts don't consider that too special either, not to mention public facing websites like GH where many people start their careers without having seen anything different.
- tptacek 4y ago
  I'm not benchmarking SQLite, I'm relating the environment in which I was tuning SQLite concurrency, because I feel like it doesn't mean much to say "here's what worked for me" without at least some basic facts about what I was working on. SQLite itself can do tens of thousands of writes per second on commodity hardware without breaking a sweat.