# Generate Workloads for CockroachDB

# Introduction

[**CockroachDB Workload**](https://github.com/cockroachlabs-field/cockroachdb-workload) is an easy-to-use command line tool for generating load against CockroachDB.

It includes the following workloads:

* **ledger** - Financial ledger use case using the double-entry principle
    
* **order** - Purchase orders as part of an e-commerce use case
    
* **outbox** - Simulates the transactional outbox and inbox patterns
    

This tool provides an interactive shell for running workloads and monitoring progress and performance. Each workload has an `init` command to set up the test fixture and one or more `run` commands to execute the actual workload for a given period of time.

## Source Code

The source code for the workload tool can be found on [**GitHub**](https://github.com/cockroachlabs-field/cockroachdb-workload).

# Project Setup

The project is packaged as a single executable JAR file and runs on any platform for which there is a Java 17+ (LTS) runtime.

## Prerequisites

* [**CockroachDB**](https://www.cockroachlabs.com/docs/v22.2/start-a-local-cluster), with a trial enterprise [**license**](https://www.cockroachlabs.com/docs/stable/licensing-faqs.html#obtain-a-license).
    
* Linux / macOS
    
* Java 17
    
    * [https://openjdk.org/projects/jdk/17/](https://openjdk.org/projects/jdk/17/)
        
    * [https://www.oracle.com/java/technologies/downloads/#java17](https://www.oracle.com/java/technologies/downloads/#java17)
        
* Maven 3+ (optional, embedded wrapper available)
    
    * [https://maven.apache.org/](https://maven.apache.org/)
        

## Setup CockroachDB

Create a local cluster of at least three nodes:

```bash
cockroach start --port=26257 --http-port=8080 --advertise-addr=localhost:26257 --join=localhost:26257 --insecure --store=datafiles/n1 --background

cockroach start --port=26258 --http-port=8081 --advertise-addr=localhost:26258 --join=localhost:26257 --insecure --store=datafiles/n2 --background

cockroach start --port=26259 --http-port=8082 --advertise-addr=localhost:26259 --join=localhost:26257 --insecure --store=datafiles/n3 --background

cockroach init --insecure --host=localhost:26257
```

Next, set up a database called `workload`:

```bash
cockroach sql --insecure --host=localhost:26257 -e "CREATE database workload"
```

## Setup Workload

### Install the JDK

Install the JDK (Ubuntu example):

```bash
sudo apt-get install openjdk-17-jdk
```

Confirm the installation by running:

```bash
java --version
```

### Clone the project

```shell
git clone git@github.com:cockroachlabs-field/cockroachdb-workload.git
cd cockroachdb-workload
```

### Build the executable jar

```shell
chmod +x mvnw
./mvnw clean install
```

# Usage

Start the shell with:

```shell
java -jar target/workload.jar --help
```

Type `help` for additional CLI guidance.

## Ledger Workload

The **Ledger** workload demonstrates a simple financial accounting system using the double-entry bookkeeping principle. The concept is to move funds between accounts using balanced, multi-legged transactions at a high frequency. As a financial system, it will conserve money at all times and provide an audit trail for all transactions performed towards the accounts.

Start the workload shell with:

```shell
java -jar target/workload.jar ledger
```

Then initialize the workload by creating an account plan:

```apache
init --help
# or just type 'help'
```

The commands can run concurrently. The "transfer" command will move funds between random accounts and the "balance" command will just read account balances.

```apache
transfer --help
```

> **Hint:** The ledger workload is implemented using both plain JDBC and JPA with Hibernate as ORM provider. You can switch between these implementations using the `--jpa` option (where JDBC is default).

## Order Workload

The **Order** workload is a basic purchase order creation and reading workload. Order creation and reading workloads can run concurrently.

Start the workload with:

```apache
java -jar target/workload.jar order
# Then in the shell:
init
run
```

## Outbox Workload

The Outbox workload simulates the transactional [outbox](https://blog.cloudneutral.se/cdc-transformations-and-the-outbox-pattern-with-cockroachdb) and [inbox](https://blog.cloudneutral.se/using-the-inbox-pattern-with-cockroachdb) design patterns. It writes event records to an outbox table that has TTL and CDC enabled (optional). It can be used to evaluate creating a CDC-based data pipeline and/or the TTL mechanism.

Start the workload with:

```apache
java -jar target/workload.jar outbox
# In the shell:
init
run
```

# Extending

It's fairly simple to extend this tool with additional workloads. There are skeleton beans available in the "`io.cockroachdb.workload.template"` namespace that can be copied to a new workload package.

The main steps include:

* Implement an `init` method for setting up the test fixture
    
    * Add drop and create SQL scripts if needed
        
* Implement a `run` method for running the workload for a defined period
    
    * Choose between JDBC or JPA or any other data access library
        

# Conclusion

In this article, we looked at a simple workload tool for CockroachDB using the Java stack. It can be used to send semi-realistic SQL traffic for pure testing and load-testing purposes. It's also quite extendable.