3 ms·

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

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).

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).