Datenbanksysteme II: Implementation of Database Systems Overview and General Architecture Material von Prof. Johann Christoph Freytag Prof. Kai-Uwe Sattler Prof. Alfons Kemper, Dr. Eickler Speicherhierarchie Register (L1/L2/L3) Cache Hauptspeicher Plattenspeicher Archivspeicher Ulf Leser: Datenbanksysteme II, Sommersemester 2005 2 Überblick: Speicherhierarchie Register Cache Hauptspeicher 1 – 8 Byte Compiler 8 – 128 Byte Cache-Controller 4 – 64 KB Betriebssystem Plattenspeicher Archivspeicher Ulf Leser: Datenbanksysteme II, Sommersemester 2005 Benutzer / Roboter 3 Überblick: Speicherhierarchie 1-10ns Register 10-100ns Cache 60-300ns Hauptspeicher 10-20 ms Plattenspeicher Zugriffslücke 105 sec - min Archivspeicher Ulf Leser: Datenbanksysteme II, Sommersemester 2005 4 Ebenen • Konzeptionelle Ebene – Relationen, Tuple – Attributwerte • Variable Länge und Typ • Logische Ebene – Dateien – Sätze – Felder • Physikalische Ebene – Laufwerke – Blöcke – Zylinder und Sektoren Ulf Leser: Datenbanksysteme II, Sommersemester 2005 5 5 Schichten Architektur Mengenorientierter Zugriff Datenmodellebene Satzorientierter Zugriff Logischer Zugriff Interne Satzschnittstelle Speicherstrukturen Systempufferschnittstelle Pufferverwaltung Dateischnittstelle Betriebssystem Anfrageübersetzung, Zugriffspfadwahl, Zugriffskontrolle, Integritätskontrolle Sortierung, Transaktionsverwaltung, Cursorverwaltung, Data Dictionary Physical Record Manager, Index Manager, Sperrverwaltung, Log / Recovery Seitenverwatung, Puffermanagement, Caching Externspeicherverwaltung Geräteschnittstelle Ulf Leser: Datenbanksysteme II, Sommersemester 2005 6 Objekte und Operationen Mengenorientierter Zugriff Datenmodellebene Satzorientierter Zugriff Logischer Zugriff Interne Satzschnittstelle Speicherstrukturen SQL: select ... from ... Where Grant access to ... Create index on ... OPEN – FETCH – CLOSE (Tab o. Ind) STORE Record STORE: RECORD auf Seiten Aktualisierung aller Zugriffspfade, Implementierung B*-Baum Systempufferschnittstelle Pufferverwaltung READ page WRITE page Dateischnittstelle Betriebssystem Disc driver MOVE head ... Geräteschnittstelle Ulf Leser: Datenbanksysteme II, Sommersemester 2005 7 Schnittstellen • Mengenorientierte Schnittstelle – Zugriff auf Tupelmengen über deklarative Sprache – SELECT ... FROM ... WHERE ... – Überwachung Datenintegrität und Autorisierung • Satzorientierte Schnittstelle – Zugriff auf typisierte Tupel – Zugriff auf logischer Zugriffspfade (Indexe, Scans) – Verwaltung Partitionen • Interne Satzschnittstelle – – – – Zugriff auf uniforme, ungetypte Tupel Sperrverwaltung Verwaltung Abbildung Tupel (alle logischen Objekte) auf Seiten Implementierung und Aktualisierung der Zugriffspfade Ulf Leser: Datenbanksysteme II, Sommersemester 2005 8 Schnittstellen • Systempufferschnittstelle – Zugriff auf Blöcke in virtuellem Adressraum – Abbildung virtuelle auf physikalische Blockadressen – Sperren und Freigeben von Blöcken • Dateischnittstelle – Zugriff auf physikalische Blöcke – Verwaltung Block zu Segmenten, Tablespaces, Dateien – Software-RAID • Geräteschnittstelle – – – – Zugriff auf Daten der Festplatte Adressierung der Platten – Platte, Spur, Sektor Cache des Controllers, Prefetching Hardware Raid Ulf Leser: Datenbanksysteme II, Sommersemester 2005 9 5-Schichten Architekur • Idealisierte Darstellung – Muss nicht so implementiert werden – Vielfältige Querverbindungen durch Synchronisierung, Recovery, ... • Schichten können zusammengefasst werden – Z.B. Schicht satzorientierte und interne Satzschnittstelle • Schichten müssen oftmals durch mehrere andere Schichten „hindurch greifen“ können – Caching benötigt für Prefetching Informationen über aktuelle Workload, nicht nur über das aktuelle Tupel • Von Schicht 4 zu Schicht 1/2 • Eventuell sogar von Schicht 5 zu Schicht 1/2 – Optimierer benötigt Informationen über physische Verteilung von Blöcken auf Disc • Von Schicht 1 zu Schicht 4/5 – Verletzung des „Information Hiding“ Prinzips Ulf Leser: Datenbanksysteme II, Sommersemester 2005 10 Von unten nach oben Aber: Viele Themen kann man nicht so schön zuordnen • Sperrverwaltung • Recovery • Anfragoptimierung • ... Datenmodellebene Logischer Zugriff Speicherstrukturen Pufferverwaltung Betriebssystem Ulf Leser: Datenbanksysteme II, Sommersemester 2005 11 Magnet- bzw. Festplattenspeicher Ulf Leser: Datenbanksysteme II, Sommersemester 2005 12 RAID 1: Spiegelung (mirroring) A B A B C D C D • Datensicherheit: durch Redundanz aller Daten (Engl. mirror) – Aber so keine Hilfe bei Bitfehlern – wer hat recht? • Doppelter Speicherbedarf • Lastbalancierung beim Lesen: z.B. kann Block A von der linken oder der rechten Platte gelesen werden • Aber beim Schreiben müssen beide Kopien geschrieben werden – Kann aber parallel geschehen – Dauert also nicht doppelt so lange wie das Schreiben nur eines Blocks Ulf Leser: Datenbanksysteme II, Sommersemester 2005 13 Von unten nach oben Datenmodellebene Logischer Zugriff Speicherstrukturen Pufferverwaltung Betriebssystem Ulf Leser: Datenbanksysteme II, Sommersemester 2005 In ca. 1 Woche: Abbildung Records und Blöcke auf Dateien 14 Introduction to Access Methods • Sequential File – Access to records by record/tuple identifier (RID or TID) – Operations: 1522 Bond ... 123 Mason ... ... ... ... 1754 Miller ... • INSERT(Record): • SEEK( TID): – FIRST ( File): – NEXT( File): – EOF ( File): Move to end of file and add, O(1) Sequential scan, O(n) O(1) O(1) O(1) • DELETE( TID): Seek TID; flag as deleted • REPLACE( TID, Record): Seek TID; write record – What happens if records have variable size? Ulf Leser: Datenbanksysteme II, Sommersemester 2005 15 Intro to Access Methods 2 • Index File Root Internal Node Leaves • Operations: – SEEK( TID): Use order in TIDs; O(log(n)) • Only if tree is perfectly balanced – INSERT( TID): – DELETE( TID): – REPLACE( TID): Seek TID and insert; might require restructuring Seek TID and remove; might require restructuring Seek TID and write • Variable size TIDs? Ulf Leser: Datenbanksysteme II, Sommersemester 2005 16 Storage in Oracle Database Tablespace Logical Data file Segment Physical Extent OracleBlock OS Block • Data files are assigned to tablespaces • • • Extents are continuous sequences of blocks on disc Space is allocated in extents (min, next, max, …) Segments logically group all extents of an object – Tablespaces may consist of multiple files – All data from one object (table, index) are in one tablespace – Unit of backup, offline, moveable tablespaces, quotas, access rights, … Ulf Leser: Datenbanksysteme II, Sommersemester 2005 17 Managing space in Oracle Ulf Leser: Datenbanksysteme II, Sommersemester 2005 18 Von unten nach oben Datenmodellebene Logischer Zugriff Speicherstrukturen Pufferverwaltung Logische zu physischen Blöcken Betriebssystem Ulf Leser: Datenbanksysteme II, Sommersemester 2005 19 Caching = Buffer Management Page 1, P100) Buffer Manager Page 1 MM Buffer P0 P1 P2 Disc Page 2 Ulf Leser: Datenbanksysteme II, Sommersemester 2005 20 Von unten nach oben Datenmodellebene Logischer Zugriff Speicherstrukturen In ca. 2 Wochen: Von Records zu virtuellen Blöcken; Indexstrukturen Pufferverwaltung Betriebssystem Ulf Leser: Datenbanksysteme II, Sommersemester 2005 21 Record Addressing • Mapping alternatives – absolute addressing: TID = <PageId, Offset> Offset Page Id – absolute addressing + search: TID = <PageId> Page Id -- Search -- Ulf Leser: Datenbanksysteme II, Sommersemester 2005 22 Life is complex • Oracle procedure for finding free space • Free space is administered at the level of segments – Logical database objects • Explanation – – – – Ulf Leser: Datenbanksysteme II, Sommersemester 2005 TFL: transaction free list PFL: process free list MFL: master free list HWM: High water mark 23 Hashing • Hash file consists of – Set of buckets (one or more pages) • B0 , B1 , ..., Bm-1 , m>1 ; – A hash function h(K) = {0 ,...m-1 } on a set K of keys; – A hash table (bucket directory) as array of size m with pointers to buckets • Hash files are structured according to one attribute value only Buckets with overflow pages Hash table Ulf Leser: Datenbanksysteme II, Sommersemester 2005 24 R-Baum Beispiel Quelle: Geppert, Data Warehousing, VL SoSe 2002 Ulf Leser: Datenbanksysteme II, Sommersemester 2005 25 Von unten nach oben Datenmodellebene Logischer Zugriff In ca. 4-5 Wochen: Anfrageoptimierung Speicherstrukturen Pufferverwaltung Betriebssystem Ulf Leser: Datenbanksysteme II, Sommersemester 2005 26 Schichtenmodell Externes Schema Externes Schema Datenmodellebene Konzeptionelles Schema Herstellerabhängig Internes Schema Externes Schema Ulf Leser: Datenbanksysteme II, Sommersemester 2005 Sichten Logisches Modell (Tabellen, Attribute, Anfragen Physisches Modell (Indexierung, Speicherung) 27 Anfragen Externes Schema Externes Schema Externes Schema Konzeptionelles Schema Internes Schema Umschreiben der Anfragen; Viewexpansion Übersetzung der Anfrage Zugriffspfade, Indexwahl, Joinreihenfolge, Optimierung Ausführung des gewählten Plans Ulf Leser: Datenbanksysteme II, Sommersemester 2005 28 Queries and Languages • User Languages: – SQL, QUEL, Embedded-SQL, 4GL • Data Definition Language (DDL): – Create, delete, change relations and other DB objects • Tablespaces and partitions • Indexes and views • Users – Authorization and authentication – (Manage processes, system parameter, transaction isolation level, …) • Data Manipulation Language (DML): – Read, Delete, Create, Change Tuples Ulf Leser: Datenbanksysteme II, Sommersemester 2005 29 Query Processing • Declarative query SELECT Name, Address, Checking, Balance FROM customer C, account A WHERE Name = “Bond” and C.Account# = A.Account# • Generate a “Query Execution Plan” • QEP FOR EACH c in CUSTOMER DO IF k.Name = “Bond” THEN FOR EACH a IN ACCOUNT DO IF a.Account# = c.Account# THEN Output (“Bond”, c.Address, a.Checking, a.Balance) – Procedural Specification – Semantically equivalent to query Ulf Leser: Datenbanksysteme II, Sommersemester 2005 30 Query processing – the interesting bit • There are usually many, many possible QEPs for a query FOR EACH a in ACCOUNT DO FOR EACH c IN CUSTOMER DO IF a.Account# = c.Account# THEN IF c.Name = “BOND” THEN Output (“Bond”, c.Address, a.Checking, a. Balance) FOR EACH c in CUSTOMER WITH Name=“Bond” BY INDEX DO FOR EACH a IN ACCOUNT DO IF a.Account# = c.Account# THEN Output (“Bond”, c.Address, a.Checking, a. Balance) Ulf Leser: Datenbanksysteme II, Sommersemester 2005 31 Query Optimizer (cont.) QEP 1: Suppose index KName on Customer(Name) index Kno on Account(Account#) Use Index KName for “Bond” Search KNo Index using C,Account# Read CUSTOMER data file Read account info from ACCOUNT data file QEP 2: Account File is sorted Use Index KName for “Bond” Read CUSTOMER data file ACCOUNT data file (sorted) Ulf Leser: Datenbanksysteme II, Sommersemester 2005 Sort CUSTOMERs on CUSTOMER.Account# Merge-Join Account# = Account# 32 Query optimization • Task: Find the (hopefully) fastest QEP • Proof equivalence by rewriting algebra terms – P1: – P2: σName=Bond(Account ⋈ Customer) Account ⋈ σName=Bond(Customer) • Enumerate and evaluate QEPs – Usually can’t be performed exhaustively • General goal: Minimize size of intermediate results – This is a local criteria – Might mislead • Expansive subplan with sorted result • Cheap subplan with unsorted result Ulf Leser: Datenbanksysteme II, Sommersemester 2005 33 Query optimization • Method 1 – Use heuristics to find better plans – Push selections as far as possible – Push projections as far as possible – Does not use information about current state of relations and indexes – Does not help much for join order Ulf Leser: Datenbanksysteme II, Sommersemester 2005 34 Query optimization • Method 2 – Use statistics – Use information on current state • Size, value distribution, fragmentation, cluster factors, … – Let selectivity of σName=Bond be 5%, |Customer|=10.000, |Account|=12.000 FOR EACH a in ACCOUNT DO FOR EACH c IN CUSTOMER DO IF a.Account# = c.Account# THEN IF c.Name = “BOND” THEN Output (“Bond”, c.Address, a.Checking, a. Balance) – 10.000 * 12.000 = 1.2E8 comparisons for join, then filter FOR EACH c in CUSTOMER WITH Name=“Bond” BY INDEX DO FOR EACH a IN ACCOUNT DO IF a.Account# = c.Account# THEN Output (“Bond”, c.Address, a.Checking, a. Balance) – Reduces Customer to 500 tuples, then 6E5 comparisons for join Ulf Leser: Datenbanksysteme II, Sommersemester 2005 35 Join methods • If query contains no selection • Can‘t we do better than “1.2E8 comparisons for join, …” • Nested loop join has complexity O(m*n) – m,n: sizes of joined relations • Other methods – Sort-merge join • First sort relations in O(n*log(n)+m*log(m)) • Merge results in O(m+n) • Might be better, but ... – external sorting is expensive – Doesn‘t pay off if relations already in cache – Hash join – ... Ulf Leser: Datenbanksysteme II, Sommersemester 2005 36 Data Dictionary • Statistics are useful but – Need to be stored and accessed – Need to be kept current – Difficult problem! • Query transformation and optimization needs data dictionary – – – – – Semantic parsing of query: Which relations exist? Which indexes exists? Cardinality estimates of relations? Size of buffer for in-memory sorting? ... Table_name Att_name Att_type size Avg_size Customer Name Varchar2 100 24 Customer account# Int 8 8 Customer ... Ulf Leser: Datenbanksysteme II, Sommersemester 2005 37 Access Control • Read and write access on objects • GRANT, REVOKE Operations • Example: GRANT ALL PRIVILIGES ON ACCOUNT TO Freytag WITH GRANT OPTION • “User Freytag has Read/Write access to the ACCOUNT relation • it is possible for Freytag to grant this rights to others” • No complete protection: – – – – Granularity of access rights usually relation/attribute – not tuple Access to data without DBMS Ask several questions to derive requested data In addition: File protection, encryption of data Ulf Leser: Datenbanksysteme II, Sommersemester 2005 38 Von unten nach oben Datenmodellebene Logischer Zugriff In ca. 8 Monaten: Transaktionen, Schedules, Recovery Speicherstrukturen Pufferverwaltung Betriebssystem Ulf Leser: Datenbanksysteme II, Sommersemester 2005 39 Transactions (TA) • Transaction: “Logical unit of work” Begin_Transaction UPDATE ACCOUNT SET Savings = Savings + 1M SET Checking = Checking - 1M WHERE Account# = 007; INSERT JOURNAL <007, NNN, “Transfer”, ...> End_Transaction • ACID properties: – – – – A: Atomic Execution C: Consistent DB state after updates I: Isolation: No influence on result by concurrent executions D: Durability: Updates are reflected in the database Ulf Leser: Datenbanksysteme II, Sommersemester 2005 40 Why Transactions? • Lost Update Problem Deposit $ 1,000 Deposit $ 2,000 Read account value. Read account value 5,000 5,000 5,000 Add $1,000 Add $ 2,000 6,000 Write back account value 6,000 7,000 Write back account value 7,000 Ulf Leser: Datenbanksysteme II, Sommersemester 2005 41 Synchronization and schedules ? Ulf Leser: Datenbanksysteme II, Sommersemester 2005 42 Synchronization and locks • When are two schedules „fine“? – When they are serializable – I.e., when they are equivalent to a serial schedule – Proof serializability of schedules • Checking after execution is wasteful – – – – Synchronization protocols Guarantee only serializable schedules Require certain well-behavior of transactions Methods • Two phase locking • Multi-version synchronization • Timestamp synchronization Ulf Leser: Datenbanksysteme II, Sommersemester 2005 43 Deadlocks • Locking is powerful, yet caution is necessary • Runs into deadlock • Deadlocks need to be discovered by database – If not avoided by synchronization protocol • Manage locks, lock-waits, lock-times, etc. – Data dictionary Ulf Leser: Datenbanksysteme II, Sommersemester 2005 44 Transaction Manager • • Synchronization is the “I” in ACID Transaction manager is responsible for – Concurrency Control • • • • Concurrent access to data objects Synchronization & locking Correct access protocols Deadlock detection and deadlock resolution – Recovery • • • • Compensating for system und transaction errors Based on log file Correct error recovery protocols – undo; redo Close cooperation with Buffer Mgr. and CC Mgr. Ulf Leser: Datenbanksysteme II, Sommersemester 2005 45 Recovery – Broad Principle Transactions Concurrency Control Manager Secondary Storage Main Memory (MM). Recovery-Mgr. Buffer-Mgr. Log MM-Log S-Log MM-DB S-DB DB • Store data redundantly – Save old values • Uses different file format, adapted to different access characteristics – Sequential write, rare reads Ulf Leser: Datenbanksysteme II, Sommersemester 2005 46 So many managers ... Ulf Leser: Datenbanksysteme II, Sommersemester 2005 47 Further topics • Depend on time • • • • Parallel databases and parallel query execution Information integration and federated databases Data warehouses Database programming: PL/SQL, trigger, SQLJ Ulf Leser: Datenbanksysteme II, Sommersemester 2005 48