≈
Battery is a simple and flexible SQL database load testing tool. A load test is composed by a set of scripts using either a custom DSL or plain SQL, or a combination thereof to simulate some actual production database workload.
A load test runs through a scenario, which is a sequence of phases that ramp up threads to simulate virtual users running through some SQL workflow. A scenario can be much anything, such as reading a set of rows, performing an aggregation query or updating and inserting rows.
Battery instruments and collects execution times, error rates and other metrics visualized in its web UI and exposed via a prometheus endpoint. It also provides a REST API for automation and for coordinating distributed load test, where multiple instances on different hosts can generate load in the aggregate towards a target database.
Dashboard page in the web UI, for illustration:
Workload statistics:
This tool is not supported by Cockroach Labs. Use of this tool is entirely at your own risk and Cockroach Labs makes no guarantees or warranties about its operation.
See MIT for terms and conditions.
Battery is configured using a YAML file that defines the test structure, along with SQL scripts and/or battery scripts to run different preparation steps and the actual load generating steps.
All load test configuration properties are specified in a local
config/XX/application-XX.yml file where XX is
some arbitrary profile name. It overrides the baseline configuration
in application.yml.
See the config/ directory to get an idea of the layout of load test configurations.
A SQL file is simply a multi-statement SQL file that can run transactional or implicitly with auto commits (per statement). Parameter placeholders are also supported, see more below for details.
Battery script is a custom-made DSL that supports variables, expressions, flow control structures and asynchronous fork/join statements, along with a function library for executing arbitrary SQL statements and generating random data.
Typically, you'd use it to craft workload dynamics that mimics some actual application behavior, which is hard to do with just plain SQL file templates. Rather than resorting to typical programming language idioms to create such dynamics, this scripting language offers a focused and user-friendly middle ground.
See the battery script tutorial for a language introduction.
Basic example to insert 10 rows:
// Loop 10 times and insert one row per cycle
for y from 1 to 10 {
jdbc.update("insert into account (id,city,balance) values(unordered_unique_rowid(),?,?)",
[randomWord(10 + y), randomDouble(10.00,100.00)]);
}Another example a of a decomposed UPSERT with execution branch logic (i.e. different paths depending
on some pivot):
ids = jdbc.queryForList("select id from account limit 100");
// Pick random id and pass as parameter
id = random(ids).get("id");
a = jdbc.queryForMap("select * from account where id=?",[id]);
// Either insert or update
if (a.isEmpty()) {
jdbc.update("insert into account (id,city,balance) values(unordered_unique_rowid(),?,?)",
[randomWord(12),randomDouble(10.00,100.00)]);
} else {
jdbc.update("update account set balance=balance+?::decimal where id=?",
[randomDouble(1.00,100.00), a.get("id") ]);
}Another more complex example with parallel fork and join operations:
f1 = fork {
id = jdbc.update("insert into account (id,name,balance) values(unordered_unique_id(),?,?) returning id",
[randomWord(12), randomDouble(10.00, 100.00)] );
return id;
};
f2 = fork {
id = jdbc.update("insert into account (id,name,balance) values(unordered_unique_id(),?,?) returning id",
[randomWord(12), randomDouble(10.00, 100.00)] );
return id;
};
f3 = fork {
id = jdbc.update("insert into account (id,name,balance) values(unordered_unique_id(),?,?) returning id",
[randomWord(12), randomDouble(10.00, 100.00)] );
return id;
};
// Wait for f1-f3 to run to completion
join [f1, f2, f3];
ids = [f1.get(), f2.get(), f3.get()];
jdbc.update("insert into audit_log (a,b,c) values ('inserted','account',?)", [ids]);The load test configuration YAML file must include a battery prefix section that describe
the structure of the load test. This includes any before steps, after steps, ramping phases
and scenarios with steps.
battery:
baseDir: config/XX/
before:
..
after:
..
phases:
..
scenarios:
..In addition, you can override any baseline setting in application.yml. For example the datasource and concurrency settings:
# override datasource and concurrency settings
spring:
datasource:
url: jdbc:postgresql://bigone.aws-eu-north-1.cockroachlabs.cloud:26257/battery?sslmode=require
username: craig
password: ..
concurrency:
concurrencyLimit: -1
corePoolSize: 2
queueCapacity: 32
threadType: virtual
battery:
...Before steps are optional preparation steps executed at one time to allow for things like schema creation and initial test data loading. A before step either executes a SQL script or a battery script, which in turn can execute a sequence of SQL statements.
Script variables can be passed along to scenario scripts by using the capture flag.
battery:
phases: ..
before:
steps:
- script: create-schema.sql
- script: insert-users.sql
- script: load-users.b
capture: trueAfter steps are optional cleanup steps executed at most one time after a load test scenario has
been ramped up. Notice that after steps are invoked potentially before all workloads have completed
unless awaitCompletion is true.
battery:
phases: ..
before: ..
scenarios: ..
after:
awaitCompletion: true
steps:
- name: Print Report
script: report.sqlA load test scenario is composed of workloads (next section) that run concurrently to simulate virtual users.
The phases section defines at which rate these virtual user are created or ramped up. It follows a linear
progression curve to simulate phases, such as a warmup phase, a sustained steady state phase and a final
stress phase.
The example below defines 3 phases: warmup, steady and sustained. The warmup phase runs for a total
of 60 seconds with an arrival rate of 2 up to a limit of 30 virtual users. This translates to approximately
2 threads created at 2 second intervals.
The steady state phase ramps for a total of 90s, starting 10 threads approximately
every 9:th second (interval formula is: duration / (rampTo / rampRate)).
The last sustained state doesn't start any virtual users but simply pauses for 60s to simulate a sustained steady state until the test ends.
battery:
phases:
- name: warmup
duration: 60s
rampRate: 2
rampTo: 30
- name: steady
duration: 90s
rampRate: 10
rampTo: 100
- name: sustained
duration: 60sScenarios define the set of SQL operations (encapsulated by SQL or battery scripts) that should be executed concurrently for a specified duration. A scenario has one or more steps that are executed in sequence but also concurrently in scope of a virtual user.
The concurrency level is determined by the phases. The steps to execute are defined by scenarios and only one scenario can run at any given time. A scenario step either executes a SQL script or a battery script which in turn can execute a sequence of SQL statements. Variables can be passed to both scripts from the "before" steps. Random scenarios can be picked by using a weight factor.
battery:
phases: ..
before: ..
scenarios:
- name: Read Users
duration: 2m
weight: 1
steps:
- script: select-users.b
- name: Update Users
duration: 3m
weight: 2
steps:
- script: update-users.b
- name: Insert Users
duration: 4m
weight: 3
steps:
- script: insert-users.b- MacOS (main platform)
- Linux
- JDK 21
- Databases (bundled JDBC drivers)
- CockroachDB v25.2+
- PostgreSQL 9.x+
- MySQL 8.x+
- Oracle
Things you need to build and run battery locally.
- Java 21+ JDK
- Git
MacOS (using sdkman):
curl -s "https://get.sdkman.io" | bash
sdk list java
sdk install java 21.0 (use TAB to pick edition)
Ubuntu:
sudo apt-get install openjdk-21-jdk
The following instructions assumes MacOS and brew.
See start a local cluster for setup instructions. You can also use CockroachDB Cloud basic, standard or advanced. Whether a license is needed or not depends on if your SQL schema or statements uses enterprise features.
Install PSQL on MacOS using brew:
brew install postgresql
Starting:
brew services start postgresql@14
Create the database:
psql postgres
$ create database test;
$ quit
Stopping:
brew services stop postgresql@14
Install MySQL on MacOS using brew:
brew install mysql
Starting:
brew services start colima
brew services start mysql
Create the database:
mysql -u root
$ create database test;
$ quit
Stopping:
brew services stop mysql
Install Oracle 23 Free on MacOS using brew:
brew install colima
brew install docker
colima start --arch x86_64 --memory 4
Starting:
docker run -d -p 1521:1521 -e ORACLE_PASSWORD=root -v oracle-volume:/opt/oracle/oradata gvenzl/oracle-free
Stopping:
docker ps -al
docker stop $(docker ps -a -q)
Instructions for building the project locally.
git clone git@github.com:kai-niemi/battery.git && cd battery
chmod +x mvnw
./mvnw -Pdistribution clean install
Battery is a single server component (executable jar) available at target/battery.jar.
With the distribution Maven profile activated, it also provides a target/battery-<version>.tar.gz assembly.
Starting battery requires at least one profile name that denotes which configuration to use.
This command will start the battery web server with an internal interactive shell. You can either use the shell, web UI or REST API to run load tests.
./run-server.sh
To run shell commands in headless mode, first add the commands to a plain text file. For example:
echo "help" > cmd.txt
echo "version" >> cmd.txt
echo "quit" >> cmd.txt
Then start the server in the background by passing a command file name as argument with a @ prefix:
./start-server.sh <profile> @cmd.txt
This command stops the server if it's running:
./stop-server.sh