Go SQLite benchmark parallel

go.mod

module sqlite

go 1.19

require github.com/mattn/go-sqlite3 v1.14.16

go.sum

github.com/mattn/go-sqlite3 v1.14.16 h1:yOQRA0RpS5PFz/oikGwBEqvAWhWg5ufRz4ETLjwpU1Y=
github.com/mattn/go-sqlite3 v1.14.16/go.mod h1:2eHXhiwb8IkHr+BDWZGa96P6+rkvnG63S2DGjv9HUNg=

Makefile

.PHONY: benchmark
benchmark:
	go test -bench=.

sqlite.go

package sqlite

import (
	"database/sql"
	"sync"
)

func Setup(db *sql.DB) error {
	_, err := db.Exec(`
			create table posts (
				id integer primary key,
				title text not null,
				content text not null,
				created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ'))
			)`)
	return err
}

func WriteBlogPost(db *sql.DB, title, content string) error {
	_, err := db.Exec(`insert into posts (title, content) values (?, ?)`, title, content)
	return err
}

var m sync.Mutex

func WriteBlogPostMutexed(db *sql.DB, title, content string) error {
	m.Lock()
	defer m.Unlock()
	_, err := db.Exec(`insert into posts (title, content) values (?, ?)`, title, content)
	return err
}

sqlite_test.go

package sqlite_test

import (
	"database/sql"
	"path"
	"testing"

	_ "github.com/mattn/go-sqlite3"

	"sqlite"
)

func BenchmarkWriteBlogPost(b *testing.B) {
	b.Run("write blog post without WAL", func(b *testing.B) {
		db := setupDB(b, "?_timeout=5000&_fk=true")

		b.SetParallelism(64)

		b.RunParallel(func(pb *testing.PB) {
			for pb.Next() {
				if err := sqlite.WriteBlogPost(db, "Some title", "Some content"); err != nil {
					b.Fatal(err)
				}
			}
		})
	})

	b.Run("write blog post with WAL", func(b *testing.B) {
		db := setupDB(b, "?_journal=WAL&_timeout=5000&_fk=true")

		b.SetParallelism(64)

		b.RunParallel(func(pb *testing.PB) {
			for pb.Next() {
				if err := sqlite.WriteBlogPost(db, "Some title", "Some content"); err != nil {
					b.Fatal(err)
				}
			}
		})
	})

	b.Run("write blog post with WAL and Go mutex", func(b *testing.B) {
		db := setupDB(b, "?_journal=WAL&_timeout=5000&_fk=true")

		b.SetParallelism(64)

		b.RunParallel(func(pb *testing.PB) {
			for pb.Next() {
				if err := sqlite.WriteBlogPostMutexed(db, "Some title", "Some content"); err != nil {
					b.Fatal(err)
				}
			}
		})
	})
}

func setupDB(b *testing.B, options string) *sql.DB {
	dbPath := path.Join(b.TempDir(), "benchmark.db")
	db, err := sql.Open("sqlite3", dbPath+options)
	if err != nil {
		b.Fatal(err)
	}

	if err := sqlite.Setup(db); err != nil {
		b.Fatal(err)
	}
	return db
}

Yeah, but SetMaxOpenConns effectively just makes a mutex for you, right? So it makes sense that the two latter benchmarks would be the same then.

I'm not actually sure what it does under the hood with something like SQLite that doesn't involve network connections. It is one of the many "do this to make it fast" things I just have seen around. But the absolute increase relative to not using it is very striking (and also something I can't explain).

@kalafut I think this is what's happening:

• SetMaxOpenConns sets the connection pool max to 1
• All threads must use this one connection, which is mutexed in Go
• Access to the underlying DB is thus serialized
• It's faster because SQLite uses a polling mechanism for retries with the busy timeout, whereas Go uses a lock

By the way, found a nice way to benchmark with a different number of parallelism set: https://gist.github.com/markuswustenberg/19cbbf8c26aaf778f9ce8aef760ca8af

SetMaxOpenConns effectively just makes a mutex for you, right?

Yes, but the mutex covers both read and write operations. Using a Go-side mutex has the ability to only cover the write operations, allowing for read parallelism.

SetMaxOpenConns effectively just makes a mutex for you, right?

Yes, but the mutex covers both read and write operations. Using a Go-side mutex has the ability to only cover the write operations, allowing for read parallelism.

Yes, I meant for this specific test, which only does writes. I’m fairly convinced by now that having two connection pools, one for ro and one for rw, is a good idea. Is there a db driver that perhaps does this automagically by inspecting the query?

Finally got around to writing something up: https://github.com/kalafut/go-sqlite-bench

I'm happy to explore more settings and drivers.

@kalafut Nice. I've started one myself: https://github.com/maragudk/sqlite-benchmark . Will turn it into a blog post probably next week.

@kalafut I've similarly dropped tests without WAL, and am also considering dropping tests without some sort of mutex. Ben Johnson suggested on Gophers Slack (are you on there?) to try different drivers as well, that's something I'm considering. This is super fun. 😊

Also see: https://github.com/benbjohnson/sqlite-bench

Ben Johnson suggested .. to try different drivers as well,

I tried that at; https://gist.github.com/komuw/7fe652eece1850d2148e2bf3392101b0
I specifically tried with mattn/go-sqlite3 and modernc.org/sqlite. Unfortunately modernc.org/sqlite had too many database is locked errors.
I need to try out tailscale/sqlite. Their interface is a bit different from the other drivers so I might need to re-arrange the code.

edit: I have now included tailscale/sqlite in my gist.