210 likes | 404 Views
Scaling MySQL to New Heights. ScaleDB Technical Presentation. Thursday April 17, 2008. ScaleDB for MySQL. Database. InnoDB, MyISAM, Cluster, Falcon, BDB, Merge, etc. . Storage Engine. What Makes ScaleDB Better?. ScaleDB Advantages:
E N D
Scaling MySQL to New Heights ScaleDB Technical Presentation Thursday April 17, 2008
ScaleDB for MySQL Database InnoDB, MyISAM, Cluster, Falcon, BDB, Merge, etc. Storage Engine
What Makes ScaleDB Better? ScaleDB Advantages: Performance: New indexing delivers dramatic performance improvement Scalability: Designed for clustering with Plug-and-Cluster™ Architecture
ScaleDB Indexing Special-purposeIndex Add-ons* Hash Bitmap Aggregate Etc. General PurposeIndexing ScaleDB Index: A general purpose index that also delivers much of the functionality and performance of special-purpose index add-ons Conventional Indexing(B-tree) *Only supported by high-end commercial databases
ScaleDB: Multi-Table Indexing B-tree: Only indexes the data in tables Index #1 Index #2 Index #3 Index #4 Index #5 #2 #1 #1 #2 #3 #3 #4 #4 #5 #5 ScaleDB: Indexes the data and relationships • Advantages: • Faster • Smaller • Referential integrity • More functionality ScaleDB Index
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
The Query SELECT 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 ;
Option #1: Conventional Joins Enrollment Index(s) Students Index(s) Colleges Index(s) Col_ID# Col_Name Col_Budget Col_Description Student_ID# College_ID# Student_Name Student_Desc Coll_ID# Coll_Name Coll_Budget Coll_Description College_ID# Dept_ID# Student_ID# Grade Colleges Students Enrollment Join Join 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 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 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 Query Result: 4455 B+ | 56-8037 Saul Goode African American | 008 Medicine $5,987,004 In Texas |
Option #2: Materialized View Materialized View Copies (and synchronizes) the data from individual tables into one massive view Col_ID# Col_Name Col_Budget Col_Description Coll_ID# Coll_Name Coll_Budget Coll_Description Student_ID# Student_Name Student_Desc Dept_ID# Grade 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+ ………… Materialized View Indexes Col_ID# Col_Name Col_Budget Col_Description Dept_ID# Dept_Name Coll_ID# Dept_Budget Coll_ID# Coll_Name Coll_Budget Coll_Description Course_ID# Course_Name Coll_ID# Dept_ID# Col_ID# Col_Name Col_Budget Col_Description 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+ Students Enrollment Colleges 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 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 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 Query Result: 008 Medicine $5,987,004 In Texas | 56-8037 Saul Goode African American | 4455 B+ |
Option #3: ScaleDB ScaleDB’s multi-table index is relationship-aware ScaleDB Index College A SingleIndexLookup Departments Students Courses Enrollment Enrollment Col_ID# Col_Name Col_Budget Col_Description Student_ID# College_ID# Student_Name Student_Desc Coll_ID# Coll_Name Coll_Budget Coll_Description College_ID# Dept_ID# Student_ID# Grade Enrollment Students Colleges 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 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 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 Query Result: 008 Medicine $5,987,004 In Texas | 56-8037 Saul Goode African American | 4455 B+ |
Building Relationships in ScaleDB College Create College Create Department - foreign key: College Departments Students Courses Enrollment Create Course - foreign key: Department Create Students - foreign key: College Relationship creation is automated Create Enrollment - foreign key: Students
Pros & Cons of Each Method Conventional Joins + + - - MaterializedViews - - + - + + + + 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+
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
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
ScaleDB Cluster Local LockManager Local LockManager Local LockManager Local LockManager Shared Storage
ScaleDB Cluster Global Lock Manager Shared Storage
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