Introducing dbbench

TL;DR: get me to the diagram

» Introduction

Recently, I read about CockroachDB which is a so-called NewSQL database, trying to combine the benefits of relational SQL databases and NoSQL databases. Since a few months, I’m dealing with Cassandra as a distributed database, which is also my first real usage of NoSQL databases. In my opinion, it adds quite some complexity to the development process for the sake of scalability or availability. So, I was very enthusiastic about CockroachDB and thought it would be cool to have a database combining the best of two worlds. I checked the Docker image and immediately tested converting a side project which was using PostgreSQL to CockroachDB, which went quite nice as it only uses very simple SQL statements.

So, what about the performance? I found this issue on GitHub, with a simple benchmark, comparing PostgreSQL and CockroachDB. The result shows, PostgreSQL is 2-3x faster, but on the other hand, you can easily scale CockroachDB (according to the devs).

I liked the idea of this simple benchmark and wanted to check how other databases perform in this apple and oranges comparison, that’s why I wrote dbbench. It’s a simple Go application. Thank’s to the work of several DB driver authors, it can be used with SQLite, MySQL, PostgreSQL, Cassandra, MS SQL, and compatible databases (e.g. MariaDB, TiDB, CockroachDB, ScyllaDB).

For each of these databases, there are simple INSERT, SELECT, UPDATE, and DELETE queries, which are run in a loop for a specific amount of iterations.
A goal for the future is to add more, database specific benchmarks, e.g. testing the performance of queries with relations to other tables.

But enough of the introduction, you want to see the results of this unfair benchmark which is totally not comparable and I’m even running the databases in Docker on the same host as the benchmark tool, and you shouldn’t use the results to make any conclusions out of it? Here we go! Kids, don’t try this at home.

» SQLite vs PostgreSQL vs MySQL vs MariaDB vs CockroachDB vs Cassandra vs ScyllaDB

» Setup

Hardware
MacBook Pro 15" 2017
2,8 GHz Intel Core i7
16 GB RAM

Software
MacOS 10.14.2
dbbench 0.6.0 / 0.6.1

1

docker version

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

Client: Docker Engine - Community
 Version:           18.09.0
 API version:       1.39
 Go version:        go1.10.4
 Git commit:        4d60db4
 Built:             Wed Nov  7 00:47:43 2018
 OS/Arch:           darwin/amd64
 Experimental:      false

Server: Docker Engine - Community
 Engine:
  Version:          18.09.0
  API version:      1.39 (minimum version 1.12)
  Go version:       go1.10.4
  Git commit:       4d60db4
  Built:            Wed Nov  7 00:55:00 2018
  OS/Arch:          linux/amd64
  Experimental:     true

» SQLite

SQLite is the only database which is not running in a Docker container in this comparison.

1

dbbench sqlite --iter 100000

1
2
3
4
5

inserts:	1m14.300357957s	743003	ns/op
selects:	3.353519138s	33535	ns/op
updates:	59.69622538s	596962	ns/op
deletes:	1m7.260636566s	672606	ns/op
total: 3m24.611340315s

» Update SQLite

I got an email from Dwayne Richard Hipp, the architect and primary author of SQLite 😮
He was curious, how the results would look like if I set PRAGMA journal_mode=WAL (documentation) instead of the default journal mode (DELETE).
Initially, I didn’t want to tune the databases and just use the out of the box settings, as you can always tune something to exactly match the given benchmark. But he wrote, that this is almost the universal default mode, also on iOS and Android (and an email from such a big name in the database business is a great honour for me, suddenly I also feel a bit ashamed for this post, who would expect such a prominent visitor).
Here are the results with the WAL mode enabled:

1

dbbench sqlite --iter 100000

1
2
3
4
5

inserts:        6.966202531s    69662   ns/op
selects:        2.160250654s    21602   ns/op
updates:        5.443289317s    54432   ns/op
deletes:        6.373761482s    63737   ns/op
total: 20.943860295s

That’s crazy, isn’t it? SQLite became easily the fastest of all tested databases! Check the new diagram in the summary. \end update

» PostgreSQL

Starting the container

1

docker run --name dbbench-postgres -p 5432:5432 -d postgres

Printing the version

1

docker exec -it dbbench-postgres postgres --version

1

postgres (PostgreSQL) 11.1 (Debian 11.1-1.pgdg90+1)

Running the benchmark

1

dbbench postgres --user postgres --pass example --iter 100000

1
2
3
4
5

inserts:	26.470013387s	264700	ns/op
selects:	17.934667347s	179346	ns/op
updates:	29.031683145s	290316	ns/op
deletes:	21.093109399s	210931	ns/op
total: 1m34.529769185s

» MySQL

Starting the container

1

docker run --name dbbench-mysql -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=dbbench mysql

Printing the version

1

docker exec -it dbbench-mysql mysql --version

1

mysql  Ver 8.0.13 for Linux on x86_64 (MySQL Community Server - GPL)

Running the benchmark

1

dbbench mysql --iter 100000

1
2
3
4
5

inserts:	40.358535079s	403585	ns/op
selects:	15.803806205s	158038	ns/op
updates:	1m0.001733018s	600017	ns/op
deletes:	41.021493138s	410214	ns/op
total: 2m37.185900218s

» MariaDB

Starting the container

1

docker run --name dbbench-mariadb -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=dbbench mariadb

Printing the version

1

docker exec -it dbbench-mariadb mysql --version

1

mysql  Ver 15.1 Distrib 10.3.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Running the benchmark

1

dbbench mariadb --iter 100000

1
2
3
4
5

inserts:	30.908076897s	309080	ns/op
selects:	31.066482126s	310664	ns/op
updates:	31.772009635s	317720	ns/op
deletes:	28.327519145s	283275	ns/op
total: 2m2.074409248s

» CockroachDB

Starting the container

1

docker run --name dbbench-cockroach -d -p 26257:26257 -p 8080:8080 cockroachdb/cockroach:latest start --insecure

Printing the version

1

docker exec -it dbbench-cockroach ./cockroach version

1
2
3
4
5
6
7
8

Build Tag:    v2.1.3
Build Time:   2018/12/17 19:15:31
Distribution: CCL
Platform:     linux amd64 (x86_64-unknown-linux-gnu)
Go Version:   go1.10.3
C Compiler:   gcc 6.3.0
Build SHA-1:  0c87b11cb99ba5c677c95ded55dcba385928474e
Build Type:   release

Running the benchmark

1

dbbench cockroach --iter 100000

1
2
3
4
5

inserts:	42.964478005s	429644	ns/op
selects:	27.28050541s	272805	ns/op
updates:	52.159385677s	521593	ns/op
deletes:	45.751632809s	457516	ns/op
total: 2m48.156282918s

» Cassandra

Because I made a mistake in the flag parsing, I had to release version 0.6.1 of dbbench, which is used for the Cassandra and ScyllaDB benchmark. Furthermore, the built-in benchmark uses the IF [NOT] EXISTS condition for the Cassandra/Scylla inserts, updates, deletes. Later I’ve seen that this is not yet supported for ScyllaDB and has a significant influence on the benchmark results. So, I decided to show both results here but only use the run without the condition in the resulting diagram at the end.

Starting the container

1

docker run --name dbbench-cassandra -p 9042:9042 -d cassandra:latest

Printing the version

1

docker exec -it dbbench-cassandra cassandra -v

1

3.11.3

Running the benchmark

with IF [NOT] EXISTS

1

dbbench cassandra --iter 100000

1
2
3
4
5

inserts:        1m43.956974017s 1039569 ns/op
selects:        49.899972097s   498999  ns/op
updates:        1m25.148293551s 851482  ns/op
deletes:        1m21.926269834s 819262  ns/op
total: 5m20.931850308s

without IF [NOT] EXISTS (used in the results diagram)

1

dbbench cassandra --iter 100000

1
2
3
4
5

inserts:	50.462478672s	504624	ns/op
selects:	50.958470743s	509584	ns/op
updates:	42.549341296s	425493	ns/op
deletes:	39.424111354s	394241	ns/op
total: 3m3.394705869s

» ScyllaDB

As already explained in the Cassandra benchmark, ScyllaDB does not support Lightweight Transactions yet, which will result in an error. Thus, the IF [NOT] EXISTS statements were removed in this benchmark.

Starting the container

1

docker run --name dbbench-scylla -p 9042:9042 -d scylladb/scylla

Printing the version

1

docker exec -it dbbench-scylla scylla --version

1

2.3.1-0.20181021.336c77166

Running the benchmark

1

dbbench scylla --iter 100000

1
2
3
4
5

inserts:	33.611910935s	336119	ns/op
selects:	33.307994496s	333079	ns/op
updates:	31.204360195s	312043	ns/op
deletes:	28.673362559s	286733	ns/op
total: 2m6.797884975s

» Benchmark Summary

The interesting parts I can see from the diagram is, that SQLite is the slowest in all benchmarks except the SELECT where it is the fastest by distance.
PostgreSQL is probably the overall winner, only SQLite and MySQL are faster in the SELECT benchmark.
About 9 years ago, MariaDB started as a fork of MySQL and the results show the have diverged quite heavily from a performance perspective, in favour of MariaDB – except for SELECT.
ScyllaDB is faster than Cassandra but lacks features as explained in their sections.

Remember: The purpose of distributed databases is to run in a cluster, and not as done here, running only a single node!

» Summary Update

SQLite is the fastest when the default journal mode is set to Write-Ahead Logging (last bar on the right side). See SQLite update.

When another database author writes me such a simple performance optimization, I will update this blog post again ;-) \end update

As described in the introduction, it’s a very unfair comparison. Even changing the hardware or the operating system might lead to completely different results. Furthermore, each database has it’s very specific advantages and disadvantages and can not only be compared by this simple benchmark. Maybe I’ve even manipulated the results, can you trust me? Inform yourself about the databases, their use cases, and draw your own conclusions!

The Following chapter will compare PostgreSQL on bare metal vs inside a Docker container.

» PostgreSQL: Metal vs Docker

Here is another benchmark which might make a bit more sense. Comparing the same database (PostgreSQL) on bare metal hardware vs. inside a Docker container.

» Setup

Hardware
Dell XPS 13 9370
Intel i7-8650U CPU @ 1.90GHz
16 GB RAM

Software
Kubuntu 18.10
dbbench version 0.6.0

» Metal Benchmark

Getting the version of the apt installed Postgres package with apt show postgresql-10:

1
2

Package: postgresql-10
Version: 10.6-0ubuntu0.18.10.1

Running the benchmark:

1

dbbench postgres --user postgres --pass example --iter 75000

1
2
3
4
5

inserts:        7.393019174s    98573   ns/op
selects:        3.15834201s     42111   ns/op
updates:        8.010960004s    106812  ns/op
deletes:        5.964594805s    79527   ns/op
total: 24.527919169s

» Docker Benchmark

The installed docker version:

1

docker version

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

Client:
 Version:           18.09.1-rc1
 API version:       1.39
 Go version:        go1.10.5
 Git commit:        bca0068
 Built:             Fri Dec  7 05:29:45 2018
 OS/Arch:           linux/amd64
 Experimental:      false

Server: Docker Engine - Community
 Engine:
  Version:          18.09.1-rc1
  API version:      1.39 (minimum version 1.12)
  Go version:       go1.10.5
  Git commit:       bca0068
  Built:            Fri Dec  7 04:59:10 2018
  OS/Arch:          linux/amd64
  Experimental:     false

Running the Docker image with the same version as the apt package.

1

docker run --name dbbench-postgres -p 5432:5432 -d postgres:10.6

Running the benchmark:

1

dbbench postgres --user postgres --pass example --iter 75000

1
2
3
4
5

inserts:        13.000163242s   173335  ns/op
selects:        4.903061902s    65374   ns/op
updates:        8.023648308s    106981  ns/op
deletes:        12.311042043s   164147  ns/op
total: 38.238323758s

» Summary: Metal vs Docker

Docker adds quite some overhead in this particular setup. The bare metal benchmark runs are up to twice as fast for the INSERT and DELETE statements, and a third faster for the SELECT statements. Only the UPDATE statements are almost equally fast.