golang and sqlite

a_sqlite.go

package main

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"fmt"
	"path"
	"sync"
	"sync/atomic"
	"testing"

	mattnSqlite "github.com/mattn/go-sqlite3"     // with cgo
	tailscaleSqlite "github.com/tailscale/sqlite" // with cgo
	moderncSqlite "modernc.org/sqlite"            // cgo free

	"github.com/google/uuid"
	ongId "github.com/komuw/ong/id"
)

// Also see: https://github.com/benbjohnson/sqlite-bench
// really Good: https://kerkour.com/sqlite-for-servers
//
// Sqlite index automatic recommendations: https://sqlite.org/cli.html#index_recommendations_sqlite_expert_


/*
run:
  goimports -w .;gofumpt -extra -w .;gofmt -w -s .;go mod tidy;go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -race ./...
  go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -timeout 1m -race -run=XXXX -bench=. ./...
  go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -timeout 1m -race -run=XXXX -bench=. -cpu 1,2,4,16 ./... # set different parallelism(list of GOMAXPROCS values for which the tests/benchmarks should be executed.)
  go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -timeout 1m -run=XXXX -bench=. -cpu 1,2,4,16 ./... # without RACE detector.
  # mattnSqlite & tailscaleSqlite share some C definitions. Hence we need to allow multiple of them.
  # The two libraries also have `init` funcs that try and register a driver with same name.
  # Luckily, mattnSqlite offers a way to override it using ldflags.
  # https://stackoverflow.com/a/64796847/2768067
  go test \
    --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' \
	-timeout 1m \
	-run=XXXX \
	-bench=. \
	-cpu 1,2,4,16 ./... # without RACE detector.
*/

/////////////////////////////////////////////// app ///////////////////////////////////////////////

const (
	mattnDriverName     = "mattnSqlite3"
	moderncDriverName   = "moderncSqlite3"
	tailscaleDriverName = "tailscaleSqlite3"
)

var (
	driverRegOnce sync.Once

	listOfStringIds = map[string][]string{
		"ongId":           {},
		"ongUuid4Text":    {},
		"ongUuid8Text":    {},
		"googleUuid4Text": {},
	}

	listOfByteIds = map[string][][]byte{
		"ongUuid4Blob":    {},
		"ongUuid8Blob":    {},
		"googleUuid4Blob": {},
	}
)

func init() {
	{ // driver registration.
		driverRegOnce.Do(func() {
			sql.Register(mattnDriverName, &mattnSqlite.SQLiteDriver{})
			sql.Register(moderncDriverName, &moderncSqlite.Driver{})
			{
				connInitFunc := func(ctx context.Context, conn driver.ConnPrepareContext) error {
					return nil
				}
				connector := tailscaleSqlite.Connector("/tmp/some-dummy-doesnt-matter.db", connInitFunc, nil)
				sql.Register(tailscaleDriverName, connector.Driver())
			}
		})
	}
	{ // init IDs
		for i := 0; i < 500_000; i++ {
			listOfStringIds["ongId"] = append(listOfStringIds["ongId"], ongId.New())
			listOfStringIds["ongUuid4Text"] = append(listOfStringIds["ongUuid4Text"], ongId.UUID4().String())
			listOfStringIds["ongUuid8Text"] = append(listOfStringIds["ongUuid8Text"], ongId.UUID8().String())
			listOfStringIds["googleUuid4Text"] = append(listOfStringIds["googleUuid4Text"], uuid.NewString())

			listOfByteIds["ongUuid4Blob"] = append(listOfByteIds["ongUuid4Blob"], ongId.UUID4().Bytes())
			listOfByteIds["ongUuid8Blob"] = append(listOfByteIds["ongUuid8Blob"], ongId.UUID8().Bytes())
			xxx := uuid.New()
			listOfByteIds["googleUuid4Blob"] = append(listOfByteIds["googleUuid4Blob"], xxx[:])
		}
	}
}

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
}

/////////////////////////////////////////////// app ///////////////////////////////////////////////

/////////////////////////////////////////////// tests ///////////////////////////////////////////////

func BenchmarkWriteBlog(b *testing.B) {
	b.ReportAllocs()

	for _, driver := range [3]string{mattnDriverName, moderncDriverName, tailscaleDriverName} {

		b.Run(fmt.Sprintf("%s - write blog post with WAL", driver), func(b *testing.B) {
			db := setupDB(b, driver)
			if driver == moderncDriverName || driver == tailscaleDriverName {
				// not setting this at all causes the modernc version to immediately fail with "DB locked"
				db.SetMaxOpenConns(1)
			}

			var count int32 = 1
			b.ResetTimer()
			b.RunParallel(func(pb *testing.PB) {
				for pb.Next() {
					// The loop body is executed b.N times total across all goroutines.
					if err := WriteBlogPost(db, "Some title", "Some content"); err != nil {
						b.Fatal(err)
					}
					atomic.AddInt32(&count, 1)
				}
			})
			b.ReportMetric(float64(count), "writes/s")
		})

		b.Run(fmt.Sprintf("%s - write blog post with WAL and Go mutex", driver), func(b *testing.B) {
			db := setupDB(b, driver)
			if driver == moderncDriverName || driver == tailscaleDriverName {
				// not setting this at all causes the modernc version to immediately fail with "DB locked"
				db.SetMaxOpenConns(1)
			}

			var count int32 = 1
			b.ResetTimer()
			b.RunParallel(func(pb *testing.PB) {
				for pb.Next() {
					if err := WriteBlogPostMutexed(db, "Some title", "Some content"); err != nil {
						b.Fatal(err)
					}
					atomic.AddInt32(&count, 1)
				}
			})
			b.ReportMetric(float64(count), "writes/s")
		})
	}
}

// https://kerkour.com/sqlite-for-servers
func setupDB(b *testing.B, drivername string) *sql.DB {
	// really Good: https://kerkour.com/sqlite-for-servers
	//
	dbPath := path.Join(b.TempDir(), "benchmark.db")
	db, err := sql.Open(drivername, dbPath)
	if err != nil {
		b.Fatal(err)
	}

	{ // pragmas:
		if _, err := db.Exec("PRAGMA journal_mode = WAL;"); err != nil {
			b.Fatal(err)
		}

		// busy_timeout` pragma in milliseconds: https://www.sqlite.org/pragma.html#pragma_busy_timeout
		if _, err := db.Exec("PRAGMA busy_timeout = 1000;"); err != nil {
			b.Fatal(err)
		}

		if _, err := db.Exec("PRAGMA foreign_keys = ON;"); err != nil {
			b.Fatal(err)
		}
	}

	{
		_, 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'))
		) STRICT;`)
		if err != nil {
			b.Fatal(err)
		}
	}
	return db
}

// BenchmarkIds benchmarks using different ID's.
func BenchmarkIds(b *testing.B) {
	// go test --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' -timeout 1m -race -run=XXXX -bench=BenchmarkIds ./...

	b.ReportAllocs()

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

		{ // pragmas:
			if _, err := db.Exec("PRAGMA journal_mode = WAL;"); err != nil {
				b.Fatal(err)
			}

			// busy_timeout` pragma in milliseconds: https://www.sqlite.org/pragma.html#pragma_busy_timeout
			if _, err := db.Exec("PRAGMA busy_timeout = 1000;"); err != nil {
				b.Fatal(err)
			}

			if _, err := db.Exec("PRAGMA foreign_keys = ON;"); err != nil {
				b.Fatal(err)
			}
		}

		{
			switch idType {
			default:
				b.Fatalf("id type `%v` is not known", idType)
			case "integer":
				_, err := db.Exec(`
				CREATE TABLE posts (
					id integer primary key,
					title text not null
				) STRICT;`)
				if err != nil {
					b.Fatal(err)
				}
			case "ongId", "ongUuid4Text", "ongUuid8Text", "googleUuid4Text":
				_, err := db.Exec(`
				CREATE TABLE posts (
					id text primary key,
					title text not null
				) STRICT, WITHOUT ROWID;`)
				if err != nil {
					b.Fatal(err)
				}

			case "ongUuid4Blob", "ongUuid8Blob", "googleUuid4Blob":
				_, err := db.Exec(`
				CREATE TABLE posts (
					id blob primary key,
					title text not null
				) STRICT, WITHOUT ROWID;`)
				if err != nil {
					b.Fatal(err)
				}
			}
		}

		return db
	}

	writeString := func(db *sql.DB, idType, idValue string) {
		switch idType {
		default:
			b.Fatalf("id type `%v` is not known", idType)
		case "integer":
			_, err := db.Exec(`insert into posts (title) values (?)`, "hello World")
			if err != nil {
				b.Fatal(err)
			}
		case "ongId", "ongUuid4Text", "ongUuid8Text", "googleUuid4Text":
			_, err := db.Exec(`insert into posts (id, title) values (?, ?)`, idValue, "hello World")
			if err != nil {
				b.Fatal(err)
			}
		}
	}

	writeBytes := func(db *sql.DB, idType string, idValue []byte) {
		switch idType {
		default:
			b.Fatalf("id type `%v` is not known", idType)
		case "ongUuid4Blob", "ongUuid8Blob", "googleUuid4Blob":
			_, err := db.Exec(`insert into posts (id, title) values (?, ?)`, idValue, "hello World")
			if err != nil {
				b.Fatal(err)
			}
		}
	}

	k := "integer"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, "")
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongId"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, listOfStringIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongUuid4Text"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, listOfStringIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongUuid8Text"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, listOfStringIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "googleUuid4Text"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeString(db, k, listOfStringIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongUuid4Blob"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeBytes(db, k, listOfByteIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "ongUuid8Blob"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeBytes(db, k, listOfByteIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})

	k = "googleUuid4Blob"
	b.Run(fmt.Sprintf("%s - BenchmarkIds", k), func(b *testing.B) {
		db := setup(b, k)
		var count int32 = 0
		b.ReportAllocs()
		b.ResetTimer()
		for n := 0; n < b.N; n++ {
			writeBytes(db, k, listOfByteIds[k][n])
			atomic.AddInt32(&count, 1)
		}
		b.ReportMetric(float64(count), "writes/s")
	})
}

/////////////////////////////////////////////// tests ///////////////////////////////////////////////

sqlite-and-Golang-Dockerfile

FROM golang:1.19.4-bullseye AS builder

# docker build -t benchbin .
#
# docker \
#     run \
#     -it \
#     --memory-reservation=80m \
#     --memory=120m \
#     --memory-swap=125m \
#     --cpu-quota=25000 \
#     --cpu-period=50000 \
#     benchbin:latest
#
# Restrict memory to a max of 125MB.
# Restrict cpu to 50% run-time every 50_000 microsec(50 millisec)


WORKDIR /app

COPY go.mod ./
COPY go.sum ./
RUN go mod download

COPY *.go ./

RUN go test \
    --ldflags '-extldflags "-Wl,--allow-multiple-definition" -X "github.com/mattn/go-sqlite3.driverName=my-sqlite3"' \
    -timeout 2m \
    -run=XXXX \
    -bench=. \
    -cpu 1,2,4,16 \
    -c \
    -o benchbin.test \
    ./...

CMD ["./benchbin.test", "-test.run=XXXX", "-test.bench=.", "-test.cpu=1,2,4,16"]

_with_WAL & with_WAL_and_Go_mutex basically perform the same.

Even with the busy_timeout increased from 1 second to 120 seconds, the modernc.org/sqlite driver still errors with: main_test.go:85: database is locked (5) (SQLITE_BUSY)

fixed by adding db.SetMaxOpenConns(1)

@komuw in my understanding, you basically restrict both reads and writes to one at a time when using db.SetMaxOpenConns(1).