[Top] [Contents] [Index] [ ? ]

The Documentation of RubatoDB

1. Introduction  
2. User's Guide  

2.1 Data Types  
2.2 SQL Commands  
2.3 Create Database Statement  
2.4 Create Table Statement and Table Partition  
2.5 Create Sequence  
2.6 Tutorials  

2.6.1 Join and Nested Query  
2.6.2 Temporary Table for Joins  
2.6.3 Outer Join  
2.6.4 Loading  
2.6.5 Cursor  
2.6.6 Stored Procedure  
2.6.7 Trigger  
2.6.8 Lob Operations  
2.6.9 Transaction  
3. Database Administrator's Guide  

3.1 Installation  
3.2 Monitoring Database Server  
3.3 Managing Database Server  
3.5 Database User's Management  
3.4 Server Runtime Environment  
4. Interfaces  

4.1 JDBC  
4.2 MySQL C API  
4.3 MySQL Interface  
5. Database Tests  

5.1 Big Data Load Tests  
5.2 TPC-C Benchmark Tests  
6. Performance  
7. References  
Concept Index  
Bug Report and Contact Information  
Copyright (C) 2000-2014 Shanghai Shifang Software, Inc. The document is available at www.cs.ualberta.ca/~yuan/databases/logicsql/docs/logicsql.html
[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

1. Introduction

RubatoDB is a highly scalable NewSQL system, supporting various consistency levels from ACID to BASE for OLTP, OLAP, and big data applications. RubatoDB is implemented with the following two distinguished features:

  1. It uses a staged database architecture, oritinally proposed by Welsh for parallel web servers.
  2. It uses a new formula protocol for distributed concurrency control.

RubatoDB supports all the standard SQL facilities with the ACID properties and runs on a collection of commodity servers.

RubatoDB adapts the MySQL communication protocol which enables our users to use various application tools, including the JDBC, ODBC, .Net, the MySQL interactive tool, and many MySQL graphics tools, such as MySQL Administrator.

The outstanding performance of RubatoDB is demonstrated in 6. Performance.

The underline architecture and the new distributed concurrency control protocol of RubatoDB are published in the top-tier database conferences and journals ( [1], [2], [3], [4]).

RubatoDB is designed to provide:

  1. a reliable and secure big database management system for both OLTP applications with the ACID properties and big data applications with the BASE properties; and
  2. cost-effective and easy-to-use database functionality that is compatible with the SQL2003 standard, with standard user interfaces such as the JDBC, ODBC, and embedded C interface.

The functionality of RubatoDB can be described by the following two aspects:

  1. It supports almost all data types,SQL statements and operations of MySQL; and
  2. It extends the standard SQL CREATE DATABASE and CREATE TABLE statements to allow both databases and tables to be distributed over any set of grid nodes.

Since RubatoDB adapts the MySQL communication protocol, all the interfaces tools of MySQL can be used to access the RubatoDB server.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2. User's Guide

2.1 Data Types  
2.2 SQL Commands  
2.4 Create Table Statement and Table Partition  
2.6 Tutorials  


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1 Data Types

RubatoDB supports all the data types of MySQL, and thus just a few simpl examples given below to demonstrate different data types supported by RubatoDB.

Example 1. Consider a table created and populated by the following statements.

 
create table students (sid char(10), sname varchar(100));
insert into students values( '1234567890', 'Sarah');
insert into students values( '12345', 'Tom');

Then the three queries below lead to different result tables.

 
mysql> select * from students;
+------------+-------+
| sid        | sname |
+------------+-------+
| 12345      | Tom   |
| 1234567890 | Sarah |
+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from students where sid = '12345     ';
+-------+-------+
| sid   | sname |
+-------+-------+
| 12345 | Tom   |
+-------+-------+
1 row in set (0.00 sec)

mysql> select * from students where sid = '12345';
+-------+-------+
| sid   | sname |
+-------+-------+
| 12345 | Tom   |
+-------+-------+
1 row in set (0.00 sec)

mysql> 

Note that the last two queries returns the same result set even though different strings are used in the where clauses for comparison.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1.1 Blob and Clob

BLOB/CLOB Datatype The LOB datatype BLOB, CLOB can store large and unstructured data such as text, image, video, and spatial data up to 2 gigabytes in size. The LOB access methods are based on the following facts.

Important Note about the lob size. The size limit on one BLOB/CLOB by RubatoDB is currently to 4Mb, though it can be configured up tp 2Gb.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2 SQL Commands

This section briefly describes RubatoDB commands and statements. All RubatoDB statements are classified into the following categories:

Since, except the CREATE DATABASE and CREATE TABLE (DDL) statements, all other statements of RubatoDB are the same as those of MySQL, we will discuss, in details, the CREATE DATABASE and CREATE TABLE, and use examples to demonstrate others.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2.1 Data Definition Language (DDL) statements

Data Definition Language (DDL) statements are used to perform the following tasks


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2.2 Data Manipulation Language (DML) statements

Data Manipulation Language (DML) statements are used to retrieve and update data stored in various database objects, including


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2.3 Transaction Control statements

Transaction Control statements controls the execution of transactions. It contains the following SQL statements

Example 2.
 
Consider a database consisting of the following three tables:

   student(sid, name, major, gpa)
   course(cid, title, description)
   registration(cid, sid, grade)

mysql> select * from student;
+-------+-------+----------+------+
| sid   | name  | major    | gpa  |
+-------+-------+----------+------+
|  5500 | Susan | Law      | NULL |
| 12001 | Sarah | Math     | NULL |
| 12345 | Peter | NULL     | NULL |
| 54321 | Bob   | Business | NULL |
+-------+-------+----------+------+
4 rows in set (0.00 sec)

mysql> select * from course; 
+-------+----------------------+-------------------------+
| cid   | title                | description             |
+-------+----------------------+-------------------------+
| AR100 | Cartoon Drawing      | A comic book            |
| BS499 | Marketing            | How to make quick bucks |
| CS291 | Introduction to DBMS | first database course   |
| MA101 | Calculus             | An easy course          |
+-------+----------------------+-------------------------+
4 rows in set (0.01 sec)

mysql> select * from registration;
+-------+-------+-------+
| cid   | sid   | grade |
+-------+-------+-------+
| BS499 | 54321 | B     |
| CS291 | 12001 | NULL  |
| CS291 | 54321 | A     |
| MA101 | 12001 | D     |
+-------+-------+-------+
4 rows in set (0.00 sec)

mysql> 
The script file for setting up the running database is given below. (It is contained in the tests directory of the distribution package.)
 
/*
 *  to set up the initial database
 */
DROP TABLE student;
DROP TABLE course;
DROP TABLE registration;

CREATE TABLE student (
	sid    INT,
        name   VARCHAR(64) NOT NULL,
	major  VARCHAR(10),
	gpa    NUMERIC(5, 2),
     	PRIMARY KEY(sid)
);


CREATE TABLE course (
        cid    CHAR(5),
        title  VARCHAR(64) NOT NULL,
        description VARCHAR(1024),
 	PRIMARY KEY(cid)
);

CREATE TABLE registration (
       cid     CHAR(5),
       sid     INT,
     grade     CHAR(1),
     PRIMARY KEY(cid, sid),
     CONSTRAINT cid_foreign_key
       FOREIGN KEY (cid) REFERENCES course,

     CONSTRAINT sid_foreign_key
       FOREIGN KEY (sid) REFERENCES student,

     CONSTRAINT grade_constraint
        CHECK ( grade IN ('A', 'B', 'C', 'D', 'F'))
     
);

INSERT INTO student (sid, name, major) VALUES (12001,'Sarah', 'Math');
INSERT INTO student (sid, name, major) VALUES (12345,'Peter', null);
INSERT INTO student (sid, name, major) VALUES (54321,'Bob', 'Business');
INSERT INTO student (sid, name, major) VALUES (5500, 'Susan', 'Law');

INSERT INTO course VALUES ('CS291','Introduction to DBMS','first database course');
INSERT INTO course VALUES ('MA101', 'Calculus', 'An easy course');
INSERT INTO course VALUES ('BS499', 'Marketing', 'How to make quick bucks');
INSERT INTO course VALUES ('AR100', 'Cartoon Drawing', 'A comic book');


INSERT INTO registration VALUES('CS291', 12001, null);
INSERT INTO registration VALUES('CS291', 54321, 'A');
INSERT INTO registration VALUES('MA101', 12001, 'D');
INSERT INTO registration VALUES('BS499', 54321, 'B');


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.3 Create Database Statement

One has to create a database before any tables can be created, and as RubatoDB is designed to run on a set of (shared nothing) grid nodes, a Create database must specified which grid nodes on which the database will be stored.

This subsection describes how to create a database that is stored over a collection of nodes.

 
CREATE DATABASE STATEMENT::=

CREATE DATABASE database_name
   [DISTRIBUTED INTO list_of_nodes]

list_of_nodes::= (0,N1, N2, ..., Nn)

Note that 0,N1,...,Nn are a list of distinct integers that must
contain 0.

A Create Database statement will create a RubatoDB database that will be stored over the given list of grid nodes. If the Distributed clause is not specified, the database will be stored into grid node 0.

Example 3. The following example demos how to create a database.

 
mysql> CREATE DATABASE my_db DISTRIBUTED INTO (0, 2, 6, 4);

Query OK, 0 rows affected (0.02 sec)

The Create database statement will return an error message if any node id, (i.e., an interger in list_of_nodes is beyond the list of available nodes for the RubatoDB.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.4 Create Table Statement and Table Partition

RubatoDB is designed to run on a set of (shared nothing) grid nodes (servers or vertual instances). This subsection describes how to create a table that is stored over a collection of nodes using various partition clauses.

 
CREATE TABLE STATEMENT::=

CREATE [BIG|TEMPORARY] TABLE table_name
   (create_definition)
   [partition_options]
   [grid_partitions]
   [cache_size_specification]

CREATE [BIG|TEMPORARY] TABLE table_name
   [(create_definition)]
   [partition_options]
   select_statement
   [grid_partitions]
   [cache_size_specification]


create_definition: the standard table-create clause of the form: 
   (col_name type, ...,  col_name type, ...)

partition_options:
   PARTITION BY 
     {[LINEAR] HASH (expr)
     | [LINEAR] KEY [ALGORITH={1|2}] (column_list)
     | RANGE{(expr)|COLUMNS(column_list)} 
     | LIST{(expr)|COLUMNS(column_list)} 
   [PARTITIONS num]
   [SUBPARTITION BY
      { [LINEAR] HASH(expr)
      | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES 
            {LESS THAN {(expr | value_list) | MAXVALUE} 
            | 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

select_statement:
    AS SELECT ...   (Some valid select statement)

grid_partitions:
    {PARTITION BY GRID (column_name) TO N NODES WITH  mod (N)
    |{PARTITION BY GRID (column_name) TO N NODES INTO node_specification
     |STORED AT GRID ( N )}

node_specification: (0,1,...,N-1)
     Note that node_specification (0,1,...,N-1) is the set N of any distinct
valid node ids. By a valid node id M, if 0 <= M <= MAX_ID.

cache_size_specification:
     CACHE SIZE ( GB, MB )

Please note that

  1. The list of grid nodes specified in grid_partition must be a subset of the list of grid nodes of the database of the stable.
  2. A table created using the CREATE BIG TABLE option does not support the ACID property.
  3. The table created using the select_statement will be populated only with the result sets retrieved in individual grid nodes.
  4. A temporary table will be created and stored only at the main node (for now).
  5. The user shall drop a temporary table whenever it is not needed.

Example 4. The following example demos how to create regular and temporary tables using AS SELECT statement. (See table.sql in the tests directory of the installation directory.)

 
mysql> CREATE TABLE base_db(
         t1 int primary key,
         t2 varchar(100),
         t3 int
       ) 
       PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into base_db values(10, 'just do it', 100);
Query OK, 1 row affected (0.02 sec)

mysql> insert into base_db values(11, 'yes we can', 101);
Query OK, 1 row affected (0.01 sec)

mysql> insert into base_db values(20, 'just say no', 201);
Query OK, 1 row affected (0.00 sec)

mysql> insert into base_db values(21, 'be cool', 202);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE sample_db(
         t1 int,
         t2 varchar(100),
         t3 int
       )
       AS SELECT * FROM base_db,
       PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM sample_db;
+------+-------------+------+
| t1   | t2          | t3   |
+------+-------------+------+
|   10 | just do it  |  100 |
|   20 | just say no |  201 |
|   11 | yes we can  |  101 |
|   21 | be cool     |  202 |
+------+-------------+------+
4 rows in set (0.03 sec)

mysql> CREATE TEMPORARY TABLE temp1 (
         t1 int, 
         t2 varchar(100), 
         t3 int
       ) 
       AS SELECT * FROM base_db;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM temp1;
+------+-------------+------+
| t1   | t2          | t3   |
+------+-------------+------+
|   10 | just do it  |  100 |
|   20 | just say no |  201 |
|   11 | yes we can  |  101 |
|   21 | be cool     |  202 |
+------+-------------+------+
4 rows in set (0.00 sec)

mysql> CREATE TEMPORARY TABLE temp2 (
         t1 int, 
         t2 varchar(100)
       ) 
       AS SELECT t1, t2 FROM base_db;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM temp2;
+------+-------------+
| t1   | t2          |
+------+-------------+
|   10 | just do it  |
|   20 | just say no |
|   11 | yes we can  |
|   21 | be cool     |
+------+-------------+
4 rows in set (0.00 sec)

mysql> 

Example 5. The following example demos how to create a table using both the hash partition, as specified in the partition_options, and the grid partitions.

 
create table warehouse (
	w_id smallint,
	w_name varchar(10),
	w_street varchar(20),
	w_tax tinyint,
	w_ytd integer,
	constraint warehouse_primary_key 
	primary key ( w_id)
)
PARTITION BY HASH (w_id) PARTITIONS 3,
PARTITION BY GRID (w_id) TO 2 NODES WITH  mod (2);

Example 6. The following example demos how to create a table stored at a particular grid node.

 
create table item (
	i_id integer,
	i_im_id integer,
	i_name varchar(24),
	i_price tinyint,
	i_data varchar(50),
	constraint item_primary_key
	primary key (i_id)
)
stored at grid(1);


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.5 Create Sequence

See also 2.5.1 Drop Sequence.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.5.1 Drop Sequence


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6 Tutorials

We are going to present various examples to demo how to use RubatoDB in this section.

Note that (1) one has to create a user, other than the default user system, (2) all sample programs in this section are included in the tests directory in the installation directory located in the file system of each and every grid node; and (3) most sample tables are distributed over two grid nodes.

2.6.1 Join and Nested Query  
2.6.2 Temporary Table for Joins  
2.6.3 Outer Join  
2.6.4 Loading  
2.6.5 Cursor  
2.6.6 Stored Procedure  
2.6.7 Trigger  
2.6.8 Lob Operations  
2.6.9 Transaction  


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.1 Join and Nested Query

Example 7. The following example demonstrates the evaluation of join and/or nested queries supported by RubatoDB. (Note that the set of the given sql statements are contained in RubatoDB/tests).
 
mysql> create table Student ( 
         s_id int , 
         s_name varchar(64), 
         address varchar(128),  
         major varchar(32)
       ) 
       PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> create table Scholarship ( 
         s_id int ,   
         scholarship varchar(64) ,  
         amount int
       )
       PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> insert into Student values (101,'Peter','Edmonton','CS');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Student values (102,'Sarah','Calgary','CS');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Student values (103,'Tom','Edmonton','Math');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Student values (104,'Michelle','Toronto','Math');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert into Scholarship values (102,'Ruthford',3000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into Scholarship values (103,'Ruthford',3000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into Scholarship values (103,'NSERC',6000);
Query OK, 1 row affected (0.00 sec)

mysql> -- Find all students with their scholarship and amount
mysql> SELECT s.s_id, s_name, scholarship,amount
       FROM   Student s, Scholarship p
       WHERE  s.s_id = p.s_id;
+------+--------+-------------+--------+
| s_id | s_name | scholarship | amount |
+------+--------+-------------+--------+
|  102 | Sarah  | Ruthford    |   3000 |
|  103 | Tom    | NSERC       |   6000 |
|  103 | Tom    | Ruthford    |   3000 |
+------+--------+-------------+--------+
3 rows in set (0.06 sec)

mysql> SELECT count(*)
       FROM   Student
       WHERE  s_id in (SELECT s_id FROM Scholarship );
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql> 
mysql> SELECT *
       FROM   Student
       WHERE  s_id in (SELECT s_id FROM Scholarship where amount > 3000);
+------+--------+----------+-------+
| s_id | s_name | address  | major |
+------+--------+----------+-------+
|  103 | Tom    | Edmonton | Math  |
+------+--------+----------+-------+
1 row in set (0.04 sec)

Note RubatoDB does not support distributed joins at this mement, and tus all the nested queries are evaluated at individual grid nodes.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.2 Temporary Table for Joins

Example 8. Consider the above examle. Assume one needs to find the s_id and s_name of all the students whose total amount of scholarship is larger than or equal to all other students. Because of lack of joins over different tables, this query cannot be evaluated corrected using the regular SQL queries. However, the problem can be easily resolved using a temporary table, as shown in the example below.

 
mysql> CREATE TABLE Student ( 
         s_id int , 
         s_name varchar(64), 
         address varchar(128),  
         major varchar(32)
       ) 
       PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE Scholarship ( 
         s_id int ,   
         scholarship varchar(64) ,  
         amount int
       )
       PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into Student values (101,'Peter','Edmonton','CS');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Student values (102,'Sarah','Calgary','CS');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Student values (103,'Tom','Edmonton','Math');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values (104,'Michelle','Toronto','Math');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Scholarship values (102,'Ruthford',3000);
Query OK, 1 row affected (0.03 sec)

mysql> insert into Scholarship values (103,'Ruthford',3000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into Scholarship values (103,'NSERC',6000);
Query OK, 1 row affected (0.00 sec)

mysql> -- Find the id and name of all the students with highest amount of scholarship
mysql> CREATE TEMPORARY TABLE temp1 
       AS SELECT s.s_id,s.s_name, sum(h.amount) as total
          FROM   student s, scholarship h
          WHERE  s.s_id = h.s_id
          GROUP BY s.s_id, s.s_name;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TEMPORARY TABLE temp2
       AS SELECT s.s_id,s.s_name, sum(h.amount) as total
          FROM   student s, scholarship h
          WHERE  s.s_id = h.s_id
          GROUP BY s.s_id, s.s_name;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT s_id, s_name 
       FROM   temp1 
       WHERE  total >= ALL (SELECT total FROM temp2);
+------+--------+
| s_id | s_name |
+------+--------+
|  103 | Tom    |
+------+--------+
2 rows in set (0.00 sec)

mysql> DROP TEMPORARY table temp1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TEMPORARY table temp2;
Query OK, 0 rows affected (0.00 sec)

Please note that (1) one has to drop temporary tables after uses, and (2) two temporary tables are used in this example because a temporary table cannot be used in both the main clause and the subquery clause.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.3 Outer Join

Example 9. The following example shows how to use left/right/outer joins. All queries are contained in the sql file rubatodb_dist/RubatoDB/tests/outer_join.sql.

 
mysql> create table Student ( 
         s_id int , 
         s_name varchar(64), 
         address varchar(128),  
         major varchar(32)
       ) 
       PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.01 sec)

mysql> create table Scholarship (
         s_id int ,   
         scholarship varchar(64) ,  
         amount int
       ) 
       PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into Student values (101,'Peter','Edmonton','CS');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Student values (102,'Sarah','Calgary','CS');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Student values (103,'Tom','Edmonton','Math');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values (104,'Michelle','Toronto','Math');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Scholarship values (102,'Ruthford',3000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Scholarship values (103,'Ruthford',3000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into Scholarship values (103,'NSERC',6000);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT s.s_id,   h.amount
       FROM   Student s left  join Scholarship  h 
              on s.s_id = h.s_id;
+------+--------+
| s_id | amount |
+------+--------+
|  102 |   3000 |
|  104 |   NULL |
|  103 |   6000 |
|  103 |   3000 |
|  101 |   NULL |
+------+--------+
5 rows in set (0.06 sec)

mysql> SELECT s.s_id,   sum(h.amount)
       FROM   Student s left  join Scholarship  h 
              on s.s_id = h.s_id
       GROUP BY s.s_id;
+------+---------------+
| s_id | sum(h.amount) |
+------+---------------+
|  101 |             0 |
|  102 |          3000 |
|  103 |          9000 |
|  104 |             0 |
+------+---------------+
4 rows in set (0.01 sec)

mysql> SELECT s.s_id,   h.amount
       FROM   Student s right  join Scholarship  h 
              on s.s_id = h.s_id;
+------+--------+
| s_id | amount |
+------+--------+
|  102 |   3000 |
|  103 |   6000 |
|  103 |   3000 |
+------+--------+
3 rows in set (0.04 sec)

mysql> SELECT s.s_id,   h.amount
       FROM   Student s inner  join Scholarship  h 
              on s.s_id = h.s_id;
+------+--------+
| s_id | amount |
+------+--------+
|  102 |   3000 |
|  103 |   6000 |
|  103 |   3000 |
+------+--------+
3 rows in set (0.04 sec)

mysql> 


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.4 Loading

Example 10. The following example first creates a table, stored at the grid 1, and then inserts one row using a standard insert statement. It further uses an SQL load statement to load 8 rows from a csv file, named pet.txt.

Note that one has to modify the path name of tests/pet.sql to be the actual path of the cvs file pet.txt.

 
mysql> CREATE TABLE pet (
        name    varchar(100) primary key,
        owner   varchar(100) not null,
        species varchar(30),
        sex     char(1),
        birth   date,
        death   date
     )
     stored at grid(1);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO pet VALUES('Sarah', 'Sarah', 'goat', 'f', '1979-08-31','2015-02-28');
Query OK, 1 row affected (0.02 sec)

mysql> LOAD DATA INFILE '/home/username/rubatodb0/tests/pet.txt' INTO TABLE pet;
Query OK, 8 rows affected (0.02 sec)

mysql> SELECT * FROM pet;
+----------+---------+---------+------+------------+------------+
| name     | owner   | species | sex  | birth      | death      |
+----------+---------+---------+------+------------+------------+
| Bowser   | Diane   | dog     | m    | 1979-08-31 | 1995-07-29 |
| Buffy    | Harold  | dog     | f    | 1989-05-13 | 0000-00-00 |
| Chirpy   | Gwen    | bird    | f    | 1998-09-11 | 0000-00-00 |
| Claws    | Gwen    | cat     | m    | 1994-03-17 | 0000-00-00 |
| Fang     | Benny   | dog     | m    | 1990-08-27 | 0000-00-00 |
| Fluffy   | nHarold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Sarah    | Sarah   | goat    | f    | 1979-08-31 | 2015-02-28 |
| Slim     | Benny   | snake   | m    | 1996-04-29 | 0000-00-00 |
| Whistler | Gwen    | bird    |      | 1997-12-09 | 0000-00-00 |
+----------+---------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> 


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.5 Cursor

RubatoDB supports cursors inside stored programs. The syntax is as in embedded SQL.

Example 11. The following example demos usages of RubatoDB cursors. (The statements are also contained in the file tests/cursor.sql.)

 
mysql> create table ttt(
          t1 int primary key,
          t2 varchar(100),
          t3 int
       ) 
       PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> create table ttt2 (
          t1 int primary key,
          t2 varchar(100),
          t3 int
       )  PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.03 sec)

mysql> create table ttt3 (
          t1 int primary key,
          t2 varchar(100)
       )  
       PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ttt values(10, 'just do it', 100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into ttt values(11, 'yes we can', 101);
Query OK, 1 row affected (0.01 sec)

mysql> insert into ttt values(20, 'just say no', 201);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttt values(21, 'change', 202);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert into ttt2 values(10, 'be happy', 100);
Query OK, 1 row affected (0.04 sec)

mysql> insert into ttt2 values(11, 'be cool', 101);
Query OK, 1 row affected (0.01 sec)

mysql> insert into ttt2 values(20, 'xyz says', 201);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttt2 values(21, 'abc di it', 202);
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE curdemo()
       BEGIN
         DECLARE done INT DEFAULT FALSE;
         DECLARE b varchar(100);
         DECLARE c varchar(100);
         DECLARE a INT;
         DECLARE cur1 CURSOR FOR SELECT t1,t2 FROM ttt;
         DECLARE cur2 CURSOR FOR SELECT t2 FROM ttt2;
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
       
         OPEN cur1;
         OPEN cur2;
       
         read_loop: LOOP
           FETCH cur1 INTO a, b;
           FETCH cur2 INTO c;
           IF done THEN
             LEAVE read_loop;
           END IF;
           IF b < c THEN
             INSERT INTO ttt3 VALUES (a,b);
           ELSE
             INSERT INTO ttt3 VALUES (a,c);
           END IF;
         END LOOP;
       
         CLOSE cur1;
         CLOSE cur2;
       END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> select * from ttt3;
Empty set (0.02 sec)

mysql> call curdemo();
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttt3;
+----+-------------+
| t1 | t2          |
+----+-------------+
| 10 | be happy    |
| 20 | just say no |
| 11 | be cool     |
| 21 | abc di it   |
+----+-------------+
4 rows in set (0.00 sec)

mysql> 


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.6 Stored Procedure

RubatoDB supports stored procedures and a demo example is given below. (The SQL statements for this example are contained in RubatoDB/tests/sp.sql.)

Example 12.

 
mysql> delimiter //
mysql> CREATE TABLE test(
         id int(11) NULL
       ) //
Query OK, 0 rows affected (0.02 sec)

mysql> create procedure sp1(in p int)
       begin
         declare v1 int;
         set v1 = p;
         insert into test(id) values(v1);
       end  //
Query OK, 0 rows affected (0.00 sec)

mysql> call sp1(1)//
Query OK, 2 rows affected (0.02 sec)

mysql> select * from test//
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.03 sec)

mysql> create procedure sp2(out p int)                                                   
       DETERMINISTIC
       begin
         select max(id) into p from test;
       end  //
Query OK, 0 rows affected (0.00 sec)

mysql> call sp2(@pv)//
Query OK, 2 rows affected (0.00 sec)

mysql> select @pv//
+------+
| @pv  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> create procedure sp3(in p1 int , out p2 int)
       begin
         if p1 = 1 then
           set @v = 10;
         else
           set @v = 20;
         end if;
          insert into test(id) values(@v);
         select max(id) into p2 from test;
       end //
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> call sp3(1,@ret)//
Query OK, 2 rows affected (0.00 sec)

mysql> select @ret//
+------+
| @ret |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> create procedure sp4(inout p4 int)
       begin
          if p4 = 4 then
             set @pg = 400;
          else
             set @pg = 500;
          end if; 
          select @pg;
       end//
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> call sp4(@pp)//
Query OK, 2 rows affected (0.00 sec)

mysql> select @pp//
+------+
| @pp  |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql> select @pg//
+------+
| @pg  |
+------+
|  500 |
+------+
1 row in set (0.00 sec)

mysql> set @pp = 5//
Query OK, 0 rows affected (0.00 sec)

mysql> call sp4(@pp)//
Query OK, 2 rows affected (0.00 sec)

mysql> select @pp//
+------+
| @pp  |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql> select @pg//
+------+
| @pg  |
+------+
|  500 |
+------+
1 row in set (0.00 sec)

mysql> 
mysql> delimiter ;
mysql> 


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.7 Trigger

Example 13. In this example, we use triggers on the insert to one table to populate another table. (See RubatoDB/tests/trig.sql.)

 
mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter |
mysql> 
mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
         FOR EACH ROW
         BEGIN
           INSERT INTO test2 SET a2 = NEW.a1;
         END;
       |
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> INSERT INTO test1 VALUES (2);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO test1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (7);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (8);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    2 |
|    4 |
|    4 |
|    7 |
|    8 |
+------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    2 |
|    4 |
|    4 |
|    7 |
|    8 |
+------+
6 rows in set (0.02 sec)

mysql> 

Example 14. This is another trigger example in which, we use triggers to enforce the constraint that any employee must have a supervisor. (See RubatoDB/tests/trig2.sql.)

 
mysql> CREATE TABLE employee(
         e_id int, 
         e_name varchar(64), 
         position varchar(64), 
         salary decimal(10,2) 
       ) 
       PARTITION BY GRID (e_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> CREATE TABLE supervision( 
         e_id int,
         e_name varchar(64), 
         s_name varchar(64) 
       ) 
       PARTITION BY GRID (e_id) TO 2 NODES WITH  mod (2); 
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> delimiter |
mysql> create trigger employee_supervisor
         before insert  on employee
         for each row
      
         begin
            declare   dummy int;
            declare   msg varchar(200); 
            select count(*) into dummy from supervision
            where new.e_name = supervision.e_name
                  and supervision.s_name is not NULL;
                 
            if ( dummy < 1 ) then
              set msg = 'employee must has a supervisor';  
                   SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg; 
            end if;
         end;
       |
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> INSERT INTO supervision VALUES (1,'tom','peter');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO employee VALUES (1,'tom','engineer',1000.00);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO employee VALUES (2,'shelly','engineer',1000.00);
ERROR 1644 (HY000):  employee must has a supervisor 

mysql> select * from supervision;
+------+--------+--------+
| e_id | e_name | s_name |
+------+--------+--------+
|    1 | tom    | peter  |
+------+--------+--------+
1 row in set (0.04 sec)

mysql> select * from employee;
+------+--------+----------+---------+
| e_id | e_name | position | salary  |
+------+--------+----------+---------+
|    1 | tom    | engineer | 1000.00 |
+------+--------+----------+---------+
1 row in set (0.04 sec)

mysql> 


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.8 Lob Operations

Example 15. The following two java programs are used to insert and select lobs into/from a database tables.

 
import java.io.*;
import java.sql.*;

/**
 *  A simple example to demonstrate how to use JDBCs PreparedStatement
 *  to insert a Blob into a table. The table PICTURE used in the example
 *  is created with
 *    create table picture (
 *        photo_id integer, 
 *        title varchar(48), 
 *        place varchar(48),
 *        sm_image blob,
 *        image blob,
 *        constraint picture_primary_key_constraint primary key(photo_id) )
 *
 *    create table picture (photo_id int primary key,title varchar(24),place varchar(24),sm_image blob,image blob);
 *
 *  @author  Li-Yan Yuan
 *
 */
public class InsertLobs {

    public static void main( String[] args)  {
	int  limit = 3;
	int  index, photo_id;

	//  change the following parameters to connect to other databases
	String username = "tpcone";
	String password = "a";
	String drivername = "com.mysql.jdbc.Driver";
	String dbstring = "jdbc:mysql://127.0.0.1:8010/?user="+username+"&"+password+"=a -A";

	Connection conn;
	PreparedStatement stmt;
	File file;

	// two local files of pictures
	String file1 = "rubatodb1.jpg";
	String file2 = "rubatodb2.jpg";
	
	try {
	    // to connect to the database
	    conn = getConnected(drivername,dbstring, username,password);
	 
	    System.out.println("connected ");

	    for (index=0;index < limit; index++) {
		photo_id = 10+ index;

		//  create a preparedStatement with 
		//   ?  represents the lobs to be inserted
	        stmt = conn.prepareStatement("insert into tpcone.picture values ( '" + photo_id + "',  'Rubatodb','Shanghai',?,?)" );

		// Set the first parameter 
		file = new File( file1 );
		stmt.setBinaryStream(1,new FileInputStream(file),(int)file.length());
		
		// set the second parameter
		file = new File(file2);
		stmt.setBinaryStream(2,new FileInputStream(file),(int)file.length());
	      
		// execute the insert statement
		stmt.executeUpdate();
		System.out.println( "the execution succeeds");
	    }
	    conn.close();
	} catch( Exception ex ) { 
	    System.out.println("exception");
	    System.out.println( ex.getMessage());
	}
    }

    /*
     *   To connect to the specified database
     */
    private static Connection getConnected( String drivername,
					    String dbstring,
					    String username, 
					    String password  ) 
	throws Exception {
	Class drvClass = Class.forName(drivername); 
	return( DriverManager.getConnection(dbstring));
    }
} 

 
import java.io.*;
import java.util.*;
import java.sql.*;
import java.text.*;
import java.net.*;

/**
 *  A simple example to demonstrate how to use servlet to 
 *  query and display a list of pictures
 *
 *  @author  Li-Yan Yuan
 *
 */
public class SelectLob {
    //  change the following parameters to connect to other databases
    static String username = "tpcone";
    static String password = "a";
    static String drivername = "com.mysql.jdbc.Driver";
    static String dbstring = "jdbc:mysql://127.0.0.1:8010/?user="+username+"&"+password+"=a -A";

    public static void main(String[] args) throws Exception {
	String sql = "select image FROM tpcone.picture where photo_id= 10";

	// to connect to the database
	Connection conn=getConnected(drivername,dbstring, username,password);

	PreparedStatement stmt = conn.prepareStatement(sql);
	ResultSet resultSet = stmt.executeQuery();
	File image = new File("downloaded_lob.jpg");
	FileOutputStream fos = new FileOutputStream(image);
	while (resultSet.next()) {
	    byte[] buffer = new byte[1];
	    InputStream is = resultSet.getBinaryStream(1);
	    while (is.read(buffer) > 0) {
		fos.write(buffer);
	    }
	}
	fos.close();
	conn.close();
    }

    /*
     *   To connect to the specified database
     */
    private static Connection getConnected( String drivername,
					    String dbstring,
					    String username, 
					    String password  ) 
	throws Exception {
	Class drvClass = Class.forName(drivername); 
	return( DriverManager.getConnection(dbstring));
    }
}


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6.9 Transaction

Example 16. This example demos the transaction facilities with the ACID properties supported by RubatoDB over distributed table.

 
mysql> CREATE TABLE employee( 
         e_id int, 
         e_name varchar(64), 
         position varchar(64), 
         salary decimal(10,2) 
       ) 
       PARTITION BY GRID (e_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO employee VALUES (1,'tom','engineer',1000.00);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO employee VALUES (2,'shelly','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+------+--------+----------+---------+
| e_id | e_name | position | salary  |
+------+--------+----------+---------+
|    2 | shelly | engineer | 1000.00 |
|    1 | tom    | engineer | 1000.00 |
+------+--------+----------+---------+
2 rows in set (0.04 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO employee VALUES (3,'tom','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (4,'shelly','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+------+--------+----------+---------+
| e_id | e_name | position | salary  |
+------+--------+----------+---------+
|    2 | shelly | engineer | 1000.00 |
|    1 | tom    | engineer | 1000.00 |
+------+--------+----------+---------+
2 rows in set (0.04 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO employee VALUES (3,'tom','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (4,'shelly','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select *  from employee;
+------+--------+----------+---------+
| e_id | e_name | position | salary  |
+------+--------+----------+---------+
|    2 | shelly | engineer | 1000.00 |
|    4 | shelly | engineer | 1000.00 |
|    1 | tom    | engineer | 1000.00 |
|    3 | tom    | engineer | 1000.00 |
+------+--------+----------+---------+
4 rows in set (0.04 sec)

mysql> 


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3. Database Administrator's Guide

3.1 Installation  
3.4 Server Runtime Environment  
3.5 Database User's Management  


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.1 Installation

To install the RubatoDB, one shall first take the following two steps:


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.1.1 Pre-installation

RubatoDB is designed to run on a set of commodity machines as one DBMS server, and the pre-installation outlined below must be done on each and every commodity machine in the set.

There are two approached to installing RubatoDB on a collection of Linux Servers:
[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.1.2 Manual Installation

Assuming that the distribution file is named rubatodb_dist.tar.gz, use the following to uncompress the file

 
 
Linux> tar -zxvf rubatodb_dist.tar.gz

Then the distribution package (under the directory RubatoDB_dist) contains the following directories and files:

The directory docs contains this documentation. The next three items are for the installer. The file linux_create.sh is a script file used to generate the RubatoDB system used for the 3.1.2 Manual Installation. README describes the basic info for the distribution, and the last directory RubatoDB contains the RubatoDB system.

To install RubatoDB manually on a Linux term, one needs to do the following steps.

Step 1. Creation of the Installation Packages

This is done, using linux_create.sh, with the following at the directory RubatoDB_dist.

 
        
Linux> ./linux_create.sh

This will generate the system package originally stored in RubatoDB. Please make sure that the Linux distribution (under the directory of RubatoDB_dist, after using running linux_create.sh, contains the following files and directories:

The newly added directories, bin, config, data, lib, and mysystem contain the RubatoDB system.

Step 2. Configuration of the System

To configure the Rubatodb server, one has to provide at least the following information:

An interactive configuration program, located as RubatoDB_dist/bin/one_step_setup, has been provided for configuring the aforementioned information. This program works only at the directory RubatoDB_dist/bin, and thus it should be done as follows, at the directory Rubatodb_dist

 
Linux> cd bin

Linux> one_step_setup

Follow the instructions on the screen after the program starts.

This will create system configuration files to be used for each and every node.

A screen shot of the configuration wit four grid nodes, using all the default values if possible, is given below.

 
Linux ./one_step_setup 
----------------------------------------------------------

   We are going to configure the RubatoDB server 

----------------------------------------------------------

   Enter monitor socket:  
   The   monitor socket:  8010
     Enter y to confirm:  y

----------------------------------------------------------

   The Monitor socket port is 8010 

----------------------------------------------------------


   Enter number of grid nodes:  4
   The   number of grid nodes:  4
     Enter y to confirm:  y

----------------------------------------------------------

   The number of Grid Nodes is 4 

----------------------------------------------------------


   Enter replication factor:  1
   The   replication factor:  1
     Enter y to confirm:  y

----------------------------------------------------------

   The Replication Factor is 1 

----------------------------------------------------------


----------------------------------------------------------
   Now, for each node, enter IP, Port, and Installation dorectory
----------------------------------------------------------


----------------------------------------------------------
   Node 0:  Replication 0 
           Enter the IP address:  
          Enter the port number:  
          Enter the replication port number:  
            Enter  installation:  

   Please confirm the following information 
   ------------------------------ 
        ip_address: 127.0.0.1
              port: 5000
 replication  port: 4000
      installation: /home/yuan/RubatoDB0_0
   ------------------------------ 

           Enter y to confirm::  y

----------------------------------------------------------


----------------------------------------------------------
   Node 1:  Replication 0 
           Enter the IP address:  
          Enter the port number:  
          Enter the replication port number:  
            Enter  installation:  

   Please confirm the following information 
   ------------------------------ 
        ip_address: 127.0.0.1
              port: 5100
 replication  port: 4100
      installation: /home/yuan/RubatoDB1_0
   ------------------------------ 

           Enter y to confirm::  y

----------------------------------------------------------


----------------------------------------------------------
   Node 2:  Replication 0 
           Enter the IP address:  
          Enter the port number:  
          Enter the replication port number:  
            Enter  installation:  

   Please confirm the following information 
   ------------------------------ 
        ip_address: 127.0.0.1
              port: 5200
 replication  port: 4200
      installation: /home/yuan/RubatoDB2_0
   ------------------------------ 

           Enter y to confirm::  y

----------------------------------------------------------


----------------------------------------------------------
   Node 3:  Replication 0 
           Enter the IP address:  
          Enter the port number:  
          Enter the replication port number:  
            Enter  installation:  

   Please confirm the following information 
   ------------------------------ 
        ip_address: 127.0.0.1
              port: 5300
 replication  port: 4300
      installation: /home/yuan/RubatoDB3_0
   ------------------------------ 

           Enter y to confirm::  y

----------------------------------------------------------

---------------------------------------------------------------------------------
 Node ID  Replication ID   IP Address   Port   R-Port     Installation Directory 
---------------------------------------------------------------------------------
       0        0         127.0.0.1     5000      4000     /home/yuan/RubatoDB0_0 
       1        0         127.0.0.1     5100      4100     /home/yuan/RubatoDB1_0 
       2        0         127.0.0.1     5200      4200     /home/yuan/RubatoDB2_0 
       3        0         127.0.0.1     5300      4300     /home/yuan/RubatoDB3_0 
---------------------------------------------------------------------------------

   Enter y to confirm the info:  y

Linux> 

Step 3. Installation of the System Code

In the previous step, each node is configured to be installed on Installation Directory of a server with the specific NodeIP.

The installation is a simple process of copying all documents and programs contained in the directory RubatoDB_dist into the Installation Directory of each and every node.

More specific, for each and every node, one needs to

This will complete the installation of the system code.

Step 4. Data Configuration

RubatoDB stores all the data files and lob files in the data cluster and lob cluster respectively.

The default data cluster and lob cluster are the following two directories under the installation directory, say RubatoDB,

Skip this step if you use the default setting.

A user may specify, for each and/or any node, the data cluster and/or lob cluster to be any directory under the server node, by editing the configuration file in the respective node

RubatoDB/config/logicsql.conf

where RubatoDB is the installation directory for the node.

Note that the system configuration file RubatoDB/config/logicsql.conf is used to set up desired values for the configuration variables.

In the sequence, by setting a configuration variable to be a specified value, we mean editing the line (or inserting a new line) that contains the variable such that the line will starts with the variable, followed by the value with one white space in between.

For example, by setting ServerPort to be 8010, we edit the line that contains ServerPort to the following ServerPort 8010

The following table summaries the basic task of configuration.

Note that different grid nodes need not to use the same configuration file, especially the path names of data cluster and lob cluster.

Step 5. Creation of the Initial Database

After successful installation of RubatoDB in all grid nodes, one has to create the initial database on all nodes. This can be done by using the following command in each and every node, assuming the full path name of the installation directory is /home/user_name/RubatoDB0 for the node:

Linux:> /home/user_name/RubatoDB0/bin/logicsql -d /home/user_name/RubatoDB0 -c

This will create the initial database system in the data cluster of the current node. This will also automatically create a system user named 'system' with the default password 'manager'.

The initial database will be created independently in each node, and thus, it can be done in any ordering of grid nodes.

Step 6. Starting the RubatoDB server

You must start the RubatoDB server before anyone can access the database. A simple example below to demo how to start the RubatoDB server.

Assume that you are going to run RubatoDB on four (4) grid nodes, and have configured the system accordingly. Further, all the initial databases have been created as well on all four grid nodes.

Start the RubatoDB using the following command on all our nodes, in the order of node0, node1, node2, node4, as follows:

Node0 Linux:> /home/user_name/RubatoDB0/bin/logicsql -d /home/user_name/RubatoDB0 -n0 &

Node1 Linux:> /home/user_name/RubatoDB1/bin/logicsql -d /home/user_name/RubatoDB1 -n1 &

Node2 Linux:> /home/user_name/RubatoDB2/bin/logicsql -d /home/user_name/RubatoDB2 -n2 &

Node3 Linux:> /home/user_name/RubatoDB3/bin/logicsql -d /home/user_name/RubatoDB3 -n3 &

One shall not start the server on Node 1 until the start at Node0 completes, and shall not start the server on Node2 until the start at Node1 completes, and so on so forth.

For each data cluster, only ONE grid node should be started at any time. A separate RubatoDB user account or the corresponding data cluster must be set up if you wish to run two nodes in one computer system.

New Schema One may also start the server using the provided script file to make sure the RubatoDB server will automatically re-start if it crashes for any reason.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.1.3 Auto Installation

A GUI tool, named installer, has been provided to facilitate the installation, configuration, start and stop of RubatoDB servers. Note that, at this moment, the auto installation works only for the Linux systems that support SSH with password-based authentication.

This part has not be modified to support Replication, and thus shall be ignored at this moment.

Installation process

After uncompressing, using the following:

 
 
Linux> tar -zxvf rubatodb_dist.4.0.2.tar.gz

the distribution package (under the directory RubatoDB_dist) contains the following directories and files:

The directory docs contains this documentation. The next three items are for the installer. The file linux_create.sh is a script file used to generate the RubatoDB system used for the 3.1.2 Manual Installation. The file mysql-gui-tools-5.0.2226203566.msi is used to install a MySQL GUI interface on Windows, and docs contains this documentation. README describes the basic info for the distribution, and the last directory RubatoDB contains the RubatoDB system.

RubatoDB can be easily installed using the provided GUI too. More specifically, depending on the system used, the installer starts by

The installer will first ask the user to enter the following information:

Following the prompts and instruction of the GUI, one can easily install, configure, start, and shutdown the RubatoDB over any number of servers.

After successful installation, the working directory consists of the following (and some others)

The file mysql-gui-tools-5.0.2226203566.msi is used to install a MySQL GUI interface on Windows, and docs contains this documentation.

The RubatoDB directory contains the following two files, among others,

Similar to the installer, these two files can be used to manage the server, that is, to shutdown and to start the RubatoDB server whenever needed.
[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.1.4 Installation of Application Tools

Two development tools, the MySQL interactive tool and the JDBC driver, are contained in RubatoDB/bin and RubatoDB/lib respectively, where RubatoDB is the installation directory of the node. Note that these two directories are also contained in the distribution directory RubatoDB_dist. One may also use the MySQL client package for application development.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.2 Monitoring Database Server

RubatoDB does not, at this moment, provide any GUI tools for monitoring the database server. However, the meta tables in the definition_schema contain all information about the database server status.

The following statement shows the list of all such meta tables.
 
mysql> show tables in definition_schema;
+-----------------------------+
| Tables_in_definition_schema |
+-----------------------------+
| checkpoint_record           |
| configuration_values        |
| create_table_stmt           |
| grid_nodes                  |
| grid_sockets                |
| index_files                 |
| key_column_usage            |
| partition_parent            |
| referential_constraints     |
| replication_sockets         |
| replications                |
| schemata                    |
| sqlstate_message            |
| system_limitation           |
| table_constraints           |
| table_distribution          |
| table_partitions            |
| tables                      |
| users                       |
+-----------------------------+
19 rows in set (0.00 sec)

For example, the following query displays the current status of all replicaiton nodes, where the values, 1, 2, and 3, of current_status stand for the master, slave, and not-available, respectively.

 
mysql> select * from definition_schema.replications ;
+---------+----------------+------------+---------------+-----------------+--------+
| grid_id | replication_id | ip_address | client_socket | internal_socket | status |
+---------+----------------+------------+---------------+-----------------+--------+
|       0 |              0 | 127.0.0.1  |          8010 |            5000 |      0 |
|       0 |              1 | 127.0.0.1  |          8020 |            5010 |      2 |
|       0 |              2 | 127.0.0.1  |          8030 |            5020 |      1 |
|       1 |              0 | 127.0.0.1  |             0 |            5100 |      1 |
|       1 |              1 | 127.0.0.1  |             0 |            5110 |      2 |
|       1 |              2 | 127.0.0.1  |             0 |            5120 |      2 |
+---------+----------------+------------+---------------+-----------------+--------+
6 rows in set (0.01 sec)

Another example, the following query displays the number of grid nodes configured for the server is three (3) , and the number of grid nodes that are available at the moment is two (2). That is, the grid_id 2 is not available because its current_status is 0.

 
mysql> select * from definition_schema.grid_nodes;
+---------+------------+-----------+---------------+----------------+
| grid_id | ip_address | grid_name | client_socket | current_status |
+---------+------------+-----------+---------------+----------------+
|       0 | 127.0.0.1  | Node0     |          8010 |              1 |
|       1 | 127.0.0.1  | Node1     |             0 |              1 |
|       2 | 127.0.0.1  | Node2     |             0 |              0 |
+---------+------------+-----------+---------------+----------------+

The following query shows that the server contains only one database (schema), named mytest, that is distributed over two nodes (0,1). Note that definition_schema and test are two system created databaes.

 
mysql> select * from definition_schema.schemata;
+--------------+-------------------+--------------+---------------------+----------+---------------+
| catalog_name | schema_name       | schema_owner | date_created        | no_grids | list_of_grids |
+--------------+-------------------+--------------+---------------------+----------+---------------+
| def          | definition_schema | root         | 2017-09-05 05:59:23 |        1 | 0,            |
| def          | mytest            | mytest2      | 2017-09-05 06:36:43 |        2 | 0,1,          |
| def          | test              | root         | 2017-09-05 05:59:23 |        1 | 0,            |
+--------------+-------------------+--------------+---------------------+----------+---------------+
3 rows in set (0.02 sec)


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.3 Managing Database Server


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.3.1 System Creation

After successful installation of RubatoDB in all grid nodes, one has to create the initial database on all nodes. This can be done by using the following command in each and every node, assuming the full path name of the installation directory is /home/user_name/RubatoDB0 for the node:

Linux:> /home/user_name/RubatoDB0/bin/logicsql -d /home/user_name/RubatoDB0 -c

This will create the initial database system in the data cluster of the current node. This will also automatically create a system user named 'system' with the default password 'manager'.

The initial database will be created independently in each node, and thus, it can be done in any ordering of grid nodes.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.3.2 Starting the RubatoDB server

You must start the RubatoDB server before anyone can access the database. A simple example below to demo how to start the RubatoDB server.

Assume that you are going to run RubatoDB on four (4) grid nodes, and have configured the system accordingly. Further, all the initial databases have been created as well on all four grid nodes.

Start the RubatoDB using the following command on all our nodes, in the order of node0, node1, node2, node4, as follows:

Node0 Linux:> /home/user_name/RubatoDB0/bin/logicsql -d /home/user_name/RubatoDB0 -n0 &

Node1 Linux:> /home/user_name/RubatoDB1/bin/logicsql -d /home/user_name/RubatoDB1 -n1 &

Node2 Linux:> /home/user_name/RubatoDB2/bin/logicsql -d /home/user_name/RubatoDB2 -n2 &

Node3 Linux:> /home/user_name/RubatoDB3/bin/logicsql -d /home/user_name/RubatoDB3 -n3 &

One shall not start the server on Node 1 until the start at Node0 completes, and shall not start the server on Node2 until the start at Node1 completes, and so on so forth.

For each data cluster, only ONE grid node should be started at any time. A separate RubatoDB user account or the corresponding data cluster must be set up if you wish to run two nodes in one computer system.

Command Line Options of logicsql

logicsql is used to create the initial database as well as start the server. It may also take some command line options, as listed below. Note that the command line option will override the configuration values if applicable.

 
Linux:> logicsql -h

Usage: logicsql [-options]

where options include
   -d                  specify the installation directory name, 
   -p <port_number>    specify the port number used by the server, 
                       the default is specifed by the configuation file
   -n                  the grid node ID
   -c                  create the initial database
   -r                  display all the output on screen
   -h                  print this message

Linux:>

Server Startup Failures

There are several common reasons for the failure. Some of possible reasons are: the socket is busy with other applications; the inappropriate setup of environment variables. Check the log file db_err.log in the log area for details.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.3.3 Shutting down the RubatoDB server

The RubatoDB server can be shut down with an SQL command shutdown

 
Linux> mysql --host 127.0.0.1 --port 8010 -u system
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.23-Rubato DB V4  MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> 

Of course, one may use the provided GUI tool to install, configure, start, and shutdown the RubatoDB

Note that only the system user can shutdown the database server. Also, one cannot shutdown the server if there exists any other active connections to the server.

Use the sf_dba to shutdown the server, even if there are other active connections. However, this must be used with extra precaution.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.4 Server Runtime Environment

RubatoDB user account As with any other server daemon that is connected to the world at large, it is advisable, though not necessary, to run RubatoDB under a separate user account. This user account should only own the data itself that is being managed by the server, and should not be shared with other daemons. To add a user account to your system, look for a command useradd or adduser. The user name "rubatodb" is often used but by no means required.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

3.5 Database User's Management

Managing database users and their privileges is in concept similar to that of Unix operating systems, but then again not identical enough to not warrant explanation.

There are two groups of database users, that is, a group of system users and a group of database users.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

4. Interfaces

RubatoDB adapts the MySQL communication protocol, and therefore, all the development tools of MySQL can be used to access the RubatoDB, including, but not limiting to, the following:

4.1 JDBC  
4.2 MySQL C API  
4.3 MySQL Interface  


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

4.1 JDBC

For your convenience, a JDBC driver (from MySQL) is included in the lib directory of the installation directory, and one may also download it from the net-land.

RubatoDB's JDBC drivers implement the standard JDBC (Java Database Connectivity) interface as defined by JavaSoft, except the following ones, and an SQLException will be thrown out if a method used has not been implemented.

The public interfaces in java.sql that have not been implemented are:

A description of JDBC can be found at http://www.javasoft.com

See Sample programs of using PreparedStatement.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

4.2 MySQL C API

The MySQL C API can be used to develop application programs that can access any RubatoDB server directly.

See Accessing: MySQL Database using MySQL C API at http://www.codeproject.com/Articles/34646/Accessing-MySQL-data-base-using-MySQL-C-API, for details.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

4.3 MySQL Interface

RubatoDB adapts the MySQL communication protocol and thus to use all the application development tools of MySQL. For your convenience, the binary code for Linux of MySQL Interactive rool mysql is included in the bin directory of the installation directory.

We show how to use mysql -- the MySQL command-line tool, to access the RubatoDB as follows. Note that we assume that the bin containing mysql is contained in the search path ($PATH).

  • Step 1 Start a RubatoDB server as usual.

  • Step 2 Use mysql, the MySQL interactive tool to access the RubatoDB by

     
      Linux:> mysql --host 127.0.0.1 --port 8020 --user system --database database --password -A
      Enter password: 
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 130
      Server version: 5.1.54-LogicSQL V2
    
      Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
      Oracle is a registered trademark of Oracle Corporation and/or its
      affiliates. Other names may be trademarks of their respective owners.
    
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
      mysql> create table mytest (id int, name varchar(100));
      Query OK, 0 rows affected (0.25 sec)
    
      mysql> insert into mytest values (100, 'be cool');
      Query OK, 0 rows affected (0.02 sec)
    
      mysql> insert into mytest values (200, 'just do it');
      Query OK, 0 rows affected (0.00 sec)
    
      mysql> select * from mytest;
      +-----+-------------+
      |  id  | name       |
      +-----+-------------+
      | 100 | be cool     |
      | 200 | just do it  |
      +-----+-------------+
      2 rows in set (0.00 sec)
    
      mysql> exit;
    
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5. Database Tests

    This chapter presents two different types of database tests to facilitate the users to understand the behavior and performance of RubatoDB.

    5.1 Big Data Load Tests  
    5.2 TPC-C Benchmark Tests  


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.1 Big Data Load Tests

    A simple program, named one_step_loadtest, using MySQL C API, is developed to test the load performance. It can be used to populate a set of records in the CSV format, and then load the csv file into a table.

    For example, we are able to load a table of 1 TB, partitioned into 8 grids distributed over 4 Linux server, in about 1 hour. Assume that the installation directory of the grid node 0 (the main node with the socket connection) is ~/node0. Then use the following steps to start the load test.

     
            
    Linux>  cd ~/node0/RubatoDB/bin/
    Linux>  ./one_step_loadtest
    
    

    Follow the on-screen instructions to configure and complete the test.

    The test will populate the stock table, per TPC_C specificaiton, and thus the data size depends on the number of warehouses.

    In the test, one needs to configure the following parameters.

    As per TPC-C specification, the CSV data will be 30GB per warehouses. Assume that one is going to conduct the test on 4 linux servers, connected with 1GB network cards, one may consider the following configuration

    With the CSV data size of 3TB, the population will take 48 hours, and the loading will take 4~6 hours.

    The following is a screenshot of loading using one_step_loadtest
     
    Linux> bin/one_step_loadtest 
    
    ----------------------------------------------------------
    
      We are going to conduct the load Test 
    
    ----------------------------------------------------------
               Enter the IP address:  
              Enter the port number:  
                   Enter  user name:  
                    Enter  password:  
          Enter the number of nodes:  2
          Enter the number of partitions per node:  3
    Enter the number of warehourses:  500
    
    Please confirm the following information 
    ------------------------------ 
           ip_address: 127.0.0.1
                 port: 8010
            user name: tpcone
          no of nodes: 2
     no of partitions: 3
     no of warehouses: 500
    ------------------------------ 
    
    Enter y to confirm the info:  y
    
    create_load_setup_file(setup_load.sql, 2, 3)
    It has been connected to the RubatoDB server
    
    
    ----------------------------------------------------------
    
    The RubatoDB server has 2 nodes, and the configured nodes is 2 
    
    ----------------------------------------------------------
    Cannot drop  user tpcone
    It has been connected to the RubatoDB server as user tpcone
    
    
    ----------------------------------------------------------
    
    Populate the CSV files with 500 warehouses
    
    
    ----------------------------------------------------------
    open_all_tpcfiles(4, 0)
    no_warehouses = 500
    Populate Item 
    Populate Warehouse, District and Stock for Warehouse(500): ....................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
    the loading is done and all the CSV files are in the current directory
    
    ----------------------------------------------------------
    
    Create the table stock using the sql file: 
    
    ----------------------------------------------------------
    
    Table table has been created
    
       stock table have been created,run mysql client on perform_test to see the load and query performance
    
    
    ----------------------------------------------------------
    
    Load stock table using csv files in /tmp/tpctest_csvs and run query to see performance
    
    ----------------------------------------------------------
    
     Query  1: LOAD DATA INFILE '/tmp/tpctest_csvs/stock.csv' INTO TABLE stock  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
    (Load)  50000000 rows in set (186903 millisec)
    
    
     Query  2: select count(*) from stock 
    +----------+
    | count(*) |
    +----------+
    | 50000000 |
    +----------+
     1 rows in set (68 millisec)
    
    
     Query  3: select * from stock where s_w_id=1 and s_i_id=1000 
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | s_w_id | s_i_id | s_quantity | s_dist_01 | s_dist_02 | s_dist_03 | s_dist_04 | s_dist_05 | s_dist_06 | s_dist_07 | s_dist_08 | s_dist_09 | s_dist_10 | s_ytd | s_order_cnt | s_remote_cnt | s_data |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    |      1 |   1000 |         99 |oaknnauakdeanbibvfgzwskl |jiesvjgxwukyqlitrkivtjqa |pyhzkrkuwcrhlpdfzbxmmrew |zrlptagskhedtabyrjjgoqiq |wegccibwtspvahhhlldlmbfv |nkavcnuiwwkeaizsywvfeerp |edijzsxluupkqxgnjmnslhqj |xpjqeqvkzgjtfwoclaskgftu |nfefqidgrtmiqwjxhccdsgqs |aagwudlbhrspuahpmpgjadqc |     0 |           0 |            0 |wxompppsxloayqjpkzlkidwzjhzoabxwalkq |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 rows in set (10 millisec)
    
    
     Query  4: select count(*) from stock where s_w_id=1 and s_i_id>1000 
    +----------+
    | count(*) |
    +----------+
    |    99000 |
    +----------+
     1 rows in set (26 millisec)
    
    
     Query  5: select count(*), max(s_i_id),sum(s_quantity)  from stock 
    +------------------------------------------+
    | count(*) | max(s_i_id) | sum(s_quantity) |
    +------------------------------------------+
    | 50000000 |      100000 |      2749707464 |
    +------------------------------------------+
     1 rows in set (37431 millisec)
    
    
     Query  6: select count(*), max(s_i_id),sum(s_quantity),s_w_id  from stock group by s_w_id 
    +---------------------------------------------------+
    | count(*) | max(s_i_id) | sum(s_quantity) | s_w_id |
    +---------------------------------------------------+
    |   100000 |      100000 |         5489111 |      1 |
    |   100000 |      100000 |         5496239 |      3 |
    |   100000 |      100000 |         5494360 |      5 |
    |                                                   |
    |        ...                                        |
    |                                                   |
    +---------------------------------------------------+
     500 rows in set (100679 millisec)
    
    
     Query  7: select count(*), max(s_i_id),sum(s_quantity),s_w_id  from stock group by s_w_id  having sum(s_quantity) <5500000  
    +---------------------------------------------------+
    | count(*) | max(s_i_id) | sum(s_quantity) | s_w_id |
    +---------------------------------------------------+
    |   100000 |      100000 |         5492511 |      2 |
    |   100000 |      100000 |         5492806 |     10 |
    |   100000 |      100000 |         5496553 |     12 |
    |                                                   |
    |        ...                                        |
    |                                                   |
    +---------------------------------------------------+
     261 rows in set (130582 millisec)
    
    
     Query  8: select count(*) from stock where s_w_id <=s_i_id 
    +----------+
    | count(*) |
    +----------+
    | 49875250 |
    +----------+
     1 rows in set (10224 millisec)
    
    
     Query  9: select count(*) from stock where s_w_id <s_i_id or s_w_id >s_i_id 
    +----------+
    | count(*) |
    +----------+
    | 49999500 |
    +----------+
     1 rows in set (5217 millisec)
    
    
     Query 10: select s_w_id+s_i_id,sum(s_quantity) from stock group by s_w_id+s_i_id 
    +---------------------------------+
    | s_w_id+s_i_id | sum(s_quantity) |
    +---------------------------------+
    |             2 |              54 |
    |             3 |             129 |
    |             4 |              73 |
    |                                 |
    |        ...                      |
    |                                 |
    +---------------------------------+
     100001 rows in set (157215 millisec)
    
    
     Query 11: select s_w_id+s_i_id,sum(s_quantity) from stock group by s_w_id+s_i_id order by 2 
    +---------------------------------+
    | s_w_id+s_i_id | sum(s_quantity) |
    +---------------------------------+
    |             2 |              54 |
    |             4 |              73 |
    |             3 |             129 |
    |                                 |
    |        ...                      |
    |                                 |
    +---------------------------------+
     100001 rows in set (151173 millisec)
    
    
     Query 12: select s_w_id,s_i_id,s_quantity,s_dist_01,s_ytd,s_order_cnt,s_remote_cnt from stock 
    +--------------------------------------------------------------------------------------------+
    | s_w_id | s_i_id | s_quantity | s_dist_01               | s_ytd | s_order_cnt | s_remote_cnt |
    +---------------------------------------------------------------------------------------------+
    |      6 |      1 |         22 |feksygpezxfptupvhvfktzzu |     0 |           0 |            0 |
    |      6 |      2 |         12 |cvobhvcheapkhxvehiezzkqf |     0 |           0 |            0 |
    |      6 |      3 |         81 |kfhutubjwqhasjpxeyowftbp |     0 |           0 |            0 |
    |                                                                                             |
    |        ...                                                                                  |
    |                                                                                             |
    +---------------------------------------------------------------------------------------------+
     200000 rows in set (149 millisec)
    
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.2 TPC-C Benchmark Tests

    The TPC-C benchmark test is a comprehensive database benchmark test that continues to be a popular yardstick for comparing OLTP performance on various hardware and software configurations. (see http://www.tpc.org for details).

    Similar to the big data load test, we also provide a program, named one_step_tpctest, to conduct the TPC-C benchmark test over any number of nodes. The procedure is the same as above, that is, it must be used in the directory of ~/node0/RubatoDB/bin, where ~/node0 refers to the installation directory on Node 0. The following lists the steps to conduct the TPC-C benchmark test.

     
            
    Linux>  cd ~/node0/RubatoDB/bin/
    Linux>  ./one_step_tpctest
    
    

    Follow the on-screen instructions of one_step_tpctest to configure and complete the test.

    In the test, one needs to configure the following parameters:

    Note that the default number of transactions per concurrent user is 400, which takes approximately 140 minutes to finish.

    A sample configuration for two servers, each with 128G memory is as follows:

    By the TPC-C specification, this test accommodate 10,000 individual users that concurrently access the database consisting of 8 tables distributed over 6 grid nodes. The data size of sch a TPC-C database is around 60-80 GBytes.

    For your conencience, a sample script file to construct the test with such configuration is provided as ~/node0/RubatoDB/bin/setup6.sql.

    In the current release version, the max number of concurrent users is 11,000, and the max number of grid nodes is 8.

    The following is a screen copy of such a test.

     
    Linux>  cd ~/node0/RubatoDB/bin
    
    Linux> ./one_step_tpctest
    
    ----------------------------------------------------------------------------
    
      I am going to conduct the TPC-C Bencharmark Test
    
    ----------------------------------------------------------------------------
               Enter the IP address:  
              Enter the port number:  
                   Enter  user name:  
                    Enter  password:  
          Enter the number of nodes:  8
    Enter the number of warehourses:  1000
    
    Please confirm the following information 
    ------------------------------ 
           ip_address: 127.0.0.1
                 port: 8010
            user name: tpcone
          no of nodes: 8
     no of warehouses: 1000
    ------------------------------ 
    
    Enter y to confirm the info:  y
    
    ----------------------------------------------------------
    
    The RubatoDB server has 8 nodes, and the configured nodes is 8
    
    ----------------------------------------------------------
    Cannot drop user tpcone
    User tpcone has been created
    It has been connected to the RubatoDB server as user tpcone
    
    ----------------------------------------------------------
    
    It takes approximately 50 minutes to populate 1000 warehouses 
    
    ----------------------------------------------------------
    open_all_tpcfiles(12, 0)
    Populate Item 
    Populate Stock with 1000 warehouses: ................... 100 ................... 200 ................... 300 ................... 400 ................... 500 ................... 600 ................... 700 ................... 800 ................... 900 ................... 1000  done
    Populate Customer, Orders and Order_line: ................... 100 ................... 200 ................... 300 ................... 400 ................... 500 ................... 600 ................... 700 ................... 800 ................... 900 ................... 1000  done
    the population is done and all the CSV files are in the current directory
    
    ----------------------------------------------------------
    
    Create the table schema using the sql file: setup.sql
    
    ----------------------------------------------------------
    
    Table warehouse has been created
    Table district has been created
    Table customer1 has been created
    Table customer2 has been created
    Table customer3 has been created
    Table history has been created
    Table orders has been created
    Table new_order has been created
    Table item has been created
    Table stock1 has been created
    Table stock2 has been created
    Table order_line has been created
    
       All 12 TPC-C tables have been created
    
    
    ----------------------------------------------------------
    
    Load 12 tables using csv files in /tmp/tpctest_csvs
    It takes approximately 50 minutes to load all tables
    
    ----------------------------------------------------------
    
    Table item has been loaded 
    Table warehouse has been loaded 
    Table district has been loaded 
    Table stock2 has been loaded 
    Table customer2 has been loaded 
    Table history has been loaded 
    Table orders has been loaded 
    Table new_order has been loaded 
    Table order_line has been loaded 
    Table stock1 has been loaded 
    Table customer1 has been loaded 
    Table customer3 has been loaded 
    
       All 12 TPC-C tables have been loaded
    
    It has been connected to the RubatoDB server as user tpcone
    
    ----------------------------------------------------------
    
    Conduct the TPC-C test for 10000 concurrent clients on 1000 warehouses
    
    ----------------------------------------------------------
    
       I am doing the TPC benchmark test with the following configuration
    
                 warehouse id:  start 1, end 1000, total 1000
            number of clients:  10000
      transactions per client:  400
    
      It takes approximately 200 minutes 
    
    ----------------------------------------------------------------------------
    
    
     --------------------------------------------------------
           mixed transactions: 4000000
          total time (second): 10264.000000
          transactions/minute: 23382
    
    +--------------------------------------------------------------------------------------------------+
    | chk_time            | clients | transaction_id | roll_backs | committed | transaction_per_minute |
    +--------------------------------------------------------------------------------------------------+
    | 2015-12-27 07:01:57 |   10003 |         331108 |       1674 |    319241 |                  10700 |
    | 2015-12-27 07:31:57 |   10003 |        1149353 |       2777 |    815533 |                  27277 |
    | 2015-12-27 08:01:57 |   10003 |        1975335 |       1503 |    824462 |                  27532 |
    | 2015-12-27 08:31:57 |   10003 |        2783338 |       4203 |    803786 |                  26933 |
    | 2015-12-27 09:01:57 |    9948 |        3612749 |        356 |    829012 |                  27644 |
    | 2015-12-27 09:31:57 |      42 |        4019853 |        530 |    396671 |                  13239 |
    | 2015-12-27 09:54:03 |       4 |        4082825 |          0 |       233 |                     10 |
    +--------------------------------------------------------------------------------------------------+
    
    ----------------------------------------------------------
    
    Conduct the TPC-Check to see if all 10 conditions are satisfied.
     It takes approximately 50 minutes. 
    
    ----------------------------------------------------------
    
    the server has been connected
    The total number of warehouses is 1000
    
    Test Conditions 1, 8, and 9:
    Consistency Condition 1 is satisified
    Consistency Condition 8 is satisified
    Consistency Condition 9 is satisified
    
    Test Condition 11: 
    Consistency condition 11 is satisfied
    
    Test Conditions 2 and 3:
    Consistency condition 2 is satisfied
    Consistency condition 3 is satisfied
    
    Test Condition 4:
    Consistency condition 4 is satisfied
    
    Test Conditions 5 and 7
    Consistency condition 5 is satisfied
    Consistency condition 7 is satisfied
    
    Test Condition 6: It may take a while.
    Consistency condition 6 is satisfied
    
    Test Conditions 10 and 12: this may take quite a while.
    Consistency condition 10 is satisfied
    Consistency condition 12 is satisfied
    
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    6. Performance

    We have conducted extensive experiments of RubatoDB which clearly show that RubatoDB is highly scalable with ACID under the TPC-C benchmark tests. Our experiments using the YCSB benchmark also demonstrate that the performance of RubatoDB is comparable with some popular big data systems based on the MapReduce framework that supports only the BASE properties.

    The figure below demonstrates that the performance (tpmC) of the TPC-C benchmark test of RubatoDB, from 25,000 concurrent clients running on 1 server, to 320,000 concurrent clients running on a collection of 16 commodity servers.

    scalable50

    The figure clearly shows that under the TPC-C benchmark test, the performance of RubatoDB scales up linearly with the increase of the number of servers used.

    The following figure compares the performance of RubatoDB with three other popular key-store systems, in terms of the number of throughput, a standard performance measurement of the YCSB benchmark.

    readthroughput

    From the graphics, we can see that the performance of RubatoDB is comparable with these big data systems that only support the BASE properties.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    7. References


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    Concept Index

    Jump to:   B   C   D   L   P   R   S   T   U  

    Index Entry Section

    B
    Big Data Load Tests5.1 Big Data Load Tests

    C
    create sequence2.5 Create Sequence
    creation of the initial database3.1.2 Manual Installation

    D
    data area3.1.2 Manual Installation
    data cluster3.1.2 Manual Installation
    data configuration3.1.2 Manual Installation
    Database Tests5. Database Tests
    drop sequence2.5.1 Drop Sequence

    L
    lob cluster3.1.2 Manual Installation
    lobs area3.1.2 Manual Installation
    logicsql3.3.2 Starting the RubatoDB server

    P
    Performance6. Performance

    R
    References7. References

    S
    server3.3.2 Starting the RubatoDB server
    server shut down3.3.3 Shutting down the RubatoDB server
    server startup failures3.3.2 Starting the RubatoDB server
    Shutting down the RubatoDB server3.3.3 Shutting down the RubatoDB server
    Starting the RubatoDB server3.3.2 Starting the RubatoDB server
    starting the RubatoDB server}3.1.2 Manual Installation
    system creation3.3.1 System Creation

    T
    TPC-C Benchmark Tests5.2 TPC-C Benchmark Tests

    U
    user account3.4 Server Runtime Environment

    Jump to:   B   C   D   L   P   R   S   T   U  


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    Bug Report and Contact Information

    Please report all the bugs by sending emails to lyuan@ualberta.ca


    [Top] [Contents] [Index] [ ? ]

    Table of Contents


    [Top] [Contents] [Index] [ ? ]

    Short Table of Contents

    1. Introduction
    2. User's Guide
    3. Database Administrator's Guide
    4. Interfaces
    5. Database Tests
    6. Performance
    7. References
    Concept Index
    Bug Report and Contact Information

    [Top] [Contents] [Index] [ ? ]

    About this document

    This document was generated by Li-Yan Yuan on November, 7 2017 using texi2html

    The buttons in the navigation panels have the following meaning:

    Button Name Go to From 1.2.3 go to
    [ < ] Back previous section in reading order 1.2.2
    [ > ] Forward next section in reading order 1.2.4
    [ << ] FastBack previous or up-and-previous section 1.1
    [ Up ] Up up section 1.2
    [ >> ] FastForward next or up-and-next section 1.3
    [Top] Top cover (top) of document  
    [Contents] Contents table of contents  
    [Index] Index concept index  
    [ ? ] About this page  

    where the Example assumes that the current position is at Subsubsection One-Two-Three of a document of the following structure:

    This document was generated by Li-Yan Yuan on November, 7 2017 using texi2html