1 / 21

ScaleDB Technical Presentation

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:

willa
Download Presentation

ScaleDB Technical Presentation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Scaling MySQL to New Heights ScaleDB Technical Presentation Thursday April 17, 2008

  2. ScaleDB for MySQL Database InnoDB, MyISAM, Cluster, Falcon, BDB, Merge, etc. Storage Engine

  3. What Makes ScaleDB Better? ScaleDB Advantages: Performance: New indexing delivers dramatic performance improvement Scalability: Designed for clustering with Plug-and-Cluster™ Architecture

  4. Improving Performance

  5. 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

  6. 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

  7. 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

  8. 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 ;

  9. 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 |

  10. 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+ |

  11. 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+ |

  12. 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

  13. Pros & Cons of Each Method Conventional Joins + + - - MaterializedViews - - + - + + + + ScaleDB

  14. 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+

  15. Improving Scalability

  16. 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

  17. 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

  18. ScaleDB Cluster Local LockManager Local LockManager Local LockManager Local LockManager Shared Storage

  19. ScaleDB Cluster Global Lock Manager Shared Storage

  20. Demo

  21. 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

More Related