CMU SCS Carnegie Mellon Univ Dept of Computer

  • Slides: 31
Download presentation
CMU SCS Carnegie Mellon Univ. Dept. of Computer Science 15 -415/615 - DB Applications

CMU SCS Carnegie Mellon Univ. Dept. of Computer Science 15 -415/615 - DB Applications C. Faloutsos – A. Pavlo How to Scale a Database System

CMU SCS hag·i·og·ra·phy (noun)

CMU SCS hag·i·og·ra·phy (noun)

CMU SCS Christos. The. Greek. Godof. Databases. com • Pinterest meets Causal Encounters meets

CMU SCS Christos. The. Greek. Godof. Databases. com • Pinterest meets Causal Encounters meets Kickstarter meets Twitter – With Christos!

CMU SCS Christos. The. Greek. Godof. Databases. com • More reads than writes. •

CMU SCS Christos. The. Greek. Godof. Databases. com • More reads than writes. • All media stored outside of DBMS. • How do we choose the right database architecture? Faloutsos/Pavlo CMU SCS 15 -415/615 4

CMU SCS Outline • Single-Node Databases • No. SQL Systems • New. SQL Systems

CMU SCS Outline • Single-Node Databases • No. SQL Systems • New. SQL Systems Faloutsos/Pavlo CMU SCS 15 -415/615 5

CMU SCS Late-1990 s / Early-2000 s • All the big players were heavyweight

CMU SCS Late-1990 s / Early-2000 s • All the big players were heavyweight and expensive. – Oracle, DB 2, Sybase, SQL Server, Informix. • Open-source databases were missing important features. – Postgres, m. SQL, My. SQL. Faloutsos/Pavlo CMU SCS 15 -415/615 6

CMU SCS Mid-2000 s • My. SQL + Inno. DB is widely adopted by

CMU SCS Mid-2000 s • My. SQL + Inno. DB is widely adopted by new web companies: – Supported transactions, replication, recovery. – Memcache for caching queries. Faloutsos/Pavlo CMU SCS 15 -415/615 7

CMU SCS Christos. The. Greek. Godof. Databases. com • Let’s go with My. SQL.

CMU SCS Christos. The. Greek. Godof. Databases. com • Let’s go with My. SQL. • We’re getting a lot of traffic. • Our database server is saturated! How do we increase the capacity of our database server? Faloutsos/Pavlo CMU SCS 15 -415/615 8

CMU SCS Idea #1: Buy a faster machine.

CMU SCS Idea #1: Buy a faster machine.

CMU SCS Scaling Up Application Server (+) Requires no change to application. (+) Improvements

CMU SCS Scaling Up Application Server (+) Requires no change to application. (+) Improvements are immediate. Faloutsos/Pavlo Database Server Ø More disks. Ø More RAM. Ø Faster CPUs. ØUse SSDs. (-) Expensive! Diminishing Returns. (-) Single Point of Failure. CMU SCS 15 -415/615 10

CMU SCS Idea #2: Replicate database on multiple servers.

CMU SCS Idea #2: Replicate database on multiple servers.

CMU SCS Replication Read Request Application Server (+) Requires no change to application. (+)

CMU SCS Replication Read Request Application Server (+) Requires no change to application. (+) Parallelize read operations. (+) Improved fault tolerance. Faloutsos/Pavlo Database Server Replicas (-) Expensive! Diminishing Returns. (-) Writes limited to slowest node. CMU SCS 15 -415/615 12

CMU SCS Idea #3: Cache query results.

CMU SCS Idea #3: Cache query results.

CMU SCS Query Cache Update Check Cache memcache Query Request Application Server Database Server

CMU SCS Query Cache Update Check Cache memcache Query Request Application Server Database Server (+) Reduce load on DBMS. (+) Fast API. (-) Extra roundtrip per query. (-) Requires application changes. (-) Doesn’t help write-heavy apps. Faloutsos/Pavlo CMU SCS 15 -415/615 Replicas 14

CMU SCS Idea #4: Push SQL into stored procedures.

CMU SCS Idea #4: Push SQL into stored procedures.

CMU SCS Stored Procedures Stored Procedure def get. Page(request): # Process request EXEC SQL

CMU SCS Stored Procedures Stored Procedure def get. Page(request): # Process request EXEC SQL def get. Page(request): EXEC SQL # Process request # Process results EXEC PROCEDURE if x == True: # Render HTML page EXEC SQL return (html) else: EXEC SQL # Render HTML page return (html) BEGIN: EXEC SQL if x == True: EXEC SQL else: EXEC SQL return (results) END; Database Server Application Code (+) Reduces network roundtrips. (+) Less lock contention. (+) Modularization. Faloutsos/Pavlo Replicas (-) Application logic in two places. (-) PL/SQL is not standardized. CMU SCS 15 -415/615 16

CMU SCS Idea #5: Shard database across multiple servers.

CMU SCS Idea #5: Shard database across multiple servers.

CMU SCS Sharding / Partitioning Logical Partitions Application Server (+) Parallelize all operations. (+)

CMU SCS Sharding / Partitioning Logical Partitions Application Server (+) Parallelize all operations. (+) Much easier to add more hardware. Faloutsos/Pavlo Database Cluster (-) Most DBMSs don’t support this. (-) Joins are expensive. (-) Non-trivial to split database. CMU SCS 15 -415/615 18

CMU SCS Christos. The. Greek. Godof. Databases. com • We want to scale out

CMU SCS Christos. The. Greek. Godof. Databases. com • We want to scale out but writing a sharding layer is hard. • Some parts of our application don’t need a full-featured DBMS. Faloutsos/Pavlo CMU SCS 15 -415/615

CMU SCS Idea #6: Give up ACID guarantees for scalability.

CMU SCS Idea #6: Give up ACID guarantees for scalability.

CMU SCS Faloutsos/Pavlo Update Profile ? Master Get Profile CMU SCS 15 -415/615 DBMS

CMU SCS Faloutsos/Pavlo Update Profile ? Master Get Profile CMU SCS 15 -415/615 DBMS Servers Application Servers Eventual Consistency ? Replicas 21

CMU SCS Late-2000 s (No. SQL) • No. SQL systems are able to scale

CMU SCS Late-2000 s (No. SQL) • No. SQL systems are able to scale horizontally right out of the box by giving traditional database features. Faloutsos/Pavlo CMU SCS 15 -415/615 22

CMU SCS Christos. The. Greek. Godof. Databases. com • We need to process payments.

CMU SCS Christos. The. Greek. Godof. Databases. com • We need to process payments. • We don’t want to lose orders. • We need joins and ACID transactions.

CMU SCS Strong Consistency Use Two. Phase Commit Nice Christos Pictures! -$100 Send Money

CMU SCS Strong Consistency Use Two. Phase Commit Nice Christos Pictures! -$100 Send Money +$100 Thanks! Faloutsos/Pavlo CMU SCS 15 -415/615 24

CMU SCS Idea #7: Keep guarantees, optimize for workload type.

CMU SCS Idea #7: Keep guarantees, optimize for workload type.

CMU SCS Early-2010 s (New. SQL) • New DBMSs that can scale across multiple

CMU SCS Early-2010 s (New. SQL) • New DBMSs that can scale across multiple machines natively and provide ACID guarantees.

CMU SCS Conclusion • RDBMS (Single-Node): – My. SQL, Postgres • No. SQL (Multi-Node):

CMU SCS Conclusion • RDBMS (Single-Node): – My. SQL, Postgres • No. SQL (Multi-Node): – Key-Value, Documents, Graphs • New. SQL (Multi-Node): – Transaction Processing, My. SQL Sharding Faloutsos/Pavlo CMU SCS 15 -415/615 27

CMU SCS What DBMS should my start-up use?

CMU SCS What DBMS should my start-up use?

CMU SCS Beyond the 15 -415/615 • Christos is teaching 15 -826 this fall:

CMU SCS Beyond the 15 -415/615 • Christos is teaching 15 -826 this fall: – Multimedia Databases and Data Mining • Send me an email if you’re interested in working on a database research project.