Running SQL Transactions at Scale and With Continuous Availability

In my first NuoDB in Docker blog, I outlined how to deploy the NuoDB database in Docker containers. To follow up on that, let’s put NuoDB to the test. Mileage will vary based on the available compute and disk storage used, but even the Community Edition on a single host will demonstrate NuoDB’s OLTP performance capabilities. If you’d like a full demonstration or want to conduct your own PoC test in your environment using our Enterprise Edition, just contact us. 

For this demonstration using the NuoDB Community Edition (CE), follow the instructions in the Docker (part I) blog to start your NuoDB database in Docker on a single host with:

• 1 Admin process
• 1 Storage Manager (SM)
• 1 Transaction Engine (TE).

Confirm your database is running by executing the following command:

$ docker exec -it nuoadmin-1 nuocmd show domain
server version: 4.0.4-2-019a14f800, server license: Community
server time: 2019-09-11T20:01:16.959, client token:
7d1101146e2dda772ca6dddbdbad8808e60e167a
Servers:
  [nuoadmin1] nuoadmin1:48005 (LEADER, Leader=nuoadmin1) ACTIVE:Connected *
Databases:
    test [RUNNING]
      [SM] test-sm-1/172.18.0.3:48006 (Default) [sid = 0] [server = nuoadmin1] MONITORED:RUNNING
      [TE] test-te-1/172.18.0.4:48006 (Default) [sid = 1] [server = nuoadmin1] MONITORED:RUNNING

Next, we’ll need to install:

1. A visual monitoring tool. For monitoring, we’ll use our own NuoDB Insights.
2. A SQL workload generator. For a SQL workload generator, we’ll use YCSB, the Yahoo Cloud Serving Benchmark tool. 

Step 1: Install NuoDB Insights

The use of NuoDB Insights is optional, but is highly recommended. NuoDB Insights will collect time-series performance metric data and store it locally which is then available from the NuoDB Insights visual dashboard for analysis. To install NuoDB Insights on Docker, please follow the step-by-step instructions provided at the NuoDB github repository, section Setup Manually In Docker Using Docker Run.

Once the NuoDB Insights components have been set up, NuoDB performance data can be visualized in your Web browser by navigating to the NuoDB Insights – NuoDB Ops System Overview dashboard located at URL:

where <hostgrafana> is the host that the Grafana server was started on. The default username and password in Grafana is admin/admin.

Step 2: Install and start the YCSB SQL workload benchmark tool

The first command will take 20 seconds or so to pull the image into your local Docker repo.

docker pull nuodb/ycsb:latest
  docker run -dit --name ycsb1 \
      --hostname ycsb1 \
      --network nuodb-net \
      --env PEER_ADDRESS=nuoadmin1 \
      --env DB_NAME=test \
      --env DB_USER=dba \
      --env DB_PASSWORD=goalie \
      nuodb/ycsb:latest /driver/startup.sh

By default, the YCSB container will create 10 connections to your database and each connection will run 10K SQL statements with a mix of 95% reads and 5% updates, then disconnect, and start a new connection, and so on.

Scaling Transactional Throughput

In the example below, on a MacBook with a single SSD drive, with a single NuoDB TE and single YCSB app running, NuoDB is processing 4K SQL TPS and the TE is running at about 40% CPU as reported by NuoDB Insights.

Next, add one more YCSB app (which will double the SQL workload) by running,

docker run -dit --name ycsb2 \
    --hostname ycsb2 \
    --network nuodb-net \
    --env PEER_ADDRESS=nuoadmin1 \
    --env DB_NAME=test \
    --env DB_USER=dba \
    --env DB_PASSWORD=goalie \
    nuodb/ycsb:latest /driver/startup.sh

To match the YCSB workload increase, add one more NuoDB TE to increase NuoDB transactional throughput by running, 

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

Here we learn that our single machine is actually disk I/O bound and with one TE we have already reached our max SQL TPS of 4K for this configuration, but Insights shows the two TEs are now equally processing the YCSB SQL workload, 2000 SQL TPS each.

We have run the same benchmark in cloud environments in a single AWS region across two availability zones using EBS storage and scaled up both the YCSB sample apps and the NuoDB TEs to achieve up to 60K YCSB workload “b” TPS sustained!

Note: In these examples, each SQL transaction is a single SQL statement.

Continuous Availability

Finally, to show NuoDB’s resilience to failure events and NuoDB’s continuous availability, go ahead and remove a running transaction engine (TE) container to simulate a hardware, software, or network failure event. It’s ok, you still have one remaining TE! Pick any one and the SQL load will redistribute to the one remaining TE. This type of resilience is also available for the Admin Services and Storage Managers in the NuoDB Enterprise Edition. To remove a TE engine, just type this command at the prompt:

docker rm -f test-te-1

Notice in the screenshot below that the TE count has updated to one and the lower left panel shows the remaining one TE still processing SQL transactions. The lower center panel shows the aggregate TPS remained the same and the application keeps running even though NuoDB lost one of its TEs.

For improved resiliency and fault-tolerance, we recommend running NuoDB in container management platforms, such as Red Hat OpenShift or any managed Kubernetes platform like (GKE, AKS, EKS, etc.). In these systems, process orchestration will manage the desired number of each process type. Thus, if a process is lost for any reason, the container management system will restart the process in just a few seconds!

Check out more from the NuoDB in Docker blog series:

• Running NuoDB in Docker – Part 1: Deploy the NuoDB Database in Docker Containers
• Running NuoDB in Docker – Part 3: How to use common SQL execution GUI tools with NuoDB
• Running NuoDB in Docker – Part 4: Using Docker Compose