3 ms·

@levelsio famously runs a dozen websites all backed by SQLite, with 200M reqs / month: https://twitter.com/levelsio/status/1520356430800617472 https://twitter.c

@levelsio famously runs a dozen websites all backed by SQLite, with 200M reqs / month: https://twitter.com/levelsio/status/1520356430800617472 https://twitter.com/levelsio/status/1520356430800617472 Expensify got 4 million request _per second_ out of a custom SQLite-based setup (on a huge machine, but still): 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... I would call those serious production apps.

thomascgalvin 4y ago
I specifically cited concurrent writes. SQLite is fantastic for read-only data. Those websites are using SQLite for what it's designed for. But, if they were trying to use SQLite to do even dozens of writes per second, it would crash and burn.
- markusw 4y ago
  Well, that claim is fairly easy to test. So I did: https://gist.github.com/markuswustenberg/0030360d7d4679f2656f7ecc8d865150 https://gist.github.com/markuswustenberg/0030360d7d4679f2656... Nothing fancy, just a small table that holds some blog posts with an ID, a title, some content, and a creation timestamp. I ran the benchmark with and without WAL enabled on my Macbook Air (2020, M1) with some SSD drive inside. Results: $ make benchmark go test -bench=. goos: darwin goarch: arm64 pkg: sqlite BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 6441 191735 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 102559 11205 ns/op PASS ok sqlite 3.725s That's around 89k writes per second in parallel on all available cores with WAL enabled. I know this is a trivial setup, but adjust to your liking. You'll find that SQLite probably doesn't crash and burn with dozens of writes.
  - thomascgalvin 4y ago
    This is single-threaded, which a production app would not be. That's when things get scary with SQLite. Yes, I was incorrect when I said SQLite couldn't handle lots of writes quickly; what I should have said is that it can't handle lots of writes from multiple threads quickly.
    - cy_hauser 4y ago
      I'm not understanding this. Wouldn't the lower bound on performance be the single thread? Any worse performance and you queue the write requests to match the single thread performance.
      - thomascgalvin 4y ago
        No, because of SQLite's locking mechanism. It uses filesytem-level constructs to support concurrency, and this gets dicey, quickly.
        markusw 4y ago
        As my test above shows, use a mutex for writes in Go directly and whatever locking performance problems SQLite exhibits disappear. I suspect because of the polling used in SQLite as described elsewhere on this page.
    - markusw 4y ago
      Okay, fair enough. I thought my previous test was parallel as well, but I was mistaken. Here's one for 64 parallel writes: https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7ec112bfc887be https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7... $ make benchmark go test -bench=. goos: darwin goarch: arm64 pkg: sqlite BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 100 16317415 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 1196 1198679 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8 58455 17461 ns/op PASS ok sqlite 4.557s 1e9/1198679 = 834 writes per second is still far from crash and burn territory when using just WAL mode. Of course, it gets more interesting when there are also concurrent readers, as people are trying out elsewhere in the discussion. But the point still stands: it can handle way, waaaay better than "fives of authors" in a not-at-all scary way.
      - marktangotango 4y ago
        You should duplicate this test against postgres inserts. I did the same thing for sqlite; in go using a mutex on inserts, and sqlite still out performs postgres on inserts (on the same machine).

thomascgalvin 4y ago

I specifically cited concurrent writes. SQLite is fantastic for read-only data. Those websites are using SQLite for what it's designed for. But, if they were trying to use SQLite to do even dozens of writes per second, it would crash and burn.

markusw 4y ago
Well, that claim is fairly easy to test. So I did: https://gist.github.com/markuswustenberg/0030360d7d4679f2656f7ecc8d865150 https://gist.github.com/markuswustenberg/0030360d7d4679f2656... Nothing fancy, just a small table that holds some blog posts with an ID, a title, some content, and a creation timestamp. I ran the benchmark with and without WAL enabled on my Macbook Air (2020, M1) with some SSD drive inside. Results: $ make benchmark go test -bench=. goos: darwin goarch: arm64 pkg: sqlite BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 6441 191735 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 102559 11205 ns/op PASS ok sqlite 3.725s That's around 89k writes per second in parallel on all available cores with WAL enabled. I know this is a trivial setup, but adjust to your liking. You'll find that SQLite probably doesn't crash and burn with dozens of writes.
- thomascgalvin 4y ago
  This is single-threaded, which a production app would not be. That's when things get scary with SQLite. Yes, I was incorrect when I said SQLite couldn't handle lots of writes quickly; what I should have said is that it can't handle lots of writes from multiple threads quickly.
  - cy_hauser 4y ago
    I'm not understanding this. Wouldn't the lower bound on performance be the single thread? Any worse performance and you queue the write requests to match the single thread performance.
    - thomascgalvin 4y ago
      No, because of SQLite's locking mechanism. It uses filesytem-level constructs to support concurrency, and this gets dicey, quickly.
      - markusw 4y ago
        As my test above shows, use a mutex for writes in Go directly and whatever locking performance problems SQLite exhibits disappear. I suspect because of the polling used in SQLite as described elsewhere on this page.
  - markusw 4y ago
    Okay, fair enough. I thought my previous test was parallel as well, but I was mistaken. Here's one for 64 parallel writes: https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7ec112bfc887be https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7... $ make benchmark go test -bench=. goos: darwin goarch: arm64 pkg: sqlite BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 100 16317415 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 1196 1198679 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8 58455 17461 ns/op PASS ok sqlite 4.557s 1e9/1198679 = 834 writes per second is still far from crash and burn territory when using just WAL mode. Of course, it gets more interesting when there are also concurrent readers, as people are trying out elsewhere in the discussion. But the point still stands: it can handle way, waaaay better than "fives of authors" in a not-at-all scary way.
    - marktangotango 4y ago
      You should duplicate this test against postgres inserts. I did the same thing for sqlite; in go using a mutex on inserts, and sqlite still out performs postgres on inserts (on the same machine).