dbbench

README

dbbench

dbbench is a fast, lightweight database workload generator that executes a workload defined a flexible configuration file. Using this dbbench, simple 'jobs' can be defined to describe a workload run against a server. Each job represents a single query; by composing multiple jobs together, an arbitrary workload can be described. The jobs are executed against the server and timed to produce aggregated benchmarking information that is emitted periodically and when the test completes. Exact job run data can also be logged for additional offline analysis.

Note that since dbbench is a workload driver, it does not randomly generate queries, tables, data, etc. Any random data generation or setup can be easily done with SQL queries.

Getting started

To install, first install the dependencies (golang-go and git).

sudo apt-get -y install golang
sudo apt-get -y install git

dbbench requires golang version >= 1.13. Check the golang version:

go version

If your distribution comes with an older version of golang or the required version is not included in the repository, download and install a newer version of golang.

If you are installing go for the first time, you will also need to set your GOPATH environment variable and add $GOPATH/bin to your PATH.

mkdir $HOME/go
export GOPATH=$HOME/go
export PATH=$PATH:$GOPATH/bin

Once go has been set up, use the go tool to get dbbench.

go install github.com/memsql/dbbench@latest

Running dbbench

To learn how to run dbbench, follow the tutorial.

Author

dbbench is heavily inspired by fio. It was written by Alex Reece awreece@gmail.com (Performance Engineer at MemSQL) to enable flexible testing of a database. He got tired of writing specific test applications to simulate a given workload, and found that the existing database benchmark/test tools out there weren't flexible enough to do what he wanted. For more context about the ethos of dbbench, see the blog post that introduced it.

Documentation

Index

• Variables
• func NormInverseCDF(p float64) float64
• type Config
  • func (c *Config) String() string
• type ConnectionConfig
  • func (cc *ConnectionConfig) OverrideFromURL(u url.URL)
• type Database
• type DatabaseFlavor
• type ErrorCounts
  • func (ec ErrorCounts) Add(err error, query string, df DatabaseFlavor) error
  • func (ec ErrorCounts) String() string
  • func (ec ErrorCounts) TotalAccepted(df DatabaseFlavor, errors Set) (total uint64)
  • func (ec ErrorCounts) TotalErrors() (total uint64)
  • func (ec ErrorCounts) UnhandledErrors(df DatabaseFlavor, errors Set) (newEc ErrorCounts)
• type Job
  • func (job *Job) Run(ctx context.Context, db Database, df DatabaseFlavor, results chan<- *JobResult)
  • func (job *Job) String() string
• type JobResult
• type JobStats
  • func (js *JobStats) String() string
  • func (js *JobStats) Update(config *Config, jr *JobResult)
• type SafeCSVWriter
  • func NewSafeCSVWriter(path string) (*SafeCSVWriter, error)
  • func (scw *SafeCSVWriter) Close()
  • func (scw *SafeCSVWriter) Error() error
  • func (scw *SafeCSVWriter) Flush()
  • func (scw *SafeCSVWriter) Write(record []string) error
• type Set
  • func (s Set) Add(i interface{})
  • func (s Set) Contains(i interface{}) bool
• type StreamingHistogram
  • func (sh *StreamingHistogram) Add(x uint64)
  • func (sh *StreamingHistogram) Histogram() string
• type StreamingSample
  • func (ss *StreamingSample) Add(x float64)
  • func (ss *StreamingSample) Count() int
  • func (ss *StreamingSample) Histogram(nBucketsMax int) (buckets []int, minV float64, maxV float64, extra int)
  • func (ss *StreamingSample) Samples() []float64
• type StreamingStats
  • func (ss *StreamingStats) Add(x float64)
  • func (ss *StreamingStats) Confidence(alpha float64) float64
  • func (ss *StreamingStats) Count() int
  • func (ss *StreamingStats) Mean() float64
  • func (ss *StreamingStats) SampleStdDev() float64
  • func (ss *StreamingStats) SampleVariance() float64
• type URLString
  • func (v URLString) Set(s string) error
  • func (v URLString) String() string
• type WriteFileFlagValue
  • func (wffv *WriteFileFlagValue) Get() interface{}
  • func (wffv *WriteFileFlagValue) GetFile() *os.File
  • func (wffv *WriteFileFlagValue) Set(v string) (err error)
  • func (wffv *WriteFileFlagValue) String() string

Variables

var EmptyQueryError = errors.New("empty query found")

Functions

func NormInverseCDF

func NormInverseCDF(p float64) float64

* Modified from the author's original bc code by Alex Reece * (awreece@gmail.com) on Jul 2, 2015. For information about * the algorithm, see http://home.online.no/~pjacklam/notes/invnorm/ * Original comment reproduced below. * * Lower tail quantile for standard normal distribution function. * This function returns an approximation of the inverse cumulative * standard normal distribution function. I.e., given P, it returns * an approximation to the X satisfying P = Pr{Z <= X} where Z is a * random variable from the standard normal distribution. * * The algorithm uses a minimax approximation by rational functions * and the result has a relative error whose absolute value is less * than 1.15e-9. * * Author: Peter John Acklam * Time-stamp: 2005-03-10 14:13:52 +01:00 * E-mail: pjacklam@online.no * WWW URL: http://home.online.no/~pjacklam

Types

type Config

type Config struct {
	Flavor         DatabaseFlavor
	Duration       time.Duration
	Setup          []string
	Teardown       []string
	Jobs           map[string]*Job
	AcceptedErrors Set
}

func (*Config) String

func (c *Config) String() string

type ConnectionConfig

type ConnectionConfig struct {
	Username string
	Password string
	Host     string
	Port     int
	Database string
	Params   string
}

* The user specified parameters for connecting to a database. If any * field is zero, no user preference was provided.

var GlobalConfig ConnectionConfig

func (*ConnectionConfig) OverrideFromURL

func (cc *ConnectionConfig) OverrideFromURL(u url.URL)

* Override the connection configuration with parameters from the URL. * * If a given parameter is not inside the URL, then the one from * the connection configuration is kept untouched.

type Database

type Database interface {
	/*
	 * Runs the query, returning the number of records affected.
	 * If results is not nil, write the results of the query to
	 * it.
	 *
	 * It is assumed that Database will have it's own connection pooling
	 * so that it is safe to call RunQuery from arbitrarily many
	 * goroutines without blocking.
	 */
	RunQuery(results *SafeCSVWriter, query string, args []interface{}) (int64, error)

	/*
	 * Close the database, reclaiming any resources.
	 *
	 * It is illegal to call RunQuery after a database has been closed.
	 */
	Close()
}

* An instance of a query-able database; for example, a sql.DB.

type DatabaseFlavor

type DatabaseFlavor interface {
	/*
	 * Connects to the database given by the connection config.
	 *
	 * If any connection property (e.g. host, port) is unspecified
	 * (i.e. zero), uses a database specific default for an instance
	 * of this database running on the local host.
	 */
	Connect(cc *ConnectionConfig) (Database, error)

	/*
	 * Validate that the query is supported by the underlying database
	 * driver. For example. the golang "database/sql".DB uses a connection
	 * pool so queries that affect the connection (e.g. "use", "begin") are
	 * disallowed.
	 *
	 * If the query is empty, returns EmptyQueryError.
	 */
	CheckQuery(string) error

	/*
	 * The separator used in query files for this flavor of database
	 * (e.g. ";") for most SQL databases.
	 */
	QuerySeparator() string

	/*
	 * The extracted error code (string) from the error (error) thrown by the database driver. This is needed to let
	 * dbbench handle arbitrary errors from any given database flavor.
	 */
	ErrorCode(error) (string, error)
}

* An abstract flavor of a database; for example, "postgres" or "mysql".

type ErrorCounts

type ErrorCounts map[string]errorCounts // error code (string) -> errorCounts

Go's map can only handle comparable types as a key. We can't be sure that an error thrown by any possible database driver is comparable. So, we instead key by error code.

func (ErrorCounts) Add

func (ec ErrorCounts) Add(err error, query string, df DatabaseFlavor) error

func (ErrorCounts) String

func (ec ErrorCounts) String() string

func (ErrorCounts) TotalAccepted

func (ec ErrorCounts) TotalAccepted(df DatabaseFlavor, errors Set) (total uint64)

func (ErrorCounts) TotalErrors

func (ec ErrorCounts) TotalErrors() (total uint64)

func (ErrorCounts) UnhandledErrors

func (ec ErrorCounts) UnhandledErrors(df DatabaseFlavor, errors Set) (newEc ErrorCounts)

Return a new ErrorCounts that contains just the subset of unhandled errors

type Job

type Job struct {
	Name    string
	Queries []string

	QueueDepth uint64
	Rate       float64
	Count      uint64
	BatchSize  uint64

	QueryLog     io.ReadCloser
	QueryArgs    *csv.Reader
	QueryResults *SafeCSVWriter

	Start time.Duration
	Stop  time.Duration
}

func (*Job) Run

func (job *Job) Run(ctx context.Context, db Database, df DatabaseFlavor, results chan<- *JobResult)

func (*Job) String

func (job *Job) String() string

type JobResult

type JobResult struct {
	Name         string
	Start        time.Duration
	Elapsed      time.Duration
	Queries      int
	RowsAffected int64
	Errors       ErrorCounts
}

type JobStats

type JobStats struct {
	Transactions StreamingHistogram
	Errors       StreamingHistogram
	// contains filtered or unexported fields
}

func (*JobStats) String

func (js *JobStats) String() string

func (*JobStats) Update

func (js *JobStats) Update(config *Config, jr *JobResult)

type SafeCSVWriter

type SafeCSVWriter struct {
	// contains filtered or unexported fields
}

func NewSafeCSVWriter

func NewSafeCSVWriter(path string) (*SafeCSVWriter, error)

func (*SafeCSVWriter) Close

func (scw *SafeCSVWriter) Close()

func (*SafeCSVWriter) Error

func (scw *SafeCSVWriter) Error() error

func (*SafeCSVWriter) Flush

func (scw *SafeCSVWriter) Flush()

func (*SafeCSVWriter) Write

func (scw *SafeCSVWriter) Write(record []string) error

type Set

type Set map[interface{}]struct{}

func (Set) Add

func (s Set) Add(i interface{})

func (Set) Contains

func (s Set) Contains(i interface{}) bool

type StreamingHistogram

type StreamingHistogram struct {
	Buckets [64]uint64
}

func (*StreamingHistogram) Add

func (sh *StreamingHistogram) Add(x uint64)

func (*StreamingHistogram) Histogram

func (sh *StreamingHistogram) Histogram() string

type StreamingSample

type StreamingSample struct {
	// contains filtered or unexported fields
}

func (*StreamingSample) Add

func (ss *StreamingSample) Add(x float64)

func (*StreamingSample) Count

func (ss *StreamingSample) Count() int

func (*StreamingSample) Histogram

func (ss *StreamingSample) Histogram(nBucketsMax int) (buckets []int, minV float64, maxV float64, extra int)

func (*StreamingSample) Samples

func (ss *StreamingSample) Samples() []float64

type StreamingStats

type StreamingStats struct {
	// contains filtered or unexported fields
}

* Use Welfords Method to compute variance in a stream.

func (*StreamingStats) Add

func (ss *StreamingStats) Add(x float64)

func (*StreamingStats) Confidence

func (ss *StreamingStats) Confidence(alpha float64) float64

func (*StreamingStats) Count

func (ss *StreamingStats) Count() int

func (*StreamingStats) Mean

func (ss *StreamingStats) Mean() float64

func (*StreamingStats) SampleStdDev

func (ss *StreamingStats) SampleStdDev() float64

func (*StreamingStats) SampleVariance

func (ss *StreamingStats) SampleVariance() float64

type URLString

type URLString struct {
	URL *url.URL
}

func (URLString) Set

func (v URLString) Set(s string) error

func (URLString) String

func (v URLString) String() string

type WriteFileFlagValue

type WriteFileFlagValue struct {
	// contains filtered or unexported fields
}

func (*WriteFileFlagValue) Get

func (wffv *WriteFileFlagValue) Get() interface{}

func (*WriteFileFlagValue) GetFile

func (wffv *WriteFileFlagValue) GetFile() *os.File

func (*WriteFileFlagValue) Set

func (wffv *WriteFileFlagValue) Set(v string) (err error)

func (*WriteFileFlagValue) String

func (wffv *WriteFileFlagValue) String() string