4 ms·
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 trans
by mappu 4y ago
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.
- markusw 4y agoWhat’s the advantage of the mutexed rw connection compared to the busy timeout and built-in locking?
- mappu 4y agoThe 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 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.
- markusw 4y agoOoooh, I didn’t know that detail. Thank you for sharing!
- LVB 4y agoVery 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.
- philosopher1234 4y agowould love to see the results of that, if you decide to do this
- markusw 4y agoI did a small benchmark on some trivial code: https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50fff6e14cc26f5eb 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
- markusw 4y agoMy mistake, this isn’t parallel. See https://news.ycombinator.com/item?id=33911491 https://news.ycombinator.com/item?id=33911491 for a parallel version with no readers.
- LVB 4y agoI 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
- mappu 4y ago> 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 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,
- deleted 4y ago[deleted]