# README
Acronis Database Benchmark
This project provides a set of benchmarking utilities for various databases. It includes various tests and options for database performance analysis.
Supported Databases
The Acronis Database Benchmark supports the following databases:
- MySQL / MariaDB
- PostgreSQL
- MS SQL Server
- SQLite
- ClickHouse
- Cassandra
Usage
To use the benchmarking tool, use the following command structure:
go install acronis-db-bench
acronis-db-bench --driver <database_driver> --dsn "<data_source_name>" ...
Replace <database_driver> with the driver for your database (mysql, postgres, mssql, sqlite, clickhouse) and <data_source_name> with the appropriate data source name for your database.
Options
Database options
--driver= db driver (postgres|mysql|sqlite3|clickhouse|cassandra) (default: postgres)
--dsn= dsn connection string (default: host=127.0.0.1 sslmode=disable user=test_user)
--dont-cleanup do not cleanup DB content before/after the test in '-t all' mode
--use-truncate use TRUNCATE instead of DROP TABLE in cleanup procedure
--maxopencons= Set sql/db MaxOpenConns per worker, default value is set to 2 because the benchmark uses it's own workers pool (default: 2)
--mysql-engine= mysql engine (innodb|myisam|xpand|...) (default: innodb)
--reconnect reconnect to DB before every test iteration
--dry-run do not execute any INSERT/UPDATE/DELETE queries on DB-side
Common options
-v, --verbose Show verbose debug information (-v - info, -vv - debug)
-c, --concurrency= sets number of goroutines that runs testing function (default: 0)
-l, --loops= sets TOTAL(not per worker) number of iterations of testing function(greater priority than DurationSec) (default: 0)
-d, --duration= sets duration(in seconds) for work time for every loop (default: 5)
-S, --sleep= sleep given amount of msec between requests (default: 0)
-r, --repeat= repeat the test given amount of times (default: 1)
-Q, --quiet be quiet and print as less information as possible
-s, --randseed= Seed used for random number generation (default: 1)
Embedded Postgres specific options:
--ep-port= embedded postgres port (default 5432) (default: 5432)
--ep-data-dir= embedded postgres data dir (default: $USER_HOME/.embedded-postgres-go/data)
--ep-max-connections= embedded postgres 'max_connections' (default 200) (default: 200)
Benchmark specific options
-b, --batch= batch sets the amount of rows per transaction (default: 0)
-t, --test= select a test to execute, run --list to see available tests list
-a, --list list available tests
-C, --cleanup delete/truncate all test DB tables and exit
-I, --init create all test DB tables and exit
-s, --randseed= Seed used for random number generation (default: 1)
-u, --chunk= chunk size for 'all' test (default: 500000)
-U, --limit= total rows limit for 'all' test (default: 2000000)
-i, --info provide information about tables & indexes
-e, --events simulate event generation for every new object
--tenants-working-set= set tenants working set (default: 10000)
--ctis-working-set= set CTI working set (default: 1000)
--profiler-port= open profiler on given port (e.g. 6060) (default: 0)
--describe describe what test is going to do
--describe-all describe all the tests
--explain prepend the test queries by EXPLAIN ANALYZE
-q, --query= execute given query, one can use:
{CTI} - for random CTI UUID
{TENANT} - randon tenant UUID
DB specific usage
PostgreSQL
acronis-db-bench --driver postgres --dsn "host=localhost port=5432 user=<USER> password=<PASSWORD> dbname=<DATABASE NAME> sslmode=disable|enable"
Embedded PostgreSQL
acronis-db-bench --embedded-postgres
For details, see embedded Postgres and embedded Postgres binaries
MySQL / MariaDB
acronis-db-bench --driver mysql --dsn "<USER>:<PASSWORD>@tcp(<HOST>:<PORT>)/<DATABASE NAME>"
MS SQL Server
acronis-db-bench --driver mssql --dsn "sqlserver://<USER>:<PASSWORD>@<HOST>:<PORT>?database=<DATABASE NAME>"
SQLite
acronis-db-bench --driver sqlite --dsn "<DATABASE FILE>"
ClickHouse
acronis-db-bench --driver clickhouse --dsn "tcp://<HOST>:<PORT>?username=<USER>&password=<PASSWORD>&database=<DATABASE NAME>"
Cassandra
acronis-db-bench --driver cassandra --dsn "<HOST>&port=<PORT>&username=<USER>&password=<PASSWORD>&keyspace=<DATABASE NAME>"
Examples
Run all tests
acronis-db-bench --driver postgres --dsn "host=localhost port=5432 user=<USER> password=<PASSWORD> dbname=<DATABASE NAME> sslmode=disable" -t all
Run a specific test
acronis-db-bench --driver postgres --dsn "host=localhost port=5432 user=<USER> password=<PASSWORD> dbname=<DATABASE NAME> sslmode=disable" -t insert-medium
acronis-db-bench --driver postgres --dsn "host=localhost port=5432 user=<USER> password=<PASSWORD> dbname=<DATABASE NAME> sslmode=disable" -t select-medium-rand
Run a specific test with concurrency (16 workers) and repeat (3 times)
acronis-db-bench --driver postgres --dsn "host=localhost port=5432 user=<USER> password=<PASSWORD> dbname=<DATABASE NAME> sslmode=disable" -t insert-light -c 16 -r 3
Tests available to run
acronis-db-bench --list
Output
========================================================================================================================
Acronis Database Benchmark: version v1.0.0
========================================================================================================================
-- Base tests group -------------------------------------------------------------------------------------------------------------
all : [PMWSCA] : execute all tests in the 'base' group
copy-heavy : [P-W---] : copy a row into the 'heavy' table
copy-light : [P-W---] : copy a row into the 'light' table
copy-medium : [P-W---] : copy a row into the 'medium' table
insert-cti : [PMWSCA] : insert a CTI entity into the 'cti' table
insert-heavy : [PMWS--] : insert a row into the 'heavy' table
insert-heavy-multivalue : [PMWS--] : insert a row into the 'heavy' table using INSERT INTO t (x, y, z) VALUES (..., ..., ...)
insert-heavy-prepared : [PMWS--] : insert a row into the 'heavy' table using prepared statement for the batch
insert-light : [PMWSCA] : insert a row into the 'light' table
insert-light-multivalue : [PMWS-A] : insert a row into the 'light' table using INSERT INTO t (x, y, z) VALUES (..., ..., ...)
insert-light-prepared : [PMWS--] : insert a row into the 'light' table using prepared statement for the batch
insert-medium : [PMWSCA] : insert a row into the 'medium' table
insert-medium-multivalue : [PMWS-A] : insert a row into the 'medium' table using INSERT INTO t (x, y, z) VALUES (..., ..., ...)
insert-medium-prepared : [PMWS--] : insert a row into the 'medium' table using prepared statement for the batch
insert-tenant : [PMWSCA] : insert a tenant into the 'tenants' table
select-1 : [PMWSCA] : just do 'SELECT 1'
select-heavy-last : [PMWS--] : select last row from the 'heavy' table
select-heavy-minmax-in-tenant : [PMWS--] : select min(completion_time_ns) and max(completion_time_ns) value from the 'heavy' table WHERE tenant_id = {}
select-heavy-minmax-in-tenant-and-state : [PMWS--] : select min(completion_time_ns) and max(completion_time_ns) value from the 'heavy' table WHERE tenant_id = {} AND state = {}
select-heavy-rand : [PMWS--] : select random row from the 'heavy' table
select-medium-last : [PMWSCA] : select last row from the 'medium' table with few columns and 1 index
select-medium-rand : [PMWSCA] : select random row from the 'medium' table with few columns and 1 index
update-heavy : [PMWS--] : update random row in the 'heavy' table
update-medium : [PMWS--] : update random row in the 'medium' table
-- Advanced tests group ---------------------------------------------------------------------------------------------------------
bulkupdate-heavy : [PMWS--] : update N rows (see --batch=, default 50000) in the 'heavy' table by single transaction
dbr-bulkupdate-heavy : [PMWS--] : update N rows (see --update-rows-count= ) in the 'heavy' table by single transaction using DBR query builder
insert-json : [PMWS--] : insert a row into a table with JSON(b) column
ping : [PMWSCA] : just ping DB
search-json-by-indexed-value : [PMWS--] : search a row from the 'json' table using some json condition using LIKE {}
search-json-by-nonindexed-value : [PMWS--] : search a row from the 'json' table using some json condition using LIKE {}
select-heavy-for-update-skip-locked : [PMWS--] : do SELECT FOR UPDATE SKIP LOCKED and then UPDATE
select-json-by-indexed-value : [PMWS--] : select a row from the 'json' table by some json condition
select-json-by-nonindexed-value : [PMWS--] : select a row from the 'json' table by some json condition
select-nextval : [PMWS--] : increment a DB sequence in a loop (or use SELECT FOR UPDATE, UPDATE)
update-heavy-partial-sameval : [PMWS--] : update random row in the 'heavy' table putting two values, where one of them is already exists in this row
update-heavy-sameval : [PMWS--] : update random row in the 'heavy' table putting the value which already exists
-- Tenant-aware tests -----------------------------------------------------------------------------------------------------------
select-heavy-last-in-tenant : [PMWS--] : select the last row from the 'heavy' table WHERE tenant_id = {random tenant uuid}
select-heavy-last-in-tenant-and-cti : [PMWS--] : select the last row from the 'heavy' table WHERE tenant_id = {} AND cti = {}
select-heavy-rand-in-tenant-like : [PMWS--] : select random row from the 'heavy' table WHERE tenant_id = {} AND resource_name LIKE {}
select-medium-last-in-tenant : [PMWSCA] : select the last row from the 'medium' table WHERE tenant_id = {random tenant uuid}
-- Blob tests -------------------------------------------------------------------------------------------------------------------
copy-blob : [P-W---] : copy a row with large random blob into the 'blob' table
insert-blob : [PMWSCA] : insert a row with large random blob into the 'blob' table
insert-largeobj : [P-----] : insert a row with large random object into the 'largeobject' table
select-blob-last-in-tenant : [PMWSCA] : select the last row from the 'blob' table WHERE tenant_id = {random tenant uuid}
-- Time-series tests ------------------------------------------------------------------------------------------------------------
insert-ts-sql : [PMWS-A] : batch insert into the 'timeseries' SQL table
select-ts-sql : [PMWS-A] : batch select from the 'timeseries' SQL table
-- Golang DBR query builder tests -----------------------------------------------------------------------------------------------
dbr-insert-heavy : [PMWS--] : insert a row into the 'heavy' table using golang DB query builder
dbr-insert-json : [PMWS--] : insert a row into a table with JSON(b) column using golang DBR driver
dbr-insert-light : [PMWS--] : insert a row into the 'light' table using goland DBR query builder
dbr-insert-medium : [PMWS--] : insert a row into the 'medium' table using goland DBR query builder
dbr-select-1 : [PMWS--] : do 'SELECT 1' using golang DBR query builder
dbr-select-heavy-last : [PMWS--] : select last row from the 'heavy' table using golang DBR driver
dbr-select-heavy-rand : [PMWS--] : select random row from the 'heavy' table using golang DBR query builder
dbr-select-medium-last : [PMWS--] : select last row from the 'medium' table with few columns and 1 index
dbr-select-medium-rand : [PMWS--] : select random row from the 'medium' table using golang DBR query builder
dbr-update-heavy : [PMWS--] : update random row in the 'heavy' table using golang DB driver
dbr-update-medium : [PMWS--] : update random row in the 'medium' table using golang DB driver
-- Advanced monitoring tests ----------------------------------------------------------------------------------------------------
insert-advmagentresources : [P-----] : insert into the 'adv monitoring agent resources' table
insert-advmagents : [P-----] : insert into the 'adv monitoring agents' table
insert-advmarchives : [P-----] : insert into the 'adv monitoring archives' table
insert-advmbackupresources : [P-----] : insert into the 'adv monitoring backup resources' table
insert-advmbackups : [P-----] : insert into the 'adv monitoring backups' table
insert-advmdevices : [P-----] : insert into the 'adv monitoring devices' table
insert-advmresources : [P-----] : insert into the 'adv monitoring resources' table
insert-advmresourcesstatuses : [P-----] : insert into the 'adv monitoring resources statuses' table
insert-advmtasks : [P-----] : insert into the 'adv monitoring tasks' table
insert-advmvaults : [P-----] : insert into the 'adv monitoring vaults' table
select-advmtasks-codeperweek : [P-----] : get number of rows grouped by week+result_code
select-advmtasks-last : [P-----] : get number of rows grouped by week+result_code
Databases symbol legend:
P - PostgreSQL; M - MySQL/MariaDB; W - MSSQL; S - SQLite; C - ClickHouse; A - Cassandra;
Versions
v1.0.0 - initial version v1.1.0 - added Embedded Postgres support (see --embedded-postgres and --ep-* options) v1.1.1 - fix table truncate mode (--use-truncate) on PostgreSQL
# Functions
No description provided by the author
CreateTenantWorker creates a tenant and optionally inserts an event into the event bus.
GetTests returns all tests in the package for execution.
No description provided by the author
Main is the main function of the acronis-db-bench.
NewEventBus creates a new event bus worker instance.
NewTestGroup creates a new test group.
# Constants
MinChunk is a minimum number of rows to process in a single chunk.
TestDelete is a test category for DELETE queries.
TestInsert is a test category for INSERT queries.
TestOther is a test category for other queries.
TestSelect is a test category for SELECT queries.
TestTransaction is a test category for transactions.
TestUpdate is a test category for UPDATE queries.
# Variables
ALL is a list of all supported databases.
EventBusDDL is a DDL for Acronis EventBus tables.
MaxTopics is the maximum number of topics supported by the event bus.
PMWSA is a list of all supported databases except ClickHouse.
RELATIONAL is a list of all supported relational databases.
TestBaseAll tests all tests in the 'base' group.
TestCategories is a list of all test categories.
TestCopyBlob copies a row with large random blob into the 'blob' table.
TestCopyHeavy copies a row into the 'heavy' table.
TestCopyLight copies a row into the 'light' table.
TestCopyMedium copies a row into the 'medium' table.
TestInsertAdvmAgentResources inserts into the 'adv monitoring agent resources' table.
TestInsertAdvmAgents inserts into the 'adv monitoring agents' table.
TestInsertAdvmArchives inserts into the 'adv monitoring archives' table.
TestInsertAdvmBackupResources inserts into the 'adv monitoring backup resources' table.
TestInsertAdvmBackups inserts into the 'adv monitoring backups' table.
TestInsertAdvmDevices inserts into the 'adv monitoring devices' table.
TestInsertAdvmResources inserts into the 'adv monitoring resources' table.
TestInsertAdvmResourcesStatuses inserts into the 'adv monitoring resources statuses' table.
TestInsertAdvmTasks inserts into the 'adv monitoring tasks' table.
TestInsertAdvmVaults inserts into the 'adv monitoring vaults' table.
TestInsertBlob inserts a row with large random blob into the 'blob' table.
TestInsertCTI inserts into the 'cti' table.
TestInsertHeavy inserts a row into the 'heavy' table.
TestInsertHeavyDBR inserts a row into the 'heavy' table using golang DB query builder.
TestInsertHeavyMultivalue inserts a row into the 'heavy' table using INSERT INTO t (x, y, z) VALUES (..., ..., ...) ".
TestInsertHeavyPrepared inserts a row into the 'heavy' table using prepared statement for the batch.
TestInsertJSON inserts a row into a table with JSON(b) column.
TestInsertJSONDBR inserts a row into a table with JSON(b) column using golang DBR driver.
TestInsertLargeObj inserts a row with large random object into the 'largeobject' table.
TestInsertLight inserts a row into the 'light' table.
TestInsertLightDBR inserts a row into the 'light' table using goland DBR query builder.
TestInsertLightMultiValue inserts a row into the 'light' table using INSERT INTO t (x, y, z) VALUES (..., ..., ...).
TestInsertLightPrepared inserts a row into the 'light' table using prepared statement for the batch.
TestInsertMedium inserts a row into the 'medium' table.
TestInsertMediumDBR inserts a row into the 'medium' table using goland DBR query builder.
TestInsertMediumMultiValue inserts a row into the 'medium' table using INSERT INTO t (x, y, z) VALUES (..., ..., ...).
TestInsertMediumPrepared inserts a row into the 'medium' table using prepared statement for the batch.
TestInsertTenant inserts into the 'tenants' table.
TestInsertTimeSeriesSQL inserts into the 'timeseries' SQL table.
TestPing tests just ping DB.
TestRawQuery tests do custom DB query execution.
TestSearchJSONByIndexedValue searches a row from the 'json' table using some json condition using LIKE {}.
TestSearchJSONByNonIndexedValue searches a row from the 'json' table using some json condition using LIKE {}.
TestSelectAdvmTasksCodePerWeek selects number of rows grouped by week+result_code.
TestSelectAdvmTasksLast selects last inserted row from the 'adv monitoring tasks' table.
TestSelectBlobLastTenant is the same as TestSelectBlobLast but with tenant-awareness.
TestSelectHeavyForUpdateSkipLocked selects a row from the 'heavy' table and then updates it.
TestSelectHeavyLast selects last row from the 'heavy' table.
TestSelectHeavyLastDBR selects last row from the 'heavy' table using golang DBR driver.
TestSelectHeavyLastTenant is the same as TestSelectHeavyLast but with tenant-awareness.
TestSelectHeavyLastTenantCTI is the same as TestSelectHeavyLastTenant but with CTI-awareness.
TestSelectHeavyMinMaxTenant selects min(completion_time_ns) and max(completion_time_ns) value from the 'heavy' table WHERE tenant_id = {}.
TestSelectHeavyMinMaxTenantAndState selects min(completion_time_ns) and max(completion_time_ns) value from the 'heavy' table WHERE tenant_id = {} AND state = {}.
TestSelectHeavyRand selects random row from the 'heavy' table.
TestSelectHeavyRandDBR selects random row from the 'heavy' table using golang DBR query builder.
TestSelectHeavyRandTenantLike selects random row from the 'heavy' table WHERE tenant_id = {} AND resource_name LIKE {}.
TestSelectJSONByIndexedValue selects a row from the 'json' table by some json condition.
TestSelectJSONByNonIndexedValue selects a row from the 'json' table by some json condition.
TestSelectMediumLast tests select last row from the 'medium' table with few columns and 1 index.
TestSelectMediumLastDBR tests select last row from the 'medium' table with few columns and 1 index using golang DBR query builder.
TestSelectMediumLastTenant is the same as TestSelectMediumLast but with tenant-awareness.
TestSelectMediumRand selects random row from the 'medium' table with few columns and 1 index.
TestSelectMediumRandDBR selects random row from the 'medium' table using golang DBR query builder.
TestSelectNextVal tests increment a DB sequence in a loop (or use SELECT FOR UPDATE, UPDATE).
TestSelectOne tests do 'SELECT 1'.
TestSelectOneDBR tests do 'SELECT 1' using golang DBR query builder.
TestSelectTimeSeriesSQL selects last inserted row from the 'timeseries' SQL table.
TestTableAdvmAgents is table to store agents inspired by the Event Archive table.
TestTableAdvmAgentsResources is table to store agents inspired by the Event Archive table.
TestTableAdvmArchives is table to store archives inspired by the Event Archive table.
TestTableAdvmBackupResources is table to store backups inspired by the Event Archive table.
TestTableAdvmBackups is table to store backups inspired by the Event Archive table.
TestTableAdvmDevices is table to store devices inspired by the Event Archive table.
TestTableAdvmResources is table to store resources.
TestTableAdvmResourcesStatuses is table to store resources statuses inspired by the Event Archive table.
TestTableAdvmTasks is table to store tasks.
TestTableAdvmVaults is table to store vaults inspired by the Event Archive table.
TestTableBlob is table to store blobs.
TestTableCTIEntities is table to store CTI entities.
TestTableHeavy is table to store heavy objects.
TestTableJSON is table to store JSON data.
TestTableLargeObj is table to store large objects.
TestTableLight is table to store light objects.
TestTableMedium is table to store medium objects.
TestTables is a map of all test tables available for the benchmark run.
TestTableTenants is table to store tenants.
TestTableTenantsClosure is table to store tenants closure.
TestTableTimeSeriesSQL is table to store time series data.
TestUpdateHeavy updates random row in the 'heavy' table.
TestUpdateHeavyBulk updates N rows (see --batch=, default 50000) in the 'heavy' table by single transaction.
TestUpdateHeavyBulkDBR updates N rows (see --batch=, default 50000) in the 'heavy' table by single transaction using DBR query builder.
TestUpdateHeavyDBR updates random row in the 'heavy' table using golang DBR query builder.
TestUpdateHeavyPartialSameVal updates random row in the 'heavy' table putting two values, where one of them is already exists in this row.
TestUpdateHeavySameVal updates random row in the 'heavy' table putting the value which already exists.
TestUpdateMedium updates random row in the 'medium' table.
TestUpdateMediumDBR updates random row in the 'medium' table using golang DBR query builder.
Version is a version of the acronis-db-bench.
# Structs
BenchOpts is a structure to store all the benchmark options.
CTIOpts is a structure to store all the CTI options.
DBTestData is a structure to store all the test data.
DBWorkerData is a structure to store all the worker data.
EmbeddedPostgresOpts is a structure to store all the embedded postgresql options.
EventBus is a helper structure to simulate event bus.
EventData is a helper structure to simulate event data.
EventTopic is a helper structure to simulate event topics.
EventType is a helper structure to simulate event types.
TestcaseOpts is a structure to store all the test case options.
TestDesc describes a test.
TestGroup is a group of tests.
TestOpts is a structure to store all the test options.
TestTable represents table to be used in tests and benchmarks.
# Type aliases
CreateQueryPatchFunc is a function to patch create query for specific DB.