The Revolution In Database Systems Architecture Jim Gray

The Revolution In Database Systems Architecture Jim Gray Microsoft 8 th ADBIS Eighth East-European Conference on Advances in Databases and Information Systems 22 -25 September, 2004, Budapest, Hungary Gray@microsoft. com http: //research. microsoft. com/~Gray/talks 1

Why This Talk? • What’s the most important thing I could say? • Convey the revolution and its causes – Problem: Need to integrate diverse data. – Solution: integration of OO and DB. – Promising progress so far. • Convey the fact that you helped create the revolution. 2

DB System Architecture sets records os Worked, but applications wanted to query other data types Added: sets utilities Notification Space Time Data Mine Cubes Text ETL Replication XML Queues Procedures +Text, Time, Space + Cubes, Data mining + XML, XQuery + Programming Languages + Triggers and queues + Replication, Pub/sub + Extract-Transform-Load + Many more extensions coming utilities • The classic DBMS model … records os A Mess? 6

DB Systems evolved to be containers for information services develop, deploy, and execution environment • Classic ++ – – – – + Triggers and queues + Replication, Pub/sub + Extract-Transform-Load + Text, Time, Space + Cubes, Data mining + XML, XQuery + Many more extensions coming sets records os utilities – + Programming Languages • DBMS is an ecosystem OO is the key structuring strategy: – – – Everything is a class Database is a complex object Core object is Data. Set Classes publish/consume them Depends on strong Object Model Data. Set 7 • Many of the concepts you pioneered are now mainstream.

Ask not “How to add objects to databases? ”, Ask “What kind of object is a database? ” Q: Given an object model, what is it we do? A: Data. Set class and methods (nested relation with metadata) This is the basis for the ecosystem Distributed DB Extensible DB Tables Question or Text Interoperable DB or cube Dataset …. Or…. . This was implicit in ODBC but is now explicit within the DBMS ecosystem Input: Command (any language) Output: Dataset 8

Code and Data: Separated at Birth COBOL – IDENTIFICATION: document AUTHOR, PROGRAM-ID, INSTALLATION, SOURCE-COMPUTER, OBJECT-COMPUTER, SPECIAL-NAMES, FILE-CONTROL, I-O-CONTROL, DATE-WRITTEN, DATE-COMPILED, SECURITY. – ENVIRONMENT: OS CONFIGURATION SECTION. INPUT-OUTPUT SECTION. – DATA: Files/Records FILE SECTION. WORKING-STORAGE SECTION. LINKAGE SECTION. REPORT SECTION. SCREEN SECTION. – PROCEDURE: code “them” “us” CODASYL - DBTG COnference on DAta SYstems Languages Data Base Task Group Defined DDL for a network data model Set-Relationship semantics Cursor Verbs Isolated from procedures. No encapsulation 9

Klaus Wirth: Algorithms + Data Structures = Programs The Object-Relational World marry programming languages and DBMSs • Stored procedures evolve to “real” languages Java, C#, . . With real object models. • Data encapsulated: a class with methods • Classes may be persistent Business Objects • Tables are enumerable & index-able record sets with foreign keys • Records are vectors of objects • Opaque or transparent types • Set operators on transparent classes • Transactions: – Preserve invariants – A composition strategy – An exception strategy • Ends Inside-DB Outside-DB dichotomy 10

What’s Outside? 1 ior et p om C Remote Node Co mp et ito r 2 Remote Node Internet Other us Applications Other us Our API Buffer Pool catalogs itterators Query Processor data 11

Classic: What’s Outside? Three Tier Computing • Clients do presentation, gather input • Do some workflow (script) Presentation • Send high-level requests to ORB (Object Request Broker) • ORB dispatches workflows and business objects -- proxies for client, orchestrate flows & queues • Server-side workflow invokes distributed business objects to execute task • Business object read/write database workflow Business Objects 12 Database

DBMS is Web Service! Client/server is back; the revenge of TP-lite • Web servers and runtimes (Apache, IIS, J 2 EE, . NET) displaced TP monitors & ORBS – Give persistent objects – Holistic programming model & environment D B MS • Web services (soap, wsdl, xml) are displacing current brokers • DBMS listening to Port 80 publishing WSDL, DISCO, WS-Security Servicing SOAP calls. DBMS is a web service • Basis for distributed systems. • A consequence of OR DBMS Presentation Workflow Business Objects 13 Database

Queues & Workflows • Apps are loosely connected via Queued messages Workflow: • Queues are databases. Script Execute • Basis for workflow Administer & • Queues: the first class to Expedite add to an OR DBMS all built on queues • Queues fire triggers. Active databases • Synergy with DBMS security, naming, persistence, types, query, … 14

Text, Temporal, and Spatial Data Access select Title, Abstract, Rank • Q: What comes after queues? from Books join (Title, A: Basic types: text, time, space, … Free. Text. Table Abstract, ‘XML semistructured') T • Great application of OR technology on Book. ID = T. Key • Key idea: table valued functions == indices An index is a table, organized differently Query executor uses index to map: select galaxy, distance from Get. Nearby. Obj. EQ(22, 37) Key → set (aka sequence of rows) • Table valued function can do this map Optimizer can use it. select store, holiday, sum(sales) • +extras: cost function, cardinality, … from Sales join Holiday. Dates(2004) T on Sales. day = T. day group by store, holiday • BIG DEAL: Approximate answers: Rank and Support 15

What’s new here? Question Dataset • DBMS have tight-integration with language classes (Java, C#, VB, . . ) • The DB is a class • You can add classes to DB. • Adding indices is “easy” If you have a new idea. • Now have solid Queue systems Adding workflow is “easy” If you have a new idea. • This is a vehicle for publishing data on the Web. Tables or Text or cube Or…. . 16

Column Stores & Row Stores • Users see fat base tables (universal relation) • Conceptually simple but use only some columns • To avoid reading useless data, Do vertical partitions Define 10% popular columns index • Make many skinny indices 1% columns • Query engine uses covering index • Much faster read slower insert/update • MANY! optimizations Data Pyramid BASE Obese query TAG Fat quer y (bitmaps, compression, . . ). • Column stores automate all this, see Adabase, Model 204 and… • Challenge: Automate design. Simple INDICIES Typical Semi-join 17

Cubes • Data cubes now standard SELECT <axis_spec> • MDX is very powerful FROM <cube_spec> WHERE <slicer_spec> • Dimension, Measure, Operator concepts highly evolved beyond snowflake schema • Cube stores cohabit with row stores ROLAP + MOLAP + ( x x. OLAP) (Multi-Dimensional e. Xpressions) FO CH RD EV Y 0 199 991 1 2 199 93 19 RED WHITE BLUE (relational +multidimensional online analytic processing) • Very sophisticated algorithms • A big part of the ecosystem 18

Data Mining and Machine Learning • Tasks: classification, association, prediction • Tools: Decision trees, Bayes, A Priori, clustering, regression, Neural net, … • now unified with DBs – Create table T (x, y, z, u, v, w) Learn “x, y, z” from “u, v, w” using <algorithm> – Train T with data. – Then can ask: • Probability x, y, z, u, v, w • What are the u, v, w probabilities given x, y, z – Example: Learn height from age. • Anyone with a data mining algorithm has full access to the DBMS infrastructure. • Challenge: Better learning algorithms. 19

DM – DB Synergy Create the model: CREATE MINING MODEL Height. From. Age. Sex ( ID long key, Gender text discrete, Age long continuous, Height long continuous PREDICT) USING Decision_Trees Train a data mining model: INSERT INTO Height SELECT ID, Gender, Age, Height FROM People learn height from Gender + Age DB verbs to drive Modeler Predict height from model: SELECT height, Predict. Probability(height) FROM Height PREDICTION JOIN New ON New. Gender = Height. Gender AND New. Age = Height. Age Probabilistic Reasoning 20

Notification, Stream Processing, and Sensor Processing • Traditionally: Query billions of facts • Streams: millions of queries one new fact – New protein compare to all DNA – Change in price or time Q? A! facts • Implications – New aggregation operators (extension) – New programming style – Streams in products: • Queries represented as records • New query optimizations. • Sensor networks – push queries out to sensors. – Simpler programming model – Optimizes power & bandwidth fact, fact… Q QQ Q Q Notification 21

Semi-Structured Data • “Everyone starts with the same schema: <stuff/>. ” Then they refine it. ” J. Widom • We are a “strong schema” community • That has pros-and-cons. • Files <stuff/> and XML <<foo/> <bar/>> are here to stay. Get over it! • File directories are becoming databases; – Pivot on any attribute – Folders are standing queries. – Freetext+schema search (better precision/recall) • XSD (xml schema) and x. Query are transitional; But we have to do them to get to the real answer. • Cohabit with row-stores. • Challenge: figure out what comes after XSD+x. Query 22

Publish-Subscribe, Replication Extract-Transform-Load (ETL) • • Data has many users Replicas for availability and/or performance (e. g. directories. ) Mobile users do local updates synchronize later. Classic Warehouse – Replicate to data warehouse – Data marts subscribe to publications • Disaster Recovery geoplex • Many different algorithms: – transactions, 1 -safe, snapshot, merge, log ship, … – Each algorithm seems to be best for something. • ETL is a major application & component – Data loading – Data scrubbing – Publish/subscribe workflows. • All use procedures for reconciliation, scrubbing, … 23

Restatement: DB Systems evolved to be containers for information services develop, deploy, and execution environment • This architecture uses many of your ideas • The architecture lets you add your new ideas. records os sets records os utilities – Everything is a class – Database is a complex object – Core object is Data. Set sets utilities • DBMS is an ecosystem Key structuring strategy: Data. Set 24

Is There Nothing Left For The Plumbers To Do? • Can everything be done as an extension? • • Not quite…. First, there is LOTS of plumbing in extesions Doing the OO integration is not trivial Better optimizers Deal with massive main memory Security & Privacy still an open problem Federation, Distribution, parallelism Auto-everything 25

? Smart Objects: Databases Everywhere • Phones, PDAs, Cameras, … have small DBs. • Disk drives have enough cpu, memory to run a full-blown DBMS. • All these devices want-need to share data. • They need an Esperanto. • It is the DBMS ecosystem language. • Needs a simple-but-complete dbms. 26

Late Binding in Query Plans • Cost based query optimizers are great! when they guess right. • But – if it guessed 1 minute and the query has been running for a day… – If system is busy plan is different • Better strategy: Have query optimizer learn – – From previous queries From previous instances of this query From environment. • Anyone who has waited days for a query to complete thinks this VERY important (!) 27

Massive Memory, Massive Latency • • • RAM costs ~ 100 k$. . . 300 k$/Tera. Byte 64 bit addressing everywhere the absurd disk is (almost) here 100 MB/s Latency a problem 200 Kaps 100: 1 NUMA latency a problem Checkpoint 1 TB? Restart 1 TB? Scan 1 TB • OK, now how about 100 TB? • Challenge: Algorithms for Massive Main Memory 10 years 1 TB 28

Self Managing & Always Up • People costs have always exceeded IT capital. • But now that hardware is “free” … • Self-managing, self-configuring, self-healing, selforganizing and … is key. • No DBAs for cell phones or cameras. • Requires a modular software architecture – Clear and simple knobs on modules – Software manages these knobs • So, again the class model (interfaces) are key. 29

Restatement • OO enables Object-Relational Ecosystem – Federate many kinds of data – Enables your extensions. • Yes, there are still plumbing problems left. – Framework to allow extensions – Auto-everything. 30

The Revolution In Database Systems Architecture Jim Gray Microsoft 8 th ADBIS Eighth East-European Conference on Advances in Databases and Information Systems 22 -25 September, 2004, Budapest, Hungary Gray@microsoft. com http: //research. microsoft. com/~Gray/talks 31