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
}

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.