CMU SCS Carnegie Mellon Univ Dept of Computer

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 (noun ·phy )

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. • 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 Faloutsos/Pavlo CMU SCS 15 -415/615 5

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 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. • 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 Scaling Up Ø More disks Ø More RAM Ø Faster CPU ØUse SSDs. Application Server. Database Server (+) Requires no change to application. (-) Expensive! Diminishing (+) Improvements are immediate. Single Point of Failure. (-) Faloutsos/Pavlo CMU SCS 15 -415/615 10

CMU SCS Idea #2: Replicate database on multiple

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

CMU SCS Idea #3: Cache query results.

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

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

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; Application Code Database Server Replicas (+) Reduces network roundtrips. Application logic in two (-) (+) Less lock contention. (-) PL/SQL is not standardiz (+) Modularization. Faloutsos/Pavlo CMU SCS 15 -415/615 16

CMU SCS Idea #5: Shard database across

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

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

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 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. • 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 +$100 Thanks! Faloutsos/Pavlo CMU SCS 15 -415/615 24

CMU SCS Idea #7: Keep guarantees, optimize for

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): – 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 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.