5 ms·

What’s the advantage of the mutexed rw connection compared to the busy timeout and built-in locking?

mappu 4y ago
The built-in one actually sleeps and polls to check if the write lock is free again. It's a disaster for high write throughput. https://sqlite.org/forum/info/00312b3d02bc0583 https://sqlite.org/forum/info/00312b3d02bc0583 Sqlite's internal one has to work this way to support their multi-process guarantees. In comparison, an in-process Go mutex will be readied immediately.
- markusw 4y ago
  Ooooh, I didn’t know that detail. Thank you for sharing!
- LVB 4y ago
  Very interesting. I'm now keen to write a very simple benchmark comparing the results of N goroutines leaning on the WAL lock vs in-app serialization.
  - philosopher1234 4y ago
    would love to see the results of that, if you decide to do this
    - markusw 4y ago
      I did a small benchmark on some trivial code: https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50fff6e14cc26f5eb https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50ff... Results vary a bit on my machine, but it's pretty much the same (unless I'm doing something wrong, please point it out if so): $ make benchmark go test -bench=. goos: darwin goarch: arm64 pkg: sqlite BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 6036 186719 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 102567 11169 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8 104228 11226 ns/op PASS ok sqlite 3.833s
      - markusw 4y ago
        My mistake, this isn’t parallel. See https://news.ycombinator.com/item?id=33911491 https://news.ycombinator.com/item?id=33911491 for a parallel version with no readers.
    - LVB 4y ago
      I did a small benchmark and found for high concurrency write-only loads, the results of each goroutine writing directly vs using a mutex were basically the same. But things were more interesting when I added a bunch of read goroutines to the mix too (which would be more realistic for e.g. web traffic). In that case, the mutex-protected writes were drastically reduced in favor of reads. And while I have no idea how SQlite is actually handling its locking, it makes intuitive sense that if it doesn't even know about more write load it isn't going to schedule it. I'm going to clean up the code and post some results in gist, but here is a sample: Write-only (SQL statements/s): direct: 45314 w/mutex: 45304 Read/Write mix: direct: read: 85229 write: 22664 w/mutex: read: 139053 write: 1382
      - mappu 4y ago
        > I did a small benchmark and found for high concurrency write-only loads, the results of each goroutine writing directly vs using a mutex were basically the same. I'm interested to see your gist, but just to note this specific part seems to contradict https://news.ycombinator.com/item?id=33911491 https://news.ycombinator.com/item?id=33911491 , where at 64-wide parallelism for a write-only load, the Go mutex outperforms plain WAL by a factor of ~70x,
        LVB 4y ago
        That revised version is indeed interesting. I hope to get things pushed tonight. My approach managed goroutines directly and didn't use the Benchmark parallelism feature. I don't see any obvious reason why that matters but structurally that's a bit different
        LVB 4y ago
        Pretty interesting results by adding SetMaxOpenConns() to Marcus' gist: https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7ec112bfc887be?permalink_comment_id=4396598#gistcomment-4396598 https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7...
    - LVB 4y ago
      Finally got around to this: https://github.com/kalafut/go-sqlite-bench https://github.com/kalafut/go-sqlite-bench

The built-in one actually sleeps and polls to check if the write lock is free again. It's a disaster for high write throughput. https://sqlite.org/forum/info/00312b3d02bc0583 https://sqlite.org/forum/info/00312b3d02bc0583 Sqlite's internal one has to work this way to support their multi-process guarantees. In comparison, an in-process Go mutex will be readied immediately.

markusw 4y ago
Ooooh, I didn’t know that detail. Thank you for sharing!
LVB 4y ago
Very interesting. I'm now keen to write a very simple benchmark comparing the results of N goroutines leaning on the WAL lock vs in-app serialization.
- philosopher1234 4y ago
  would love to see the results of that, if you decide to do this
  - markusw 4y ago
    I did a small benchmark on some trivial code: https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50fff6e14cc26f5eb https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50ff... Results vary a bit on my machine, but it's pretty much the same (unless I'm doing something wrong, please point it out if so): $ make benchmark go test -bench=. goos: darwin goarch: arm64 pkg: sqlite BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 6036 186719 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 102567 11169 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8 104228 11226 ns/op PASS ok sqlite 3.833s
    - markusw 4y ago
      My mistake, this isn’t parallel. See https://news.ycombinator.com/item?id=33911491 https://news.ycombinator.com/item?id=33911491 for a parallel version with no readers.
  - LVB 4y ago
    I did a small benchmark and found for high concurrency write-only loads, the results of each goroutine writing directly vs using a mutex were basically the same. But things were more interesting when I added a bunch of read goroutines to the mix too (which would be more realistic for e.g. web traffic). In that case, the mutex-protected writes were drastically reduced in favor of reads. And while I have no idea how SQlite is actually handling its locking, it makes intuitive sense that if it doesn't even know about more write load it isn't going to schedule it. I'm going to clean up the code and post some results in gist, but here is a sample: Write-only (SQL statements/s): direct: 45314 w/mutex: 45304 Read/Write mix: direct: read: 85229 write: 22664 w/mutex: read: 139053 write: 1382
    - mappu 4y ago
      > I did a small benchmark and found for high concurrency write-only loads, the results of each goroutine writing directly vs using a mutex were basically the same. I'm interested to see your gist, but just to note this specific part seems to contradict https://news.ycombinator.com/item?id=33911491 https://news.ycombinator.com/item?id=33911491 , where at 64-wide parallelism for a write-only load, the Go mutex outperforms plain WAL by a factor of ~70x,
      - LVB 4y ago
        That revised version is indeed interesting. I hope to get things pushed tonight. My approach managed goroutines directly and didn't use the Benchmark parallelism feature. I don't see any obvious reason why that matters but structurally that's a bit different
      - LVB 4y ago
        Pretty interesting results by adding SetMaxOpenConns() to Marcus' gist: https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7ec112bfc887be?permalink_comment_id=4396598#gistcomment-4396598 https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7...
  - LVB 4y ago
    Finally got around to this: https://github.com/kalafut/go-sqlite-bench https://github.com/kalafut/go-sqlite-bench