Example of Using Storage Groups and Table Partitions

Let's suppose we have a group of users in our data and that they all live in the United States. Say we have two data centers, one in San Diego, CA and the other in Cambridge, MA. We've decided, for locality of our data, that we will store user data in one data center or the other, based on the user's zip code. Assume we have a database called companydata and it is serviced by one TE/SM pair in CA and one TE/SM pair in MA.

SMs started by an automation template, are automatically started with the storage group UNPARTITIONED.

This is our existing domain and the storage groups available. You see we have one TE/SM pair running in region us-east-2 and the other TE/SM pair running in region us-west-2. There are two storage groups, ALL and UNPARTITIONED. Both SMs service the UNPARTITIONED storage group but neither services ALL storage groups.

nuodb [domain] > show domain summary
 
Hosts:
[broker] * ec2-54-200-184-21.us-east-2.compute.amazonaws.com/54.200.184.21:48004 (us-east-2) CONNECTED
[broker] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) CONNECTED
 
Database: companydata, template [Region distributed] MET, processes [2 TE, 2 SM], ACTIVE
[SM] ip-172-31-5-193/54.200.184.21:48005 (us-east-2) [ pid = 10464 ] [ nodeId = 1 ] RUNNING
[TE] ip-172-31-5-193/54.200.184.21:48006 (us-east-2) [ pid = 10481 ] [ nodeId = 2 ] RUNNING
[SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
[TE] ip-172-31-2-230/54.200.185.240:48006 (us-west-2) [ pid = 9554 ] [ nodeId = 4 ] RUNNING
 
nuodb [domain] > show storageGroups
Database: companydata
  [id=2] UNPARTITIONED Available
    Serviced by:
    [SM] ip-172-31-5-193/54.200.184.21:48005 (us-east-2) [ pid = 10464 ] [ nodeId = 1 ] RUNNING
    [SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
 
  [id=1] ALL Available
    Serviced by:

First we need to configure our domain such that the SM running in us-west-2 is part of storage group sgwest and the SM in us-east-2 is part of the storage group sgeast. In NuoDB Manager, create the storage group sgeast, associated with IP address 54.200.184.21 and the SM with pid 10464. Create storage group sgwest, associated with IP address 54.200.185.240 and the SM with pid 9525. Then show storageGroups shows that we still have the two SMs servicing UNPARTITIONED and now the us-east-2 SM services a storage group called sgeast and the us-west-2 SM services a storage group called sgwest.

nuodb [domain] > add storageGroup   
Storage group name: sgeast
Host: 54.200.184.21
Process ID: 10464
Success
nuodb [domain] > add storageGroup
Storage group name: sgwest
Host: 54.200.185.240
Process ID: 9525
Success
nuodb [domain] > show storageGroups
Database: companydata
  [id=2] UNPARTITIONED Available
    Serviced by:
    [SM] ip-172-31-5-193/54.200.184.21:48005 (us-east-2) [ pid = 10464 ] [ nodeId = 1 ] RUNNING
    [SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
 
  [id=3] SGEAST Available
    Serviced by:
    [SM] ip-172-31-5-193/54.200.184.21:48005 (us-east-2) [ pid = 10464 ] [ nodeId = 1 ] RUNNING
 
  [id=4] SGWEST Available
    Serviced by:
    [SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
 
  [id=1] ALL Available
    Serviced by:

This same scenario could be created with an unmanaged databaseA database created without a NuoDB database template. (that is, a database created using start process commands rather than create database and an automation template) by using the storage-group option as in the following commands:

nuodb [domain] > show domain summary
 
Hosts:
[broker] * ec2-54-200-184-21.us-east-2.compute.amazonaws.com/54.200.184.21:48004 (us-east-2) CONNECTED
[broker] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) CONNECTED
 
# Start an SM servicing Storage Group sgeast and a TE on the same host
nuodb [domain] > start process sm
Database: companydata
Host: 52.200.184.21
Process command-line options (optional): storage-group sgeast
Archive directory: /var/opt/nuodb/production-archives/companydata
Initialize archive (optional): true
Started: [SM] ec2-54-200-184-21.us-east-2.compute.amazonaws.com/54.200.184.21:48004 (us-east-2) [ pid = 26779 ] [ db = companydata ] [ nodeId = 1 ] ACTIVE
nuodb [domain/companydata] > start process te
Host: 52.200.184.21
Process command-line options (optional): dba-user dba dba-password dba
Started: [TE] ec2-54-200-184-21.us-east-2.compute.amazonaws.com/54.200.184.21:48004 (us-east-2) [ pid = 26819 ] [ db = companydata ] [ nodeId = 3 ] ACTIVE
 
# Start an SM servicing Storage Group sgwest and a TE on the same host
nuodb [domain/companydata] > start process sm
Host: 54.200.185.240
Process command-line options (optional): storage-group sgwest
Archive directory: /var/opt/nuodb/production-archives/companydata
Initialize archive (optional): true
Started: [SM] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) [ pid = 30231 ] [ db = companydata ] [ nodeId = 5 ] ACTIVE
nuodb [domain/companydata] > start process te
Host: 54.200.185.240
Process command-line options (optional): dba-user dba dba-password dba
Started: [TE] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) [ pid = 30242 ] [ db = companydata ] [ nodeId = 7 ] ACTIVE

Now we can switch to nuosql and create a partitioned table.

SQL> CREATE DOMAIN zipcode CHAR(5);
SQL> CREATE TABLE users (
first STRING, last STRING, zip ZIPCODE )
PARTITION BY RANGE (zip)
(PARTITION p_east VALUES LESS THAN ('70000') STORE IN sgeast
PARTITION p_west VALUES LESS THAN (MAXVALUE) STORE IN sgwest);
SQL> INSERT INTO users VALUES ('Joe','Friendly', '02180');
SQL> INSERT INTO users VALUES ('Nancy','Nike', '97008');

User data for Joe Friendly is stored with the SM running on the machine in region us-east-2 and the user data for Nancy Nike is stored with the SM running on the machine in region us-west-2.

Alternatively, we could have created the same partitioned table by mapping the partition name to the storage group name before creating the table using MAP PARTITION and omitting the STORE IN part of the PARTITION clause:

SQL> CREATE DOMAIN zipcode CHAR(5);
SQL> MAP PARTITION p_east STORE IN sgeast;
SQL> MAP PARTITION p_west STORE IN sgwest;
SQL> CREATE TABLE users (
first STRING, last STRING, zip ZIPCODE )
PARTITION BY RANGE (zip)
(PARTITION p_east VALUES LESS THAN ('70000')
PARTITION p_west VALUES LESS THAN (MAXVALUE));

We can verify this by shutting down one of our two SMs. We will shutdown the us-east-2 SM. With a managed database, enforcement would normally restart the SM, so you will need to disable the enforcer so that the enforcer does not start it back up automatically. With an unmanaged database (one created with just start process commands), it is not necessary to disable the enforcer.

# The following command is not necessary for unmanaged Databases
nuodb [domain] > disable enforcer database companydata

nuodb [domain] > shutdown process
Host: 54.200.184.21
Process ID: 10464
Shutdown gracefully (optional): yes
Shutdown even if database would become non-durable (optional): 
Timeout (duration ms/s/m/h/d/w) (default: 10000ms): 
Process shutdown cleanly
nuodb [domain] > show domain summary
 
Hosts:
[broker] * ec2-54-200-184-21.us-east-2.compute.amazonaws.com/172.31.5.193:48004 (us-west-2) CONNECTED
[broker] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) CONNECTED
 
Database: users, template [Region distributed] UNMET, processes [2 TE, 1 SM], ACTIVE
[TE] ip-172-31-5-193/54.200.184.21:48006 (us-west-2) [ pid = 10481 ] [ nodeId = 2 ] RUNNING
[SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
[TE] ip-172-31-2-230/54.200.185.240:48006 (us-west-2) [ pid = 9554 ] [ nodeId = 4 ] RUNNING

We can still insert data with a zip code value greater than or equal to '70000', but we can no longer insert data with a zip code less than '70000'.

SQL> INSERT INTO users VALUES ('Stu','Cormier','87020');
SQL> INSERT INTO users VALUES ('Betty','Boop','06430');
While processing exception (insufficient number of storage managers servicing 
storage group to commit transaction. SG: SGEAST, require 1, have 0), exception 
(A system error has occurred which makes the transaction unable to commit) was 
also generated