Benchmarking RamSQL
In a previous article, I was taking stock on RamSQL and concluded it was coherent to go for a stable v1 instead of archiving and dropping the entire project.
In the tentative roadmap I wrote, the first point of order was to correctly handling transactions. This would means rewriting the entire project to have everything moving within a transaction instead of shoeing it in.
Since I was breaking everything, I took the opportunity to introduce benchmarks:
bench:
go test -bench=. -count 6 | tee newbench.txt
benchstat bench.txt newbench.txt | tee benchstat.txt
with SQLite in:memory: as a baseline
func BenchmarkRamSQLSelectHashMap10K(b *testing.B) {
db, err := sql.Open("ramsql", "BenchmarkSQLSelectHashMap10K")
if err != nil {
b.Fatalf("cannot open ramsql db")
}
n := 10000
setupInsertN(b, db, n)
benchmarkSelectHashMap(b, db)
}
func BenchmarkSQLiteSelectHashMap10K(b *testing.B) {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
b.Fatalf("cannot open sqlite")
}
n := 10000
setupInsertN(b, db, n)
benchmarkSelectHashMap(b, db)
}
func benchmarkSelectHashMap(b *testing.B, db *sql.DB) {
var id int64
var email string
b.ResetTimer()
for n := 0; n < b.N; n++ {
query := `SELECT account.id, account.email FROM account WHERE id = $1`
rows, err := db.Query(query, n)
if err != nil {
b.Fatalf("cannot query rows: %s", err)
}
for rows.Next() {
err = rows.Scan(&id, &email)
if err != nil {
b.Fatalf("cannot scan rows: %s", err)
}
}
}
_ = id
_ = email
_, err := db.Exec(`DROP TABLE account`)
if err != nil {
b.Fatalf("sql.Exec: %s", err)
}
}
go test output a mean time for each operation:
$ go test -bench=. -count 6 | tee newbench.txt
goos: linux
goarch: amd64
pkg: github.com/proullon/ramsql
cpu: Intel(R) Core(TM) i7-7700K CPU @ 4.20GHz
BenchmarkRamSQLSelect-8 3766 297732 ns/op
BenchmarkRamSQLSelect-8 4114 286406 ns/op
BenchmarkRamSQLSelect-8 3974 287603 ns/op
BenchmarkRamSQLSelect-8 3874 286818 ns/op
BenchmarkRamSQLSelect-8 4036 286082 ns/op
BenchmarkRamSQLSelect-8 3924 285952 ns/op
BenchmarkSQLiteSelect-8 110434 10706 ns/op
BenchmarkSQLiteSelect-8 112434 10805 ns/op
BenchmarkSQLiteSelect-8 99657 11055 ns/op
BenchmarkSQLiteSelect-8 111702 10689 ns/op
BenchmarkSQLiteSelect-8 111669 10756 ns/op
BenchmarkSQLiteSelect-8 111285 10638 ns/op
BenchmarkRamSQLSelect10K-8 160 7384466 ns/op
BenchmarkRamSQLSelect10K-8 162 7938318 ns/op
BenchmarkRamSQLSelect10K-8 160 7456492 ns/op
BenchmarkRamSQLSelect10K-8 159 7502256 ns/op
BenchmarkRamSQLSelect10K-8 164 7540972 ns/op
BenchmarkRamSQLSelect10K-8 144 7451605 ns/op
BenchmarkSQLiteSelect10K-8 109460 10979 ns/op
BenchmarkSQLiteSelect10K-8 105711 11033 ns/op
BenchmarkSQLiteSelect10K-8 104372 11035 ns/op
BenchmarkSQLiteSelect10K-8 107712 11063 ns/op
BenchmarkSQLiteSelect10K-8 107395 11060 ns/op
BenchmarkSQLiteSelect10K-8 108666 11116 ns/op
BenchmarkRamSQLInsert10-8 4424 268468 ns/op
BenchmarkRamSQLInsert10-8 4477 298751 ns/op
BenchmarkRamSQLInsert10-8 4242 280029 ns/op
BenchmarkRamSQLInsert10-8 3828 283994 ns/op
BenchmarkRamSQLInsert10-8 4328 283040 ns/op
BenchmarkRamSQLInsert10-8 4436 276176 ns/op
BenchmarkSQLiteInsert10-8 3459 332308 ns/op
BenchmarkSQLiteInsert10-8 3721 328695 ns/op
BenchmarkSQLiteInsert10-8 3709 330266 ns/op
BenchmarkSQLiteInsert10-8 3670 343643 ns/op
BenchmarkSQLiteInsert10-8 3680 320361 ns/op
BenchmarkSQLiteInsert10-8 3819 325789 ns/op
BenchmarkRamSQLSetup-8 1430150 832.9 ns/op
BenchmarkRamSQLSetup-8 1436343 835.1 ns/op
BenchmarkRamSQLSetup-8 1439770 833.9 ns/op
BenchmarkRamSQLSetup-8 1331280 806.1 ns/op
BenchmarkRamSQLSetup-8 1485778 807.0 ns/op
BenchmarkRamSQLSetup-8 1486251 806.3 ns/op
BenchmarkSQLiteSetup-8 1477396 810.5 ns/op
BenchmarkSQLiteSetup-8 1464448 809.5 ns/op
BenchmarkSQLiteSetup-8 1380595 814.2 ns/op
BenchmarkSQLiteSetup-8 1482067 811.0 ns/op
BenchmarkSQLiteSetup-8 1481173 813.0 ns/op
BenchmarkSQLiteSetup-8 1480530 811.3 ns/op
PASS
ok github.com/proullon/ramsql 87.537s
It’s nice and all but useless alone. I use benchstat to get a diff between current version and the last committed one:
$ benchstat bench.txt newbench.txt | tee benchstat.txt
goos: linux
goarch: amd64
pkg: github.com/proullon/ramsql
cpu: Intel(R) Core(TM) i7-7700K CPU @ 4.20GHz
│ bench.txt │ newbench.txt │
│ sec/op │ sec/op vs base │
RamSQLSelect-8 287.8µ ± 4% 286.6µ ± 4% ~ (p=0.818 n=6)
SQLiteSelect-8 10.98µ ± 11% 10.73µ ± 3% ~ (p=0.065 n=6)
RamSQLSelect10K-8 8.246m ± 5% 7.479m ± 6% -9.30% (p=0.004 n=6)
SQLiteSelect10K-8 11.54µ ± 11% 11.05µ ± 1% -4.28% (p=0.002 n=6)
RamSQLInsert10-8 312.3µ ± 4% 281.5µ ± 6% -9.86% (p=0.002 n=6)
SQLiteInsert10-8 341.5µ ± 1% 329.5µ ± 4% -3.53% (p=0.041 n=6)
RamSQLSetup-8 855.3n ± 4% 820.0n ± 2% ~ (p=0.065 n=6)
SQLiteSetup-8 844.1n ± 2% 811.1n ± 0% -3.90% (p=0.002 n=6)
geomean 46.84µ 44.61µ -4.75%
This is so cool, it’s easy to spot performance variation between last commit (“bench.txt”) and current one (“vs base”).
In the example above, we can see the change gains 10% on SELECT and INSERT. We can also see that RamSQL SELECT is 100x slower than SQLite (7479µs vs 11µs)…
To go further into performance investigation, one can
$ go test -trace=driver.out ./driver
$ go tool trace --pprof=syscall driver.out
$ go tool pprof -traces syscall.pprof
RamSQL version v0.1.3, after refactoring, is much more correct:
│ bench.txt │ newbench.txt │
│ sec/op │ sec/op vs base │
RamSQLSelectBTree-8 49.68µ ± 4% 54.52µ ± 4% +9.73% (p=0.002 n=6)
SQLiteSelectBTree-8 10.92µ ± 5% 11.43µ ± 3% +4.63% (p=0.026 n=6)
RamSQLSelectBTree10K-8 543.6µ ± 2% 682.3µ ± 17% +25.51% (p=0.002 n=6)
SQLiteSelectBTree10K-8 11.20µ ± 2% 12.50µ ± 12% +11.63% (p=0.002 n=6)
RamSQLSelectHashMap10K-8 24.92µ ± 2% 30.73µ ± 7% +23.32% (p=0.002 n=6)
SQLiteSelectHashMap10K-8 8.450µ ± 2% 8.651µ ± 4% ~ (p=0.132 n=6)
RamSQLSelectBTree100K-8 14.00m ± 2% 16.09m ± 11% +14.93% (p=0.002 n=6)
SQLiteSelectBTree100K-8 11.33µ ± 2% 12.70µ ± 7% +12.04% (p=0.002 n=6)
RamSQLSelectHashMap100K-8 26.47µ ± 2% 29.76µ ± 4% +12.42% (p=0.002 n=6)
SQLiteSelectHashMap100K-8 8.581µ ± 4% 9.469µ ± 8% +10.35% (p=0.002 n=6)
RamSQLInsert10-8 177.9µ ± 2% 211.9µ ± 5% +19.13% (p=0.002 n=6)
SQLiteInsert10-8 330.6µ ± 4% 106.7µ ± 4% -67.73% (p=0.002 n=6)
RamSQLSetup-8 834.9n ± 1% 841.6n ± 2% +0.81% (p=0.004 n=6)
SQLiteSetup-8 826.9n ± 0% 855.4n ± 2% +3.45% (p=0.002 n=6)
geomean 31.66µ 32.26µ +1.89%
INSERT,Setuphave the same order of magnitude between RamSQL and SQLiteSELECTworks well when RamSQL can use a HashMap index- B-Tree indexes are clearly TODO