Download - ScaleDB Technical Presentation
![Page 1: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/1.jpg)
Thursday April 17, 2008
ScaleDB Technical Presentation
Scaling MySQL to New Heights
![Page 2: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/2.jpg)
ScaleDB
ScaleDB for MySQL
Database
Storage EngineInnoDB, MyISAM, Cluster, Falcon, BDB, Merge, etc.
![Page 3: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/3.jpg)
ScaleDB
What Makes ScaleDB Better?
• ScaleDB Advantages:• Performance: New indexing delivers dramatic
performance improvement• Scalability: Designed for clustering with Plug-
and-Cluster™ Architecture
![Page 4: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/4.jpg)
Improving Performance
![Page 5: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/5.jpg)
ScaleDB
ScaleDB Indexing
Conventional Indexing(B-tree)
General PurposeIndexing
Hash Bitmap Aggregate Etc.Special-purposeIndex Add-ons*
*Only supported by high-end commercial databases
ScaleDB Index: A general purpose index that also delivers much of the functionality and performance of special-purpose index add-ons
![Page 6: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/6.jpg)
ScaleDB
ScaleDB: Multi-Table IndexingB-tree: Only indexes the data in tables
Index #1
#1 #2Index #2 Index #3 Index #4 Index #5
#3 #4 #5
ScaleDB Index
#1
#2
#3
#4
#5
ScaleDB: Indexes the data and relationships Advantages:• Faster• Smaller• Referential integrity• More functionality
![Page 7: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/7.jpg)
ScaleDB
Describing Our Demo
• Scenario: Select information that is spread across 3 tables: Colleges, Students and Enrollment
• Relationships: Students are enrolled in courses within departments of colleges
• DDL Definitions
![Page 8: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/8.jpg)
ScaleDB
The QuerySELECT c1.CollName, s.StudName, c2.CourseName , e.Grade FROM College AS c1 STRAIGHT_JOIN Student AS s STRAIGHT_JOIN Enrollment AS e STRAIGHT_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 ;
![Page 9: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/9.jpg)
ScaleDB
Option #1: Conventional Joins
Col_ID# Col_Name Col_Budget Col_DescriptionColleges
001 Agriculture $1,234,567 Nice place to visit
002 Arts $5,432,567 Sports not so good
003 Business $9,999,666 Cool logo
004 Education $3,234,567 Ugh Worcester
005 Engineering $8,238,568 Serious work
006 Law $7,237,767 Jumpy students
007 Liberal Arts $9,898,777 Pretty campus
008 Medicine $5,987,004 In Texas
Students
56-8033 008 Mike Hogan Caucasian
56-8045 008 Moshe Smith Caucasian
56-8044 008 Sally Shadmon Native American
56-8055 008 Billy Fleegle African American
56-8037 008 Saul Goode African American
56-8122 008 Tim Collins Polynesian
56-8233 008 Sam Gee Asian
56-8334 008 Rod Paulino Asian
Enrollment
008 4455 56-8037 B+
008 4455 56-8033 C
008 4455 56-8045 B+
008 4456 56-8044 A-
008 4456 56-8122 B-
008 4454 56-8233 C
008 4455 56-8334 F
008 4454 56-8055 D
Coll_ID# Coll_Name Coll_Budget Coll_Description Student_ID# College_ID# Student_Name Student_Desc College_ID# Dept_ID# Student_ID# Grade
Colleges Index(s) Students Index(s) Enrollment Index(s)
Query Result: 008 Medicine $5,987,004 In Texas | 56-8037 Saul Goode African American | 4455 B+ |
Join Join
![Page 10: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/10.jpg)
ScaleDB
Col_ID# Col_Name Col_Budget Col_DescriptionMaterialized View
Coll_ID# Coll_Name Coll_Budget Coll_Description Student_ID# Student_Name Student_Desc Dept_ID# Grade
Col_ID# Col_Name Col_Budget Col_Description
…………
001 Agriculture $1,234,567 Nice place to visit 56-8033 Mike Hogan Caucasian 3345 A
001 Agriculture $1,234,567 Nice place to visit 56-8033 Mike Hogan Caucasian 3235 B+
001 Agriculture $1,234,567 Nice place to visit 56-8033 Mike Hogan Caucasian 3245 A-
001 Agriculture $1,234,567 Nice place to visit 56-8033 Mike Hogan Caucasian 3245 B
001 Agriculture $1,234,567 Nice place to visit 56-8033 Mike Hogan Caucasian 3235 A+
001 Agriculture $1,234,567 Nice place to visit 56-8034 Paul Martyn Caucasian 3239 A-
001 Agriculture $1,234,567 Nice place to visit 56-8034 Paul Martyn Caucasian 3239 B
001 Agriculture $1,234,567 Nice place to visit 56-8034 Paul Martyn Caucasian 3240 A+
008 Medicine $5,987,004 In Texas 56-8037 Saul Goode African American 4455 A
008 Medicine $ 5,987,004 In Texas 56-8037 Saul Goode African American 4455 A
008 Medicine $ 5,987,004 In Texas 56-8037 Saul Goode African American 4455 B+
008 Medicine $ 5,987,004 In Texas 56-8037 Saul Goode African American 4455 A-
008 Medicine $ 5,987,004 In Texas 56-8037 Saul Goode African American 4455 B
008 Medicine $ 5,987,004 In Texas 56-8039 Paul Martyn Caucasian 4454 A-
008 Medicine $ 5,987,004 In Texas 56-8039 Paul Martyn Caucasian 4454 B
008 Medicine $ 5,987,004 In Texas 56-8039 Paul Martyn Caucasian 4454 A+
Enrollment
008 4455 56-8037 B+
008 4455 56-8033 C
008 4455 56-8045 B+
008 4456 56-8044 A-
008 4456 56-8122 B-
008 4454 56-8233 C
008 4455 56-8334 F
008 4454 56-8055 D
Col_ID# Col_Name Col_Budget Col_DescriptionColleges
001 Agriculture $1,234,567 Nice place to visit
002 Arts $5,432,567 Sports not so good
003 Business $9,999,666 Cool logo
004 Education $3,234,567 Ugh Worcester
005 Engineering $8,238,568 Serious work
006 Law $7,237,767 Jumpy students
007 Liberal Arts $9,898,777 Pretty campus
008 Medicine $5,987,004 In Texas
Students
56-8033 008 Mike Hogan Caucasian
56-8045 008 Moshe Smith Caucasian
56-8044 008 Sally Shadmon Native American
56-8055 008 Billy Fleegle African American
56-8037 008 Saul Goode African American
56-8122 008 Tim Collins Polynesian
56-8233 008 Sam Gee Asian
56-8334 008 Rod Paulino Asian
Coll_ID# Coll_Name Coll_Budget Coll_Description Dept_ID# Dept_Name Coll_ID# Dept_Budget Course_ID# Course_Name Coll_ID# Dept_ID#
Copies (and synchronizes) the data from individual tables into one massive view
Option #2: Materialized View
Query Result: 008 Medicine $5,987,004 In Texas | 56-8037 Saul Goode African American | 4455 B+ |
Materialized View Indexes
![Page 11: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/11.jpg)
ScaleDB
Col_ID# Col_Name Col_Budget Col_DescriptionColleges
001 Agriculture $1,234,567 Nice place to visit
002 Arts $5,432,567 Sports not so good
003 Business $9,999,666 Cool logo
004 Education $3,234,567 Ugh Worcester
005 Engineering $8,238,568 Serious work
006 Law $7,237,767 Jumpy students
007 Liberal Arts $9,898,777 Pretty campus
008 Medicine $5,987,004 In Texas
Students
56-8033 008 Mike Hogan Caucasian
56-8045 008 Moshe Smith Caucasian
56-8044 008 Sally Shadmon Native American
56-8055 008 Billy Fleegle African American
56-8037 008 Saul Goode African American
56-8122 008 Tim Collins Polynesian
56-8233 008 Sam Gee Asian
56-8334 008 Rod Paulino Asian
Enrollment
008 4455 56-8037 B+
008 4455 56-8033 C
008 4455 56-8045 B+
008 4456 56-8044 A-
008 4456 56-8122 B-
008 4454 56-8233 C
008 4455 56-8334 F
008 4454 56-8055 D
Coll_ID# Coll_Name Coll_Budget Coll_Description Student_ID# College_ID# Student_Name Student_Desc College_ID# Dept_ID# Student_ID# Grade
Option #3: ScaleDB
Query Result: 008 Medicine $5,987,004 In Texas | 56-8037 Saul Goode African American | 4455 B+ |
CollegeCollege
StudentsStudents
EnrollmentEnrollment
ScaleDB’s multi-table index is relationship-aware
A SingleIndex
LookupDepartmentsDepartments
CoursesCourses
ScaleDB Index
EnrollmentEnrollment
![Page 12: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/12.jpg)
ScaleDB
Building Relationships in ScaleDBCollegeCollegeCreate College
DepartmentsDepartmentsCreate Department - foreign key: College
CoursesCoursesCreate Course - foreign key: Department
StudentsStudents
Create Students - foreign key: College
EnrollmentEnrollment
Create Enrollment - foreign key: Students
Relationship creation is automated
![Page 13: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/13.jpg)
ScaleDB
Pros & Cons of Each Method
Ease of Implementation
Real-Time Data
Performance Tuning
ConventionalJoins + + - -+ + - -MaterializedViews - - + -- - + -
ScaleDB + + + ++ + + +
![Page 14: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/14.jpg)
ScaleDB
Performance Variables
• Early performance benchmarks• Used a vanilla scenario• Our performance advantage increases with:
• Query/Schema Complexity• Referential Integrity Checks• Key Size• Data Size/Number of Keys
• Performance Advantage: 2X – 20X+
![Page 15: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/15.jpg)
Improving Scalability
![Page 16: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/16.jpg)
ScaleDB
The Challenges of Scaling
• How do I partition data?• Predict usage patterns, application evolution, data
growth patterns…all are moving targets• Avoid data skew: bottlenecks caused by frequently
accessed data on just a few nodes• Data shipping between nodes (2-phase commit)• Searches outside the partition column require
participation by all nodes
• Scaling becomes an exercise in fire fighting
![Page 17: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/17.jpg)
ScaleDB
ScaleDB’s Plug-and-Cluster™
• Cluster-ready solution, just plug in a server• No need to partition the data• Based on shared-everything architecture
• Found in the highest-end commercial databases• Eliminates all of the data partitioning problems
![Page 18: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/18.jpg)
ScaleDB
Local LockManager
Shared StorageShared Storage
ScaleDB ClusterLocal LockManager
Local LockManager
Local LockManager
![Page 19: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/19.jpg)
ScaleDB
ScaleDB Cluster
Shared StorageShared Storage
Global Lock ManagerGlobal Lock Manager
![Page 20: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/20.jpg)
ScaleDB
Demo
![Page 21: ScaleDB Technical Presentation](https://reader030.vdocuments.us/reader030/viewer/2022033100/5681369e550346895d9e4102/html5/thumbnails/21.jpg)
ScaleDB
Summary• Revolutionary indexing solution delivers a
quantum leap in performance & scalability• Results:
• Performance improvements of 2X and up• 7X smaller index size (average)
• Stop jumping through hoops to avoid joins…FREE JOINS!
• Enables more complex applications, fresh data, lower TCO, superior scalability & performance
• We’re looking for appropriate beta testers