Investigating CockroachDB Efficiency and Stability With 22k Databases and 300k Tables

1. Create a 9-node CockroachDB cluster.
2. Load x instances of the TPC-C database.
3. Use the built-in cockroach workload command and a script to execute x workloads against x number of databases.
4. Monitor stability and performance throughout the exercise.

For this experiment, I created a cluster in Google Cloud Platform (GCP) with the below specs.

I won’t be doing a how-to on the CockroachDB setup in this blog, but below are some links to get you started. I ended up deploying all my nodes in a single AZ since I didn’t care about survivability. I also did not create a load balancer in GCP, but rather used HaProxy on the VM I used to run a workload.

There are a few cluster settings I enabled to improve performance. The main setting we’ll keep throughout the duration of the cluster is:

set cluster setting spanconfig.host_coalesce_adjacent.enabled = true;

This setting allows multiple tables per range, rather than a table per range, which can come in handy when adding as many tables as I plan to.

Load Databases/Tables

The base command for initializing a schema is quite simple with the built-in command. It looks like the following, where localhost is ideally a load balancer instead.

cockroach workload init tpcc 'postgresql://root@localhost:26257?sslmode=disable'

This command will create 9 tables in a database named tpcc and load 1 warehouse worth of data which comes out to ~200MB. The number of warehouses is configurable via a flag, but we’ll keep it with the default of 1.

Once the data is compressed and replicated it comes out to around 70MB in CockroachDB. We’ll have to keep this number in mind since we currently have 3.1TB of storage available. The final database result for 1 warehouse looks like this.

There are a few things we need to do to expand on the base command. The first one should be obvious: we can’t use the name tpcc if we are loading tens of thousands of databases.

Depending on how much time you have, this second tip may be equally important. The workload init command does an INSERT by default. While this is ok and may be something you want to test, I’d like to switch to IMPORT and make things a bit faster.

With these two thoughts in mind, our base command now looks something more like this:

cockroach workload init tpcc --db database_$i --data-loader import 'postgresql://root@localhost:26257?sslmode=disable'

Throw in a Bash Script

You’ll notice the command above specifies database_$i since we want to load i instances. Feel free to get as fancy as you want here with your scripting abilities, but in its simplest form, we can do something like the following.

#!/usr/bin/env bash
for i in 1..20000
do    cockroach workload init tpcc --db test$i --data-loader import 'postgresql://root@localhost:26257?sslmode=disable'
done

This will admittedly take a while as we are loading 20,000 databases, 180,000 tables, and ~1.5TB of data (after compression). As I said, feel free to write a more impressive script to parallelize this logic and speed up the process.

As I discussed above, 20k iterations solely will get us to 180k tables. I would prefer to get nearer to 300k tables managed, however I am not tremendous involved in including extra knowledge as we’re already at 50% capability.

At this level, I added ~700 extra databases, every with 150 tables. This schema was fairly completely different than the TPC-C tables already loaded. How, or if, you selected to get to this increased variety of tables, I am going to go away it as much as you.

Only a fast affirmation of the cluster measurement and objects added!

First, we’ll see the whole databases rely is simply over 22.5k. Then we’ll do a rely on the descriptors. Descriptors embrace tables, amongst just a few different issues.

Right here is the Cluster Overview. 

Working a Workload

Now that we have now every part loaded, we will undergo the same course of to run workloads in opposition to a few of these an identical databases. Whereas I haven’t got the infrastructure in place to run 22.5k concurrent TPC-C workloads, hopefully, I can get one thing respectable going.

Identical to the preliminary load, Cockroach makes really operating the load easy as effectively. The instance under will run a TPC-C workload for 60 minutes.

cockroach workload run tpcc --duration=60m 'postgresql://root@localhost:26257?sslmode=disable'

Increasing the Base Command

There are just a few modifications I made right here as effectively which finally ends up being fairly just like the preliminary load.

Now that we have now our base command, we will create one other script to loop by means of and begin these processes. I used a separate digital machine, outdoors of the cluster, and with HaProxy put in. FYI, decrease these numbers fairly a bit if you do not have a strong VM. I used to be utilizing n2-standard-32.

Under is the straightforward script I used that loops by means of beginning the workload 500 occasions.

#!/usr/bin/env bash
for i in 1..500
do     nohup cockroach workload run tpcc --db database_$i --duration=60m --conns=1 --display-every=1m 'postgres://root@localhost:26257?sslmode=disable' > outcomes/$i.txt 2>&1 &
carried out

The code on the finish throws the output of every workload into its personal file inside a /outcomes folder.

For my precise checks, I leveraged the next syntax so I wasn’t at all times deciding on simply the primary 500. (I notice there is a semi-decent likelihood for dupes with this logic).

nohup cockroach workload run tpcc --db database_$(( ( RANDOM % 20000 ) + 1 )) --conns=1

Under are the outcomes for workloads of sizes 500, 750, and 1000. Every ran for 1 hour.

General I believed CockroachDB dealt with these checks effectively whereas sustaining a TPC-C efc proportion of 97% all through all checks. Mileage will differ relying on the variety of nodes and vCPU, however this might be a viable choice if the present answer right this moment is making a bodily database per tenant.

There are some things I would develop on when testing out this mannequin extra:

1. Influence and pace of creating a schema change throughout every database
2. Implementing role-based entry to every database
3. QPS in opposition to a non-TPC-C workload
4. Much more tables! Though not talked about within the weblog, I did check 200k vs 300k and noticed no variations in efficiency.