14-848 Cloud Infrastructure

1. 14-848Cloud Infrastructure Lecture 10 * Fall 2018 * Kesden

2. Socrative • https://api.socrative.com/rc/Nfu6Lp

3. Today’s Goals • A brief introduction to relational databases • A brief introduction to the SQL language used by relational databases • A brief look at how relational databases are organized on disk • Reflect on how they can be used within clouds • Reflect on why there are limits to their ability to scale

4. Not Among Today’s GoalS • A deep study of relational databases • Or, even minimal functional competency with SQL databases • If interested, take 15-615 or 15-645

5. Database “A database is an organized collection of data, stored and accessed electronically.” -- https://en.wikipedia.org/wiki/Database

6. Relational Database • “A relational database is a digital database based on the relational model of data, as proposed by E. F. Codd in 1970. “ • “The relational model (RM) for database management is an approach to managing data using a structure and language consistent with first-order predicate logic…where all data is represented in terms of tuples, grouped into relations. • “Virtually all relational database systems use SQL (Structured Query Language) for querying and maintaining the database.” -- https://en.wikipedia.org/wiki/Relational_database -- https://en.wikipedia.org/wiki/Relational_model

7. Relational DatabasesRole In Clouds • Most widely used type of database since the 1980s • Unlikely to lose that title, or see reduced growth in use, any time soon • Good for up to millions of records • Beyond that, for “Big Data” applications, we’ll talk about various NoSQL databases • Everything from managing system configuration data to customer accounts to inventories to order and shipping records, to HR data, etc.

8. Relational Database, Informally • A database wherein the data is structured in tables known as relations. • Rows represent entities and are unordered • A relational database, although drawn as a table, functions more like an unordered set of rows. • Columns represent attributes • Constraints can be enforced w.r.t. types and values • Schema for each table describes attributes and associated types. • Operations can be performed across tables.

9. Example Relation:Student Table

10. Keys:What Are They? • Used to name rows within tables • Used to associate rows across tables • Can be tuple of attributes

11. KeYS:Example Role • Unique within relation • Used to associate relations

12. SQL (“Sequel”)Structured Query Language • Used by nearly all relational databases • Both a Data Definition Language (DDL) and a Data Manipulation Language (DML) • DDL: Create, define, modify, and administer (access restrictions, etc) relations • Structure, types, constraints, etc – not values therein • DML: Query and modify values: Tuples and attributes thereof • Hides organization of data, e.g. whether and how indexed, how stored, location, etc. • Big difference from older database languages that described tree paths, etc. • Name derived from earlier IBM predecessor, “Standard English QUeryLanguage”

13. Keys:Types • Candidate – Unique to row • Primary – Used as name for row • Foreign – Key used in another table • Think of a foreign key as a pointer

14. SQL:CREATE CREATE TABLE StudentBios ( StudentID CHAR(9) NOT NULL, FName VARCHAR(200)NOT NULL, LName VARCHAR(200)NOT NULL, YearEnrolled YEAR )

15. SQL:CREATE, REVISITED CREATE TABLE StudentCourses ( StudentID CHAR(9) NOT NULL, Course CHAR(6) NOT NULL, Semester CHAR(3) NOT NULL, Grade CHAR(2) PRIMARY KEY (StudentID, Course, Semester), FOREIGN KEY (StudentID) REFERENCES StudentBios ) Why is the primary key (StudentID, Course, Semester)? Not just StudentID?

16. SQL:INSERT INSERT INTO StudentBios (CHAR(9), VARCHAR(200),VARCHAR(200),YEAR) VALUES (123456789, ‘lkennedy’, ‘Laurie’, ‘Kennedy’, 2014)

17. SQL:SELECT SELECTFname, Lname from StudentBios WHERE YearEnrolled >= 2015

18. SQL:SELECT, Continued SELECTStudentID, Fname, Lname from StudentBios WHERE YearEnrolled <= 2015 ORDER BY FName,LName

19. SQL:DELETE DELETE FROM StudentBios WHERE StudentBios.Fname = ‘Laurie’

20. SQL:JOIN • Combines columns from one or more relations into a new relation • Rows are aligned based upon a matching key • Columns are selected from logically combined rows • Yes, a relation can be joined with itself, hence one or more  • Complexity • Some keys may be present in some relations but not others • Depending upon which table is being iterated, some rows may be missed or some columns may be empty • This leads to different flavors of JOIN

21. SQL:JOIN SELECT AndrewID, Course, Semester, Grade FROM StudentBiosINNER JOIN StudentCourses WHEREStudentBIOS.StudentID = StudentCourses.StudentID • This effectively forms a new relation which is the union of the StudentBios and StudentCourses tables • The rows of the two are associated up based upon the StudentID of the the rows matching. • INNER JOIN requires matches in both tables • If there isn’t a matching key in both tables it isn’t included • The result has (AndrewID, Course, Semester, Grade) columns

22. SQL:Views CREATEVIEWStudentClassList AS SELECT AndrewID, Course, Semester FROM StudentBios INNER JOIN StudentCourses WHERE StudentBIOS.StudentID = StudentCourses.StudentID • This forms what is essentially a virtual table • Abstracts away complexity of underlying tables • Allows view to be created from tables that may be from different domains or systems • Allows protections to be applied to table, e.g. can access course list for student, but not course grades • May enable better system performance by aiding caching, etc.

23. Relational Databases:Memory Hierarchy: A Quick Review • Really approximate access times (latency): • Registers: Speed of processor • SRAM (Cache): 2-3nS = ~2.5x10-6 mS = ~2.5/1,000,000 mS • DRAM (Main Memory): 20-30nS = ~2.5x10-5 mS = 2.5/100,000 mS • FlashRAM (SSD): 0.035 mS • Disk Drive: 1 mS, sequential • Disk Drive: 10 mS, random • The name of the game is… • To build a system that performs nearly as well as if all of its memory is of the fastest type • At a cost nearly as low as if all of its memory is of the slowest type • Caching is the magic sauce

24. Relational Databases:Additional Considerations • Memory is limited • High throughput is key • ACID properties • Atomicity – A transaction is all done, or note done at all, but not half done (Succeed or fail) • Consistency – Find things consistent, leave things consistent. Don’t break things with side-effects. • Isolation – Even if there is concurrency, effects should be the same as if sequential • Durability – Once it is done, it is done (.) • Minimize overhead, while maximizing performance

25. Relational Databases:Implications OF ENVIRONMENT, GOALS • Caching is critical • Durability will require consideration of hardware • SSD or other FlashRAM layer? • Battery powered backup? • Etc • Indexes and data stored separately • Pack as many indexes into RAM as possible. Think like Haystack, but 40 years earlier. • Multiple indexes, separate for each use • Indexes and data different caching domains

26. Relational Databases:Typical Organization • Block/Page based storage of data pages • Often LRU cached • All same size for interchangeable allocation and caching, mapping to disk blocks, etc • Indexes constructed to speed queries • B-Tree based – sorted, range-based, etc • Caching may be page-based LRU. • Hash based – fast lookup without rich features • Trades disk space for lookup speed • Exact lookup only, not ranges, etc. • May be dynamic • Lookup results can be cached. • Indexes point into data pages • Commonly created by administrator, not based upon any intrinsic understanding by the database, itself

27. Relational Databases:Obtaining Robustness • Backups are hard because they may require a freeze • Blurry backups may not be useful because of ACID • Some algorithms can make blurry backup and fix-up • Replicated databases are a common option • A few replicas provide robustness without blowing scale • Need policies for working through failure and obtaining recovery • Need atomic commit protocol or master-slave, etc, to maintain consistency

28. Relational Databases:Scaling Up • Scaling to a point is easy enough • Buy bigger disk • Solution can be replicated • Scaling even bigger is easy enough • Buy an array • Solution can be replicated • Scaling even bigger gets complicated • Distributing whole tables makes joins more expensive • Sharding tables by row makes summarizing across attributes slow • Sharding tables by column makes lookup by entity slow • At a certain point, it may be better to focus on precise needs and structure a solution to meet them • NoSQL Databases: Row-Oriented, Column-Oriented, Key-Value Stores, etc.

29. Relational Databases:Scaling Up, Simple Ideas • Distill data as much as possible. • Make representation of data as dense as possible. • Keep data redundantly, if (absolutely needed) to allow distribution with performance

30. SQL:The STORY CONTINUES • There is a ton of theory behind relational databases • There is a ton more to the SQL language • There is a ton more to how SQL databases are organized, indexed, etc. • Take 15-615 or 15-645 if interested.

31. Moving Forward:Data (Not File) Storage at Scale • Key-Value Stores • Column, Row, and Document-Oriented Databases • RAM layer storage