Lecture 4 DBMS Architecture Sept 5 2007 ChengXiang Zhai Most slides are adapted from Kevin Chang s lecture slides CS511 Advanced Database Management Systems 1 DBMS Mission Statement Simply maintenance and computation of data Data Operations Results But how to do it CS511 Advanced Database Management Systems 2 DBMS Architecture User Web Forms Applications DBA query transaction Query Parser Transaction Manager Query Rewriter Query Optimizer Lock Manager Logging Recovery Query Executor Files Access Methods Buffer Manager Storage Manager Buffers Lock Tables Main Memory Storage CS511 Advanced Database Management Systems 3 A Design Dilemma To what extent should we reuse OS services Reuse as much as we can Performance problem inefficient Lack of control incorrect crash recovery Replicating some OS functions mini OS Have its own buffer pool Directly manage record structures with files CS511 Advanced Database Management Systems 4 OS vs DBMS Similarities What do they manage What do they provide CS511 Advanced Database Management Systems 5 OS vs DBMS Similarities Purpose of an OS managing hardware presenting interface abstraction to applications DBMS is in some sense an OS DBMS manages data presenting interface abstraction to applications Both as API for application development CS511 Advanced Database Management Systems 6 Applications built upon DBMS ERP Enterprise Resource Planning SAP Baan PeopleSoft Oracle IBM CRM Customer Relationship Management E phiphany Siebel Vantive Oracle IBM SCM Supply Chain Management Trilogy i2 Oracle IBM A lot more in the Info Tech era e business software scientific data multimedia data analysis and decision support CS511 Advanced Database Management Systems 7 OS vs DBMS Related Concepts Process Management What DB concepts process synchronization deadlock handling Storage management What DB concepts virtual memory file system Protection and security What DB concepts authentication access control CS511 Advanced Database Management Systems 8 OS vs DBMS Differences CS511 Advanced Database Management Systems 9 OS vs DBMS Differences DBMS Top down to encapsulate high level semantics Data data with particular logical structures Queries query language with well defined operations Transactions transactions with ACID properties OS Bottom up to present low level hardware CS511 Advanced Database Management Systems 10 DBMS on top of OS Relations vs File system Data object abstraction file array of characters relation set of tuples Physical contiguity large DB files want clustering of blocks sol1 managing raw disks by DBMS sol2 simulate by managing free spaces in DBMS Multiple trees access methods file access directory hierarchy user access method block access inodes tuple access DBMS indexes CS511 Advanced Database Management Systems 11 Problems with DBMS on top of OS Buffer pool management File system Process management Consistency control Paged virtual memory CS511 Advanced Database Management Systems 12 Buffer Pool Management LRU replacement Query aware replacement needed for performance Examples hash join short merge join Prefetching DBMS knows exactly which block is to be fetched next Crash recovery Need selected force out CS511 Advanced Database Management Systems 13 Updating Semantics Update emp sal 0 8 emp sal if emp sal mgr sal empname sal manager Smith 10k Brown Jones 9k Brown 11k Jones what are the possible semantics INGRES solution deferred updates buffer updates in intentions list for actual updates also serve as redo log an example of needing buffer knowledge in DBMS so perhaps not sensible to do BM totally in OS CS511 Advanced Database Management Systems 14 As the data model and application context change so does the DBMS architecture CS511 Advanced Database Management Systems 15 Post Relational DB Projects Motivation RDBMS not powerful enough for non administrative dataintensive applications such as CAD CAM GIS Buzz terms object oriented extensible Sample projects Postgres U C Berkeley Starburst IBM Almaden highly extensible after System R relational R distributed ultimately finding its way into IBM DB2 UDB Exodus U Wisconsin not a complete DB an OO style storage manager toolkit followed by Shore at Wisconsin Predator at Cornell CS511 Advanced Database Management Systems 16 Quest for a Richer Model Object oriented data model Extensible ADTs Programming language constructs CS511 Advanced Database Management Systems 17 ORDBMS vs OODBMS Question How important is the relation ORDBMS RDBMS OO features query based OODBMS OO PL database features persistent objects programming based Meeting in the middle CS511 Advanced Database Management Systems 18 Stonebraker s Matrix Simple Data Complex Data Query RDBMS ORDBMS No Query File System OODBMS Prediction ORDBMS will dominate evidence big DB players are all on this side CS511 Advanced Database Management Systems 19 Object Orientation Concepts Classes classes as types encapsulation interface implementation inheritance building class hierarchies Objects complex objects built from constructors e g set of array nested objs object identity OID system generated as unique object reference enables efficient object linking and navigation CS511 Advanced Database Management Systems 20 POSTGRES Data Model POSTGRES data model OO constructs classes as relations object class instance tuple object id tuple id method attribute or function of attributes inheritance multiple parents ADT constructs types functions CS511 Advanced Database Management Systems 21 POSTGRES Functions Arbitrary C functions e g overpaid Employee arbitrary semantics not optimized no fancy access methods typically sequential scan Binary operators hints to provide semantics extensible access methods extensible B tree or user defined index PostQuel procedures parameterized queries as functions e g sal lookup name retrieve Emp salary where Emp name name CS511 Advanced Database Management Systems 22 POSTGRES Storage System We were guided by a missionary zeal to do something different No overwrite system Logging old values are not overwritten no value logging necessary log only needs to keep transaction state commit abort going crash recovery how Vacuum cleaner daemon to archive historical data Advantages recovery is cheap time travel is easy CS511 Advanced Database Management Systems 23 Storage System Problems Problems flushing differential data by commit time can be costly unless stable main memory more costly than sequentially writing out logs reads have to stitch