In this post, we will learn how to install a NuoDB Community Edition in kubernetes infrastructure using OpenEBS for storage

Introduction to NuoDB

Elastic Scalability is the capability of the database to provide amazing scale-out performance and scale-in cost savings on-demand.

Every organization wants to make the most of any growth opportunity presented to them. A successful business often anticipates when it will need more resources for its database. However, there are times in the lives of organizations when they grow so fast that they need to scale up fairly quickly. This is the time when you need elastic scalability more than any other day. You don’t want your application to crash or start performing poorly when the business opportunities are more numerous.

Scaling a traditional relational database usually requires a lot of planning and is a very nervous road even for experienced DBAs. Whereas NoSQL often requires development work for the application.

Now you don’t have to worry about rewriting your applications when you need to scale your database. NuoDB preserves ACID guarantees and a standard SQL interface while offering simple and elastic scaling. In other words, you can add and remove database capabilities as you need them, which lowers the total cost of ownership and your application team won’t have to worry about programming or database architecture. There are three unique advantages :

1. Reduce total cost by matching resources to demand
2. Improve your product by focusing on the application code
3. Grow your business without technical constraints

NuoDB is a relational database designed for the cloud. It’s a true SQL service that has all the properties of ACID transactions, supports the standard SQL language and relational logic that you love. It’s also designed from the ground up to provide the scalability you’d expect from a service designed for the cloud.

The architecture of the NuoDB database is based on three main components. The first one is a broker that interprets JDBC, ODBC and SQL requests from different clients. This broker acts as an interpreter but also as a load balancer for the requests. The second component is a transactional engine that ensures the ACID (Atomicity, Consistency, Isolation and Durability) of the transactions, which guarantees that NuoDB behaves like a real relational database in terms of transaction consistency. Finally, the last component is a storage engine that relies on a Key Value Store system. This engine can rely on local storage but also on storage services such as Amazon’s S3 or HDFS (Hadoop File System), provided that these storages meet the required performance constraints. NuoDB’s architecture is fully distributed and allows to design a geodistributed database in active-active mode

Each of these components can be duplicated at will according to the database’s scalability needs (and there is no notion of master or slave). It is thus possible to multiply the storage engines in order to guarantee optimal data integrity (data is replicated between Key Value Stores) or to deploy a large number of transactional engines to handle a large number of transactions.

The NuoDB Community Edition is available for Kubernetes, Docker, and physical / virtual machine host environments. Community Edition of NuoDB limits your deployment to:

• 3 Transaction Engines and 1 Storage Manager
• Deployment in a single location

NuoDB processes:

• Admin Services (AP) 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.

• 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.

• 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.

We have one Admin Service per domain, several databases per domain, one Transaction Engines (or more) and one Storage Managers (or more) per database. It is recommended to have more than one Admin Service (AP) per domain.

Handling Fault Tolerance & High Availability Requirements

NuoDB is a highly consistent database, using replication between a user-defined set of replicas. NuoDB offers a highly available, scale-out database without the added complexities of adopting NoSQL for transactional applications or the need for sharding.

Prerequisites

Before you get started, you’ll need to have these things:

• A kubernetes cluster (1 master and 3 workers nodes) on linux instance
• Configuration of each node : 16 vCPUs - 32GB RAM - 120 GB of storage
• HELM > 3.0.x
• OpenEBS 1.4.0
• package tuned installed

Architecture

I will use the following architecture :

Advantages of using OpenEBS for NuoDB database:

• No need to manage the local disks, they are managed by OpenEBS
• Large size PVs can be provisioned by OpenEBS and NuoDB
• Start with small storage and add disks as needed on the fly. Sometimes NuoDB instances are scaled up because of capacity on the nodes. With OpenEBS persistent volumes, capacity can be thin provisioned and disks can be added to OpenEBS on the fly without disruption of service
• If required, take backup of the NuoDB data periodically and back them up to S3 or any object storage so that restoration of the same data is possible to the same or any other Kubernetes cluster

In this post I will not detail the installation of OpenEBS see the previous posts : Using HELM Chart to Deploying OpenEBS to an Kubernetes Cluster using Terraform and OpenEBS CStor CSI Driver

The storage class we are going to use is called: sc-cstor-csi-student1 it uses the CSI OpenEBS driver

Deployment Steps

To deploy NuoDB in Kubernetes, we will use Helm Charts. NuoDB Helm Charts are production ready and fully support day two operational tasks such as backup and restore, continuous upgrade, and can be deployed in multi-cloud and multi-cloud environments.

Disable Linux Transparent Huge Pages on all cluster nodes that will host NuoDB pods.

Run the following commands on each node of the cluster (or deploy a Terraform script for example …) :

$> wget https://raw.githubusercontent.com/nuodb/nuodb-helm-charts/master/stable/transparent-hugepage/files/tuned.sh
$> chmod 755 tuned.sh
$> sudo ./tuned.sh
$>

Add NuoDB Helm Charts repository

$> helm repo add nuodb https://storage.googleapis.com/nuodb-charts
nuodb" has been added to your repositories
$>

Create the nuodb namespace to install the NuoDB components

$> kubectl create namespace nuodb
namespace/nuodb created
$>

Install the NuoDB Admin domain administrative tier with monitoring and specified storage class

NuoDB Admin (nuoadmin) is NuoDB’s domain and database management tier as of release 4.0. NuoDB Admin provides the following benefits:

• Simplification of operational tasks when running NuoDB Command (nuocmd) which supports command line completion and provides online help for commands and options.
• Improved database availability and reliability in the event of common network delay and failure scenarios.
• Improved domain and database health and root cause information to help diagnose error conditions and restore operations quickly.
• In containerized management environments, such as Kubernetes, process error detection and self-healing is automatic resulting in zero downtime.

When using NuoDB Admin, use the NuoDB Command nuocmd command line interface to control, monitor, and analyze a NuoDB domain. For information on using NuoDB Command and other NuoDB utilities, see Command Line Tools.

$> helm install admin nuodb/admin --set nuocollector.enabled=true -n nuodb

** Please be patient while the chart is being deployed **
NuoDB can be accessed via port 48004 on the following DNS name from within your cluster:
  nuodb.default.svc.cluster.local - Read/Write connection
To display your NuoDB cluster state run:
  $ kubectl exec admin-nuodb-cluster0-0 -- nuocmd show domain
$>

Verify the pods are running :

$> kubectl get pods -n nuodb
NAME                     READY   STATUS    RESTARTS   AGE
admin-nuodb-cluster0-0   3/3     Running   0          1m

Verify the connected states of the database domain

$> kubectl -n nuodb exec -it admin-nuodb-cluster0-0 -- nuocmd show domain

server version: 4.2.1.vee-3-c42866be32, server license: Community
server time: 2021-11-09T15:53:22.184, client token: 5b53b1c20fafe90eece7f0a927a62205dda654d6
Servers:
  [admin-nuodb-cluster0-0] admin-nuodb-cluster0-0.nuodb.default.svc.cluster.local:48005 [last_ack = 1.51]
  ACTIVE (LEADER, Leader=admin-nuodb-cluster0-0, log=0/4/4) Connected *
Databases
$>

Install the NuoDB database Storage Manager (SM) and Transaction Engine (TE) components

$> helm install database nuodb/database  --set database.sm.hotCopy.replicas=0 --set database.sm.noHotCopy.replicas=1 \
--set nuocollector.enabled=true -n nuodb

NuoDB can be accessed via port 48004 on the following DNS name from within your cluster:
  nuodb.nuodb.svc.cluster.local - Read/Write connection
$>

Verify the pods are running:

$> kubectl get pods -n nuodb
NAME                                               READY   STATUS    RESTARTS   AGE
admin-nuodb-cluster0-0                             3/3     Running   0           2m
sm-database-nuodb-cluster0-demo-0                  1/1     Running   0          55s
te-database-nuodb-cluster0-demo-68fddff796-rgdhw   1/1     Running   0          55s

Verify the connected states of the database domain:

$> kubectl -n nuodb  exec -it admin-nuodb-cluster0-0 -- nuocmd show domain

Defaulting container name to admin.
.............
Servers:
  [admin-nuodb-cluster0-0] admin-nuodb-cluster0-0.nuodb.nuodb.svc.cluster.local:48005 [last_ack = 0.03]
  ACTIVE (LEADER, Leader=admin-nuodb-cluster0-0, log=0/26/26) Connected *
Databases:
  demo [state = RUNNING]
    [SM] sm-database-nuodb-cluster0-demo-0/10.36.0.6:48006 [start_id = 0] [server_id = admin-nuodb-cluster0-0]
    [pid = 96] [node_id = 1] [last_ack =  8.45] MONITORED:RUNNING
    [TE] te-database-nuodb-cluster0-demo-68fddff796-rgdhw/10.36.0.5:48006 [start_id = 1]
    [server_id = admin-nuodb-cluster0-0] [pid = 73] [node_id = 2] [last_ack =  3.62] MONITORED:RUNNING

To scale the TEs, run the following command:

$> kubectl -n nuodb scale deployment te-database-nuodb-cluster0-demo --replicas=2
deployment.apps/te-database-nuodb-cluster0-demo scaled
$>

we will have two TE processes :

$> kubectl -n nuodb  exec -it admin-nuodb-cluster0-0 -- nuocmd show domain

Defaulting container name to admin.
......

Servers:
  [admin-nuodb-cluster0-0] admin-nuodb-cluster0-0.nuodb.nuodb.svc.cluster.local:48005 [last_ack = 1.85]
  ACTIVE (LEADER, Leader=admin-nuodb-cluster0-0, log=0/35/35) Connected *
Databases:
  demo [state = RUNNING]
    [SM] sm-database-nuodb-cluster0-demo-0/10.36.0.6:48006 [start_id = 0] [server_id = admin-nuodb-cluster0-0] [pid = 96]
    [node_id = 1] [last_ack =  2.48] MONITORED:RUNNING
    [TE] te-database-nuodb-cluster0-demo-68fddff796-rgdhw/10.36.0.5:48006 [start_id = 1]
    [server_id = admin-nuodb-cluster0-0] [pid = 73] [node_id = 2] [last_ack =  7.48] MONITORED:RUNNING
    [TE] te-database-nuodb-cluster0-demo-68fddff796-5xdxx/10.42.0.5:48006 [start_id = 2]
    [server_id = admin-nuodb-cluster0-0] [pid = 44] [node_id = 3] [last_ack =  0.38] MONITORED:RUNNING
$>

Create A New Database

We have a database that has been created at installation that is called demo we will create another database: hockeydb01. We will use the helm charts (used previously to deploy our demo database)

Run the following command :

$> helm install database02 nuodb/database --set database.name=hockeydb01 --set database.sm.hotCopy.replicas=0 \
--set database.sm.noHotCopy.replicas=1 --set nuocollector.enabled=true -n nuodb

** Please be patient while the chart is being deployed **
NuoDB can be accessed via port 48004 on the following DNS name from within your cluster:
 nuodb.nuodb.svc.cluster.local - Read/Write connection
$>

Verify the pods are running:

$> kubectl get pods -n nuodb |grep database02
sm-database02-nuodb-cluster0-hockeydb01-0                  3/3     Running   0          16m
te-database02-nuodb-cluster0-hockeydb01-856568f5c7-r7sqh   3/3     Running   0          16m
$>

We have our SM and TE processes started for the chart database02.

Let’s check the connection of the database hockeydb01 to the domain demo

$> kubectl -n nuodb  exec -it admin-nuodb-cluster0-0 -- nuocmd show domain

Defaulting container name to admin.
......

Servers:
  [admin-nuodb-cluster0-0] admin-nuodb-cluster0-0.nuodb.nuodb.svc.cluster.local:48005 [last_ack = 1.85]
  ACTIVE (LEADER, Leader=admin-nuodb-cluster0-0, log=0/35/35) Connected *

  hockeydb01 [state = RUNNING]
    [SM] sm-database02-nuodb-cluster0-hockeydb01-0/10.42.0.8:48006 [start_id = 7]
    [server_id = admin-nuodb-cluster0-0] [pid = 142] [node_id = 1] [last_ack =  8.61] MONITORED:RUNNING
    [TE] te-database02-nuodb-cluster0-hockeydb01-856568f5c7-r7sqh/10.44.0.4:48006 [start_id = 8]
    [server_id = admin-nuodb-cluster0-0] [pid = 96] [node_id = 2] [last_ack =  6.59] MONITORED:RUNNING
$>

Populate your Database

NuoDB comes with a sample database, which contains North American ice hockey data. It is located in /opt/nuodb/samples/quickstart/sql in our pod sm-database02-nuodb… and we will use these SQL files to populate our new database with field hockey data.

Copy the SQL directory from the sm-database02-nuodb… pod to the admin-nuodb-cluster0-0 pod or download this file : https://github.com/colussim/ansible-aws-k8s/raw/main/NuoDB/nuodbdatabase.tar and copy and decompress the file in the /home/nuodb/ directory of the admin-nuodb-cluster0-0 pod.

You can execute the following commands (for example from the master node) :

$> wget https://github.com/colussim/ansible-aws-k8s/raw/main/NuoDB/nuodbdatabase.tar
$> kubectl cp nuodbdatabase.tar nuodb/admin-nuodb-cluster0-0:/home/nuodb/
$> kubectl -n nuodb  exec -it admin-nuodb-cluster0-0 -- tar -xcf /home/nuodb/nuodbdatabase.tar
$>

We will use the command line utility delivered with NuoDB : nuosql We will use the sql script : create-db.sql

$> kubectl -n nuodb  exec -it admin-nuodb-cluster0-0 -- bash
bash-4.4$ nuosql hockeydb01@admin-nuodb-cluster0-0 --user dba --password secret --file /home/nuodb/sql/create-db.sql

DROP TABLE IF EXISTS Hockey;
DROP TABLE IF EXISTS vw_player_stats;
DROP TABLE IF EXISTS scoring;
DROP TABLE IF EXISTS teams;
DROP TABLE IF EXISTS players;

create table Hockey
 (
    Id       BIGINT not NULL generated always as identity primary key,
    Number   Integer,
    Name     String,
    Position String,
    Team     String
 );
create unique index player_idx on Hockey (Number, Name, Team);
insert into Hockey (Number,Name,Position,Team)  values (37,'PATRICE BERGERON','Forward','Bruins');
insert into Hockey (Number,Name,Position,Team)  values (48,'CHRIS BOURQUE','Forward','Bruins');
......

Load DATA

bash-4.4$ nuosql hockeydb01@admin-nuodb-cluster0-0 --user dba --password secret --file /home/nuodb/sql/Players.sql

insert into players (playerID,firstName,lastName,height,weight,firstNHL,lastNHL,position,birthYear,birthMon,birthDay,birthCountry,birthState,birthCity) values ....
......
commit;
bash-4.4$

bash-4.4$ nuosql hockeydb01@admin-nuodb-cluster0-0 --user dba --password secret --file /home/nuodb/sql/Scoring.sql

insert into scoring (playerID,year,stint,teamID,position,gamesPlayed,goals,assists,penaltyMinutes) values .....
......
commit;
bash-4.4$

bash-4.4$ nuosql hockeydb01@admin-nuodb-cluster0-0 --user dba --password secret --file /home/nuodb/sql/Teams.sql

insert into teams (year,teamID,conferenceID,divisionID,rank,playoff,games,wins,losses,ties,overtimeLosses,name) values ....
......
commit;
bash-4.4$

Explore your Database using SQL Commands

NuoDB supports a rich and comprehensive set of SQL features, which includes the full ANSI SQL standard as well as additional procedural extensions. It also provides a typical SQL database permission model, allowing you to control who or what roles are allowed to perform actions on a schema, table, view or procedure.

Enter the NuoDB SQL tool by specifying the database you are accessing, your username, and password:

bash-4.4$ nuosql hockeydb01 --user dba --password secret
SQL>

Use the show schemas command to take a look at what schemas our database includes:

SQL> show schemas

    Found 2 schemas
        SYSTEM
        USER (current)
SQL>

Show the tables in that schema :

SQL> use USER
SQL> show tables

    Tables in schema USER
        HOCKEY
        PLAYERS
        SCORING
        TEAMS
        VW_PLAYER_STATS is a view
SQL>

Show number of entries in HOCKEY table :

SQL> select count(*) from HOCKEY;

 [COUNT]  
 --------

    24    

SQL> select * from HOCKEY limit 10;

 ID  NUMBER        NAME       POSITION   TEAM  
 --- ------- ---------------- --------- ------

  1    37    PATRICE BERGERON  Forward  Bruins
  2    48    CHRIS BOURQUE     Forward  Bruins
  3    11    GREGORY CAMPBELL  Forward  Bruins
  4    18    NATHAN HORTON     Forward  Bruins
  5    23    CHRIS KELLY       Forward  Bruins
  6    46    DAVID KREJCI      Forward  Bruins
  7    17    MILAN LUCIC       Forward  Bruins
  8    64    LANE MACDERMID    Forward  Bruins
  9    63    BRAD MARCHAND     Forward  Bruins
 10    20    DANIEL PAILLE     Forward  Bruins

SQL>

We can also have information about our configuration: number of connections, number of nodes (TE and SM process) :

SQL> set output vertical;
SQL> select * from system.nodes;
==================================== Row #1 ====================================
ID: 1
STARTID: 7
LOCALID: 1
PORT: 48006
ADDRESS: 10.42.0.8
HOSTNAME: 10.42.0.8
STATE: Running
TYPE: Storage
CONNSTATE: Ready
MSGQSIZE: 0
TRIPTIME: 548
GEOREGION: 0
MINATOM_VER: 1.0.1
DISK_ENCRYPTION: NONE
PLATFORM_VER: 1441792
RELEASE_VER: 4.2.1.vee-3-c42866be32
DEFAULT_SQLENGINE:
==================================== Row #2 ====================================
ID: 2
STARTID: 8
LOCALID: 0
PORT: 48006
ADDRESS: 10.44.0.4
HOSTNAME: 10.44.0.4
STATE: Running
TYPE: Transaction
CONNSTATE: Ready
MSGQSIZE: 0
TRIPTIME: 0
GEOREGION: 0
MINATOM_VER:
DISK_ENCRYPTION: NONE
PLATFORM_VER: 1441792
RELEASE_VER: 4.2.1.vee-3-c42866be32
DEFAULT_SQLENGINE: Vectorized


SQL> select * from system.connections;
==================================== Row #1 ====================================
SQLSTRING: select * from system.connections;
COUNT: 8
RUNTIME: 25
USER: DBA
SCHEMA: USER
NUMPARAM: 0
PARAMS:
CONNID: 8589934598
OPEN: 1
MEMUSAGE: 12376
HANDLE: 1
OPENRESULTS: 0
NODEID: 2
EXECID: 18446744099479355394
TRANSID: 14504322
TRANSRUNTIME: 154
AUTOCOMMITFLAGS: 1
ISOLATIONLEVEL: 8
CLIENTHOST: 10.36.0.4
CLIENTPROCESSID: 22558
CLIENTINFO: nuosql
AUTOCOMMITSPMODE: FALSE
ROLLBACKMODE: procedure
EXECUTIONSTACK:
CREATED: 2021-11-12 14:56:15.903758
LASTEXECUTED: 2021-11-12 15:12:11.048302
SQLENGINE: Vectorized

You can also connect with your favorite graphic tool like DBeaver or DbVisualizer

We are going to open an ssh tunnel on the listening port of our TE process linked to our hockeydb01 database.

$> kubectl -n nuodb  exec -it admin-nuodb-cluster0-0 -- nuocmd show domain

Defaulting container name to admin.
......

Servers:
  [admin-nuodb-cluster0-0] admin-nuodb-cluster0-0.nuodb.nuodb.svc.cluster.local:48005 [last_ack = 1.85]
  ACTIVE (LEADER, Leader=admin-nuodb-cluster0-0, log=0/35/35) Connected *

  hockeydb01 [state = RUNNING]
    [SM] sm-database02-nuodb-cluster0-hockeydb01-0/10.42.0.8:48006 [start_id = 7]
    [server_id = admin-nuodb-cluster0-0] [pid = 142] [node_id = 1] [last_ack =  8.88] MONITORED:RUNNING
    [TE] te-database02-nuodb-cluster0-hockeydb01-856568f5c7-r7sqh/10.44.0.4:48006 [start_id = 8]
    [server_id = admin-nuodb-cluster0-0] [pid = 96] [node_id = 2] [last_ack =  6.84] MONITORED:RUNNING

Our TE process binds to the address : 10.44.0.4:48006

Our ssh tunnel command will be as follows :

$> ssh -L 48006:10.44.0.4:48006 -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ssh-keys/id_rsa_aws ec2-user@master01
$>

we connect with the public ip address of our master node.

In my test I use DBeaver, and I use as connection string:

• host : localhost
• port : 48006
• database : hockeydb01
• user : dba
• password : default_password : secret

Conclusion

As an elastic SQL database, NuoDB was conceived fundamentally as a peer-to-peer distributed architecture that provides database services. So, it appears as a single logical ANSI SQL database to the application, and that makes it very straightforward and easy for developers to work with. In future posts we will see the Scale-Out, Continuous Availability and monitoring part

Next Step

NuoDB Monitoring

Scale-Out, Continuous Availability

Resources :

NuoDB Charts

Kubernetes Environments

nuocmd Command

The Community Edition

Thank You