Hacker News Reader

Comment by LVB | Hacker News Reader

This is a good article that neatly covers a lot of the tips I've seen spread across various posts, talks, etc. Thanks!

I'm curious what experience you or others have dealing specifically with the write concurrency elements of this setup, should you find it is actually an issue. I've occasionally seen mention of restructuring things to queue writes within the app (e.g. having a single writer goroutine fed with a channel), but I be interested in more details about when folks hit the point of needing to do that, and what they did (and did it help?)

View on HN

If you turn on WAL mode SQLite will queue the writes for you, and since most writes complete in under 1ms you'll likely find that this isn't really worth worrying about at all.

I did some trivial benchmarking around this recently: https://simonwillison.net/2022/Oct/23/datasette-gunicorn/

WAL mode does have some slight decrease in durability by default. If you pull the power immediately after a commit the commit may be reverted when you come back up.

But yeah most of the time it isn't an issue.

This is news to me. What’s your source for that?

AFAIK, SQLite is fully and completely ACID also in WAL mode.

https://www.sqlite.org/pragma.html#pragma_synchronous

When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode.

Interesting, thanks! I didn’t know this was a configurable tradeoff. Might update the article with a little bonus info.

Go + SQLite user at $DAYJOB here. WAL is essential, and the next essential trick is to open a single db.Conn up-front and hide that behind a mutex for any transactions/queries that will write. All callers can pick this.db_rw or this.db_ro as required.

What’s the advantage of the mutexed rw connection compared to the busy timeout and built-in locking?

The built-in one actually sleeps and polls to check if the write lock is free again. It's a disaster for high write throughput.

https://sqlite.org/forum/info/00312b3d02bc0583

Sqlite's internal one has to work this way to support their multi-process guarantees. In comparison, an in-process Go mutex will be readied immediately.

Ooooh, I didn’t know that detail. Thank you for sharing!

Very interesting. I'm now keen to write a very simple benchmark comparing the results of N goroutines leaning on the WAL lock vs in-app serialization.

would love to see the results of that, if you decide to do this

I did a small benchmark on some trivial code: https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50ff...

Results vary a bit on my machine, but it's pretty much the same (unless I'm doing something wrong, please point it out if so):

  $ make benchmark
  go test -bench=.
  goos: darwin
  goarch: arm64
  pkg: sqlite
  BenchmarkWriteBlogPost/write_blog_post_without_WAL-8              6036     186719 ns/op
  BenchmarkWriteBlogPost/write_blog_post_with_WAL-8               102567      11169 ns/op
  BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8            104228      11226 ns/op
  PASS
  ok   sqlite 3.833s

My mistake, this isn’t parallel. See https://news.ycombinator.com/item?id=33911491 for a parallel version with no readers.

I did a small benchmark and found for high concurrency write-only loads, the results of each goroutine writing directly vs using a mutex were basically the same. But things were more interesting when I added a bunch of read goroutines to the mix too (which would be more realistic for e.g. web traffic). In that case, the mutex-protected writes were drastically reduced in favor of reads. And while I have no idea how SQlite is actually handling its locking, it makes intuitive sense that if it doesn't even know about more write load it isn't going to schedule it.

I'm going to clean up the code and post some results in gist, but here is a sample:

  Write-only (SQL statements/s):

  direct:  45314
  w/mutex: 45304


  Read/Write mix:

  direct:  read: 85229   write: 22664
  w/mutex: read: 139053  write: 1382

> I did a small benchmark and found for high concurrency write-only loads, the results of each goroutine writing directly vs using a mutex were basically the same.

I'm interested to see your gist, but just to note this specific part seems to contradict https://news.ycombinator.com/item?id=33911491 , where at 64-wide parallelism for a write-only load, the Go mutex outperforms plain WAL by a factor of ~70x,

That revised version is indeed interesting. I hope to get things pushed tonight. My approach managed goroutines directly and didn't use the Benchmark parallelism feature. I don't see any obvious reason why that matters but structurally that's a bit different

Pretty interesting results by adding SetMaxOpenConns() to Marcus' gist:

https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7...

Finally got around to this: https://github.com/kalafut/go-sqlite-bench

Thank you for the kind words! :)

The built-in busy timeout basically takes care of write queueing, so a standard setup like this will take you very, very far.

Reply on news.ycombinator.com