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:
- CockroachDB
- YCSB
- JDBC Driver
- 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
