Example of Using Storage Groups and Table Partitions
Assumptions:
-
The user data is stored in a database called
companydata
and it is serviced by one Transacytion Engine/Storage Manager (TE/SM) pair in CA and one TE/SM pair in MA. -
There are two data centers, one in San Diego, CA and the other in Cambridge, MA.
-
The user data is stored in the data center, based on the user’s zip code.
-
The users are residents of the United States of America.
Steps for Managing Storage Groups and Table Partitioning
-
To review the existing domain, run the
show domain
command.nuocmd show domain
server version: 4.0.rel40dev-40-142e911b90, server license: Enterprise server time: 2019-07-03T16:03:36.713, client token: ef8a25e299d3b031702629982b88abb4b3593e7d Servers: [r0db0] 172.31.32.99:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (LEADER, Leader=r0db0, log=4/34/34) Connected * [r0db1] 172.31.37.71:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected [r1db0] 172.31.31.231:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected [r1db1] 172.31.27.89:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected Databases: companydata [state = RUNNING] [SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r0db0/172.31.32.99:48006 [start_id = 0] [server_id = r0db0] [pid = 17196] [node_id = 4] [last_ack = 0.06] MONITORED:RUNNING [SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r1db0/172.31.31.231:48006 [start_id = 1] [server_id = r1db0] [pid = 16960] [node_id = 1] [last_ack = 2.06] MONITORED:RUNNING [TE] 3a835d43-f771-4bed-a56c-b13498e0dd99-r0db1/172.31.37.71:48006 [start_id = 2] [server_id = r0db1] [pid = 16948] [node_id = 2] [last_ack = 9.07] MONITORED:RUNNING [TE] 3a835d43-f771-4bed-a56c-b13498e0dd99-r1db1/172.31.27.89:48006 [start_id = 3] [server_id = r1db1] [pid = 16950] [node_id = 3] [last_ack = 8.09] MONITORED:RUNNING
The database is configured using two separate TE and SM pairs.
The
show domain
command is issued using NuoDB Command (nuocmd
). For more information on NuoDB Command and other command line tools, see Command Line Tools. -
To display the archives associated with the
companydata
database, run theshow archive
command.nuocmd show archives
[0] r0db0 : /var/opt/nuodb/production-archives/companydata @ companydata [journal_path = ] [snapshot_archive_path = ] RUNNING [SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r0db0/172.31.32.99:48006 [start_id = 0] [server_id = r0db0] [pid = 17196] [node_id = 4] [last_ack = 5.81] MONITORED:RUNNING [1] r1db0 : /var/opt/nuodb/production-archives/companydata @ companydata [journal_path = ] [snapshot_archive_path = ] RUNNING [SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r1db0/172.31.31.231:48006 [start_id = 1] [server_id = r1db0] [pid = 16960] [node_id = 1] [last_ack = 9.97] MONITORED:RUNNING
There are two archives associated with the
companydata
database. -
To check the status of the storage groups connected to the database, run
get storage-groups
command.nuocmd get storage-groups --db-name companydata
StorageGroup(archive_states={}, db_name=companydata, id=1, leader_candidates=[], name=ALL, process_states={}, state=Available) StorageGroup(archive_states={}, db_name=companydata, id=2, leader_candidates=[0, 1], name=UNPARTITIONED, process_states={1: RUNNING, 0: RUNNING}, state=Available)
Both the archives serve only the
UNPARTITIONED
storage group and not theALL
storage group as indicated by theprocess_states={1: RUNNING, 0: RUNNING}
attribute listed on theUNPARTITIONED
storage group. -
To configure our domain such that one archive is associated with the storage group
sgwest
and the other archive is associated with the storage groupsgeast
, runadd storage-group
command.nuocmd add storage-group --db-name companydata --sg-name sgeast --archive-id 0 nuocmd add storage-group --db-name companydata --sg-name sgwest --archive-id 1
-
To check the status of the storage groups, run the
get storage-groups
command.nuocmd get storage-groups --db-name companydata
StorageGroup(archive_states={}, db_name=companydata, id=1, leader_candidates=[], name=ALL, process_states={}, state=Available) StorageGroup(archive_states={0: ADDED}, db_name=companydata, id=10, leader_candidates=[0], name=SGEAST, process_states={0: RUNNING}, state=Available) StorageGroup(archive_states={1: ADDED}, db_name=companydata, id=11, leader_candidates=[1], name=SGWEST, process_states={1: RUNNING}, state=Available) StorageGroup(archive_states={}, db_name=companydata, id=2, leader_candidates=[0, 1], name=UNPARTITIONED, process_states={1: RUNNING, 0: RUNNING}, state=Available)
Both the archives service
UNPARTITIONED
storage group. The archive0
SM services a storage groupsgeast
and the archive1
SM services a storage groupsgwest
. -
Switch to NuoDB SQL using
nuosql
command and create a partitioned table.SQL> CREATE TABLE users (first STRING, last STRING, zip CHAR(5) ) 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 using archive
0
and the user data for Nancy Nike is stored with the SM running using archive1
.Alternatively, you can create the same partitioned table by mapping the partition name to the storage group name before creating the table using
MAP PARTITION
and omitting theSTORE IN
part of thePARTITION
clause: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 CHAR(5) ) PARTITION BY RANGE (zip) (PARTITION p_east VALUES LESS THAN ('70000') PARTITION p_west VALUES LESS THAN (MAXVALUE)); SQL> INSERT INTO users VALUES ('Joe','Friendly', '02180'); SQL> INSERT INTO users VALUES ('Nancy','Nike', '97008');
In this example, if one of the SMs is stopped, the database remains in a
RUNNING
state. For example, if the SM using archive0
is stopped, it is still possible to insert data with a zip code value greater than or equal to70000
, but you can no longer insert data with a zip code less than70000
.SQL> INSERT INTO users VALUES ('Stu','Cormier','87020'); SQL> INSERT INTO users VALUES ('Betty','Boop','06430'); insufficient number of Storage Managers servicing storage group to commit transaction. SG: SGEAST, require 1, have 0