7 ms·

I benchmarked six Go SQLite drivers

192 points by cvilsmeier 3y ago

cp9 3y ago
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
- ncruces 3y ago
  It doesn't support everything. No driver likely does. An example of one thing missing is the pointer passing interface which limits the ability to create complex extensions. If support was added, the way function creation and virtual tables where wrapped would make it hard to use. But even more trivial things like a decent interface to incremental blob IO have been left unaddressed. https://www.sqlite.org/bindptr.html https://www.sqlite.org/bindptr.html https://github.com/mattn/go-sqlite3/issues/239 https://github.com/mattn/go-sqlite3/issues/239
matharmin 3y ago
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).
HackerThemAll 3y ago
[flagged]
- rpsw 3y ago
  Did you run the Go benchmarks on the same laptop to compare?
  - HackerThemAll 3y ago
    Yes, I did. Never exceeded 40k trivial queries per second. That was my last day with Go lang. Did not analyze what amounted to such bad perf, I entirely lost interest with the language. Later I learnt the way of Go's date formatting and then I put it in the same drawer as INTERCAL or Brainf*ck.
neonsunset 3y ago
There was a comment from https://news.ycombinator.com/user?id=HackerThemAll 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 3y ago
  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.
  - neonsunset 3y ago
    FFI very much remains a priority today which can be seen with changes and migration to [LibraryImport], bespoke NativeAOT modes to produce native dynamically or statically linked libraries which expose C ABI functions with [UnmanagedCallersOnly] and now there is also a project to implement direct Swift library evolution ABI interop for improved support of iOS with MAUI as one of its main goals.
justinclift 3y ago
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 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 3y ago
> Mattn, although the de-facto standard, is not the best overall solution. What's wrong with it?
- cvilsmeier 3y ago
  Nothing wrong with it, but there are other drivers that cross-compile better and are faster.
ncruces 3y ago
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 https://github.com/tetratelabs/wazero/pull/1869
- ncruces 3y ago
  The performance difference was larger than I had expected. But this is good. To fix a recent crash [1] that was happening due to a particular case of reentrancy, which only showed up when I implemented virtual tables and queried other tables to implement one (e.g.: Go calls sqlite3_step to execute a query, which calls Go because it's a query on virtual table, which calls sqlite3_step to scan another table) I introduced a performance regression. The fix [2] was not to reuse some objects I was allocating once per connection. A mitigation for the regression was (very naive) caching [3]. TLDR: my caching is just not good enough. Simply caching more will go a long way (confirmed already by doubling cache size), but now that I have a good benchmark, I'll do better. I expect to cut numbers for CPU bound tests in half due to this mishap. So, thanks cvilsmeier! [1]: https://github.com/ncruces/go-sqlite3/commit/a9e32fd3f0b9f394355a1953d4892243f6e365ff https://github.com/ncruces/go-sqlite3/commit/a9e32fd3f0b9f39... [2]: https://github.com/ncruces/go-sqlite3/commit/d862f47d95d522fb7a63aacf1259714aff986d46 https://github.com/ncruces/go-sqlite3/commit/d862f47d95d522f... [3]: https://github.com/ncruces/go-sqlite3/commit/9c562f5d8bf7436e17cb1b761f160bfe98d70679 https://github.com/ncruces/go-sqlite3/commit/9c562f5d8bf7436...
  - ncruces 3y ago
    OK, so if I'm looking at this right, a smarter, wider cache goes a great length to fixing the issue. In [1] I implemented a simple PLRU bit cache, and I'm seeing an 8x performance improvement in some of the tests I was doing worse in: Before: bench-ncruces - simple insert query dbsize bench-ncruces - simple 21224 16495 58687488 bench-ncruces - complex/200/100/20 insert query dbsize bench-ncruces - complex/200/100/20 14993 15228 25354240 bench-ncruces - many/N=10 query dbsize bench-ncruces - many/N=10 483 36864 bench-ncruces - many/N=100 query dbsize bench-ncruces - many/N=100 3129 36864 bench-ncruces - many/N=1000 query dbsize bench-ncruces - many/N=1000 28034 94208 bench-ncruces - large/N=50000 query dbsize bench-ncruces - large/N=50000 428 501981184 bench-ncruces - large/N=100000 query dbsize bench-ncruces - large/N=100000 779 1003761664 bench-ncruces - large/N=200000 query dbsize bench-ncruces - large/N=200000 1475 2007330816 bench-ncruces - concurrent/N=2 query dbsize bench-ncruces - concurrent/N=2 13091 56573952 bench-ncruces - concurrent/N=4 query dbsize bench-ncruces - concurrent/N=4 14731 56573952 bench-ncruces - concurrent/N=8 query dbsize bench-ncruces - concurrent/N=8 24730 56573952 After: bench-ncruces - simple insert query dbsize bench-ncruces - simple 5128 3026 58687488 bench-ncruces - complex/200/100/20 insert query dbsize bench-ncruces - complex/200/100/20 3127 3730 25354240 bench-ncruces - many/N=10 query dbsize bench-ncruces - many/N=10 93 36864 bench-ncruces - many/N=100 query dbsize bench-ncruces - many/N=100 403 36864 bench-ncruces - many/N=1000 query dbsize bench-ncruces - many/N=1000 3470 94208 bench-ncruces - large/N=50000 query dbsize bench-ncruces - large/N=50000 444 501981184 bench-ncruces - large/N=100000 query dbsize bench-ncruces - large/N=100000 717 1003761664 bench-ncruces - large/N=200000 query dbsize bench-ncruces - large/N=200000 1401 2007330816 bench-ncruces - concurrent/N=2 query dbsize bench-ncruces - concurrent/N=2 3275 56573952 bench-ncruces - concurrent/N=4 query dbsize bench-ncruces - concurrent/N=4 3404 56573952 bench-ncruces - concurrent/N=8 query dbsize bench-ncruces - concurrent/N=8 4918 56573952 There's still work to do. I could use ints rather than strings for function identifiers. I'll evaluate that later. [1]: https://github.com/ncruces/go-sqlite3/commit/964a42c76deb9c7dcff2dca5c19f0453e062c55f https://github.com/ncruces/go-sqlite3/commit/964a42c76deb9c7...
dave78 3y ago
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 3y ago
  (Sqinn is made by the same guy that made these benchmarks in case you didn't realize)
  - dave78 3y ago
    I did not notice that, thanks for pointing it out.
    - cvilsmeier 3y ago
      Yes, in fact the benchmark started out as a comparison between sqinn and mattn. (There was no modernc at that time.)
- cvilsmeier 3y ago
  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 3y ago
    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 3y ago
      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.
iansinnott 3y ago
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 https://www.sqlite.org/fts5.html
Traubenfuchs 3y ago
What a curious state, there is just one jdbc (=java) driver for SQLite. Why are there 6 (or more!) for Go?
- morelisp 3y ago
  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 3y ago
    For light integration testing, Apache Derby has pretty good fidelity with prod SQL databases. Never benched it but perf wasn’t a problem.
- cvilsmeier 3y ago
  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 3y ago
    Also because there's a mismatch between goroutines and C threads as described here https://www.cockroachlabs.com/blog/the-cost-and-complexity-of-cgo/ https://www.cockroachlabs.com/blog/the-cost-and-complexity-o... while Java threads can map 1:1 to C threads.
eatonphil 3y ago
I've done some variations of this as well recently. For inserts: https://github.com/eatonphil/databases-intuition/blob/main/README.md#sqlite-prepared-insert https://github.com/eatonphil/databases-intuition/blob/main/R.... And for selects: https://github.com/eatonphil/databases-intuition/blob/main/README.md#sqlite 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 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 3y ago
"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 3y ago
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 https://gitlab.com/cznic/ccgo
- mariusor 3y ago
  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 3y ago
  > 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-go https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn so I don't see any "actually written in go".
  - acatton 3y ago
    The modernc one is the one I was refering to. https://gitlab.com/cznic/sqlite https://gitlab.com/cznic/sqlite
    - shp0ngle 3y ago
      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.
  - 3y ago
auspiv 3y ago
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 3y ago
  … what? Lots of graphs for performance (especially latency) use smaller bars to mean better numbers.
effnorwood 3y ago
[dead]

192 points by cvilsmeier 3y ago

cp9 3y ago

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

ncruces 3y ago
It doesn't support everything. No driver likely does. An example of one thing missing is the pointer passing interface which limits the ability to create complex extensions. If support was added, the way function creation and virtual tables where wrapped would make it hard to use. But even more trivial things like a decent interface to incremental blob IO have been left unaddressed. https://www.sqlite.org/bindptr.html https://www.sqlite.org/bindptr.html https://github.com/mattn/go-sqlite3/issues/239 https://github.com/mattn/go-sqlite3/issues/239

matharmin 3y ago

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

HackerThemAll 3y ago

[flagged]

rpsw 3y ago
Did you run the Go benchmarks on the same laptop to compare?
- HackerThemAll 3y ago
  Yes, I did. Never exceeded 40k trivial queries per second. That was my last day with Go lang. Did not analyze what amounted to such bad perf, I entirely lost interest with the language. Later I learnt the way of Go's date formatting and then I put it in the same drawer as INTERCAL or Brainf*ck.

neonsunset 3y ago

There was a comment from https://news.ycombinator.com/user?id=HackerThemAll 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 3y ago
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.
- neonsunset 3y ago
  FFI very much remains a priority today which can be seen with changes and migration to [LibraryImport], bespoke NativeAOT modes to produce native dynamically or statically linked libraries which expose C ABI functions with [UnmanagedCallersOnly] and now there is also a project to implement direct Swift library evolution ABI interop for improved support of iOS with MAUI as one of its main goals.

justinclift 3y ago

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 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 3y ago

> Mattn, although the de-facto standard, is not the best overall solution. What's wrong with it?

cvilsmeier 3y ago
Nothing wrong with it, but there are other drivers that cross-compile better and are faster.

ncruces 3y ago

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 https://github.com/tetratelabs/wazero/pull/1869

ncruces 3y ago
The performance difference was larger than I had expected. But this is good. To fix a recent crash [1] that was happening due to a particular case of reentrancy, which only showed up when I implemented virtual tables and queried other tables to implement one (e.g.: Go calls sqlite3_step to execute a query, which calls Go because it's a query on virtual table, which calls sqlite3_step to scan another table) I introduced a performance regression. The fix [2] was not to reuse some objects I was allocating once per connection. A mitigation for the regression was (very naive) caching [3]. TLDR: my caching is just not good enough. Simply caching more will go a long way (confirmed already by doubling cache size), but now that I have a good benchmark, I'll do better. I expect to cut numbers for CPU bound tests in half due to this mishap. So, thanks cvilsmeier! [1]: https://github.com/ncruces/go-sqlite3/commit/a9e32fd3f0b9f394355a1953d4892243f6e365ff https://github.com/ncruces/go-sqlite3/commit/a9e32fd3f0b9f39... [2]: https://github.com/ncruces/go-sqlite3/commit/d862f47d95d522fb7a63aacf1259714aff986d46 https://github.com/ncruces/go-sqlite3/commit/d862f47d95d522f... [3]: https://github.com/ncruces/go-sqlite3/commit/9c562f5d8bf7436e17cb1b761f160bfe98d70679 https://github.com/ncruces/go-sqlite3/commit/9c562f5d8bf7436...
- ncruces 3y ago
  OK, so if I'm looking at this right, a smarter, wider cache goes a great length to fixing the issue. In [1] I implemented a simple PLRU bit cache, and I'm seeing an 8x performance improvement in some of the tests I was doing worse in: Before: bench-ncruces - simple insert query dbsize bench-ncruces - simple 21224 16495 58687488 bench-ncruces - complex/200/100/20 insert query dbsize bench-ncruces - complex/200/100/20 14993 15228 25354240 bench-ncruces - many/N=10 query dbsize bench-ncruces - many/N=10 483 36864 bench-ncruces - many/N=100 query dbsize bench-ncruces - many/N=100 3129 36864 bench-ncruces - many/N=1000 query dbsize bench-ncruces - many/N=1000 28034 94208 bench-ncruces - large/N=50000 query dbsize bench-ncruces - large/N=50000 428 501981184 bench-ncruces - large/N=100000 query dbsize bench-ncruces - large/N=100000 779 1003761664 bench-ncruces - large/N=200000 query dbsize bench-ncruces - large/N=200000 1475 2007330816 bench-ncruces - concurrent/N=2 query dbsize bench-ncruces - concurrent/N=2 13091 56573952 bench-ncruces - concurrent/N=4 query dbsize bench-ncruces - concurrent/N=4 14731 56573952 bench-ncruces - concurrent/N=8 query dbsize bench-ncruces - concurrent/N=8 24730 56573952 After: bench-ncruces - simple insert query dbsize bench-ncruces - simple 5128 3026 58687488 bench-ncruces - complex/200/100/20 insert query dbsize bench-ncruces - complex/200/100/20 3127 3730 25354240 bench-ncruces - many/N=10 query dbsize bench-ncruces - many/N=10 93 36864 bench-ncruces - many/N=100 query dbsize bench-ncruces - many/N=100 403 36864 bench-ncruces - many/N=1000 query dbsize bench-ncruces - many/N=1000 3470 94208 bench-ncruces - large/N=50000 query dbsize bench-ncruces - large/N=50000 444 501981184 bench-ncruces - large/N=100000 query dbsize bench-ncruces - large/N=100000 717 1003761664 bench-ncruces - large/N=200000 query dbsize bench-ncruces - large/N=200000 1401 2007330816 bench-ncruces - concurrent/N=2 query dbsize bench-ncruces - concurrent/N=2 3275 56573952 bench-ncruces - concurrent/N=4 query dbsize bench-ncruces - concurrent/N=4 3404 56573952 bench-ncruces - concurrent/N=8 query dbsize bench-ncruces - concurrent/N=8 4918 56573952 There's still work to do. I could use ints rather than strings for function identifiers. I'll evaluate that later. [1]: https://github.com/ncruces/go-sqlite3/commit/964a42c76deb9c7dcff2dca5c19f0453e062c55f https://github.com/ncruces/go-sqlite3/commit/964a42c76deb9c7...

dave78 3y ago

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 3y ago
(Sqinn is made by the same guy that made these benchmarks in case you didn't realize)
- dave78 3y ago
  I did not notice that, thanks for pointing it out.
  - cvilsmeier 3y ago
    Yes, in fact the benchmark started out as a comparison between sqinn and mattn. (There was no modernc at that time.)
cvilsmeier 3y ago
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 3y ago
  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 3y ago
    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.

iansinnott 3y ago

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 https://www.sqlite.org/fts5.html

Traubenfuchs 3y ago

What a curious state, there is just one jdbc (=java) driver for SQLite. Why are there 6 (or more!) for Go?

morelisp 3y ago
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 3y ago
  For light integration testing, Apache Derby has pretty good fidelity with prod SQL databases. Never benched it but perf wasn’t a problem.
cvilsmeier 3y ago
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 3y ago
  Also because there's a mismatch between goroutines and C threads as described here https://www.cockroachlabs.com/blog/the-cost-and-complexity-of-cgo/ https://www.cockroachlabs.com/blog/the-cost-and-complexity-o... while Java threads can map 1:1 to C threads.

eatonphil 3y ago

I've done some variations of this as well recently. For inserts: https://github.com/eatonphil/databases-intuition/blob/main/README.md#sqlite-prepared-insert https://github.com/eatonphil/databases-intuition/blob/main/R.... And for selects: https://github.com/eatonphil/databases-intuition/blob/main/README.md#sqlite 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 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 3y ago

"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 3y ago

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 https://gitlab.com/cznic/ccgo

mariusor 3y ago
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 3y ago
> 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-go https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn so I don't see any "actually written in go".
- acatton 3y ago
  The modernc one is the one I was refering to. https://gitlab.com/cznic/sqlite https://gitlab.com/cznic/sqlite
  - shp0ngle 3y ago
    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.
- 3y ago

auspiv 3y ago

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 3y ago
… what? Lots of graphs for performance (especially latency) use smaller bars to mean better numbers.

effnorwood 3y ago

[dead]