Datenbanken Prof. Dr. Ralf Möller Universität zu Lübeck Institut für Informationssysteme Karsten Martiny (Übungen) Architektur eines DBMS Architecture of a DBMS / Course Outline Anwendungen Applications Webformulare Web Forms SQL-Schnittstelle SQL Interface Operator-Evaluierer Operator Evaluator Optimierer Optimizer Transaction TransaktionsVerwalter Manager Lock SperrVerwalter Manager Dateiverwaltungsund Zugriffsmethoden Files and Access Methods Puffer-Verwalter Buffer Manager WiederRecovery herstellungsManager Verwalter this course Parser Parser dieser Teil des Kurses Ausführer Executor Figure inspired by Ramakrishnan/Gehrke: “Database Management Systems”, McGraw-Hill 2003. SQL-Kommandos SQL Commands Verwalter für externen Speicher Disk Space Manager DBMS Dateien für Daten und Indexe. . . data files, indices, Fall 2008 Datenbank Database Systems Group — Department of Computer Science — ETH Zürich 3 2 Anfragebeantwortung Aggregation SELECT C.CUST_ID, C.NAME, SUM (O.TOTAL) AS REVENUE FROM CUSTOMERS AS C, ORDERS AS O Selektion WHERE C.ZIPCODE BETWEEN 8000 AND 8999 AND C.CUST_ID = O.CUST_ID Join GROUP BY C.CUST_ID Gruppierung ORDER BY C.CUST_ID, C.NAME Sortierung Ein DBMS muss eine Menge von Aufgaben erledigen: • mit minimalen Ressourcen • über großen Datenmengen • und auch noch so schnell wie möglich 3 Danksagung • Diese Vorlesung ist inspiriert von den Präsentationen zu dem Kurs: „Architecture and Implementation of Database Systems“ von Jens Teubner an der ETH Zürich • Graphiken und Code-Bestandteile wurden mit Zustimmung des Autors und ggf. kleinen Änderungen aus diesem Kurs übernommen 4 Sortierung Wichtige Datenbankoperation mit vielen Anwendungen • Eine SQL-Anfrage kann Sortierung anfordern SELECT A,B,C FROM R ORDER BY A • Bulk-loading eines B+-Baumes fußt auf sortierten Daten • Duplikate-Elimination wird besonders einfach SELECT DISTINCT A,B,C FROM R • Einige Datenbankoperatoren setzen sortierte Eingabedateien voraus (kommt später) Wie können wir eine Datei sortieren, die nicht in den Hauptspeicher passt (und auf keinen Fall in den vom Pufferverwalter bereitgestellten Platz)? 5 Zwei-Wege-Mischsortieren (Merge Sort) Sortierung von Dateien beliebiger Größe in nur 3 Seiten aus dem Pufferverwalter • Sortierung von N Seiten in mehreren Durchgängen 6 Beispiel Illustration / Example input file 65 1-page runs 56 2-page runs 43 34 47 47 89 89 52 25 13 13 8 8 Pass 0 Pass 1 34 56 47 89 12 35 8 Pass 2 4-page runs 7-page run Fall 2008 34 45 67 89 12 35 8 Pass 3 12 33 44 55 67 88 9 Systems Group — Department of Computer Science — ETH Zürich 123 7 Zwei-Wege-Mischsortieren Pass 0 (Input: N = 2k unsorted pages; Output: 2k sorted runs) 1. Read N pages, one page at a time 2. Sort records in main memory. 3. Write sorted pages to disk (each page results in a run). This pass requires one page of buffer space. Pass 1 (Input: N = 2k sorted runs; Output: 2k 1 sorted runs) 1. Open two runs r1 and r2 from Pass 0 for reading. 2. Merge records from r1 and r2 , reading input page-by-page. 3. Write new two-page run to disk (page-by-page). This pass requires three pages of buffer space. .. . Pass n (Input: 2k n+1 sorted runs; Output: 2k n sorted runs) 1. Open two runs r1 and r2 from Pass n 1 for reading. 2. Merge records from r1 and r2 , reading input page-by-page. 3. Write new 2n -page run to disk (page-by-page). Fall 2008 This pass requires three pages of buffer space. .. . Systems Group — Department of Computer Science — ETH Zürich 122 8 I/O-Verhalten • Um eine Datei mit N Seiten zu sortieren, werden in jedem Durchgang N Seiten gelesen und geschrieben ⟶ 2∙N I/O-Operationen pro Durchgang • Anzahl der Durchgänge: 1 + ⎡ log2 N ⎤ Durchgang 0 Durchgänge 1..k • Anzahl der I/O-Operationen: 2 ∙ N ∙ (1 + ⎡ log2 N ⎤ ) 9 Aufgabe: Anzahl der Durchgänge: 1 + ⎡ log2 N ⎤ Anzahl der I/O-Operationen: 2 ∙ N ∙ (1 + ⎡ log2 N ⎤ ) Wie lange dauert die Sortierung einer 8GB Datei bei einer Travelstar 7k200? – ts = 10 ms, tr = 4.17 ms, ttr = 0,16ms, 8KB pro Seite 10 Lösung ≈ 6d (Herleitung an der Tafel) 11 Externes Mischsortieren • Bisher freiwillig nur 3 Seiten verwendet • Wie kann ein großer Pufferbereich genutzt werden: B Seiten auf einmal bearbeiten • Zwei wesentliche Stellgrößen – Reduktion der initialen Durchgänge durch Verwendung des Pufferspeichers beim Sortieren im Hauptspeicher – Reduktion der Anzahl der Durchgänge durch Mischen von mehr als 2 Seiten 12 Reduktion der Anzahl der Durchgänge Reducing the Number of Initial Runs Mit B Seiten im Puffer können B Seiten eingelesen und im With B frames available in the buffer pool, we can read B pages at Hauptspeicher a time during Pass sortiert 0 and sortwerden them in memory (% slide 122): Pass 0 (Input: N unsorted pages; Output: :::::::::::::::::: dN/Be sorted runs) 1. Read N pages, B pages at a time :::::::::::::::::: 2. Sort records in main memory. N/Be runs). 3. Write sorted pages to disk (resulting in d:::::::::: This pass uses :::::::: B pages of buffer space. •TheAnzahl I/O-Operationen: numberder of initial runs determines the number of passes we need to make (% slide 124): 2 ∙ N ∙ (1 + ⎡ log ⌃ 2 ⎡ N/B ⎤⌥⎤ ) ! Total number of I/O operations: 2 · N · 1 + log2 dN/Be . • Was ist das Zugriffsmuster auf diese Ein-Ausgaben? How many I/Os does it now take to sort an 8 GB file? Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 126 13 Aufgabe: Wie lange dauert die Sortierung einer 8GB Datei mit B=1000 Pufferseiten je 8KB mit einer Travelstar 7k200? – ts = 10 ms, tr = 4.17 ms, ttr = 0,16ms, 8KB pro Seite Anzahl der Durchgänge: 1 + ⎡ log2 ⎡ N /B ⎤ ⎤ Anzahl der I/O-Operationen: 2 ∙ N ∙ (1 + ⎡ log 2 ⎡ N/B ⎤ ⎤ ) 14 Lösung ≈ 3d (Herleitung an der Tafel) 15 Reduktion der Anzahl der Durchgänge Mit B Seiten im Puffer können auch B-1 Seiten gemischt werden (eine Seite dient als Schreibpuffer) (B-1)-Wege-Mischen: • Anzahl der I/O-Operationen: 2 ∙ N ∙ (1 + ⎡ logB-1 ⎡ N/B ⎤⎤ ) • Was ist das Zugriffsmuster auf diese Ein-Ausgaben? 16 Aufgabe: Wie lange dauert die Sortierung einer 8GB Datei mit B=1000 Pufferseiten je 8KB mit einer Travelstar 7k200? – ts = 10 ms, tr = 4.17 ms, ttr = 0,16ms, 8KB pro Seite Anzahl der Durchgänge: 1 + ⎡ logB-1 N /B⎤ Anzahl der I/O-Operationen: 2 ∙ N ∙ (1 + ⎡ log B -1 ⎡ N/B ⎤ ⎤ ) 17 Lösung ca. 4 ∙ 106 Plattenzugriffe mit je 14.33ms ≈ 16h (Herleitung an der Tafel) 18 Blockweise Ein-Ausgabe Man kann das I/O-Muster verbessern, in dem man Blöcke von b Seiten in den Mischphasen verarbeitet • Alloziere b Seiten für jede Eingabe (statt nur eine) • Reduktion der Ein-Ausgabe um Faktor b • Preis: Reduzierte Einfächerung (was in mehr Durchgängen und damit in mehr I/O-Operationen resultiert) 19 Aufgabe: Wie lange dauert die Sortierung einer 8GB Datei mit B=1000 Pufferseiten je 8KB und blockweisem IO mit b=32 bei einer Travelstar 7k200 ? – ts = 10 ms, tr = 4.17 ms, ttr = 0,16ms, 8KB pro Seite – 63 Sektoren pro Spur, Track-to-Track-Suchzeit ts,track-to-track =1 ms – Ein Block mit 8 KB benötigt 16 Sektoren Anzahl der Durchgänge: 1 + ⎡ log ⎡ B/b ⎤ -1 N /(B/b)⎤ Anzahl der I/O-Operationen: 2 ∙ ⎡ N/b ⎤ ∙ (1 + ⎡ log ⎡ B/b ⎤ -1 ⎡ N/(B/b) ⎤ ⎤ ) 20 Lösung Mit blockweisem I/O (B=1000 Blöcke mit b=32 Seiten): ca. 8 ∙ 31250 Plattenzugriffe mit je 27.41 ms ≈2h (Herleitung in der Übung) 21 Hauptsspeicher als Ressource In der Praxis meist genügend Hauptspeicher vorhanden, so dass Dateien in einem Mischdurchgang sortiert werden kann (mit blockweisem I/O). 22 Aufgabe: Wieviel Hauptspeicher wird bei Pufferseiten mit je 8KB und blockweisem IO mit b=32 benötigt, so dass ein Mischvorgang für eine 8GB Datei reicht? Anzahl der Durchgänge: 1 + ⎡ log ⎡ B/b ⎤ -1 N /(B/b)⎤ ⎡ log ⎡ B/b ⎤ -1 ⎡ N/(B/b) ⎤ ⎤ = 1 23 Lösung ≈ 1h (Herleitung in der Übung) 24 Bisher nur IO-Zeit betrachtet Selection Trees Auswahl des nächsten Datensatzes aus Choosing the next record from B 1 (or B/b 1) input runs can be B-1 (oder B/b -1) Eingabeläufen kann quite CPU intensive (B 2 comparisons). CPU-intensiv sein (B-2 Vergleiche) I Use a selection tree to reduce this cost. • Verwende zur TAoCP, Kostenreduktion I E.g., “tree Auswahlbaum of losers” (% D. Knuth, vol. 3): („Tree of Losers“) 23 95 79 142 91 985 985 . . . 650 670 23 . . . 850 132 873 91 670 605 850 873 . . . . . . . . . . . . . . . 190 79 190 132 . . . . . . . . . 278 412 901 95 412 142 390 278 901 . . . . . . . . . . . . . . . . . . • Reduktion dernumber Vergleiche auf log2to(B-1) I This cuts the of comparisons log (B 2 Fall 2008 390 Systems Department of Computer Science — ETH Zürich D.Group Knuth,—The Art of Computer Programming,vol.3, 1973 1). 130 25 Externes Sortieren: Diskussion • Misch-Schritte können auch parallel ausgeführt werden • Bei ausreichend Speicher reichen zwei Durchgänge auch für große Dateien • Mögliche Optimierungen: – Seitenersetzung während des Sortierens: Erneutes Laden neuer Seiten während des initialen Laufs (dadurch Erhöhen der initialen Lauflänge) – Doppelpufferung: Verschränkung des Seitenladevorgangs und der Verarbeitung, um Latenzzeiten der Festplattenspeicher zu kaschieren 26 Datenbanken Prof. Dr. Ralf Möller Universität zu Lübeck Institut für Informationssysteme Karsten Martiny (Übungen) Ausführungspläne Query Plans RETURN | NLJOIN /---/ \---\ FETCH / \ RIDSCN Table: | G_PRE_SIZE SORT DOC | IXSCAN / \ Index: Table: G_PRE_SIZE G_PRE_SIZE PROP_IDX DOC IXSCAN / \ Index: Table: G_PRE_SIZE G_PRE_SIZE PARENT_IDX DOC Actual DB2 execution(DB2) plan. Ausführungsplan I • Externes Sortieren ist eine External sorting is one Instanzofeines physikalischen instance a (physical) Datenbankoperators database operator. I can bekönnen assembled • Operators Operatoren zu into a query execution plan. Ausführungsplänen I Each plan operator performs zusammengesetzt werden one sub-task of a given • query. JederTogether, Planoperator führt zur the operators of a planeiner evaluate Verarbeitung vollthe full query. Anfrage eine ständigen Unteraufgabe aus I We’ll have a deeper look into join operators next. Wir werden zunächst Verbundoperatoren betrachten Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 132 28 The Verbundoperator Join Operator 1 (Join) R⋈S The operator 1p is actually a short-hand for afür combination of Einjoin Verbundoperator ⋈p ist eine Abkürzung die cross product ⇥ and selection p . Zusammensetzung von Kreuzprodukt × und Selektion σp R 1p p , S R ⇥ S Daraus ergibt sich eine einfache Implementierung von ⋈p One way to implement 1p is to follow this equivalence: 1. Enumeriere alle Datensätze aus R × S 1. Enumerate all records in the cross product of R and S. 2. Wähle die Datensätze, die p erfüllen 2. Then pick those that satisfy p. Ineffizienz aus Schritt 1 kann überwunden werden More advanced algorithms try to avoid the obvious inefficiency in (Größe des Zwischenresultats: |R| × |S|) Step 1 (the size of the intermediate result is |R| · |S|). Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 29 133 Verbund-als-geschachtelte-Schleifen Nested Loops Join The nested loops join is the straightforward implementation of Einfache Implementierung des Verbundes: the –⇥ combination: 1 2 3 4 5 Function: nljoin (R, S, p) foreach record r 2 R do foreach record s 2 S do if hr, si satisfies p then append hr, si to result Sei NRR and undNN Seitenzahl in in R und undpSpbe S die S die Let N number of pages R andS,S; sei let ppRRand the S the Anzahl Datensätze number der of records per pagepro in RSeite and S.in R und S Anzahl Plattenzugriffe: The totalder number of disk reads is then NNRR++pprR∙·NNRR ∙·NNSS . | {z } #Tupel in #R tuples in R Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 134 30 Verbund-als-geschachtelte-Schleifen Nur 3 Seiten nötig (zwei Seiten fürs Lesen von R und S und eine um das Ergebnis zu schreiben) I/O-Verhalten: Leider sehr viele Zugriffe • Annahme pR = pS = 100, NR = 1000, Ns = 500: 1000 + 5 ∙ 107 Seiten zu lesen • Mit einer Zugriffszeit von 10ms für jede Seite dauert der Vorgang 140 Stunden • Vertauschen von R und S (kleinere Relation S nach außen) verbessert die Situation nur marginal Seitenweises Lesen bedingt volle Plattenlatenz, obwohl beide Relationen in sequentieller Ordnung verarbeitet werden. 31 Blockweiser Verbund mit Schleifen Block Nested Loops Join Again we can save random access cost by reading R and S in Einsparung von Kosten durch wahlfreien Zugriff durch blocks of, say,Lesen bR andvon bS pages. blockweises R und S mit bR und bS vielen Seiten 1 2 3 4 Function: block nljoin (R, S, p) foreach bR -sized block in R do foreach bS -sized block in S do find matches in current R- and S-blocks and append them to the result ; • R wird vollständig gelesen, aber mit nur ⎡NR/bR⎤ I R is still read once, but now with only dNR/bR e disk seeks. Lesezugriffen I S is scanned only dNR/bR e times now, and we need to perform • S nur ⎡NR/bR⎤ mal gelesen, dNR/bR e · dNS/bS e disk seeks to do this. mit ⎡NR/bR⎤ ∙ ⎡NS/bS⎤ Plattenzugriffen Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 32 136 Wahl von bR und bS Choosing bR and bS Pufferbereich mit B = 100 Rahmen, N = 1000, NS = 500: E.g., buffer pool with B = 100 frames, N =R1000, N = 500: R Plattenzugriffe disk seeks 7000 90 S Blockgröße fürs Lesen von S (bSs(b ) S) block size used for reading 80 70 60 50 40 30 20 10 7000 6000 6000 5000 5000 4000 4000 3000 3000 2000 2000 1000 1000 0 10 20 30 40 50 60 70 80 block size used for reading R (b ) Blockgröße fürs Lesen von R (b ) R 90 0 R Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 137 33 Datenbanken Prof. Dr. Ralf Möller Universität zu Lübeck Institut für Informationssysteme Karsten Martiny (Übungen) Blockweiser Verbund mit Schleifen Block Nested Loops Join Again we can save random access cost by reading R and S in Einsparung von Kosten durch wahlfreien Zugriff durch blocks of, say,Lesen bR andvon bS pages. blockweises R und S mit bR und bS vielen Seiten 1 2 3 4 Function: block nljoin (R, S, p) foreach bR -sized block in R do foreach bS -sized block in S do find matches in current R- and S-blocks and append them to the result ; • R wird vollständig gelesen, aber mit nur ⎡NR/bR⎤ I R is still read once, but now with only dNR/bR e disk seeks. Lesezugriffen I S is scanned only dNR/bR e times now, and we need to perform • S nur ⎡NR/bR⎤ mal gelesen, dNR/bR e · dNS/bS e disk seeks to do this. mit ⎡NR/bR⎤ ∙ ⎡NS/bS⎤ Plattenzugriffen Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 35 136 Performanz des Hauptspeicher-Verbunds In-Memory Join Performance • Zeile 4 in block_nljoin(R, S, p) bedingt einen I Line 4 in block nljoin (R, S, p) implies an in-memory join Hauptspeicherverbund zwischen Blocken aus R und S between the R- and S-blocks currently in memory. • Aufbau Ieiner Hashtabelle kann Verbund Building a hash table over theden R-block can speederheblich up this join considerably. beschleunigen 1 2 3 4 5 6 Function: block nljoin’ (R, S, p) foreach bR -sized block in R do build an in-memory hash table H for the current R-block ; foreach bS -sized block in S do foreach record s in current S-block do probe H and append matching hr, si tuples to result ; • Funktioniert nur für Equi-Verbunde I Fall 2008 Note that this optimization only helps equi-joins. Systems Group — Department of Computer Science — ETH Zürich 138 36 Indexbasierte Verbunde R⋈S Index Nested Loops Join Verwendung einesloops Index dieadvantage innere Relation S on the The index nested joinfür takes of an index (ggf. innere und äußere inner relation (swap outervertauschen) $ inner if necessary): 1 2 3 Function: index nljoin (R, S, p) foreach record r 2 R do probe index using r and append all matching tuples to result ; I The • Index muss mit derwith Verbundbedingung indexverträglich must be compatible the join condition p.sein I Hash indices, only support equality predicates. – Hash-Index (nur füre.g., Gleichheitsprädikate) I Remember the discussion about composite keys in – Vergleiche auch die Diskussion über + B -trees (% slide 73). zusammengesetzte Schlüssel in B+-Bäumen • Such predicates are also called sargable (SARG: search Argumente „sargable“ (SARG= search argument) argumentheißen % Selinger et al., SIGMOD 1979) I Fall 2008 Access Path Selection in a Relational Database Management Selinger et al., SIGMOD Systems GroupSystem, — Department of Computer Science1979 — ETH Zürich 139 37 I/O-Verhalten Für jeden Datensatz in R verwende Index zum Auffinden von korrespondierenden S-Tupeln. Für jedes R-Tupel sind folgende Kosten einzukalkulieren: 1. Zugriffskosten für den Index zum Auffinden des ersten Eintrags: Nidx I/O-Operationen 2. Entlanglaufen an den Indexwerten (Scan), um passende Rids zu finden (I/O-Kosten vernachlässigbar) 3. Holen der passenden S-Tupel aus den Datenseiten – Für ungeclusterten Index: n I/O-Operationen – Für geclusterten Index: ⎡n/ps⎤ I/O-Operationen Wegen 2. und 3. Kosten von der Verbundgröße abhängig 38 Zugriffskosten für Index Falls Index ein B+-Baum: • Einzelner Indexzugriff benötigt Zugriff auf h Indexseiten1 • Bei wiederholtem Zugriff sind diese Seiten im Puffer • Effektiver Wert der I/O-Kosten 1-3 I/O-Operationen Falls Index ein Hash-Index: • Caching nicht effektiv (kein lokaler Zugriff auf Hashfeld) • Typischer Wert für I/O-Kosten: 1,2 I/O-Operationen (unter Berücksichtigung von Überlaufseiten) Index rentiert sich stark, wenn nur einige Tupel aus einer großen Tabelle im Verbund landen 1 h = Höhe des B+-Baumes 39 Sortier-Misch-Verbund Sort-Merge Join Verbundberechnung wird einfach wenn Join computation becomes particularly simple if both inputs are Eingaberelationen bzgl. Verbundattribut(en) sortiert sorted with respect to the join attribute(s). • Misch-Verbund mischt Eingabetabellen ähnlich I The merge join essentially merges both input tables, much wie beim Sortieren like we did for sorting. I Contrast • Es gibt aber mehrfache Korrespondenzen to sorting, however, we need to be careful in der whenever a tuple has multiple matches in the other relation: anderen Relation A "foo" "foo" "bar" "baz" "baf" B 1 2 2 2 4 1 B=C C D 1 false 2 true 2 false 3 true I Merge join is typically for equi-joins only. verwendbar • Misch-Verbund nur used für Equi-Verbünde Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 142 40 Misch-Verbund 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Function: merge join (R, S, ↵ = ) // ↵, : join columns in R, S r position of first tuple in R ; s position of first tuple in S ; while r 6= eof and s 6= eof do while r.↵ < s. do advance r ; // r, s, s0 : cursors over R, S, S // eof: end of file marker while r.↵ > s. do advance s ; s0 s; // Remember current position in S while r.↵ = s0 . do // All R-tuples with same ↵ value s s0 ; // Rewind s to s0 while r.↵ = s. do // All S-tuples with same value append hr, si to result ; advance s ; advance r ; 15 Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 143 41 I/O-Verhalten • Wenn beide Eingaben sortiert und keine außergewöhnlich langen Sequenzen mit identischen Schlüsselwerten vorhanden, dann ist der I/O-Aufwand NR + NS (das ist dann optimal) • Durch blockweises I/O treten fast immer sequentielle Lesevorgänge auf • Es kann sich für die Verbundberechnung auszahlen, vorher zu sortieren, insbesondere wenn später eine Sortierung der Ausgabe gefordert wird • Ein abschließender Sortiervorgang kann auch mit einem Misch-Verbund kombiniert werden, um Festplattentransfers einzusparen 42 Aufgabe: Bei welchen Eingaben tritt beim SortierMisch-Verbund das schlimmste Verhalten auf? 43 Lösung • Wenn all verbundenen Attribute gleiche Werte beinhalten, dann ist das Ergebnis ein Kreuzprodukt. • Der Sortier-Misch-Verbund verhält sich dann wie ein geschachtelte-Schleifen-Verbund. 44 Hash-Verbund • Sortierung bringt korrespondierende Tupel in eine „räumliche Nähe“, so dass eine effiziente Verarbeitung möglich ist • Ein ähnlicher Effekt erreichbar mit Hash-Verfahren Hash Join I Sorting effectively brought related tuples into spacial • Zerlege R und S in Teilrelationen R1, ..., Rn und S1,...,Sn proximity, which we exploited in the merge join algorithm. I Wegleichen can achieve a similar effect with hashing, too. mit der Hashfunktion (angewendet auf die I Partition R and S into partitions R1 , . . . , Rn and S1 , . . . , Sn Verbundattribute) using the same hash function (applied to the join attributes). Partition 1 (R1 and S1 ) Relation R h Partition 2 (R2 and S2 ) Partition 3 (R3 and S3 ) Relation S h .. . Partition n (Rn and Sn ) thatalle Ri 1 Sij = • Ri ⋈ RIj Observe = ∅ für ≠?j for all i 6= j. Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 145 45 Hash-Verbund • Durch Partitionierung werden kleine Relationen Ri and Si geschaffen • Korrespondierende Datensätze kommen garantiert in korrespondierende Partitionen der Relationen • Es muss Ri ⋈ Si (für all i) berechnet werden (einfacher) • Die Anzahl der Partitionen n (d.h. die Hashfunktion) sollte mit Bedacht gewählt werden, so dass Ri ⋈ Si als Hauptspeicher-Verbund berechnet werden kann • Hierzu kann wiederum eine (andere) Hashfunktion verwendet werden (siehe blockweisen Verbund mit Schleifen) Warum eine andere Hashfunktion? 46 Hash-Verbund-Algorithmus Hash Join Algorithm 1 2 3 4 5 6 7 8 9 10 Function: hash join (R, S, ↵ = ) foreach record r 2 R do append r to partition Rh(r.↵) foreach record s 2 S do append s to partition Sh(s. ) foreach partition i 2 1, . . . , n do build hash table H for Ri , using hash function h0 ; foreach block in Si do foreach record s in current Si -block do probe H and append matching tuples to result ; Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 47 147 Gruppierung und Duplikate-Elimination • Herausforderung: Finde identische Datensätze in einer Datei • Ähnlichkeiten zum Eigenverbund (self-join) basierend auf allen Spalten der Relation – Man könnte einen Hash-Verbund-ähnlichen Algorithmus verwenden oder Sortierung, um Duplikate-Elimination oder Gruppierung zu realisieren 48 Andere Anfrage-Operatoren Projektion π • Implementierung durch a. Entfernen nicht benötigter Spalten b. Eliminierung von Duplikaten • Die Implementierung von a) bedingt das Ablaufen (scan) aller Datensätze in der Datei, b) siehe oben • Systeme vermeiden b) sofern möglich (in SQL muss Duplikate-Eliminierung angefordert werden) Selektion σ • Ablaufen (scan) aller Datensätze • Eventuell Sortierung ausnutzen oder Index verwenden 49 Organisation der Operator-Evaluierung Orchestrating Operator Evaluation • Bisher gehen wir davon aus, dass Operatoren So far we have assumed that all database operators consume ganze and produce files (i.e., on-disk items): Dateien verarbeiten ··· ··· 1 ··· ⇡ file1 file2 file3 ⇡ filen • DasI erzeugt offensichtlich viel I/O Obviously, this causes a lot of I/O. I In addition, we suffer from long response times: • Außerdem: lange Antwortzeiten I An operator cannot start computing its result before all – Ein Operator kann nicht anfangen solange nicht seine its input files are fully generated (“materialized”). Eingaben vollständig bestimmt sind (materialisiert sind) Effectively, all operators are executed in sequence. – Operatoren werden nacheinander ausgeführt I Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 152 50 Pipeline-orientierte Verarbeitung • Alternativ könnte jeder Operator seine Ergebnisse direkt an den nachfolgenden senden, ohne die Ergebnisse erst auf die Platte zu schreiben • Ergebnisse werden so früh wie möglich weitergereicht und verarbeitet (Pipeline-Prinzip) • Granularität ist bedeutsam: – Kleinere Brocken reduzieren Antwortzeit des Systems – Größere Brocken erhöhen Effektivität von InstruktionsCachespeichern – In der Praxis meist tupelweises Verarbeiten verwendet 51 Finding the right plan can dramatically impact performance. Auswirkungen auf die Performanz SELECT L.L PARTKEY, L.L QUANTITY, L.L EXTENDEDPRICE FROM LINEITEM L, ORDERS O, CUSTOMER C SELECT L.L_PARTKEY, L.L_QUANTITY, L.L_EXTENDEDPRICE WHERE L.L ORDERKEY ORDERKEY FROM LINEITEM L, ORDERS=O,O.O CUSTOMER C WHERE ORDERKEY AND L.L O.OORDERKEY CUSTKEY= O.O = C.C CUSTKEY AND O.O_CUSTKEY = C.C_CUSTKEY AND AND C.CC.C_NAME NAME == ’IBM Corp.’ ’IBM Corp.’ 57 • Tupelweises57 Verarbeiten 1 1 der Relation C 14 6 mio 1 6 mio 1 L • Sofortiges Weiterleiten 1.5 mio 1 1 O 150,000 1.5 mio 6 mio nach der Selektion 150,000 L O C • Kombiniert mit C tupelweisem Verarbeiten I In terms of execution times, these differences can easily der Relationen O und days.” L mean “seconds versus Fall 2008 Systems Group — Department of Computer Science — ETH Zürich 163 52 Volcano Iteratormodell • Aufrufschnittstelle wie bei Unix-Prozess-Pipelines • Im Datenbankkontext auch Open-Next-CloseSchnittstelle oder Volcano Iteratormodell genannt • Jeder Operator implementiert – open() Initialisiere den internen Zustand des Operators – next() Produziere den nächsten Ausgabe-Datensatz – close() Schließe allozierte Ressourcen • Zustandsinformation wird Operator-lokal vorgehalten Goetz Graefe. Volcano—An Extensibel and Parallel Query Evaluation System. Trans. Knowl. Data Eng. vol. 6, no. 1, February 1994 53 Beispiel: Selektion (σ) Example: Selection ( ) I Input operator predicatepp. Eingabe: Relation R,R,Prädikat 1 2 R.open () ; 1 Function: close () 2 R.close () ; 1 Function: next () 4 while ((r R.next ()) 6= eof) do if p(r) then return r ; 5 return eof ; 2 3 Fall 2008 Function: open () Systems Group — Department of Computer Science — ETH Zürich 156 54 Geschachtelte Schleifen Verbund: Volcano-Stil How would you implement a Volcano-style nested loops join? 1 4 R.open () ; S.open () ; r R.next () ; 1 Function: next () 2 3 2 3 4 5 6 7 8 9 Fall 2008 Function: open () 1 2 3 Function: close () R.close () ; S.close () ; while (r 6= eof) do while ((s S.next ()) 6= eof) do if p(r, s) then return hr, si ; S.close () ; S.open () ; sr ⟵ R.next(); S.next () ; return eof ; Systems Group — Department of Computer Science — ETH Zürich 157 55 Blockierende Operatoren • Pipelining reduziert Speicheranforderungen und Antwortzeiten, da jeder Datensatz gleich weitergeleitet • Funktioniert so nicht für alle Operatoren • Welche? – Externe Sortierung – Hash-Verbund – Gruppierung und Duplikate-Elimination über einer unsortierten Eingabe • Solche Operatoren nennt man blockierend • Blockierende Operatoren konsumieren die gesamte Eingabe in einem Rutsch bevor die Ausgabe erzeugt werden kann (Daten auf Festplatte zwischengespeichert) 56 Zusammenfassung Teile-und-Herrsche • Zerlegung einer großen Anfrage in kleine Teile • Beispiel: – Laufgenerierung und externe Sortierung – Partitionierung mit Hashfunktion (Hash-Verbund) Blockweises Durchführen von I/O • Lesen und Schreiben von größeren Einheiten kann die Verarbeitungszeit deutlich reduzieren, da wahlfreier Zugriff auf Daten vermieden wird Pipeline-orientierte Verarbeitung – Speicherreduktion und Laufzeitverbesserung durch Vermeidung der vollständigen Materialisierung von Zwischenresultaten Beim nächsten Mal... Architecture of a DBMS / Course Outline Anwendungen Applications Webformulare Web Forms SQL-Schnittstelle SQL Interface Operator-Evaluierer Operator Evaluator Optimierer Optimizer Transaction TransaktionsVerwalter Manager Lock SperrVerwalter Manager Dateiverwaltungsund Zugriffsmethoden Files and Access Methods Puffer-Verwalter Buffer Manager WiederRecovery herstellungsManager Verwalter this course Parser Parser dieser Teil des Kurses Ausführer Executor Figure inspired by Ramakrishnan/Gehrke: “Database Management Systems”, McGraw-Hill 2003. SQL-Kommandos SQL Commands Verwalter für externen Speicher Disk Space Manager DBMS Dateien für Daten und Indexe. . . data files, indices, Fall 2008 Datenbank Database Systems Group — Department of Computer Science — ETH Zürich 3 58