Hacker News Reader

Comment by markusw | Hacker News Reader

Well, that claim is fairly easy to test. So I did: 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.

View on HN

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.

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.

No, because of SQLite's locking mechanism. It uses filesytem-level constructs to support concurrency, and this gets dicey, quickly.

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.

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

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

Reply on news.ycombinator.com