S - IFIS Uni Lübeck - Universität zu Lübeck

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