Exploring Cockroach Distributed Database - 7 nodes, 9 nodes even, in containers.

TL;DR: I managed to create a 7-node, and even a 9 node playground with a CockroachDB cluster running on my old macbook. There is a Lot to Explore but it looks Very Interesting.

Background: "Distributed Databases".

For a good while now we are looking at "serverless" databases. Either some DBaaS running at a (cloud-) provider, or a locally created cluster, which mostly run in k8s or docker.

Note: Nobody seems to realise that for Truly Performant database access, the less layers, and the less chattyness, The Better. But then again, to obtain Resilience and Scale-Out: you need multi-node deployment and some form of replication, redundancy, or Sharding.

To be able to properly Guide Customers in Database Deployments, I want to know how these system "Tick". I want to know what the strong- and weak points of "distributed" are. For this reason, I'm experimenting with some of the systems out there. 

Setup, using containers in Docker.

Note: My primitive setup is not meant to be Performant. I'm just trying to get a feel for the product, and to find out what "Concepts" I need to understand it and to use it. My example was from the ppl at cockroach: this guide worked fine (link). 

Here is how I started: Create Network and Volumes:

Could not resist: I added nrs 8 and 9, just in case I wanted to try...

Could not resist to make a few adjustments: notably port-mapping numbers. And soon after I started using haproxy or something similar to help my client-app (mostly psql in my case) to search for any available port. 

So, I created my first node using:

# first container,

docker run -d --name=roach1 --hostname=roach1 --net=roachnet \
  -p 26257:26257 -p 8081:8080               \
  -v "vol_roach1:/cockroach/cockroach-data" \
  cockroachdb/cockroach:v23.1.9 start \
         --http-addr=roach1:8080      \
          --sql-addr=roach1:26257     \
    --advertise-addr=roach1:26357     \
       --listen-addr=roach1:26357     \
    --insecure   --join=roach1:26357,roach2:26357,roach3:26357 

That went well, and I could even see inside using "docker exec -it roach1 bash". But the cockroach-provided container is quite lean+mean as I described in my first-attempt. Not much to see in there. I did learn how to remove log-files later, different story.

The 2nd and following containers were easily created using this template:

# another 6 nodes to add...
# not using for-loop yet bcse of port-mappings

docker run -d --name=roach2 --hostname=roach2 --net=roachnet \
  -p 26258:26257 -p 8082:8080               \
  -v "vol_roach2:/cockroach/cockroach-data" \
  cockroachdb/cockroach:v23.1.9 start \
         --http-addr=roach2:8080    \
          --sql-addr=roach2:26257   \
    --advertise-addr=roach2:26357   \
       --listen-addr=roach2:26357   \
    --insecure     --join=roach1:26357,roach2:26357,roach3:26357

Note: the mapping of he web-gui port 8080: for roach1, it maps to localhost 8081 and for roach2 maps to 8082 etc... 

And note the mapping of the sql-addr port to localhost: roach2 two maps to 26258, and roach3 will map to 26259, etc.. 

So, I started roach2, roach3, ... roach7, with some 5 sleep-seconds in between each node, and that all worked fine:

Apologies for the wide screen, docker seems to assume you have an ultra wide.... 

Easy so far, I had the containers with the relevant processes inside them running (no way to see inside, because no "ps" or other tools on those containers yet...) 

Now to initiate the database, and see if we can connect (straight from the examples, but I added the select to show the nodes...):

Select node_id, address, is_live from crdb_internal.gossip_nodes;

We notice the 7 nodes (yes, seven, deliberately picked a weird number), and they all seem to be "live". We can also inspect the cluster via the web-gui at any of the mapped ports 8080:

Notice I've use the last node (nodename roach7, mapped to port 8087) to query the web-gui, and it nicely reported there is a cluster.

Result: Looks like we have a Cluster!

First Queries: see what is in there.

Nice to have a database, but I want to run queries (against tables and view...) and inspect the data-beast myself if possible. I found a very rich set of internal functions: select * from crdb_internal.builtin_functions : 

The lists of internal functions, views and tables in the schema crdb_internal looks promising. For example, the information in "leases" and "ranges" looks like it will give me an insight in where+how data is spread (shareded) over the cluster, and that is what I want to know when nodes are removed from the system: What will be affected when node42 is lost ...

To find my way around, I already created two views, mainly for use at logon and during testing, just to give me an SQL-view of my cluster:

create or replace view crx_vnodes as (
select node_id
, split_part ( advertise_sql_address, ':', 1 )   as nodename
, advertise_sql_address                          as sql_addr
, case when is_live then 'live' else '-out-' end as live
, ranges, leases
from crdb_internal.gossip_nodes  ) ;

create or replace view crx_vnodeinfo as (
select bi.node_id                       as node_id
     , split_part ( gn.address, ':', 1) as nodename
     , bi.value                         as build
from crdb_internal.node_build_info bi
   , crdb_internal.gossip_nodes    gn
where bi.field   = 'Build'
  and gn.node_id = bi.node_id ) ;

And then just create and run those:

Those views are created, and ... There are the nodes of my cluster! 

Those views are going to be part of my refresh-cluster script: I will create them soon after I create the cluster and use them at logon. 

Lessons so far: it works, and there is a lot to discover.

So far, this cockroachDB looks quite Dev-friendly to me: you can easily set up a (single) test-node or even a test-cluster. The web-gui looks informative. The SQL and DDL syntax is very PostgreSQL like (but totally "compatible", no triggers no plpgsql, the "extensions" from cockroach are not the same as postgres extensions)

For easy of deployment, and richness in functionality: Cockroach Looks Interesting!

Hope to research more in the near future.

-------- End of Blogpost -- notes and questions below --------

PS: the additional volumes 8 and 9 in the volume-create script: Of Course I tried adding the extra nodes. Worked Fine! 

(screen crd08 with 9 nodes?)

Proof in Docker two extra nodes :

And Proof from psql:

Nodes Added, and some re-distribution already happened..

Now.. I cant wait to stop / kill / mess with some of these nodes..