4 ms·

Many web apps also write a lot. Are you suggesting to have individual databases per process and then read from all of them and aggregate the data you want in me

Many web apps also write a lot. Are you suggesting to have individual databases per process and then read from all of them and aggregate the data you want in memory? That would sound too much like I'm building a database, which I don't want to do. I'm not talking about clusters and replication, just a single database on a single server, like Wordpress for example. It can handle large amounts of concurrent users if you're using InnoDB (which has been the default since how long?) and nobody will even notice that there's anyone else because it's not locking the table (or the whole database) to write, and you don't have to wait or deal with frequent busy timeouts.

simonw 4y ago
Most SQLite writes can be measured in microseconds. If your app is doing less than 1,000 writes a second you probably won't even notice that SQLite is serializing your writes rather than applying them concurrently.
- luckylion 4y ago
  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.
- ec109685 4y ago
  There's more to most applications than interactions with the database. Once your web app exceeds the capacity of a single machine, is SQLite a good choice for state?
russell_h 4y ago
I think he’s saying to shard writes by tenant, or something else that makes sense for your app - so you can route reads to an individual shard. Same as you’d do to shard any other database, if a little more DIY. Speaking of Wordpress though, that’s a pretty great example of a web app that typically handles almost exclusively reads. I’ll bet almost any Wordpress instance would work great on SQLite.
- tptacek 4y ago
  WordPress is the archetypical application that SQLite would kill for, but for historical reasons, WordPress has always wanted MySQL. It's funny, because there's a whole movement towards static sites that is, in part, a reaction to how bad the n-tier architecture behind WordPress is!
  - luckylion 4y ago
    They're working on supporting SQLite as a backend: https://make.wordpress.org/core/2022/12/20/help-us-test-the-sqlite-implementation/ https://make.wordpress.org/core/2022/12/20/help-us-test-the-...
    - tptacek 4y ago
      It's crazy that this is only happening as of 2022, right? They could have kept a whole generation of web sites on WordPress by eliminating the MySQL dependency, and gained a performance and deployability advantage in the process. Even multitenant WordPress would have come out great with SQLite. I'm waiting for someone closer to WordPress to tell me how wrong I am about this, because I have to be missing something.
      - luckylion 4y ago
        I'm not sure that it'll bring a lot of sites back to or keep them on WP, but it'll be great for testing. Much of it is always integration testing with WP because of how filters and actions work your code is rarely ever isolated, SQLite will make that much easier and faster. It's also really not a big switch for the most part. I'm not aware of that many MySQL-specific things happening in WP. Things like fulltext indexes aren't in core, so it's really just using a different driver to get the basics working. I'm sure it would break a lot of plugins, but that's fine, they have platform requirements anyhow, adding "does work with sqlite" isn't a huge step and can likely be automated to a high degree (if the plugin never uses wpdb->query, ->get_results etc, it's compatible).

simonw 4y ago

Most SQLite writes can be measured in microseconds. If your app is doing less than 1,000 writes a second you probably won't even notice that SQLite is serializing your writes rather than applying them concurrently.

luckylion 4y ago
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.
ec109685 4y ago
There's more to most applications than interactions with the database. Once your web app exceeds the capacity of a single machine, is SQLite a good choice for state?

russell_h 4y ago

I think he’s saying to shard writes by tenant, or something else that makes sense for your app - so you can route reads to an individual shard. Same as you’d do to shard any other database, if a little more DIY. Speaking of Wordpress though, that’s a pretty great example of a web app that typically handles almost exclusively reads. I’ll bet almost any Wordpress instance would work great on SQLite.

tptacek 4y ago
WordPress is the archetypical application that SQLite would kill for, but for historical reasons, WordPress has always wanted MySQL. It's funny, because there's a whole movement towards static sites that is, in part, a reaction to how bad the n-tier architecture behind WordPress is!
- luckylion 4y ago
  They're working on supporting SQLite as a backend: https://make.wordpress.org/core/2022/12/20/help-us-test-the-sqlite-implementation/ https://make.wordpress.org/core/2022/12/20/help-us-test-the-...
  - tptacek 4y ago
    It's crazy that this is only happening as of 2022, right? They could have kept a whole generation of web sites on WordPress by eliminating the MySQL dependency, and gained a performance and deployability advantage in the process. Even multitenant WordPress would have come out great with SQLite. I'm waiting for someone closer to WordPress to tell me how wrong I am about this, because I have to be missing something.
    - luckylion 4y ago
      I'm not sure that it'll bring a lot of sites back to or keep them on WP, but it'll be great for testing. Much of it is always integration testing with WP because of how filters and actions work your code is rarely ever isolated, SQLite will make that much easier and faster. It's also really not a big switch for the most part. I'm not aware of that many MySQL-specific things happening in WP. Things like fulltext indexes aren't in core, so it's really just using a different driver to get the basics working. I'm sure it would break a lot of plugins, but that's fine, they have platform requirements anyhow, adding "does work with sqlite" isn't a huge step and can likely be automated to a high degree (if the plugin never uses wpdb->query, ->get_results etc, it's compatible).