simple parallel query throughuput benchmarking of SQLite3 with Go and go-sqlite3 lib

README.md

simple benchmarking SQLite3 with Go and go-sqlite3 lib

licence of materials on this gist are MIT license
Copylight 2023 Ryo Kanbayashi

go.mod

module sqlite-bench-with-golang

go 1.21

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

go.sum

github.com/mattn/go-sqlite3 v1.14.17 h1:mCRHCLDUBXgpKAqIKsaAaAsrAlbkeomtRFKXh2L6YIM=
github.com/mattn/go-sqlite3 v1.14.17/go.mod h1:2eHXhiwb8IkHr+BDWZGa96P6+rkvnG63S2DGjv9HUNg=

sqlite.go

package sqlite

import (
	"database/sql"
	_ "embed"
	"errors"
)

type DB struct {
	DB *sql.DB
}

func NewDB(db *sql.DB) *DB {
	return &DB{db}
}

func (d *DB) InsertRecord(k int, v int) error {
	_, err := d.DB.Exec(`insert into k_v_list (k, v) values ($1, $2)`, k, v)
	return err
}

type KV struct {
	ID int
	K  int
	V  int
}

func (d *DB) SelectVal(id int) (p *KV, err error) {
	p = &KV{K: id}

	row := d.DB.QueryRow(`select v from k_v_list where k = $1`, id)
	if err = row.Scan(&p.V); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			p = nil
		}
		return
	}

	return
}

sqlite.sql

create table k_v_list (
    id primary key,
    k integer,
    v integer
);

create index k_idx on k_v_list (k);
create index v_idx on k_v_list (v);

sqlite_test.go

package sqlite_test

import (
	"database/sql"
	_ "embed"
	"fmt"
	"math/rand"
	"path"
	"runtime"
	sqlite "sqlite-bench-with-golang"
	"strconv"
	"testing"
	"time"

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

func TestSQLiteBench(t *testing.T) {
	opTimes := 10000

	queryVals := make([]int, 0)

	for ii := 0; ii < opTimes; ii++ {
		randVal := rand.Int31()
		queryVals = append(queryVals, int(randVal))
	}

	writeDB, readDB := setupSQLitePool(t)

	insCh := make(chan int)
	for ii := 0; ii < opTimes; ii++ {
		go func(val int) {
			err := writeDB.InsertRecord(val, val)
			if err != nil {
				panic(err)
			}
			insCh <- val
		}(queryVals[ii])
	}
	for ii := 0; ii < opTimes; ii++ {
		<-insCh
	}

	// shuffle query vals array elements
	rand.Shuffle(len(queryVals), func(i, j int) { queryVals[i], queryVals[j] = queryVals[j], queryVals[i] })

	fmt.Println("records insertion done.")

	THREAD_NUM := 24
	runtime.GOMAXPROCS(THREAD_NUM)

	ch := make(chan [2]int)

	runningThCnt := 0
	allCnt := 0
	commitedCnt := 0
	abotedCnt := 0

	startTime := time.Now()
	for ii := 0; ii < opTimes; ii++ {
		// wait last go routines finishes
		if ii == opTimes-1 {
			for runningThCnt > 0 {
				recvRslt := <-ch
				allCnt++
				if recvRslt[1] == -1 {
					abotedCnt++
				} else {
					commitedCnt++
					if recvRslt[0] != recvRslt[1] {
						panic("failed to select val: " + strconv.Itoa(int(recvRslt[0])))
					}
				}
				runningThCnt--
			}
			break
		}

		// wait for keeping THREAD_NUM * 2 groroutine existing
		for runningThCnt >= THREAD_NUM*2 {
			recvRslt := <-ch
			runningThCnt--
			allCnt++
			if allCnt%500 == 0 {
				fmt.Printf(strconv.Itoa(allCnt) + " queries done\n")
			}
			if recvRslt[1] == -1 {
				abotedCnt++
			} else {
				commitedCnt++
				if recvRslt[0] != recvRslt[1] {
					panic("failed to select val: " + strconv.Itoa(int(recvRslt[0])))
				}
			}
		}

		go func(queryVal int) {
			kv, err_ := readDB.SelectVal(queryVal)
			if err_ != nil {
				fmt.Println(err_)
				ch <- [2]int{queryVal, -1}
				return
			}

			gotValue := kv.V
			ch <- [2]int{queryVal, gotValue}
		}(queryVals[ii])

		runningThCnt++
	}

	fmt.Println("allCnt: " + strconv.Itoa(allCnt))
	fmt.Println("abotedCnt: " + strconv.Itoa(abotedCnt))
	fmt.Println("commitedCnt: " + strconv.Itoa(commitedCnt))
	d := time.Since(startTime)
	fmt.Printf("%f qps: elapsed %f sec\n", float32(opTimes)/float32(d.Seconds()), d.Seconds())
}

//go:embed sqlite.sql
var sqliteSchema string

func setupSQLitePool(t *testing.T) (*sqlite.DB, *sqlite.DB) {
	t.Helper()

	p := path.Join(t.TempDir(), "benchmark.db")
	fmt.Println(p)

	writeDB, err := sql.Open("sqlite3", p+"?_journal=WAL&_timeout=10000&_fk=true&_cache_size=500")
	printErrIfNotNil(t, err)

	writeDB.SetMaxOpenConns(1)

	_, err = writeDB.Exec(sqliteSchema)
	printErrIfNotNil(t, err)

	newWriteDB := sqlite.NewDB(writeDB)

	readDB, err := sql.Open("sqlite3", p+"?_journal=WAL&_timeout=10000&_fk=true&_cache_size=500")
	printErrIfNotNil(t, err)

	newReadDB := sqlite.NewDB(readDB)

	return newWriteDB, newReadDB
}

func printErrIfNotNil(t *testing.T, err error) {
	if err != nil {
		t.Fatal("Error is not nil:", err)
	}
}