Low Latency Reads and Writes in a Multi-Region Cluster

1. Simulate a multi-region CockroachDB cluster on your local machine.
2. Run a workload on the cluster using our fictional vehicle-sharing application called MovR.
3. See the effects of network latency on SQL query performance in the default (non-multi-region) configuration.
4. Configure the cluster for good multi-region performance by issuing SQL statements that choose the right survival goals and table localities.

​

Considerations

• To show the relative magnitude of the performance improvements to expect when you configure a multi-region cluster correctly.
• To be as easy to try as possible with minimal configuration and setup.

​

Before you begin

• A basic understanding of the MovR application
• Docker installed on the local machine

​

A basic understanding of the MovR application

• europe-west1, covering the cities of Amsterdam, Paris, and Rome.
• us-east1, covering the cities of New York, Boston, and Washington, D.C.
• us-west1, covering the cities of Los Angeles, San Francisco, and Seattle.

​

The MovR schema

​

Step 1. Simulate a multi-region cluster

$ cockroach demo --global --nodes 9 --no-example-database --insecure

• Viewing the DB Console: http://127.0.0.1:8080.
• Connecting to the database from a SQL shell or a programming language: postgres://root@127.0.0.1:26257?sslmode=disable.

#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB cluster of 9 nodes.
#
# This demo session will attempt to enable enterprise features
# by acquiring a temporary license from Cockroach Labs in the background.
# To disable this behavior, set the environment variable
# COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true.
#
# Reminder: your changes to data stored in the demo session will not be saved!
#
# Connection parameters:
#   (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
#   (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
#   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257
#
# To display connection parameters for other nodes, use \demo ls.
#
# The user "demo" with password "demo76950" has been created. Use it to access the Web UI!
#
# Server version: CockroachDB CCL v21.1.2 (x86_64-apple-darwin19, built 2021/06/07 18:13:04, go1.15.11) (same version as client)
# Cluster ID: bfd9fc91-69bd-4417-a2f7-66e556bf2cfd
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
#

​

Step 2. Determine node locations

1. The output of the \demo ls from the SQL shell, which shows the TCP ports on the local machine that we will connect to from the MovR application.
2. The node IDs shown on the Network Latency Page.

> \demo ls

node 1:
  (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257

node 2:
  (webui)    http://127.0.0.1:8081/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26258?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26258

node 3:
  (webui)    http://127.0.0.1:8082/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26259?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26259

node 4:
  (webui)    http://127.0.0.1:8083/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26260?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26260

node 5:
  (webui)    http://127.0.0.1:8084/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26261?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26261

node 6:
  (webui)    http://127.0.0.1:8085/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26262?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26262

node 7:
  (webui)    http://127.0.0.1:8086/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26263?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26263

node 8:
  (webui)    http://127.0.0.1:8087/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26264?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26264

node 9:
  (webui)    http://127.0.0.1:8088/demologin?password=demo76950&username=demo
  (sql)      postgres://demo:demo76950@127.0.0.1:26265?sslmode=require
  (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26265

​

Step 3. Load and run MovR

1. In the SQL shell, create the movr database:
   CREATE DATABASE IF NOT EXISTS movr;
   
2. Open a second terminal and run the command below to populate the MovR data set. The options are mostly self-explanatory. We limit the application to 1 thread because using multiple threads quickly overloads this small demo cluster’s ability to ingest data. As a result, loading the data takes about 90 seconds on a fast laptop.
       docker run -it --rm cockroachdb/movr:20.11.1 \
           --app-name "movr-load" \
           --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" \
           --num-threads 1 \
           load \
           --num-users 100 \
           --num-rides 100 \
           --num-vehicles 10 \
           --city "boston" \
           --city "new york" \
           --city "washington dc" \
           --city="amsterdam" \
           --city="paris" \
           --city="rome" \
           --city="los angeles" \
           --city="san francisco" \
           --city="seattle"
   
   [INFO] (MainThread) connected to movr database @ postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable
   [INFO] (MainThread) Loading single region MovR
   [INFO] (MainThread) initializing tables
   [INFO] (MainThread) loading cities ['boston', 'new york', 'washington dc', 'amsterdam', 'paris', 'rome', 'los angeles', 'san francisco', 'seattle']
   [INFO] (MainThread) loading movr data with ~100 users, ~10 vehicles, ~100 rides, ~1000 histories, and ~1000 promo codes
   [INFO] (Thread-1  ) Generating user data for boston...
   ... output snipped ...
   [INFO] (Thread-1  ) Generating 1000 promo codes...
   [INFO] (MainThread) populated 9 cities in 86.986230 seconds
   
3. In the same terminal window, run the following command:
   docker run -it --rm cockroachdb/movr:20.11.1 \
       --app-name "movr-us-east" \
       --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" \
       run \
       --city="boston" \
       --city="new york" \
       --city="washington dc"
   
   [INFO] (MainThread) connected to movr database @ postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable
   [INFO] (MainThread) simulating movr load for cities ['boston', 'new york', 'washington dc']
   [INFO] (MainThread) warming up....
   [INFO] (MainThread) running single region queries...
   ...
   
4. Open a third terminal and run the following command:
   docker run -it --rm cockroachdb/movr:20.11.1 \
       --app-name "movr-us-west" \
       --url "postgres://root@docker.for.mac.localhost:26260/movr?sslmode=disable" \
       run \
       --city="los angeles" \
       --city="san francisco" \
       --city="seattle"
   
   [INFO] (MainThread) connected to movr database @ postgres://root@docker.for.mac.localhost:26260/movr?sslmode=disable
   [INFO] (MainThread) simulating movr load for cities ['los angeles', 'san francisco', 'seattle']
   [INFO] (MainThread) warming up....
   [INFO] (MainThread) running single region queries...
   
5. Open a fourth terminal and run the following command:
   docker run -it --rm cockroachdb/movr:20.11.1 \
      --app-name "movr-eu-west" \
      --url "postgres://root@docker.for.mac.localhost:26264/movr?sslmode=disable" \
      run \
      --city="amsterdam" \
      --city="paris" \
      --city="rome"
   
   [INFO] (MainThread) connected to movr database @ postgres://root@docker.for.mac.localhost:26264/movr?sslmode=disable
   [INFO] (MainThread) simulating movr load for cities ['amsterdam', 'paris', 'rome']
   [INFO] (MainThread) warming up....
   [INFO] (MainThread) running single region queries...
   ...
   

​

Step 4. Check service latency

• Round-trip latency between N2 in europe-west1 and N3 in us-east1 is 87 ms.
• Round-trip latency between N2 in europe-west1 and N4 in us-west1 is 196 ms.

​

Step 5. Execute multi-region SQL statements

• The database’s available regions.
• Which data should be optimized for access from which region.

​

Configure database regions

USE movr;

ALTER DATABASE movr PRIMARY REGION "us-east1";
ALTER DATABASE movr ADD REGION "europe-west1";
ALTER DATABASE movr ADD REGION "us-west1";

​

Configure table localities

​

Configure GLOBAL tables

ALTER TABLE promo_codes SET locality GLOBAL;

ALTER TABLE user_promo_codes
  ADD CONSTRAINT user_promo_codes_code_fk
    FOREIGN KEY (code)
    REFERENCES promo_codes (code)
    ON UPDATE CASCADE;

​

Configure REGIONAL BY ROW tables

• rides
  ALTER TABLE rides ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE rides ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE rides SET LOCALITY REGIONAL BY ROW AS "region";
  
• user_promo_codes
  ALTER TABLE user_promo_codes ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE user_promo_codes ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE user_promo_codes SET LOCALITY REGIONAL BY ROW AS "region";
  
• users
  ALTER TABLE users ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE users ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE users SET LOCALITY REGIONAL BY ROW AS "region";
  
• vehicle_location_histories
  ALTER TABLE vehicle_location_histories ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE vehicle_location_histories ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE vehicle_location_histories SET LOCALITY REGIONAL BY ROW AS "region";
  
• vehicles
  ALTER TABLE vehicles ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE vehicles ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE vehicles SET LOCALITY REGIONAL BY ROW AS "region";
  

​

Step 6. Re-check service latency

• SQL Queries: This number should go up, since the cluster can service more load due to better performance (due to better data locality and lower latency). In this particular run, the QPS has almost doubled, from 87 to 164.
• Service Latency: SQL, 99th percentile: In general, even on a small demo cluster like this, the P99 latency should drop and also get less spiky over time, as schema changes finish and data is moved around. For this particular run, the P99 latency has dropped from ~1200 ms to ~870 ms, an over 25% improvement.
• Replicas per Node: This will increase, since the data needs to be spread across more nodes in order to service the multi-region workload appropriately. There is nothing you need to do about this, except to note that it happens, and is required for CockroachDB’s improved multi-region performance features to work.

​

See also

• Multi-Region Capabilities Overview
• How to Choose a Multi-Region Configuration
• When to Use ZONE vs. REGION Survival Goals
• When to Use REGIONAL vs. GLOBAL Tables
• Migrate to Multi-Region SQL
• Secondary regions
• SET SECONDARY REGION
• DROP SECONDARY REGION
• Reads and Writes in CockroachDB
• Install CockroachDB