dbbench
Description
dbbench is a simple tool to benchmark or stress test databases. You can use the simple built-in benchmarks or run your own queries.
Attention: This tool comes with no warranty. Don’t run it on production databases.
Example
$ dbbench postgres --user postgres --pass example --iter 100000
inserts 6.199670776s 61996 ns/op
updates 7.74049898s 77404 ns/op
selects 2.911541197s 29115 ns/op
deletes 5.999572479s 59995 ns/op
total: 22.85141994s
Homebrew
Using the Homebrew package manager for macOS:
brew install sj14/tap/dbbench
Manually
It’s also possible to install the current development snapshot with go get (not recommended):
go get -u github.com/sj14/dbbench/cmd/dbbench
Docker
docker run ghcr.io/sj14/dbbench:latest
Check here for available images.
Custom Scripts
You can run your own SQL statements with the --script flag. You can use the auto-generate tables. Beware the file size as it will be completely loaded into memory.
The script must contain valid SQL statements for your database.
There are some built-in variables and functions which can be used in the script. It’s using the golang template engine which uses the delimiters {{ and }}. Functions are executed with the call command and arguments are passed after the function name.
Example
Exemplary sqlite_bench.sql file:
-- Create table
\benchmark once \name init
CREATE TABLE dbbench_simple (id INT PRIMARY KEY, balance DECIMAL);
-- How long takes an insert and delete?
\benchmark loop \name single
INSERT INTO dbbench_simple (id, balance) VALUES({{.Iter}}, {{call .RandInt63}});
DELETE FROM dbbench_simple WHERE id = {{.Iter}};
-- How long takes it in a single transaction?
\benchmark loop \name batch
BEGIN TRANSACTION;
INSERT INTO dbbench_simple (id, balance) VALUES({{.Iter}}, {{call .RandInt63}});
DELETE FROM dbbench_simple WHERE id = {{.Iter}};
COMMIT;
-- Delete table
\benchmark once \name clean
DROP TABLE dbbench_simple;
In this script, we create and delete the table manually, thus we will pass the --noinit and --noclean flag, which would otherwise create this default table for us:
dbbench sqlite --script scripts/sqlite_bench.sql --iter 5000 --noinit --noclean
output:
(once) init: 3.404784ms 3404784 ns/op
(loop) single: 10.568390874s 2113678 ns/op
(loop) batch: 5.739021596s 1147804 ns/op
(once) clean: 1.065703ms 1065703 ns/op
total: 16.312319959s
Development
Below are some examples how to run different databases and the equivalent call of dbbench for testing/developing.
Cassandra
docker run --name dbbench-cassandra -p 9042:9042 -d cassandra:latest
dbbench cassandra
CockroachDB
# port 8080 is the webinterface (optional)
docker run --name dbbench-cockroach -d -p 26257:26257 -p 8080:8080 cockroachdb/cockroach:latest start --insecure
dbbench cockroach
Microsoft SQL Server
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d microsoft/mssql-server-linux
dbbench mssql -user sa -pass 'yourStrong(!)Password'
MariaDB
docker run --name dbbench-mariadb -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=root mariadb
dbbench mariadb
MySQL
docker run --name dbbench-mysql -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=root mysql
dbbench mysql
PostgreSQL
docker run --name dbbench-postgres -p 5432:5432 -d postgres
dbbench postgres --user postgres --pass example
ScyllaDB
docker run --name dbbench-scylla -p 9042:9042 -d scylladb/scylla
dbbench scylla
SQLite
dbbench sqlite
TiDB
git clone https://github.com/pingcap/tidb-docker-compose.git
cd tidb-docker-compose && docker-compose pull
docker-compose up -d
dbbench tidb --pass '' --port 4000
Acknowledgements
Thanks to the authors of Go and those of the directly and indirectly used libraries, especially the driver developers. It wouldn’t be possible without all your work.
This tool was highly inspired by the snippet from user Fale and the tool pgbench. Later, also inspired by MemSQL’s dbbench which had the name and a similar idea before.