With CockroachDB, all nodes are equal.  Each CockroachDB node can run any SQL (DCL, DDL, DML, and TCL).  This greatly simplifies the load balancer configuration used when connecting to CockroachDB.  The simplicity opens up a plethora of load balancer options. 

Three classes of load balancer options are application-based, driver, and external load balancers.  As a general rule, an application-based load balancer could be a fit for specific situations. The driver load balancer is simple to use and quick to get started.  External load balancers take a bit more setup but will provide greater flexibility.  

The choice of infrastructure impacts performance greatly.  The choice of load balancer impacts performance greatly as well.  We will save a performance comparison for another post.

For a simple demonstration, we will use open-source YCSB to show how the three classes of load balancer can be used with CockroachDB.   YCSB has a built-in load balancer feature. JDBC driver used by YCSB JDBC DB binding has a load balancer.  External load balancer, HAProxy, configuration is supported by CockroachDB.   

Load Balancer Topology

The below topology diagrams show two basic differences in the load balancer topology.  The diagram on the left shows the application and JDBC load balancer. The diagram on the right shows an external HAProxy load balancer.  Other documented external load balancers can substitute the HAProxy in the right diagram.    

Application and JDBC Load Balancer	External HAProxy Load Balancer

Load Balancer Configuration

Application-based load balancing on YCSB 0.17.0 via PR #1233 will be used.  The below syntax shows the YCSB based load balancer used via the YCSB parameter -p db.url 

jdbc:postgresql://host1:port1/database;jdbc:postgresql://host2:port2/database

Postgres JDBC driver load balancing feature is called Connection Fail-over.  CockroachDB is a Postgres wire protocol compatible database and as such, this feature can be used.  The below syntax shows the JDBC driver based load balancer used via the YCSB parameter -p db.url

jdbc:postgresql://host1:port1,host2:port2/database

An external load balancer such as HAProxy uses a single virtual IP (VIP) that is used by the load balancer to target multiple nodes.  CockroachDB has a command to generate the required HAProxy configuration file. The below syntax shows the single VIP used via the YCSB parameter -p db.url 

jdbc:postgresql://host1:port1/database

Load Balancer Usage Comparison

Each CockroachDB node can run any SQL (DCL, DDL, DML, and TCL).  YCSB is used to demonstrate how any CockroachDB node can accept DDL (create) and DML (insert, update, select).   To prove this:

• YCSB site.ycsb.db.JdbcDBCreateTable will be used to create the table (DDL) load balanced
• YCSB load will be used to insert the data (DML) load balanced
• YCSB run workload A will be used to update and select (DML) load balanced

Component Install and Setup

Below is an instruction that can be used to demonstrate the techniques on a laptop (tested on OSX).  The general steps are to setup:

1. CockroachDB 
2. YCSB
3. JDBC Driver
4. HAProxy 

CockroachDB

Below is a copy and paste of Start a Local Cluster (Insecure) for convenience.

cockroach start \
--insecure \
--store=node1 \
--listen-addr=localhost:26257 \
--http-addr=localhost:8080 \
--join=localhost:26257,localhost:26258,localhost:26259 \
--background

cockroach start \
--insecure \
--store=node2 \
--listen-addr=localhost:26258 \
--http-addr=localhost:8081 \
--join=localhost:26257,localhost:26258,localhost:26259 \
--background

cockroach start \
--insecure \
--store=node3 \
--listen-addr=localhost:26259 \
--http-addr=localhost:8082 \
--join=localhost:26257,localhost:26258,localhost:26259 \
--background

YCSB

Copy and paste of YCSB Install with modifications to install only JDBC DB Binding. 

curl --location https://github.com/brianfrankcooper/YCSB/releases/download/0.17.0/ycsb-jdbc-binding-0.17.0.tar.gz | gzip -dc - | tar -xvf -
export YCSB=~/ycsb-jdbc-binding-0.17.0

Postgres JDBC

Place the Postgres JDBC driver in the YCSB lib directory.  

cd $YCSB/lib; curl -O --location https://jdbc.postgresql.org/download/postgresql-42.2.4.jar

HAProxy

The original Cockroach Labs instruction with added steps to change the VIP port number to 26256 for the purpose of the demonstration.

cockroach gen haproxy --insecure
sed -i.bak -e 's/bind :26257/bind :26256/g' haproxy.cfg
haproxy -f haproxy.cfg &

Running Tests

Below are steps for the application, JDBC and HAproxy load balancers.  Please note the following:

• db.url is highlighted to show how each balancer is used
• threadcount=3 to match the number of CockroachDB nodes.  This will ensure there is one connection per CockroachDB used by a round-robin connection.  The application load balancer will automatically create one thread per node.
• recordcount=1000000 will create 1 million rows
• requestdistribution=uniform will ensure all of the nodes get used equally
• operationcount=300000 will run YCSB workload A this many times

Application Load Balancer

export YCSB=~/ycsb-jdbc-binding-0.17.0

cd $YCSB

cockroach sql --insecure -e "drop table if exists usertable"

java -cp lib/jdbc-binding-0.17.0.jar:lib/postgresql-42.2.4.jar site.ycsb.db.JdbcDBCreateTable -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26257/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true;jdbc:postgresql://127.0.0.1:26259/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true;jdbc:postgresql://127.0.0.1:26261/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true" -p db.user=root -p db.passwd="" -n usertable 

bin/ycsb load jdbc -s -P workloads/workloada -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26257/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true;jdbc:postgresql://127.0.0.1:26259/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true;jdbc:postgresql://127.0.0.1:26261/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true" -p db.user=root -p db.passwd="" -p db.batchsize=128  -p jdbc.fetchsize=10 -p jdbc.autocommit=true -p jdbc.batchupdateapi=true -p db.batchsize=128 -p recordcount=1000000  

bin/ycsb run jdbc -s -P workloads/workloada -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26257/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true;jdbc:postgresql://127.0.0.1:26259/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true;jdbc:postgresql://127.0.0.1:26261/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true" -p db.user=root -p db.passwd="" -p db.batchsize=128  -p jdbc.fetchsize=10 -p jdbc.autocommit=true -p jdbc.batchupdateapi=true -p db.batchsize=128 -p recordcount=1000000 -p requestdistribution=uniform -p operationcount=300000 

JDBC Load Balancer

export YCSB=~/ycsb-jdbc-binding-0.17.0

cd $YCSB

cockroach sql --insecure -e "drop table if exists usertable"

java -cp lib/jdbc-binding-0.17.0.jar:lib/postgresql-42.2.4.jar site.ycsb.db.JdbcDBCreateTable -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26261,127.0.0.1:26259,127.0.0.1:26257/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true&loadBalanceHosts=true" -p db.user=root -p db.passwd="" -n usertable

bin/ycsb load jdbc -s -P workloads/workloada -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26261,127.0.0.1:26259,127.0.0.1:26257/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true&loadBalanceHosts=true" -p db.user=root -p db.passwd="" -p db.batchsize=128  -p jdbc.fetchsize=10 -p jdbc.autocommit=true -p jdbc.batchupdateapi=true -p db.batchsize=128 -p recordcount=1000000 -p threadcount=3

bin/ycsb run jdbc -s -P workloads/workloada -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26261,127.0.0.1:26259,127.0.0.1:26257/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true&loadBalanceHosts=true" -p db.user=root -p db.passwd="" -p db.batchsize=128  -p jdbc.fetchsize=10 -p jdbc.autocommit=true -p jdbc.batchupdateapi=true -p db.batchsize=128 -p recordcount=1000000 -p requestdistribution=uniform -p operationcount=300000 -p threadcount=3

HA Proxy Load Balancer

export YCSB=~/ycsb-jdbc-binding-0.17.0

cd $YCSB

cockroach sql --insecure -e "drop table if exists usertable"

java -cp lib/jdbc-binding-0.17.0.jar:lib/postgresql-42.2.4.jar site.ycsb.db.JdbcDBCreateTable -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26256/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true&loadBalanceHosts=true" -p db.user=root -p db.passwd="" -n usertable

bin/ycsb load jdbc -s -P workloads/workloada -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26256/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true&loadBalanceHosts=true" -p db.user=root -p db.passwd="" -p db.batchsize=128  -p jdbc.fetchsize=10 -p jdbc.autocommit=true -p jdbc.batchupdateapi=true -p db.batchsize=128 -p recordcount=1000000 -p threadcount=3

bin/ycsb run jdbc -s -P workloads/workloada -p db.driver=org.postgresql.Driver -p db.url="jdbc:postgresql://127.0.0.1:26256/defaultdb?autoReconnect=true&sslmode=disable&ssl=false&reWriteBatchedInserts=true&loadBalanceHosts=true" -p db.user=root -p db.passwd="" -p db.batchsize=128  -p jdbc.fetchsize=10 -p jdbc.autocommit=true -p jdbc.batchupdateapi=true -p db.batchsize=128 -p recordcount=1000000 -p requestdistribution=uniform -p operationcount=300000 -p threadcount=3