Skip to content

This issue was moved to a discussion.

You can continue the conversation there. Go to discussion →

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Question about performance #22

Closed
jlelse opened this issue Sep 4, 2021 · 7 comments
Closed

Question about performance #22

jlelse opened this issue Sep 4, 2021 · 7 comments
Labels
question Further information is requested

Comments

@jlelse
Copy link

jlelse commented Sep 4, 2021

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 zombiezen added the question Further information is requested label Sep 6, 2021
@zombiezen
Copy link
Owner

zombiezen commented Sep 6, 2021

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:

@jlelse
Copy link
Author

jlelse commented Sep 6, 2021

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
Copy link
Author

jlelse commented Sep 6, 2021

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
Copy link
Owner

zombiezen commented Sep 6, 2021

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
Copy link
Author

jlelse commented Sep 7, 2021

$ 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
Copy link
Author

jlelse commented Sep 7, 2021

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

@delaneyj
Copy link

delaneyj commented Sep 10, 2021

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 ?

Repository owner locked and limited conversation to collaborators Sep 11, 2021

This issue was moved to a discussion.

You can continue the conversation there. Go to discussion →

Labels
question Further information is requested
Projects
None yet
Development

No branches or pull requests

3 participants