You are here

Deploy the NuoDB Database in Docker Containers - Part I

At NuoDB, we’ve not only been working hard to improve upon on our distributed SQL database engine, but also to make it easier to adopt NuoDB into CI/CD pipelines. NuoDB is a ANSI standard and ACID compliant SQL database that is suitable for all common deployment models, such as bare-metal, virtual machines, cloud and hybrid-cloud, and most recently container-based environments, such as Red Hat OpenShift Container Platform. At its core, NuoDB is designed to be highly scalable and fault-tolerant with the ability to load-balance across a distributed architecture.

If you would like to try NuoDB’s Community Edition (CE) in Docker, we have made available a NuoDB Docker container running the latest version of NuoDB. The container image is available on Docker Hub, and we’ll be continually making updates to improve ease of deployment into every cloud and container-based environment. 

The NuoDB Community Edition (CE) is a full featured version of NuoDB, but limited to one Storage Manager (SM) and three Transaction Engine (TE) processes. The Community Edition is free of charge and allows users to self-evaluate NuoDB at their own pace. For more information, see the Community Edition Download page.

MORE ABOUT NUODB BEFORE WE GET STARTED...

If you’re not familiar with NuoDB’s architecture, take a read through our architectural white paper. You can also check out our eBook on how NuoDB and Docker fit for a high-level architectural discussion.

The NuoDB architecture distributes administrative and database processing across three separate components. Separating the Admin and database functions allows NuoDB to utilize services like AWS ’Auto Scaling Groups or Kubernetes’ autoscaling Pods to increase throughput and high availability of NuoDB’s Transaction Engines (TEs) and Storage Managers (SMs) processes. Because of this, in cloud-native environments, we recommend each NuoDB process runs its own container.

Each NuoDB container doesn’t have to be located on the same host, but will need to be on the same overlay network in order to communicate with each other. Alternatively, you can map host ports to the container to connect over the larger network. We also recommend placing the TE container on the same host as your application. This allows your application to communicate locally with your cached database with no network hops in between.

NuoDB processes:

  • Admin Services are responsible for managing the domain and database state. The Admin service is also responsible for the load balancing function that connects client applications to NuoDB TE processes.
  • Storage Managers (SMs) are responsible for ensuring on-disk data durability and replicating changes to new started SMs. Each SM is managed by a single Admin service.
  • Transaction Engines (TEs) are responsible for connecting client applications to the database and processing SQL transactions. They also perform in-memory data caching of data tables to optimize SQL performance. Each TE is managed by a single Admin service.

GETTING STARTED WITH NUODB IN DOCKER

Now that you have a good fundamental understanding of NuoDB architecture, let's move on deploying your first NuoDB database in Docker!

Step 1: Install Docker into your RHEL/CentOS, Ubuntu or MacOS environment

This demo is designed to run the NuoDB CE on a single server or desktop computer to demonstrate the value and benefits of using NuoDB. I'm using a MacBook and installed Docker from Docker for MacOS.

Step 2: Pull a copy of the NuoDB container image

Once Docker is installed, pull the NuoDB CE container image into your local Docker repo. This command will take several minutes to complete.

docker pull nuodb/nuodb-ce:latest


Step 3: Create a Docker network

docker network create nuodb-net


Step 4: Start the Admin Service

Let’s get NuoDB fired up by starting several NuoDB Admin services. An Admin service (and its peers) is the single fixed point for NuoDB and its domain administrative tier. When the SM and TE processes start, they need to know the name of the Admin Service process they are starting on. 

From a command line, run the following command:

docker run -d --name nuoadmin1 \
   --hostname nuoadmin1 \
   --network nuodb-net \
   --publish 8888:8888 \
   --volume nuoadmin-raft-1:/var/opt/nuodb \
   --env NUODB_DOMAIN_ENTRYPOINT=nuoadmin1 \
   nuodb/nuodb-ce:latest nuoadmin

To create a fault tolerant admin management tier, create two more Admin services by running the following commands:

docker run -d --name nuoadmin2 \
    --hostname nuoadmin2 \
    --network nuodb-net \
    --publish 8889:8888 \
    --volume nuoadmin-raft-2:/var/opt/nuodb \
    --env NUODB_DOMAIN_ENTRYPOINT=nuoadmin1 \
    nuodb/nuodb-ce:latest nuoadmin

docker run -d --name nuoadmin3 \
    --hostname nuoadmin3 \
    --network nuodb-net \
    --publish 8890:8888 \
    --volume nuoadmin-raft-3:/var/opt/nuodb \
    --env NUODB_DOMAIN_ENTRYPOINT=nuoadmin1 \
    nuodb/nuodb-ce:latest nuoadmin

Note: When creating additional Admin Services, the first port number in the --publish argument increments by 1. By example, your second Admin Service will have the value 8889:8888. Also, the NUODB_DOMAIN_ENTRYPOINT variable must remain nuoadmin1! This value indicates the leader Admin Service that other Admin services will peer into to join the NuoDB domain. 

Following the example, go ahead and create a third Admin service. To confirm your NuoDB Admin service(s) were created successfully and are active and connected, type the commands highlighted in bold text.

$ docker exec -it nuoadmin1 nuocmd show domain
server version: 4.0.4-2-019a14f800, server license: Community
server time: 2020-01-22T19:33:24.566, client token: bd4dafec9dff90867aa61c73a55b6b32d4013868
Servers:
  [nuoadmin1] nuoadmin1:48005 (LEADER, Leader=nuoadmin1) ACTIVE:Connected *
  [nuoadmin2] nuoadmin2:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
  [nuoadmin3] nuoadmin3:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
Databases:
$


Step 5: Start the database Storage Manager (SM)

To start a NuoDB database, start a Storage Manager (SM) process by running the following command. This command will create a database named test with database login credentials of user dba and password goalie.

docker run -d --name test-sm-1 \
      --hostname test-sm-1 \
      --network nuodb-net \
      --volume test-arch-vol-1:/var/opt/nuodb \
      nuodb/nuodb-ce:latest nuodocker \
           --api-server nuoadmin1:8888 \
           start sm --db-name test \
                --server-id nuoadmin1 \
                --dba-user dba \
                --dba-password goalie


Note: We have created our Admin services and SM processes with persistent storage volumes by using the --volume argument on our Docker run commands. We highly recommend using persistent storage volumes to ensure that if a container is deleted or removed (for any reason), then the next time a replacement container starts it will find the already existing storage volume and reuse it -- picking up where it left off last. In the case of NuoDB, the newly started Admin service or Storage Manager container will sync with an already active process of its same type; if available, before the storage is actively used again by NuoDB.

Step 6: Start a database Transaction Engine (TE)

Start a Transaction Engine (TE) process by running the following command. The use of the --labels argument is optional. The use of database process labels allows a NuoDB Admin process to assign a client user application a TE that matches a desired label name.

docker run -d --name test-te-1 \
   --hostname test-te-1 \
   --network nuodb-net \
   nuodb/nuodb-ce:latest nuodocker \
       --api-server nuoadmin1:8888 \
       start te --db-name test \
                --server-id nuoadmin1 \
                --labels "te te1"

Go ahead and scale-out your database by starting up another TE by rerunning the above command replacing the highlighted 1 values with 2. Following the example, go ahead and create a third TE.

To confirm your NuoDB database has been created properly, type:

$ docker exec -it nuoadmin1 nuocmd show domain
server version: 4.0.4-2-019a14f800, server license: Community
server time: 2020-01-22T19:43:05.116, client token: 131e645a39b2be1535f51d043db0aaa4d5435a47
Servers:
  [nuoadmin1] nuoadmin1:48005 (LEADER, Leader=nuoadmin1) ACTIVE:Connected *
  [nuoadmin2] nuoadmin2:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
  [nuoadmin3] nuoadmin3:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
Databases:
  test [RUNNING]
    [SM] test-sm-1/192.168.0.5:48006 (Default) [start_id = 0] [server_id = nuoadmin1] [pid = 42] [node_id = 1] MONITORED:RUNNING
    [TE] test-te-3/192.168.0.8:48006 (Default) [start_id = 1] [server_id = nuoadmin3] [pid = 42] [node_id = 4] MONITORED:RUNNING
    [TE] test-te-1/192.168.0.6:48006 (Default) [start_id = 2] [server_id = nuoadmin1] [pid = 41] [node_id = 2] MONITORED:RUNNING
    [TE] test-te-2/192.168.0.7:48006 (Default) [start_id = 3] [server_id = nuoadmin2] [pid = 42] [node_id = 3] MONITORED:RUNNING
$
bash-4.2$ exit


RUNNING SQL

Now that you have a running NuoDB database, let's run some SQL statements! You can do this easily by installing the NuoDB sample "hockey" database.

Open a terminal window to your nuoadmin1 container:

docker exec -it nuoadmin1 bash

Then, cut-and-paste and run the following commands:

nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/create-db.sql>& /dev/null
nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/create-db.sql>& /dev/null

nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/Players.sql>& /dev/null
nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/Scoring.sql>& /dev/null
nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/Teams.sql>& /dev/null

Done. You now have a sample hockey player and team database! You can run any SQL you’d like against the hockey schema. 

Here are a few examples. First, log into the the database using the NuoDB SQL run tool nuosql.

SQL Exercise 1.
 

$ nuosql test@nuoadmin1 --schema hockey --user dba --password goalie
SQL> use hockey;
SQL> show tables;


    Tables in schema HOCKEY
        HOCKEY
        PLAYERS
        SCORING
        TEAMS
        VW_PLAYER_STATS is a view

SQL> select /* top 10 goal scorers in single season */
  firstname, lastname, birthyear, s.year "YEAR PLAYED", t.name "TEAM", s.goals
from scoring s, players p, teams t
where s.playerid = p.playerid
and   s.year     = t.year
and   s.teamid   = t.teamid
order by s.goals desc
limit 10;

FIRSTNAME  LASTNAME  BIRTHYEAR      YEAR         PLAYED TEAM     GOALS  

---------- --------- ---------- ------------ ------------------- ------ 
Wayne      Gretzky      1961        1981     Edmonton Oilers       92   
Wayne      Gretzky      1961        1983     Edmonton Oilers       87   
Brett      Hull         1964        1990     St. Louis Blues       86   
Mario      Lemieux      1965        1988     Pittsburgh Penguins   85   
Bobby      Hull         1939        1974     Winnipeg Jets         77   
Phil       Esposito     1942        1970     Boston Bruins         76   
Alexander  Mogilny      1969        1992     Buffalo Sabres        76   
Teemu      Selanne      1970        1992     Winnipeg Jets         76   
Real       Cloutier     1956        1978     Quebec Nordiques      75   
Wayne      Gretzky      1961        1984     Edmonton Oilers       73   

SQL> select /* top all-time cummulative goal scorers */
  firstname, lastname, birthyear,
  count(s.year) "YEARS PLAYED", round(avg(s.goals),2) "AVG GOALS/YR", sum(s.goals) "TOT GOALS"
from scoring s, players p, teams t
where s.playerid = p.playerid
and   s.year     = t.year
and   s.teamid   = t.teamid
group by firstname, lastname, birthyear
order by sum(s.goals) desc
limit 10;

FIRSTNAME  LASTNAME  BIRTHYEAR  YEARS PLAYED  AVG GOALS/YR  TOT GOALS
---------- --------- ---------- ------------- ------------- ----------
Gordie     Howe        1928          32           30.47        975
Wayne      Gretzky     1961          23           40.87        940
Bobby      Hull        1939          24           38.04        913
Brett      Hull        1964          21           35.29        741
Mike       Gartner     1959          23           31.96        735
Marcel     Dionne      1951          19           38.47        731
Phil       Esposito    1942          19           37.74        717
Mark       Messier     1961          27           25.74        695
Steve      Yzerman     1965          22           31.45        692
Mario      Lemieux     1965          17           40.59        690

SQL>


SQL Exercise 2.

NuoDB supports ANSI standard SQL and, additionally, many string, date, and arithmetic functions found in other vendors SQL RDBMS products. Have fun and try some of your own SQL. 

Here is an example of the first SQL statement above rewritten using ANSI SQL 92 syntax. Run this SQL statement to confirm the SQL results are exactly the same as the first SQL statement.

SQL> select /* top goal scorers in single season */
  firstname, lastname, birthyear, s.year "YEAR PLAYED", t.name "TEAM", s.goals
from scoring s
join players using (playerid)
join teams t using (year,teamid)
order by s.goals desc

Limit 10;


SQL Exercise 3.

When you connect to the database using the nuosql command, by default the connection to obtain a Transaction Engine (TE) is made via the default round robin load balancer. Here is how you can override that if you would like to connect to a specific TE based on label value. Recall, when we created our TEs, we created them using labels!

Try this out:

$ nuosql test@nuoadmin1 --user dba --password goalie --connection-property "LBQuery=round_robin(first(label(te te1) any))"

SQL> Select * from system.connections;
SQL> select nodeid, clientinfo, created from system.connections;

 NODEID   CLIENTINFO           CREATED           

 ------- ----------- -------------------------- 

    2      nuosql   2018-12-07 18:37:02.671364 

SQL>

 

Notice the nuosql command specifies to connect first to a TE labeled te1 and if it doesn't find one, then just connect to "any" TE.

The SELECT query shows that the nuosql session connected to nodeid 2. If you run the nuocmd show domain command, you will see (in this example) that TE1 is in fact node 2 in the NuoDB domain of database processes as demonstrated below, see the blue highlights.

$ nuocmd show domain
server version: 3.3-1-14df3bb390, server license: Community
server time: 2018-12-07T19:06:58.749, client token: 7b51af9937f32efbdbc1e800e04251276751acd5
Servers:
  [nuoadmin1] nuoadmin1:48005 (LEADER, Leader=nuoadmin1) ACTIVE:Connected *
  [nuoadmin2] nuoadmin2:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
  [nuoadmin3] nuoadmin3:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
Databases:
  test [RUNNING]
    [SM] test-sm-1/192.168.0.5:48006 (Default) [start_id = 0] [server_id = nuoadmin1] [pid = 42] [node_id = 1] MONITORED:RUNNING
    [TE] test-te-3/192.168.0.8:48006 (Default) [start_id = 1] [server_id = nuoadmin3] [pid = 42] [node_id = 4] MONITORED:RUNNING
    [TE] test-te-1/192.168.0.6:48006 (Default) [start_id = 2] [server_id = nuoadmin1] [pid = 41] [node_id = 2] MONITORED:RUNNING
    [TE] test-te-2/192.168.0.7:48006 (Default) [start_id = 3] [server_id = nuoadmin2] [pid = 42] [node_id = 3] MONITORED:RUNNING

 

You can try this yourself using the same nuosql command, but instead connect directly to either TE test-te-2 or test-te-3 using the correct label specification, either te2 or te3.

HOW TO SHUTDOWN AND RESTART NUODB

To shutdown and restart your database, just issue the NuoDB shutdown database command, remove the database SM and TE containers, and recreate them. This command will remove the database containers.

docker exec -it nuoadmin1 nuocmd shutdown database --db-name test
docker rm -f test-sm-1 test-te-1 test-te-2 test-te-3


To prove the database has been shutdown and is now in a non-running state, run:

docker exec -it nuoadmin1 nuocmd show domain

 

To startup the database again, rerun the same docker run commands listed earlier to create a new SM container and three new TE containers. When the new SM process starts, it will discover the already existing storage volume test-arch-vol-1 and mount it. Thus, your database will start from where it left off last. To demonstrate this, log into the NuoDB SQL tool nuosql as you did earlier. You will notice your sample hockey schema and its data will still be there.

CLEANING UP - REMOVING YOUR DATABASE

When working with containers, clean up is easy. You can simply remove your database by deleting your NuoDB containers and storage volumes.

docker rm -f nuoadmin1 nuoadmin2 nuoadmin3
docker rm -f test-sm-1 test-te-1 test-te-2 test-te-3
docker volume rm -f nuoadmin-raft-1 nuoadmin-raft-2 nuoadmin-raft-3
docker volume rm -f test-arch-vol-1

 

Next Steps

Please check out  my next Docker Tech Blog demonstration that goes into depth on how to run and visually monitor NuoDB at scale and with continuous availability.

 

Updated from original publishing date January 7, 2019

Add new comment