[HN Gopher] I benchmarked six Go SQLite drivers
___________________________________________________________________
I benchmarked six Go SQLite drivers
Author : cvilsmeier
Score : 170 points
Date : 2023-12-13 13:00 UTC (10 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| dave78 wrote:
| I hadn't heard of sqinn before. According to these benchmarks it
| beats Cgo-based solutions most of the time, which makes it a very
| interesting candidate. Anyone have real-world experience using
| it?
| KomoD wrote:
| (Sqinn is made by the same guy that made these benchmarks in
| case you didn't realize)
| dave78 wrote:
| I did not notice that, thanks for pointing it out.
| cvilsmeier wrote:
| Yes, in fact the benchmark started out as a comparison
| between sqinn and mattn. (There was no modernc at that
| time.)
| cvilsmeier wrote:
| Sqinn author here. Yes, sqinn performs quite well compared to
| the 'standard' mattn driver. The only use case it clearly
| breaks down is when SELECTing very large (gigabytes)
| resultsets.
| microtherion wrote:
| That stood out to me as well. Any insights why sqinn and
| zombie underperform in this case, and is the problem inherent
| to their design?
| cvilsmeier wrote:
| For sqinn it's because of its design: Shuffling that much
| data over process boundaries takes time. For zombie, more
| pprof would be needed to explain the behaviour.
| ncruces wrote:
| I would suspect some unnecessary copying. Nothing else
| explains a "lower level" binding (zombie) performing
| worse than modernc.
|
| My (WASM) binding also has a lower level API. But I
| worked hard to make database/sql work first class, so I
| wouldn't expect much improvement.
| iansinnott wrote:
| Thanks for creating the benchmarks, I hadn't heard of most of
| those libs.
|
| I seem to remember that modernc worked great until i added a FTS5
| [1] table and things became very slow. This was a while ago now,
| so it may have changed or I may mave misattributed the slowness
| to the non-CGO implementation.
|
| I'd be curious to see how each performs with a fts index and some
| triggers.
|
| [1]: https://www.sqlite.org/fts5.html
| Traubenfuchs wrote:
| What a curious state, there is just one jdbc (=java) driver for
| SQLite. Why are there 6 (or more!) for Go?
| cvilsmeier wrote:
| Maybe because Go devs are more allergic to 'non-Go' solutions
| that Java devs are to 'non-Java' solutions? (Explain: Java's
| xerial driver is a DLL/SO wrapped in a Java library)
| lmz wrote:
| Also because there's a mismatch between goroutines and C
| threads as described here
| https://www.cockroachlabs.com/blog/the-cost-and-
| complexity-o... while Java threads can map 1:1 to C threads.
| butlerm wrote:
| Perhaps someone should define a new C compatible threading
| API to allow C libraries (including glibc or a wrapper
| around glibc) to work with something other than native
| pthreads. Such as goroutines or Java threads and so on.
| morelisp wrote:
| Many general M:N threading solutions have been tried over
| the years. As far as I know current thinking is still
| that you need substantial cooperation from a language
| runtime to make it worthwhile. (And even then it's hard -
| Java's first attempt failed and they went 1:1 essentially
| between 1998-2022.)
| eatonphil wrote:
| If you search "topic:jdbc topic:sqlite" on GitHub [0] (and look
| closely, since most results are not relevant) you'll see there
| are more than one (e.g. [1]). xerial may be the defacto SQLite
| driver for Java. mattn/go-sqlite3 is the defacto SQLite driver
| for Go. But there are other options.
|
| [0]
| https://github.com/search?q=topic%3Ajdbc%20topic%3Asqlite&ty...
|
| [1] https://github.com/gwenn/sqlite-jna
| morelisp wrote:
| Aside from the threading mismatch,
|
| - There is a version transpiled from C to Go. A transpilation
| from C to Java (or even the JVM) would be considerably more
| difficult, a naive translation would likely undergo a much
| larger performance hit. Maybe with Valhalla this will change.
|
| - Modern Java deployment is not generally complicated by
| JNI/JNA (it's already that complicated to start with; Maven
| already wrangles some of it, although it's still a pain in many
| cases). Go deployments are simpler if no C linkage is involved.
|
| - Some of these are not database/sql, the equivalent of JDBC,
| drivers. They're purpose-built drivers that expose rich SQLite-
| specific features. With the huge popularity of Spring most
| developers don't even interact at the JDBC level today, only
| e.g. JPA. IMO Java developers are missing out on richer SQL
| features in their DBs, but well, they seem to mostly manage.
| erik_seaberg wrote:
| For light integration testing, Apache Derby has pretty good
| fidelity with prod SQL databases. Never benched it but perf
| wasn't a problem.
| eatonphil wrote:
| I've done some variations of this as well recently. For inserts:
| https://github.com/eatonphil/databases-intuition/blob/main/R....
| And for selects: https://github.com/eatonphil/databases-
| intuition/blob/main/R....
|
| Our workloads are a bit different and obviously our machines are
| a bit different.
|
| Mine only compares mattn/go-sqlite3 to my own fork of
| https://github.com/bvinc/go-sqlite-lite. go-sqlite-lite seemed
| like an easier-to-use version of crawshaw's package but it was
| abandoned so I forked it to bring it up to date.
|
| I agree, for best performance you shouldn't use mattn/go-sqlite3.
| It does some extra work in hotpaths. It is also higher level and
| easier to use though. So pick what's important to you.
| Groxx wrote:
| "without cgo" benchmarks should probably have comparisons for
| stuff like indexing too, since that's no longer in the shared C
| library that every other library uses - I would generally expect
| them to be slower here. And interop with Go code for custom
| funcs, since sqlite makes that so easy, where I'd expect them to
| be faster in at least some cases (no repeated cgo overhead).
|
| "Complex" kinda covers this, since there are foreign keys
| involved, and it's also where e.g. Zombie shows an
| uncharacteristic slowdown compared to the other benchmarks. Seems
| like it's probably not a coincidence?
| acatton wrote:
| In my personal opinion and usage, the performance doesn't matter.
| Only one driver is written in pure go, and can be easily
| statically compiled and/or cross-compiled.
|
| > modernc, modernc.org/sqlite, a pure Go solution. This is a
| newer library, based on the SQLite C code re-written in Go.
|
| Unless I'm mistaken, this is not a re-write in Go. This is a
| transpilation of the the SQLite C library into go, using
| https://gitlab.com/cznic/ccgo
| vbezhenar wrote:
| Sqlite developers go to very great lengths making sure their
| software works reliably. You can't just introduce some
| transpiler in-between and expect that it'll work. It's a
| different software and it should not even be called sqlite IMO.
|
| The only proper way to use sqlite is to use FFI.
| arp242 wrote:
| > You can't just introduce some transpiler in-between and
| expect that it'll work
|
| Obviously it "works" and they didn't "just" transpile it but
| spent quite a bit of effort on this. Whether it's free of
| bugs that are not present in SQLite is a different matter.
| And while it's not run against the SQLite proprietary tests,
| it does pass all of the TCL tests IIRC, which are quite
| comprehensive on their own, so there shouldn't be huge
| glaring bugs.
| shp0ngle wrote:
| They run the whole testsuite on the transpiled code. I think.
|
| But yeah it does give me a little shiver to think you are
| transpiling C code to go code, and the go-code is not really
| "pure go", but has a platform-dependent unsafe operations.
| Just look at the repo
|
| https://gitlab.com/cznic/sqlite/-/tree/master/lib?ref_type=h.
| ..
| mook wrote:
| My understanding is that the full test suite is actually
| proprietary and it's part of how sqlite is funded -- the
| maintainers can provide better claims. There is a public
| test suite but it's a subset of what they do.
|
| https://www.sqlite.org/testing.html
| shepherdjerred wrote:
| We use go-sqlite3 at work and have had a positive experience,
| with the benefit of having fewer system dependencies and a
| simpler build process.
| Aissen wrote:
| The biggest issue of the modernc approach is that it can't use
| the proprietary sqlite test suites and fuzzer to ensure that it
| works like the original: https://www.sqlite.org/testing.html
| Aissen wrote:
| (I just realized that probably goes for libsql as well)
| psanford wrote:
| I cross compile mattn/go-sqlite3 a lot. It's about the easiest
| cgo project to cross compile since it has no external
| dependencies.
|
| Just use zig as your c compiler and it all just works.
| makeworld wrote:
| Do you have any links for using Zig for this?
| psanford wrote:
| export CC="zig cc -target aarch64-linux-musl"
| el-dude-arino wrote:
| Well I found my side project for the day
| kristoff_it wrote:
| https://zig.news/kristoff/building-sqlite-with-cgo-for-
| every...
| mariusor wrote:
| My only complain so far about the package is that it required
| transpiled versions of the whole dependency chain for sqlite in
| order to get it working, from libc to tcl. The whole tree for
| the latest version is about 2G. This is not a trivial amount of
| traffic/space to use for every compilation.
| shp0ngle wrote:
| > Only one driver is written in pure go, and can be easily
| statically compiled and/or cross-compiled.
|
| Which one do you mean? The WASM one? It includes WASM, it needs
| to be compiled too.
|
| Ah github.com/cvilsmeier/sqinn-go. Which... is made by the same
| person that made this benchmark...?
|
| edit: and that requires some random binary to be pre-
| installed...? Which is in C anyway?
|
| https://github.com/cvilsmeier/sqinn-go
|
| https://github.com/cvilsmeier/sqinn
|
| so I don't see any "actually written in go".
| acatton wrote:
| The modernc one is the one I was refering to.
|
| https://gitlab.com/cznic/sqlite
| shp0ngle wrote:
| But that's not pure go, that's C transpiled to go, but even
| the transpilation is using OS specific and CPU-specific
| unsafe code.
| ncruces wrote:
| For the WASM one (disclaimer: I built it) it's at least
| reasonably simple to reproduce the build [1], and the
| resulting blob is fully cross platform.
|
| Also, although I do (lightly) patch SQLite which might
| invalidate results in your view, the SQLite team is
| producing/testing/releasing WASM builds of SQLite built with
| much the same toolchain, which, hopefully, smokes out
| compiler bugs.
|
| [1]: https://github.com/ncruces/go-
| sqlite3/blob/main/.github/work...
| zombiezen wrote:
| zombiezen.com/go/sqlite is built on top of modernc.org/sqlite,
| so also has the same properties.
| alephnan wrote:
| How come Zombiezen came out faster ? I'd expect the opposite
| cvilsmeier wrote:
| > How come Zombiezen came out faster ? I'd expect the
| opposite
|
| (OP here) Honestly, me too. Maybe it has to do with leaving
| out that database/sql driver layer. I guess it would need
| another round of pprof to find out the real reason.
| tedunangst wrote:
| Have you tested that the cross-compiled pure go sqlite runs,
| not just compiles, on every platform? (Spoiler: it does not.)
| auspiv wrote:
| Sure showing time and N works. But it'd be a lot easier to
| interpret the data quickly if it was shown in terms of operations
| per second. Smaller bars do not usually mean improved
| performance.
| jen20 wrote:
| ... what? Lots of graphs for performance (especially latency)
| use smaller bars to mean better numbers.
| cp9 wrote:
| the thing about the mattn driver is that it supports all the
| features that sqlite itself supports. you can compile in vtables,
| extra stat stuff, which FTS option you want, anything. and if you
| use zig as your cross compiler you don't even need separate
| toolchains for the different arch/OS combos, it all just works
| matharmin wrote:
| I'd recommend doing these benchmarks with WAL mode and
| synchronous = NORMAL as well. It makes quite a big difference in
| performance especially with many small transactions (e.g. the
| first example that inserts without explicit transactions).
| neonsunset wrote:
| There was a comment from
| https://news.ycombinator.com/user?id=HackerThemAll with neutral
| rating that was likely taken down by a mod action despite not
| violating the rules.
|
| I am reposting it below shortly with minor stylistic changes for
| politeness.
|
| "Go's SQLite drivers exhibit surprisingly poor performance. In C#
| I was able to conduct 2 millions of point queries per second on
| my laptop. And it's not the fastest language in the world.
|
| Go is overrated. It's crudely trying to imitate what Pascal had
| in '80s using awkward syntax and tooling, but giving you extra
| CVEs for free."
|
| Personally, I did not expect it to be this bad...and C# SQLite
| drivers aren't even something new - most of them have been
| written eons ago and consist of fairly standard somewhat
| allocatey code.
|
| I wonder if it's because of significant interop overhead in Go,
| or just fundamental language limitations and quality issues.
| zmj wrote:
| FFI was high priority for C# in the early days, when most
| meaningful .NET software was running on Windows and doing
| interop with win32. The runtime was designed to ensure that
| scenario would be low-overhead, and that legacy lives on in
| modern .NET.
| justinclift wrote:
| As a data point, the gwenn/gosqlite one for GitHub returns field
| data correctly even when an individual fields' data type doesn't
| match the column definition.
|
| https://github.com/gwenn/gosqlite
|
| That's important when processing data from untrusted sources
| (user generated content, etc).
|
| No idea how it compares to the others performance wise though. :)
| tedunangst wrote:
| > Mattn, although the de-facto standard, is not the best overall
| solution.
|
| What's wrong with it?
| cvilsmeier wrote:
| Nothing wrong with it, but there are other drivers that cross-
| compile better and are faster.
| ncruces wrote:
| I'm the author or the WASM (+wazero) based github.com/ncruces/go-
| sqlite3. Happy to field questions.
|
| W.r.t. benchmark results.
|
| wazero's current compiler is somewhat naive, which may explain a
| large performance delta in CPU bound tests. A new compiler is in
| the works [1].
|
| OTOH it seems interesting that in the (IO bound?) large test I'm
| doing better than modernc. I wonder why.
|
| I'll dig deeper into the results.
|
| [1]: https://github.com/tetratelabs/wazero/pull/1869
___________________________________________________________________
(page generated 2023-12-13 23:01 UTC)