# Exploring Go with CGO Overhead and sqlc
In a recent endeavor, I’ve been tinkering with building a server in Golang. It’s built around a low-level TCP protocol, each connection sparking its goroutine. What makes this interesting is using cgo to interface with a library. As many Golang fans might know, each cgo call incurs a time penalty due to the transition from C to Go. This bridge had me curious - has this ever been a performance bottleneck for others?
To paint a clearer picture, I stumbled upon
Shane’s article discussing
the performance of CGO in Go 1.21. A quick synopsis: cgo calls roughly take
about 40ns – on par with the time encoding/json spends parsing a single-digit
integer. This performance scales with a core count of about 16 cores, after
which contention issues slow down the process. It’s worth noting that while
Shane suggests cgo performance might be satisfactory, it’s not a universal
endorsement of using cgo. A 20-core machine was his testing playground, after
all.
Jokes about nanosecond optimizations aside, I considered moving my inner loop to Rust or C. If the cgo boundary is crossed frequently, it’s worth contemplating the potential performance gains. Plus, learning Rust has been on my bucket list.
Another intriguing aspect of my journey was discovering that the library I’m interfacing with might involve more than just one cgo call. Dive deeper? Well, that’s on my to-do list.
I also dabbled with the Go runtime settings in the name of optimization. I
naively considered setting GOMAXPROCS=1 after removing a mutex, expecting Go’s
runtime to ensure sequential operation. To my surprise, putting it to
GOMAXPROCS=2 performed better. But, a word of caution: trusting the runtime as
its mutex beyond this point led to a drop in performance.
Integrating SQLite through the CGO library added another layer of depth to my experiment. Here’s the gist of my learnings:
- Using
db/sql, you can open multiple database connections using a mutex. - SQLite has distinct methods to lock files, transactions, and operations (using mutex).
- Setting the max open connections to 1 and modifying SQLite settings can boost performance by 2-3 times. However, you can’t simultaneously access the database file with CLI SQLite3.
And for those curious about the code, feel free to dive into sqlettus on GitHub.
As the project evolved, my vision crystallized. The aim? Building something compatible with the Redis protocol but backed by databases. Why? With its transaction capabilities and established protocols, SQL is the perfect match. Besides, being able to inspect your database through both SQL and the Redis protocol has its perks.
Using sqlc in the Golang project was beneficial. Its purpose is to remove the
need for boilerplate code when writing, executing, and returning the values of
SQL queries. It was great until it wasn’t.
It supports Postgres, MySQL, and SQLite databases. It seems, however, to support
Postgres features. I’ve been using SQLite for the low barrier to entry, and it
supports advanced JSON features. sqlc did not like the usage of json_each,
json_value, etc. in some places, however.
My only workaround for more complex queries was to write the boilerplate code I was trying to avoid.
Here’s a sneak peek into my progress:
- Built a router to handle TCP directives.
- Emphasized handling tasks at the SQL level, harnessing
sqlcto cut down on boilerplate (unearthed a few bugs in the process!). - Continual refactoring to make adding directives a breeze.
- Hunting for a consistent pattern for various database backends.
As a bonus, I’ve received some great suggestions, like renaming it to
sqlettuce. And yes, spelling can be a challenging game!