You are here

Deploy the NuoDB Database in Docker Containers

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 an 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 NuoDB version 3.3. 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 container-native environments, we recommend each NuoDB process runs its own container.

Each 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 CE 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 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 CE 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 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 another Admin service by rerunning the above command replacing the highlighted 1 values with 2.

Note:  When creating additional Admin Services, the first port number in the --publish argument increments by 1. Therefore for your second Admin Service it 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 bash
bash-4.2$ bash
bash-4.2$ nuocmd show domain
server version: 3.3-1-14df3bb390, server license: Community
server time: 2018-12-07T17:01:58.663, client token: 38700031203e8bc3d0b97b1146e21d75997e3962
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:

bash-4.2$ exit


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. This is highly recommended to ensure that if a container is deleted or removed (for any reason), then the next time the container restarts 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 restarted Admin service or Storage Manager container will sync with another 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. It allows client applications to target a specific TE by 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 bash
bash-4.2$ bash
bash-4.2$ nuocmd show domain
server version: 3.3-1-14df3bb390, server license: Community
server time: 2018-12-07T17:01:58.663, client token: 38700031203e8bc3d0b97b1146e21d75997e3962
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


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 connected 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

This is just the beginning… Be on the lookout for the next TechBlog that will go into depth on NuoDB Insights Visual Monitoring integration, YCSB load creation for benchmarking, and Continuous Availability demonstration. Coming soon!

Add new comment