9 ms·

What’s the case against cgo for SQLite? Just the usual cgo performance overhead? It seems like a pretty good cgo use case: a decent amount of work, which is ty

What’s the case against cgo for SQLite? Just the usual cgo performance overhead? It seems like a pretty good cgo use case: a decent amount of work, which is typically slow enough that cgo overhead isn’t perf critical (because DB usually means disk reads), a super robust and well tested C library with a super well maintained cgo wrapper (mattn).

llimllib 3y ago
the other pain point I know of is that it's hard to cross-compile. You can do it with zig[1], but it's still not pleasant. 1: https://zig.news/kristoff/building-sqlite-with-cgo-for-every-os-4cic https://zig.news/kristoff/building-sqlite-with-cgo-for-every...
- tptacek 3y ago
  Or with musl-cross: https://github.com/FiloSottile/homebrew-musl-cross https://github.com/FiloSottile/homebrew-musl-cross It works pretty well! It's a thing you might keep in your back pocket to test builds from your ARM dev machine on a dev host, and then let the CI/CD system build the real version later.
- arp242 3y ago
  And Zig only covers a pretty limited range of platforms to start with.
infogulch 3y ago
mattn's sqlite3 is probably the most ideal use case for cgo imaginable. But it can still be annoying to set up cgo to build across many platforms. I think Go should just pull a Zig and just embed a full blown C compiler into go build.
- scosman 3y ago
  Exactly. Good to know I didn’t miss a trade off in the mix. If I’m choosing to use a C framework (SQLite) I’m okay signing up for the environment costs. Prefer that over abstractions in an intermediate layer that might not be maintained in a few years.
  - scosman 3y ago
    Just for the record, had to compile sqlite in gomobile literally the day after this comment and it was a big pain . But got it working and sticking with this approach.
- kunley 3y ago
  Yeah, once I was on windows and couldn't get cgo sqlite working on a variety of mingw and alike compilers... felt like 90s. Using this cznic's pure-Go sqlite saved the day.
- pram 3y ago
  Didn't it? Isn't that what 5c, 6c, and 8c were.
- oefrha 3y ago
  Pulling a zig isn't going to solve all problems, as the zig cross compiler itself runs into problems fairly often. I already use CC='zig cc -target x86_64-linux-musl' (or whatever the target is) with cgo to cross-compile mattn/go-sqlite3, and relatively recently it simply stopped working[1] and a workaround had to do be added to about every single project of mine using SQLite through cgo. I also once tried to figure out a way to cross compile mattn/go-sqlite3 to 32bit Windows with zig, and failed. The best way to make cross compiling painless is to not use cgo at all. Which is why I use modernc/sqlite whenever possible now. Btw, bundling a C compiler is also much harder when you don't build on top of LLVM. [1] https://github.com/mattn/go-sqlite3/issues/1164 https://github.com/mattn/go-sqlite3/issues/1164
  - hnlmorg 3y ago
    I’m going to add a counter argument to all the cgo views raised: Personally I’ve had much more portability problems with a lot of the native Go ports of sqlite than I have with mattn’s cgo library. I author a shell that targets most of the architectures and platforms supported by Go. At the request of some users, I added support for a Go native library because they didn’t want to install a C compilers as well as a Go compiler. I tried a few different sqlite ports (though off hand cannot recall which ones) and they all had massive limitations, like only compiling on Windows and Linux (in one example). In the end, I gave up and reverted back to the cgo version with the option for other libraries hidden behind a compiler flag. I found my build pipeline manages just fine with cross compiling and haven’t had any complaints (thus far) with the binaries bar one individual running an ancient version of CentOS. Maybe I’ve been trying the wrong sqlite ports. But here lies the problem: with cgo I know I’m getting a stable, tested, library. With other ports it’s entirely a lottery with regards to how well maintained and tested it might be. For personal projects that’s a fine risk to take but for any larger open source (or even commercial) projects, that additional uncertainty is a risk that distracts me and the other contributors from working on the core part of the project. And thus defeats the connivance of using 3rd party libraries.
    - ncruces 3y ago
      Can you say more, like name your project? I'm building https://github.com/ncruces/go-sqlite3 https://github.com/ncruces/go-sqlite3 and am a community maintainer of https://wazero.io https://wazero.io I can cross compile SQLite into all platforms that Go OOB compiles too, with the caveat that any that aren't linux/windows/darwin/freebsd/illumos (CPU architecture doesn't matter) need a build flag because of file locking: https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.md#portability https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.m... Anything that helps me test portability, or any feedback you might have on it, would be greatly appreciated.
parhamn 3y ago
> Just the usual cgo performance overhead? No, the performance is certainly orders of magnitude faster than serializing over std streams on a subprocess (c ffi calls in cgo are 10s of nanoseconds). But one of the big draws of golang is the write-once-compile-anywhere toolchain and calls cgo makes that harder.
- ComputerGuru 3y ago
  But compiling the non-go assistant process is not going to be any easier than cgo, right?
  - codetrotter 3y ago
    In the mentioned https://gitlab.com/cznic/sqlite https://gitlab.com/cznic/sqlite there would not be any assistant process, right?
    - jerf 3y ago
      No, but that has the disadvantage of being C compiled into Go, then being compiled into native executable. I'm actually surprised by how readable this came out; props to the Go->C compiler author. But you can guess that pushing this sort of thing through the Go compiler is going to cause some slowdowns due to sheer paradigm mismatch: https://gitlab.com/cznic/sqlite/-/blob/master/lib/sqlite_linux_riscv64.go?ref_type=heads#L13192 https://gitlab.com/cznic/sqlite/-/blob/master/lib/sqlite_lin...
      - throwaway894345 3y ago
        I don’t think the paradigm is particularly mismatched, right? If you translate C to Go, it would be pretty much best case for Go (neither language likes lots of small allocations). But Go lacks a lot of optimizations that most C compilers support, like return value optimization, aggressive inlining, etc. C also has lighter-weight function calls and so on that you pay for in Go. Maybe this is what you mean by paradigm mismatch, but usually I would think of something like translating an allocation-heavy Java app into Go as paradigm mismatch.
cvilsmeier 3y ago
Sqinn author here. Nothing against CGO, but I develop/deploy on Win/Linux, and cross-compiling CGO is very painful. Regarding performance: To my own surprise, Sqinn out-performs mattn (and others) for normal workloads, see https://github.com/cvilsmeier/sqinn-go-bench https://github.com/cvilsmeier/sqinn-go-bench
- eatonphil 3y ago
  I think it's a somewhat unfair (though who cares if it's unfair) comparison because you aren't using the database/sql interface and mattn does. If you drop that interface, you get much better performance. See: https://github.com/eatonphil/gosqlite https://github.com/eatonphil/gosqlite for example. Edit: Nevermind, you did include crawshaw (which doesn't use database/sql) in your benchmark!

llimllib 3y ago

the other pain point I know of is that it's hard to cross-compile. You can do it with zig[1], but it's still not pleasant. 1: https://zig.news/kristoff/building-sqlite-with-cgo-for-every-os-4cic https://zig.news/kristoff/building-sqlite-with-cgo-for-every...

tptacek 3y ago
Or with musl-cross: https://github.com/FiloSottile/homebrew-musl-cross https://github.com/FiloSottile/homebrew-musl-cross It works pretty well! It's a thing you might keep in your back pocket to test builds from your ARM dev machine on a dev host, and then let the CI/CD system build the real version later.
arp242 3y ago
And Zig only covers a pretty limited range of platforms to start with.

infogulch 3y ago

mattn's sqlite3 is probably the most ideal use case for cgo imaginable. But it can still be annoying to set up cgo to build across many platforms. I think Go should just pull a Zig and just embed a full blown C compiler into go build.

scosman 3y ago
Exactly. Good to know I didn’t miss a trade off in the mix. If I’m choosing to use a C framework (SQLite) I’m okay signing up for the environment costs. Prefer that over abstractions in an intermediate layer that might not be maintained in a few years.
- scosman 3y ago
  Just for the record, had to compile sqlite in gomobile literally the day after this comment and it was a big pain . But got it working and sticking with this approach.
kunley 3y ago
Yeah, once I was on windows and couldn't get cgo sqlite working on a variety of mingw and alike compilers... felt like 90s. Using this cznic's pure-Go sqlite saved the day.
pram 3y ago
Didn't it? Isn't that what 5c, 6c, and 8c were.
oefrha 3y ago
Pulling a zig isn't going to solve all problems, as the zig cross compiler itself runs into problems fairly often. I already use CC='zig cc -target x86_64-linux-musl' (or whatever the target is) with cgo to cross-compile mattn/go-sqlite3, and relatively recently it simply stopped working[1] and a workaround had to do be added to about every single project of mine using SQLite through cgo. I also once tried to figure out a way to cross compile mattn/go-sqlite3 to 32bit Windows with zig, and failed. The best way to make cross compiling painless is to not use cgo at all. Which is why I use modernc/sqlite whenever possible now. Btw, bundling a C compiler is also much harder when you don't build on top of LLVM. [1] https://github.com/mattn/go-sqlite3/issues/1164 https://github.com/mattn/go-sqlite3/issues/1164
- hnlmorg 3y ago
  I’m going to add a counter argument to all the cgo views raised: Personally I’ve had much more portability problems with a lot of the native Go ports of sqlite than I have with mattn’s cgo library. I author a shell that targets most of the architectures and platforms supported by Go. At the request of some users, I added support for a Go native library because they didn’t want to install a C compilers as well as a Go compiler. I tried a few different sqlite ports (though off hand cannot recall which ones) and they all had massive limitations, like only compiling on Windows and Linux (in one example). In the end, I gave up and reverted back to the cgo version with the option for other libraries hidden behind a compiler flag. I found my build pipeline manages just fine with cross compiling and haven’t had any complaints (thus far) with the binaries bar one individual running an ancient version of CentOS. Maybe I’ve been trying the wrong sqlite ports. But here lies the problem: with cgo I know I’m getting a stable, tested, library. With other ports it’s entirely a lottery with regards to how well maintained and tested it might be. For personal projects that’s a fine risk to take but for any larger open source (or even commercial) projects, that additional uncertainty is a risk that distracts me and the other contributors from working on the core part of the project. And thus defeats the connivance of using 3rd party libraries.
  - ncruces 3y ago
    Can you say more, like name your project? I'm building https://github.com/ncruces/go-sqlite3 https://github.com/ncruces/go-sqlite3 and am a community maintainer of https://wazero.io https://wazero.io I can cross compile SQLite into all platforms that Go OOB compiles too, with the caveat that any that aren't linux/windows/darwin/freebsd/illumos (CPU architecture doesn't matter) need a build flag because of file locking: https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.md#portability https://github.com/ncruces/go-sqlite3/blob/main/vfs/README.m... Anything that helps me test portability, or any feedback you might have on it, would be greatly appreciated.

parhamn 3y ago

> Just the usual cgo performance overhead? No, the performance is certainly orders of magnitude faster than serializing over std streams on a subprocess (c ffi calls in cgo are 10s of nanoseconds). But one of the big draws of golang is the write-once-compile-anywhere toolchain and calls cgo makes that harder.

ComputerGuru 3y ago
But compiling the non-go assistant process is not going to be any easier than cgo, right?
- codetrotter 3y ago
  In the mentioned https://gitlab.com/cznic/sqlite https://gitlab.com/cznic/sqlite there would not be any assistant process, right?
  - jerf 3y ago
    No, but that has the disadvantage of being C compiled into Go, then being compiled into native executable. I'm actually surprised by how readable this came out; props to the Go->C compiler author. But you can guess that pushing this sort of thing through the Go compiler is going to cause some slowdowns due to sheer paradigm mismatch: https://gitlab.com/cznic/sqlite/-/blob/master/lib/sqlite_linux_riscv64.go?ref_type=heads#L13192 https://gitlab.com/cznic/sqlite/-/blob/master/lib/sqlite_lin...
    - throwaway894345 3y ago
      I don’t think the paradigm is particularly mismatched, right? If you translate C to Go, it would be pretty much best case for Go (neither language likes lots of small allocations). But Go lacks a lot of optimizations that most C compilers support, like return value optimization, aggressive inlining, etc. C also has lighter-weight function calls and so on that you pay for in Go. Maybe this is what you mean by paradigm mismatch, but usually I would think of something like translating an allocation-heavy Java app into Go as paradigm mismatch.

cvilsmeier 3y ago

Sqinn author here. Nothing against CGO, but I develop/deploy on Win/Linux, and cross-compiling CGO is very painful. Regarding performance: To my own surprise, Sqinn out-performs mattn (and others) for normal workloads, see https://github.com/cvilsmeier/sqinn-go-bench https://github.com/cvilsmeier/sqinn-go-bench

eatonphil 3y ago
I think it's a somewhat unfair (though who cares if it's unfair) comparison because you aren't using the database/sql interface and mattn does. If you drop that interface, you get much better performance. See: https://github.com/eatonphil/gosqlite https://github.com/eatonphil/gosqlite for example. Edit: Nevermind, you did include crawshaw (which doesn't use database/sql) in your benchmark!