sj14/dbbench

🏋️ dbbench is a simple database benchmarking tool which supports several databases and own scripts

dbbench's Issues

SQLite: If DB file existed before, don't remove it during cleanup

When benchmarking an already present sqlite file, the cleanup() routine will automatically remove it, which is not a desired behaviour.

Add some statistics (min, max, avg...)

Calculate some more statistics during benchmarking, e.g. min, max, avg latency.

Add a flag for prepared statements

Add a flag to use prepared SQL statements.

Automate Releases

Currently, I'm running:

git tag -a vX.X.X

docker run --rm --privileged \      
  -v $PWD:/go/src/github.com/sj14/dbbench \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -w /go/src/github.com/sj14/dbbench \
  -e GITHUB_TOKEN=$GH_TOKEN \
  -e GO111MODULE=on \
  goreleaser/goreleaser --rm-dist

sudo mv dist/dbbench.rb homebrew-tap
cd homebrew-tap
git commit -am "dbbench vX.X.X
git push

Check if it's possible to automate the releases with azure pipelines.

Connection issues with DBs inside Docker on Linux

After enough iteration, the connection gets unintentionally closed. Might be a Docker bug on Linux.

MySQL and MariaDB

[mysql] 2018/12/16 14:33:08 packets.go:36: unexpected EOF
2018/12/16 14:33:08 INSERT INTO dbbench.simple (id, balance) VALUES( 9458, 3611810842); failed: driver: bad connection

PostgreSQL

2018/12/16 13:53:19 DELETE FROM dbbench.simple WHERE id = 56669; failed: read tcp 127.0.0.1:42836->127.0.0.1:5432: read: connection reset by peer

Wrong benchmark name

Under certain circumstances, the benchmark name from a script is wrong. Happens when mode is loop and \name comes before \mode after a benchmark without \name command.

\name insert
\mode loop
INSERT INTO dbbench_simple (id, balance) VALUES( {{.Iter}}, {{call .RandInt63}});

\mode loop
UPDATE dbbench_simple SET balance = {{call .RandInt63}} WHERE id = {{.Iter}};

\name delete
\mode loop
DELETE FROM dbbench_simple WHERE id = {{.Iter}};

dbbench -type sqlite -script scripts/sqlite_bench.sql -iter 1

Result is wrong, 3rd benchmark should be named delete and 2nd should be line 6

(loop) insert:  1.234339ms      1234339 ns/op
(loop) delete:  1.415112ms      1415112 ns/op
(loop) line 10-10:      1.546326ms      1546326 ns/op
total: 4.516038ms

Fix release builds with CGO

Currently, goreleaser doesn't support CGO builds, which is required for sqlite support (and maybe more in the future). Check for a way to build releases with CGO support.

Add support for ClickHouse

https://clickhouse.tech/

Select benchmarks based on regular expressions

When using the -run flag, match benchmarks based on regular expressions instead of exact names.

Cant connect to remote postgresql server

There is no option to specify the database name for connectiong with a remote postgresql server:

$ dbbench postgres --host "172.24.1.63/mydbname" --port 5439 --user my_user --pass my_password  --iter 10 --threads 2 --conns 2 --sleep 5s --script website-benchmark.sql
2019/10/25 19:13:15 failed to ping db: dial tcp: lookup 172.24.1.63/mydbname: no such host

$ dbbench postgres --host 172.24.1.63 --port 5439 --user my_user --pass my_password  --iter 10 --threads 2 --conns 2 --sleep 5s --script website-benchmark.sql
2019/10/25 19:13:27 failed to ping db: pq: database "my_user" does not exist

Notice that also ping is disabled by network admin:

$ ping 172.24.1.63
PING 172.24.1.63 (172.24.1.63) 56(84) bytes of data.
^C
--- 172.24.1.63 ping statistics ---
10 packets transmitted, 0 received, 100% packet loss, time 9224ms

What I missed is some command line options:

1. --no-ping flag for skipping ping connection test
2. --connectionoption for connecting using go pg connectionstring directly and also could be used instead of --host --port --user --pass

Wrong ns/op for mode once

Don't divide the duration by the iterations when statement was executed only once.

(once) init:    4.100387ms      820     ns/op (WRONG)
(loop) single:  12.623048911s   2524609 ns/op
(loop) batch:   6.575640186s    1315128 ns/op
(once) clean:   1.110485ms      222     ns/op (WRONG)
total: 19.204362858s

Add parallel mode

When desired, allow to execute benchmark statements in parallel (compared to the current blocking/queue mode).

Consider using non-cgo sqlite

e.g. https://gitlab.com/cznic/sqlite

Generate random string values in templates

This tool would be more useful to me if it was capable of generating things like names and addresses to benchmark on more realistic data.

Would you accept a PR adding a fake data generation library dependency and some new template functions to go with it?

Better output formatting

Try to format the output better, e.g. with https://golang.org/pkg/text/tabwriter/

Currently it looks like this:

inserts:        7.010234901s    280409  ns/op
selects:        1.193978836s    47759   ns/op
updates:        7.061338251s    282453  ns/op
deletes:        6.68747513s     267499  ns/op
relation_insert0:       7.045241106s    281809  ns/op
relation_insert1:       9.43514862s     377405  ns/op
relation_select:        2.499483223s    99979   ns/op
relation_delete1:       8.389839477s    335593  ns/op
relation_delete0:       8.026760922s    321070  ns/op
total: 57.349712525s

Catch SIGINT and cleanup

Catch SIGINT (ctrl-c) and run the cleanup before exiting dbbench.