The ScaleDB Storage Engine - PowerPoint PPT Presentation

Title: The ScaleDB Storage Engine


1
The ScaleDB Storage Engine

• Enabling high performance and scalability, using
  a Multi-Table Index, and a Shared-Disk Clustering
  Architecture

Moshe Shadmon moshe_at_scaledb.com
2
Agenda

• Overview
• ScaleDBs Clustering Architecture
• Shared-Disk vs. Shared-Nothing
• MySQL and a Shared-Disk Storage Engine
• ScaleDB Installation
• Demo
• ScaleDBs Indexing Technology
• Multi-Table Index
• Enabling Multi-Table Index in MySQL
• Demo
• Summary
• ScaleDB Status Product Availability

3
Overview

• Plug-in Storage Engine for MySQL
• Main Features
• Shared-Disk Architecture
• Innovative Multi-Table Indexing
• Transactional
• Row-Level Locking
• ACID Compliant
• Atomicity All tasks of a transaction performed
  or none of them are.
• Consistency The database is in a consistent
  state before and after the transaction.
• Isolation Data is not available in an
  intermediate state during a transaction
• Durability When a transaction completes, the
  transactions data will persist
• Disk-Based Storage Engine

4
Shared-Disk vs. Shared-Nothing

• Manageability
• Adaptability
• Availability/Fault-Tolerance
• Scalability
• Performance
• Total Cost of Ownership (TCO)

5
Shared-Nothing
DatabaseInstance 1
Table A
Table B
Table C
6
Shared Nothing Partitioning Your DataHow

• Predict usage patterns, application evolution,
  data growth patternsall are moving targets
• Avoid data skew bottlenecks caused by frequently
  accessed data on just a few nodes
• Avoid data shipping between nodes
• Avoid delays from distributed 2-phase commit
• Searches outside the partition column require
  participation by all nodes
• Scaling becomes an exercise in fire fighting

7
Shared-NothingHorizontal Partitioning
Logical View
salary
name
age
Partitioned by Salary
salary
name
age
salary
name
age
salary
name
age
Physical View
Horizontal Partitioning Salary 3
8
Shared-NothingHorizontal Partitioning Pitfalls

• Selections with equality predicates referencing
  the partitioning attribute are directed to a
  single node
• Retrieve Emp where salary 60K
• SELECT FROM Emp WHERE salary60K
• Equality predicates referencing a
  non-partitioning attribute and range predicates
  are directed to all nodes
• Retrieve Emp where age 20
• Retrieve Emp where salary lt 20K
• SELECT FROM Emp WHERE salarylt20K

9
Shared-DiskNo Partitioning, Full Access to Data
DatabaseInstance 1
Table A
Table B
Table C
10
Scalability Availability Shared Nothing
11
Scalability Availability Shared Disk
MySQL Servers with ScaleDB Engine
Node A
Node B
Node C
Data
12
Shared-DiskSummarizing Shared-Disk Benefits

• Grow by simply adding nodes to the cluster
• Servers can be added and removed dynamically
  according to your needs
• No interruption to your application
• High-Availability with dynamic failover
• Existing nodes automatically take over
• Significantly reduced maintenance costs
• Can be built on low-cost commodity hardware
• No data partitioning
• No need for slaves
• Low Total Cost of Ownership (TCO)

13
Shared-DiskMaking it work with MySQL
Server Instance A
ScaleDB Engine Instance A
ClusterManager
Comm. Layer
Buffer Manager
14
Shared-Disk Insert New Row
Node 1
Node 2
Server Instance A
Server Instance B
ScaleDB Engine Instance A
ScaleDB Engine Instance B
ClusterManager
ClusterManager
Comm. Layer
Comm. Layer
Buffer Manager
Buffer Manager
15
Shared-Disk Select
Node 1
Node 2
Server Instance A
Server Instance B
ScaleDB Engine Instance A
ScaleDB Engine Instance B
ClusterManager
ClusterManager
Comm. Layer
Comm. Layer
Buffer Manager
Buffer Manager
16
Shared-Disk Create Table
Node 1
Node 2
Server Instance A
Server Instance B
ScaleDB Engine Instance A
ScaleDB Engine Instance B
ClusterManager
ClusterManager
Comm. Layer
Comm. Layer
Buffer Manager
Buffer Manager
17
ScaleDB Installation

• Define cluster true in ScaleDB Config file
• ScaleDB.cnf is at the same directory as my.cnf
• Cluster params
• cluster true
• nodes_in_cluster 2
• node_id 1
• this_machine_port 100
• next_machine_ip_address 192.168.0.101
• next_machine_port 100
• log_directory /share/logs/

18
Demo - Sysbench

• ScaleDB cluster one node show throughput
• ScaleDB cluster 2nd node show throughput

19
ScaleDB Multi-Table Indexing
B-tree Only indexes the data in tables
Index 1
Index 2
Index 3
Index 4
Index 5

• Advantages
• Faster
• Smaller
• Referential integrity

20
Example

• Scenario Select information that is spread
  across 3 tables Colleges, Students and
  Enrollment
• Relationships Students are enrolled in courses
  within departments of colleges

SELECT c1.CollName, s.StudName, c2.CourseName ,
e.Grade FROM College AS c1 JOIN Student AS s
JOIN Enrollment AS e JOIN Course AS c2 ON
( c1.CollNo s.CollNo AND s.CollNo
e.CollNo AND s.StudentNo e.StudentNo
AND e.CollNo c2.CollNo AND
e.DeptNo c2.DeptNo AND e.CourseNum
c2.CourseNum ) WHERE c1.CollNo X AND
s.StudentNo Y
21
Option 1 Conventional Joins
College Table
Students Table
Enrollment Table
22
Option 2 Materialized View
. . .
23
Mapping Foreign Keys to Data Views

• The Parent-Child tables are Created in MySQL Such
  that MySQL is able to operate over the new tables
• The data of the Parent-Child tables is assembled
  on the fly from the source tables

24
Mapping Foreign Keys to Data Views
Department
College
Course
Department
College
Students
College
Enrollment
Students
College
25
Enabling the MySQL optimizer to use a Multi-Table
Index
SELECT c1.CollName, s.StudName,
c2.CourseName , e.Grade FROM College AS c1
JOIN Student AS s JOIN Enrollment AS e JOIN
Course AS c2 ON ( c1.CollNo s.CollNo AND
s.CollNo e.CollNo AND
s.StudentNo e.StudentNo AND e.CollNo
c2.CollNo AND e.DeptNo c2.DeptNo AND
e.CourseNum c2.CourseNum ) WHERE
c1.CollNo X AND s.StudentNo Y
CREATE TABLE sdb_view_college_course_student (
L1_CollNo INT NOT NULL, L1_CollName CHAR(32)
NOT NULL, L1_CollBudget INT NOT NULL,
L1_CollDescription CHAR(60) NOT NULL, Table
College Columns L2_StudNo INT NOT NULL,
L2_StudName CHAR(48) NOT NULL, Table Student
Columns L3_CourseNum CHAR(9) NOT NULL,
L3_Grade CHAR(2) NOT NULL, Table Enrollment
Columns PRIMARY KEY ( L1_CollNo, L2_StudtNo,
L3_CourseNum)) ENGINE SCALEDB

• Select L1_CollName, L2_StudName, L3_CourseName,
  L3_Grade
• FROM sdb_view_college_course_student WHERE
  l1_CollNo X AND l2_StudentNo Y

26
Option 3 Multi-Table Index
ScaleDB Multi-Table Index
College
Students
Departments
Enrollment
Courses
Col_ID Col_Name Col_Budget
Col_Description
Coll_ID Coll_Name Coll_Budget Coll_Description
Student_ID College_ID Student_Name
Student_Desc
College_ID Dept_ID Student_ID Grade
27
The Multi-Table Index

• Multi-Table Index appears to MySQL as a data
  table
• ScaleDB does not maintain data file associated
  with the Multi-Table Index
• For a query using virtual table, ScaleDB
  assembles the rows on the fly using the
  Multi-Table Index
• ScaleDB indexes are different than B-tree indexes
• ScaleDB indexes provide the same functionality as
  B-tree, plus
• They maintain referential integrity with minimal
  overhead
• They allow you to search for the data and
  relationships
• They are much smaller in size

28
Demo

• Query with join
• Query with Multi-Table Index
• 2nd node virtual table

29
Benchmarking ScaleDB Index
30
Summary

• ScaleDB Cluster
• Multiple ScaleDB instances share the same
  physical data.
• Connecting to the cluster is similar to
  connecting to a single node.
• For the application, the cluster appears as a
  single node.
• Transparent application failover
• Transparent Scalability
• ScaleDB Indexes
• Provide the B-tree functionality
• High performance
• Map relationships
• Maintain referential integrity
• Smaller footprint
• Independent of the key size

31
ScaleDB Status and Product Availability

• Started Beta Process
• We are looking for beta companies
• Product launch is scheduled for June timeframe
• Please talk to us if you are developer interested
  in working with ScaleDB
• moshe_at_scaledb.com