Question about performance

[jlelse]

Hello,

I was searching for alternatives to mattn/go-sqlite3 and came across this library. I really like it so far!

But because I don't just want to make my Go programs a lot slower, I thought about doing some benchmarks first. (I'm new to benchmarking in Go!) You can see them here: https://git.jlel.se/jlelse/GoSqliteBench/src/commit/12db3eac1fd3c0d32e078e9a2d8feb917e83df84/SqliteBench_test.go

Unfortunately the results look like this:

$ go test -bench=.
goos: linux
goarch: amd64
pkg: git.jlel.se/jlelse/GoSqliteBench
cpu: Intel(R) Core(TM) i5-4590S CPU @ 3.00GHz
Benchmark_Zombiezen/Queries-4             149104              7141 ns/op
Benchmark_Mattn/Queries-4                 269184              4256 ns/op
PASS
ok      git.jlel.se/jlelse/GoSqliteBench        3.274s

Am I doing something wrong? Why is this library so much slower?

[zombiezen]

Hello,

I was searching for alternatives to mattn/go-sqlite3 and came across this library. I really like it so far!

Yay, thanks!

But because I don't just want to make my Go programs a lot slower, I thought about doing some benchmarks first. (I'm new to benchmarking in Go!) You can see them here: https://git.jlel.se/jlelse/GoSqliteBench/src/commit/12db3eac1fd3c0d32e078e9a2d8feb917e83df84/SqliteBench_test.go

Unfortunately the results look like this:

$ go test -bench=.
goos: linux
goarch: amd64
pkg: git.jlel.se/jlelse/GoSqliteBench
cpu: Intel(R) Core(TM) i5-4590S CPU @ 3.00GHz
Benchmark_Zombiezen/Queries-4             149104              7141 ns/op
Benchmark_Mattn/Queries-4                 269184              4256 ns/op
PASS
ok      git.jlel.se/jlelse/GoSqliteBench        3.274s

Am I doing something wrong? Why is this library so much slower?

Your benchmark seems like it's a pretty direct comparison in operations. I'd be curious to see whether it makes a difference to move the dbpool.Get outside the loop: that would help narrow down whether it's a slowness with the pool or whether it's coming from modernc.org/sqlite.

You may be interested in looking at the upstream performance issues:

• https://gitlab.com/cznic/sqlite/-/issues/27
• https://gitlab.com/cznic/sqlite/-/issues/39

[jlelse]

Thanks for the links. Makes sense, it's probably due to the C to Go translation. After all +67% isn't too bad after all. But sure, I will try it without the DB pool.

[jlelse]

I added some more benchmarks: https://git.jlel.se/jlelse/GoSqliteBench/src/commit/7171467de1ad2da8390436d91e4e404af7db45d4/SqliteBench_test.go

This are the results:

$ go test -bench=.
goos: linux
goarch: amd64
pkg: git.jlel.se/jlelse/GoSqliteBench
cpu: Intel(R) Core(TM) i5-4590S CPU @ 3.00GHz
Benchmark_Zombiezen/Parallel_Queries_with_Pool-4                  157695              7046 ns/op
Benchmark_Zombiezen/Sequential_Queries_with_Pool-4                 99048             10946 ns/op
Benchmark_Zombiezen/Sequential_Queries_without_Pool-4             191665              5821 ns/op
Benchmark_Mattn/Parallel_Queries-4                                260556              4338 ns/op
Benchmark_Mattn/Sequential_Queries-4                               79332             15115 ns/op
PASS
ok      git.jlel.se/jlelse/GoSqliteBench        7.088s

[zombiezen]

I'm surprised the sequential case performs worse for both packages. You might want to use the Go CPU profiler on those benchmarks to try to get a sense for what's the bottleneck.

[jlelse]

$ go test -bench=Zombiezen/Parallel -run=^$ -benchmem -cpuprofile profile.out
goos: linux
goarch: amd64
pkg: git.jlel.se/jlelse/GoSqliteBench
cpu: Intel(R) Core(TM) i5-4590S CPU @ 3.00GHz
Benchmark_Zombiezen/Parallel_Queries_with_Pool-4                  148962              7203 ns/op             408 B/op         16 allocs/op
PASS
ok      git.jlel.se/jlelse/GoSqliteBench        2.221s

$ go tool pprof profile.out 
File: GoSqliteBench.test
Build ID: 6bb7c167ac23edc994844c5df646faf161429ff7
Type: cpu
Time: Sep 7, 2021 at 8:52am (CEST)
Duration: 2.21s, Total samples = 2.51s (113.79%)
Entering interactive mode (type "help" for commands, "o" for options)
(pprof) top
Showing nodes accounting for 1040ms, 41.43% of 2510ms total
Dropped 126 nodes (cum <= 12.55ms)
Showing top 10 nodes out of 204
      flat  flat%   sum%        cum   cum%
     250ms  9.96%  9.96%      340ms 13.55%  sync.(*Mutex).lockSlow
     220ms  8.76% 18.73%      560ms 22.31%  sync.(*Mutex).Lock (inline)
     180ms  7.17% 25.90%      230ms  9.16%  sync.(*Mutex).Unlock
      90ms  3.59% 29.48%      460ms 18.33%  modernc.org/sqlite/lib.Xsqlite3BtreeMovetoUnpacked
      60ms  2.39% 31.87%       60ms  2.39%  runtime.futex
      50ms  1.99% 33.86%       60ms  2.39%  modernc.org/sqlite/lib.pcache1FetchNoMutex
      50ms  1.99% 35.86%       50ms  1.99%  runtime.lock2
      50ms  1.99% 37.85%       50ms  1.99%  runtime.unlock2
      50ms  1.99% 39.84%       60ms  2.39%  syscall.Syscall
      40ms  1.59% 41.43%       40ms  1.59%  modernc.org/libc.(*TLS).Alloc

Looks like the mutex functions introduce some overhead.

[jlelse]

An alternative to the mutex in the pool struct might be to use a sync.Map.

[delaneyj]

Just a heads up sync.Map is just a wrapper for a mutex and map. Would a RWMutex help? What's the ratio of read to write ?

[delaneyj]

I moved an event sourcing system from crawshaw to zombiezen. This is writing to 2 tables per event, thought I might be of some value for discussions. While slower on write it does flatten out well.

Very curiously about the details related to why in modernc/sqlite the reads can be so much faster, Haven't test out fully if anyone has a deeper understanding I'd love to know

[zombiezen]

Cool! I would imagine broadly that avoiding cgo overhead is the root cause, but only profiling could really answer that question.

[anacrolix]

I did notice some extra buffers used for copying things around in zombiezen sqlite. I guess they're to help interact with modernc.org/sqlite?

[zombiezen]

Yup, especially for blobs, there are a few additional copies necessary for correctness. There might be room for improvement there, but I hazily remember it being complicated.