dbbench - GitLite

KO | EN

gitlite — search

#javascript #hacktoberfest #go #machine-learning #rust #linux #java #awesome #cli #kubernetes #vue #awesome-list #ios #open-source

dbbench

(★ 118)

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

파일 탐색기

최종 버전 다운로드 (.zip)

- - docker.yaml
  - go.yaml
  - release.yaml
- dependabot.yml
- benchmark.go
- benchmark_test.go
- parser.go
- parser_test.go
- - main.go
- cassandra.go
- cockroach.go
- mssql.go
- mysql.go
- postgres.go
- spanner.go
- sqlite.go
- sqlite_bench.sql
.gitignore
.goreleaser.yml
dbbench.sqlite
Dockerfile
go.mod
go.sum
LICENSE
README.md

# 설치 가이드

1. 코드 내려받기

git clone https://github.com/sj14/dbbench

깃허브에서 프로젝트 코드 전체를 내 컴퓨터로 내려받습니다.

cd dbbench

방금 내려받은 프로젝트 폴더 안으로 이동합니다.

2. Docker

쉬움 추천

사전 준비물

Git GitHub에서 프로젝트 코드를 내려받으려면 필요합니다.
Docker Desktop 컨테이너를 빌드하고 실행하려면 필요합니다. 설치 후 실행해서 백그라운드에 켜두세요.

docker build -t dbbench .

Dockerfile을 기반으로 실행 가능한 이미지를 빌드합니다.

docker run -p 8080:80 dbbench

빌드된 이미지를 실제 컨테이너로 실행합니다.

✅ 터미널에 docker compose ps 를 입력해 컨테이너들이 Up 상태인지 확인하세요. README에 포트 번호가 적혀있다면 브라우저에서 http://localhost:포트번호 로 접속해보세요.

3. Go

보통

사전 준비물

Git GitHub에서 프로젝트 코드를 내려받으려면 필요합니다.
Go Go 언어 컴파일러 및 도구 모음입니다.

It's also possible to install the current development snapshot with `go install` (not recommended):

이 명령어를 터미널에 그대로 입력해 실행하세요.

✅ 빌드가 에러 없이 끝나면 성공입니다. go run . 을 썼다면 터미널 출력을 확인하세요.

이 레포의 README에 적힌 실제 명령어를 그대로 가져왔습니다.

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# dbbench

![Action](https://github.com/sj14/dbbench/workflows/Go/badge.svg)
[![Go Report Card](https://goreportcard.com/badge/github.com/sj14/dbbench)](https://goreportcard.com/report/github.com/sj14/dbbench)
[![Coverage Status](https://coveralls.io/repos/github/sj14/dbbench/badge.svg?branch=master)](https://coveralls.io/github/sj14/dbbench?branch=master)

## Table of Contents

- [Description](#description)
- [Example](#example)
- [Installation](#installation)
- [Supported Databases](#supported-Databases-/-Driver)
- [Usage](#usage)
- [Custom Scripts](#custom-scripts)
- [Troubeshooting](#troubleshooting)
- [Development](#development)
- [Acknowledgements](#acknowledgements)

## 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

``` text
$ 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
```

## Installation

### Precompiled Binaries

Binaries are available for all major platforms. See the [releases](https://github.com/sj14/dbbench/releases) page.

### Homebrew

Using the [Homebrew](https://brew.sh/) package manager for macOS:

```console
brew install sj14/tap/dbbench
```

### Manually

It's also possible to install the current development snapshot with `go install` (not recommended):

```console
go install github.com/sj14/dbbench/cmd/dbbench
```

### Docker

```console
docker run ghcr.io/sj14/dbbench:latest
```

Check [here](https://github.com/sj14/dbbench/pkgs/container/dbbench) for available images.

## Supported Databases / Driver

Databases | Driver
----------|-----------
Cassandra and compatible databases (e.g. ScyllaDB) | github.com/gocql/gocql
MS SQL and compatible databases (no built-in benchmarks yet) | github.com/denisenkom/go-mssqldb
MySQL and compatible databases (e.g. MariaDB and TiDB) | github.com/go-sql-driver/mysql
PostgreSQL and compatible databases (e.g. CockroachDB) | github.com/lib/pq
SQLite3 and compatible databases | modernc.org/sqlite

## Usage

``` text
Available subcommands:
        cassandra|cockroach|mssql|mysql|postgres|sqlite
        Use 'subcommand --help' for all flags of the specified command.
Generic flags for all subcommands:
      --clean            only cleanup benchmark data, e.g. after a crash
      --iter int         how many iterations should be run (default 1000)
      --noclean          keep benchmark data
      --noinit           do not initialize database and tables, e.g. when only running own script
      --run string       only run the specified benchmarks, e.g. "inserts deletes" (default "all")
      --script string    custom sql file to execute
      --sleep duration   how long to pause after each single benchmark (valid units: ns, us, ms, s, m, h)
      --threads int      max. number of green threads (iter >= threads > 0) (default 25)
      --version          print version information
```

## 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](https://golang.org/pkg/text/template/) which uses the delimiters `{{` and `}}`. Functions are executed with the `call` command and arguments are passed after the function name.

### Benchmark Settings

A new benchmark is created with the `\benchmark` keyword, followed by either `once` or `loop`. Optional parameters can be added afterwards in the same line.

The the usage description and the example subsection for more information.

Usage                     | Description                                   |
--------------------------|-----------------------------------------------|
`\benchmark once`                | Execute the following statements (lines) only once (e.g. to create and delete tables).
`\benchmark loop`                | Default mode. Execute the following statements (lines) in a loop. Executes them one after another and then starts a new iteration. Add another `\benchmark loop` to start another benchmark of statements.
`\name insert`              | Set a custom name for the DB statement(s), which will be output instead the line numbers (`insert` is an examplay name).

### Statement Substitutions

Usage                     | Description                                   |
--------------------------|-----------------------------------------------|
`{{.Iter}}`                 | The iteration counter. Will return `1` when `\benchmark once`.
`{{call .RandInt64}}`       | [godoc](https://pkg.go.dev/math/rand/v2#Int64)
`{{call .RandInt64N 9999}}` | [godoc](https://pkg.go.dev/math/rand/v2#Int64N) (`9999` is an examplary upper limit)
`{{call .RandUint64}}`       | [godoc](https://pkg.go.dev/math/rand/v2#Uint64)
`{{call .RandUint64N 9999}}` | [godoc](https://pkg.go.dev/math/rand/v2#Uint64N) (`9999` is an examplary upper limit)
`{{call .RandFloat32}}`     | [godoc](https://pkg.go.dev/math/rand/v2#Float32)  
`{{call .RandFloat64}}`     | [godoc](https://pkg.go.dev/math/rand/v2#Float64)
`{{call .RandExpFloat64}}`  | [godoc](https://pkg.go.dev/math/rand/v2#ExpFloat64)
`{{call .RandNormFloat64}}` | [godoc](https://pkg.go.dev/math/rand/v2#NormFloat64)

### Example

Exemplary `sqlite_bench.sql` file:

``` sql
-- 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 .RandInt64}});
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 .RandInt64}});
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:

``` text
dbbench sqlite --script scripts/sqlite_bench.sql --iter 5000 --noinit --noclean
```

output:

``` text
(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
```

## Troubleshooting

**Error message**

``` text
failed to insert: UNIQUE constraint failed: dbbench_simple.id
```

**Description**
The previous data wasn't removed (e.g. because the benchmark was canceled). Try to run the same command again, but with the `--clean` flag attached, which will remove the old data. Then run the original command again.

## Development

Below are some examples how to run different databases and the equivalent call of `dbbench` for testing/developing.

### Cassandra

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

``` text
dbbench cassandra
```

### CockroachDB

``` text
# port 8080 is the webinterface (optional)
docker run --name dbbench-cockroach -d -p 26257:26257 -p 8080:8080 cockroachdb/cockroach:latest start --insecure
```

``` text
dbbench cockroach
```

### Microsoft SQL Server

``` text
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d microsoft/mssql-server-linux
```

``` text
dbbench mssql -user sa -pass 'yourStrong(!)Password'
```

### MariaDB

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

``` text
dbbench mariadb
```

### MySQL

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

``` text
dbbench mysql
```

### PostgreSQL

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

``` text
dbbench postgres --user postgres --pass example
```

### ScyllaDB

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

``` text
dbbench scylla
```

### SQLite

``` text
dbbench sqlite
```

### TiDB

``` text
git clone https://github.com/pingcap/tidb-docker-compose.git
cd tidb-docker-compose && docker-compose pull
docker-compose up -d
```

``` text
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](https://github.com/cockroachdb/cockroach/issues/23061#issue-300012178) and the tool [pgbench](https://www.postgresql.org/docs/current/pgbench.html). Later, also inspired by [MemSQL's dbbench](https://github.com/memsql/dbbench) which had the name and a similar idea before.