05.07.2005Elena Hensinger1 Green Query Optimization using Query Clustering Seminar zu...
-
Upload
walthari-westerhoff -
Category
Documents
-
view
102 -
download
0
Transcript of 05.07.2005Elena Hensinger1 Green Query Optimization using Query Clustering Seminar zu...
05.07.2005 Elena Hensinger 1
Green Query Optimization using Query ClusteringSeminar zu Datenbanksystemen
Universität Hannover
Sommersemester 2005
Elena Hensinger
05.07.2005
05.07.2005 Elena Hensinger 2
Einleitung Idee des Plan Recycling Darstellung von Anfragen Vergleich von Anfragen Gruppierung Plandiagramm PLASTIC Leistungsfähigkeit und Einschränkungen Veränderungen und Ausblick
Inhaltsverzeichnis
05.07.2005 Elena Hensinger 3
Optimierung von Ausführungsplänen ist ein recheninten-siver Prozess, insbesondere für sehr große Datenban-ken oder komplexe Anfragen
Viele Pläne müssen verglichen werden Auswahl des besten Planes
In der Regel wird für jede Anfrage ein neuer Plan gene-riert
Ausführungspläne
OptimiererAnfrage Q
Plan mit minimalenKosten für Anfrage Q
DB-Kataloge Kostenmodell
05.07.2005 Elena Hensinger 4
Oracle Optimierer: „stored outlines“
Speicherung von SQL-Anfragen auf textueller Basis und den dazugehörigen Ausführungs-plänen
Wiederbenutzung von Plänen nur möglich, wenn starke textuelle Ähnlichkeit zwischen der gespeicherten und der neuen Anfrage
Plan Recycling ?
05.07.2005 Elena Hensinger 5
Neuer Ansatz
Vergleichen von Anfragen nicht auf Grund ihres Textes, sondern auf Grund von für einen Ausführungsplan Ausschlag gebenden Eigenschaften
Beobachtung: Anfragen mit unterschiedlichen Selektionen, Projektionen, Joinprädikaten und Tabellen können denselben Operatorbaum haben
Je ähnlicher sich zwei Anfragen sind, desto wahrscheinlicher ist es, dass der Optimierer für sie gleiche Ausführungspläne generiert
05.07.2005 Elena Hensinger 6
Alle Anfragen werden auf Grund ihrer Merkmale in Gruppen eingeteilt
Jede Gruppe hat einen Repräsentanten, dessen vom Optimierer generierte Ausführungsplanvorlage gespei-chert wird
Ist eine neue Anfrage „ähnlich“ mit einem Repräsen-tanten, so wird seine gespeicherte Planvorlage an die neue Anfrage angepasst und benutzt
Ist eine neue Anfrage „unähnlich“, so wird wie üblich ein Plan vom Optimierer generiert
Grundsätzlicher Ablauf
05.07.2005 Elena Hensinger 7
Ausführungsplan, bei dem alle Datenbankope-ratoren (z.B. Sortierung und Joins) beibehalten wurden, die bestimmten Werte der Eingaben für diese, wie Tabellennamen und Attributnamen, durch Bindevariablen ersetzt wurden
Ausführungsplanvorlage
A B
A B
:1 :2
:1 :2
05.07.2005 Elena Hensinger 8
Die Hauptfrage:Welche Eigenschaften charakterisieren eine Anfrage und sind gleichzeitig ausschlaggebend für die Auswahl eines Ausführungsplanes?
Durch unterschiedliche Arten von Joins (z.B. Nested-Loops, Merge-Join) und ihre Anord-nungsreihenfolgen mit den unterschiedlichen Operationen (Selektion, Projektion und Gruppierung) gibt es viele mögliche Ausfüh-rungspläne
Merkmalsvektor
05.07.2005 Elena Hensinger 9
Die Kosten für die Ausführungspläne hängen von den Eigenschaften der in der Anfrage benutzten Tabellen und der Anfrageprädikate ab
Nötig sind also sowohl die für die Anfrage typischen strukturellen Informationen wie z. B. die Anzahl der Tabellen, als auch spezifische Eigenschaften der benutzten Tabellen wie z. B. das Vorhandensein und die Benutzung von Indexen
Merkmalsvektor
05.07.2005 Elena Hensinger 10
Informationen werden direkt aus der Anfrage und den Katalogen des Datanbankmanage-mentsystems gewonnen
Solche charakterisierenden Merkmale wurden von Haritsa et al. 2002 vorgestellt
Aufteilung in strukturelle und statistische Merkmale
Merkmalsvektor
05.07.2005 Elena Hensinger 11
werden aus der Anfrage gewonnen
Degree of Table (DT): Anzahl der Joinprädikate für eine Tabelle (Prädikate, die sich auf die Spalten der zu joinenden Tabellen beziehen); 1 für einzelne Tabelle, 0 für Tabelle ohne Joinprädikate Kreuzprodukt
Degree-Sequence of a query (DS): Ein Vektor aus den DTs der einzelnen Tabellen
Number of join predicates (NJC): Gesamtanzahl der Joinprädikate in der Anfrage
Strukturelle Eigenschaften (1)
05.07.2005 Elena Hensinger 12
Join Predicate Index Counts (JIC): Ein Joinprädikat hat eine „index characteristic“ 0, 1, oder 2, wenn es 0, 1 oder 2 indexierte Teile hat
Für jede Anfrage wird die Anzahl der Joinprädikate be-züglich der jeweiligen charakteristischen Werte gezählt
A.a B.b {x,y,z}
Index Flag of a Table (IF): zeigt an, ob man auf einer Tabelle ausschließlich mit Indexzugriffen arbeiten kann
Gdw. für eine Tabelle alle Selektionsprädikate und Pro-jektionsattribute durch den Zugriff auf einen gemeinsa-men Index ausgewertet werden können
Strukturelle Eigenschaften (2)
05.07.2005 Elena Hensinger 13
Nicht immer, wenn in der WHERE-Bedingung auf einem Prädikat ein Index existiert, kann dieser benutzt werden
Prädikat muss auch SARGable (Search-ARGument-able) sein
Beispiele für SARGable: „=“, „<“ Beispiele für NON-SARGable: „IS NULL“, „<>“, „NOT
EXISTS“, „ NOT LIKE“
Predicate Counts of a Table (PC): Zähler für indexierte SARGable- und NON-SARGable-Prädikate
Number of Tables (NT): Anzahl der Tabellen in der Anfrage
Strukturelle Eigenschaften (3)
05.07.2005 Elena Hensinger 14
werden aus den Tabellenstatistiken in dem Systemkata-logen gewonnen:
Table Size (TS): die Größe der Tabelle in Tupeln
Effective Table Size (ETS): effektive Größe der in ei-nem Join teilnehmenden Tabelle
Gewonnen durch Abschätzungen und Statistiken aus den Systemkatalogen und den Einfluss des Herunter-schiebens (”push down“) der Selektionen und Projektionen im Ausführungsplan
Statistische Eigenschaften
05.07.2005 Elena Hensinger 15
Select A.a1, B.b1
from A, B
where A.a1 = B.b1 and
B.b2 < 50;
Indexe über A.a1, B.b2
Globale Eigenschaften
NT 2
DS (1,1)
NJP 1
JIC [0,1,2] (0,1,0)
PCsarg 2
PCnsarg 0 NT = Anzahl Tabellen DS = Vektor der Grade der Tabellen NJP = Anzahl Joinprädikate JIC = Join index count; wie viele Joinprädikate indexiert PC = Eigenschaften der indexierten Prädikate für Benutzung der Indexe
1. Beispiel
05.07.2005 Elena Hensinger 16
Select A.a1, B.b1
from A, B
where A.a1 = B.b1 and
B.b2 < 50;
Indexe über A.a1, B.b2 Selektivität von B.b2<50 : 0,5
Tabelle A Tabelle B
1 DT 1
1 IF 0
(0,1,0) JIC [0,1,2] (0,1,0)
1 PCsarg 1
0 PCnsarg 0
2000 TS 4000
2000 ETS 2000
Tabelleneigenschaften
DT = Anzahl Joins für Tabelle TS = Größe der Tabelle ETS = Effektive Größe der Tabelle IF = Index Flag; ist Zugriff nur über einen Index möglich?
1. Beispiel (2)
05.07.2005 Elena Hensinger 17
Eingabe: zwei Anfragen in Form von Merkmalsvektoren Ausgabe: boolscher Wert, ob die Anfragen ähnlich
1.Teil: Merkmalsvektorenvergleich; Aussieben ganz unähnlicher Anfragen
2.Teil: „Mapping tables“, Abbildung von Tabellen der beiden Anfragen aufeinander mit einer Distanzfunktion
Bei mehreren Möglichkeiten wird die Abbildung mit der kleinsten Distanz gewählt
Wenn Summe aller Distanzen kleiner als voreingestellter Schwellwert ist, dann sind die Anfragen ähnlich
SIMCHECK-Algorithmus
05.07.2005 Elena Hensinger 18
Pseudocode SIMCHECK-Algorithmus
05.07.2005 Elena Hensinger 19
Werte für w1 = 0,7 und w2 = 0,3 Schwellwert = 0,01
Schwellwert bestimmt im weiteren Verlauf die Ausdehnung der Cluster, in die alle ähnliche Anfragen gruppiert werden
SIMCHECK-Algorithmus
05.07.2005 Elena Hensinger 20
Select C.c1, D.d1
from C, D
where C.c1 = D.d1 and
D.d2 = 50;
Indexe über C.c1, D.d2 Selektivität D.d2 = 50 : 0,7
Globale Eigenschaften
2. Beispiel
NT 2
DS (1,1)
NJP 1
JIC [0,1,2] (0,1,0)
PCsarg 2
PCnsarg 0
Tabelle C Tabelle D
1 DT 1
1 IF 0
(0,1,0) JIC [0,1,2] (0,1,0)
1 PCsarg 1
0 PCnsarg 0
1500 TS 4000
1500 ETS 2800
Tabelleneigenschaften
05.07.2005 Elena Hensinger 21
Merkmalsvergleich: Anzahl Tabellen (NT), Anzahl der Joinprädikate (NJP), Vektor der Grade der Tabellen (DS) und Anzahl der Tabellen, bei denen man den vorhandenen Index benutzen kann (PC), ist gleich
Tabellenabbildung: Aufteilung in Gruppen gleichen Grades
1. Anfrage: Grad 1 {A, B}, 2. Anfrage: Grad 1 {C, D}
Keine weiteren Gruppen vorhanden
Mögliche Abbildungen: {A,C}, {A, D}, {B, C}, {B, D}
2. Beispiel
05.07.2005 Elena Hensinger 22
distA,C = 0,7 * |2000-1500| + 0,3 * |2000-1500|
|2000|
distA,D = 0,7 * |2000-4000| + 0,3 * |2000-2800|
|4000|
distB,C = 0,7 * |4000-1500| + 0,3 * |2000-1500|
|4000|
distB,D = 0,7 * |4000-4000| + 0,3 * |2000-2800|
|4000|
Mindestdistanzen von 1:1-Abbildungen: {A, C} + {B, D} = 0,25 + 0,06 = 0,31 > 0,01
A B
2000 TS 4000
2000 ETS 2000
C D
1500 TS 4000
1500 ETS 2800
distA,C distA,D distB,C distB,D
0,25 0,41 0,475 0,06
2. Beispiel
05.07.2005 Elena Hensinger 23
Wann ist das Ergebnis < 0,01? Tabellen A und B unverändert Größe von Tabelle C : 1996 Selektivität von D.d2 = 50 : 0,5265
A B
2000 TS 4000
2000 ETS 2000
distA,C = 0,002 distA,D = 0,358
distB,C = 0,351 distB,D = 0,00795
Möglichkeiten: {A, D} + {B, C} = 0,709 {A, C} + {B, D} = 0,00995 < 0,01
C D
1996 TS 4000
1996 ETS 2106
3. Beispiel
05.07.2005 Elena Hensinger 24
Daten / Objekte in Gruppen segmentieren Cluster und Anzahl können vorgegeben sein oder auch
nicht Ideal: Starke Ähnlichkeit der Objekte innerhalb eines
Clusters, starke Unähnlichkeit zwischen den Objekten in den einzelnen Clustern
Anzahl Cluster vorgegeben partitionierende Verfahren Anzahl nicht vorgegeben hierarchische Verfahren
Clustering
05.07.2005 Elena Hensinger 25
1.Objekt wird „Leader“ / „Anführer“ eines Clusters
Alle weiteren Objekte werden mit den vorhandenen Leadern durch eine Distanzfunktion verglichen
Besteht Ähnlichkeit, dann Einordnung in den Cluster des Leaders mit der kleinsten Distanz zum Objekt
Keine Ähnlichkeit Objekt wird Leader eines neuen Clusters
Ende, wenn alle Objekte abgearbeitet sind
Leader-Algorithmus
05.07.2005 Elena Hensinger 26
Bildung der Cluster hängt von der Bearbeitungsreihen-folge der Objekte ab
Keine Überarbeitung aller Cluster beim Erstellen eines neuen Clusters nötig
Laufzeit O(k*n) mit k Clustern und n Anfragen
In der Regel k<<n, da jeder Leader u.U. Hunderte von
Anfragen vertreten kann
Der Leader eines Clusters ist dessen Repräsentant
Eigenschaften des Leader-Algorithmus
05.07.2005 Elena Hensinger 27
“The TPC (Transaction Processing Performance Council) is a non-profit corporation founded to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry” von http://www.tpc.org
22 businessorientierte ad-hoc Anfragen mit Bindevari-ablen für unterschiedliche Selektivitäten
Tabelle Kardinalität Größe in MB
PART 200.000 29,8
PARTSUPP 800.000 124,7
CUSTOMER 150.000 26,6
SUPPLIER 10.000 1,7
LINEITEM 4.859.686 658,6
NATION 25 2*10-3
REGION 5 4*10-4
TPC-H benchmark
05.07.2005 Elena Hensinger 28
Original: mit Unteranfrage und „order by“
Kontostand (acctbal), Name (s_name), Land (n_name), Nummer (p_partkey) und Hersteller (p_mfgr) des Artikels, Adresse (address), Telefonnum-mer (phone) und Bemerkung
Beispielanfrage
(comment) aller Lieferanten aus einer bestimmten Region (r_name), die Artikel von einer bestimmten Größe (size) und Typ (type) zu einem bestimmten Preis (supplycost) liefern können
05.07.2005 Elena Hensinger 29
Veränderung der Selektivitäten der Tabellen PART und PARTSUPP durch Bindervariablen :1, :2 und :4 Übersicht über die Ausführungspläne = Plandiagramm
Plandiagramm
Achsen repräsentieren die Selektivitäten
12 verschiedene Pläne Plan 1 und 2 decken
den meisten Raum ab
05.07.2005 Elena Hensinger 30
High-volatility-Regionen: sich oft verändernde Ausfüh-rungspläne
Typischerweise in stark selektiven Bereichen Low-volatility-Regionen: wenig Veränderung der Pläne
Regionen
05.07.2005 Elena Hensinger 31
Clusterabdeckung Bildhafte Abdeckung durch Cluster gleicher Größe
Viele überflüssige Cluster mit gleichen Plänen in den low-volatility-Regionen
unterbesetzte Cluster bzw. mit mehreren Plänen in den high-volatility-Regionen
05.07.2005 Elena Hensinger 32
Viele kleinere Cluster in Regionen mit sich oft verändernden Plänen
Wenige große Cluster in Regionen mit wenig Änderung
Fehlerreduzierung um ca. 50 Prozent
Variable Clustergrößen
05.07.2005 Elena Hensinger 33
Klassifizierung möglich durch Leader-Algorithmus ABER: Vergleich mit allen Repräsentanten kann zu
lange dauern Schnellere Variante: Entscheidungsbaum Kann nach einer bestimmten Anzahl vorhandener
Cluster erstellt werden
Knoten = Kriterien für
Gruppierung Kanten = Werte der
Kriterien
Klassifizierung zu Clustern
05.07.2005 Elena Hensinger 34
(PLAn Selection Through Incremental Clustering) Tool zur Erweiterung von Optimierern
PLASTIC
Nicht passend
System-kataloge
Datenbank der
Anfragecluster
Datenbankder
Planvorlagen
Optimierer Planvorlagen-Generator
KlassifizierungExtrahierung desMerkmalsvektors
Vergleich Plangenerator
Anfrage
Merkmalsvektor passend Plan
Merkm
alsvektor
05.07.2005 Elena Hensinger 35
Umwandlung einer Anfrage in einen Merkmalsvektor Vergleich mit den vorhandenen Repräsentanten Falls Ähnlichkeit zu einem gegeben (durchgezogene
Linien), dann wird dessen Ausführungsplanvorlage an die Anfrage angepasst und dieser Plan zur Ausführung benutzt
Falls keine Ähnlichkeit (gestrichelte Linien), wird der Merkmalsvektor als der Leader eines neuen Clusters gespeichert, der Ausführungsplan wie gewohnt vom Optimierer generiert, gleichzeitig auch in eine Planvor-lage umgewandelt und gespeichert
Ablauf
05.07.2005 Elena Hensinger 36
Annahme, dass Testsystem stabil und Beschränkung auf Selection-Projection-Join (SPJ) – Anfragen
Tests sowohl zum „Intra-query Plan-Sharing“, also wenn nur die Selektivitäten verändert werden,
als auch zum „Inter-query Plan Sharing“, mit veränderten Projektionen, Selektionsprädikaten, Joinprädikaten und sogar unterschiedlichen Tabellen
Für die Anfrage Q2 aus TPC-H wurden 65 Cluster gebildet
Tests
05.07.2005 Elena Hensinger 37
TestresultateMetrik DB2 P-DB2
Leader Entscheidungsbaum
Exaktheit 100% 90,76% 88,8%
Effizienz 0,1 s 4*10-3 s 2,5*10-4 s
average case worst case
Speicherplatz - 1,97 KB 3,96 KB
P-DB2 ist die um PLASTIC erweiterte DB2 Klassifizierung entweder mit dem Leader-Algorithmus
oder einem Entscheidungsbaum Exaktheit ist die Anzahl der Fälle, in denen derselbe Plan
wie der des Optimierers gewählt wurde Effizienz ist die Zeit zur Klassifizierung und Ausgabe des
Planes
05.07.2005 Elena Hensinger 38
Eindeutiger Gewinn in der Schnelligkeit der Plangene-rierung
Erhöhung der Kosten bei einer Fehlentscheidung ≤ 2%, da in einem solchen Fall meistens der zweitbeste Plan gewählt wird
Möglichkeit für den Optimierer, immer im besten (und rechenaufwändigsten) Modus zu arbeiten, da die Rech-enkosten sich auf alle Anfragen, die von den Clustern profitieren, aufteilen
Testresultate
05.07.2005 Elena Hensinger 39
Wenn ein Cluster im Merkmalsraum mehr als einen Plan im Planraum abdeckt
Wann Fehlentscheidungen?
Cluster
Hängt von der Ausdehnung der Cluster ab = Schwell-wert der Distanzfunktion
Schlechtester Fall: Ansiedlung eines
Repräsentanten am Rande eines Planes
Schwellwert klein mehr Cluster, exaktere Zuordnung Nachteil: Zeit für Klassifizierung stiegt mit Clusteranzahl
05.07.2005 Elena Hensinger 40
Merkmalsvektor geändert, PCsarg und PCnsarg entfernt, dafür andere Merkmale neu hinzugefügt (z.B. Indextyp)
Distanzfunktion verändert, da neue Erkenntnisse, wie sich die Tabellengröße und die effektive Größe auf Planzuordnung auswirken
Einbau mehrerer Module zur Plananalyse, automatische Berechnung der Schwellwerte zur Anpassung der Größe der Cluster, u.a.
Masterarbeiten in 2003 und 2004 Veröffentlichungen u.a. auch auf der VLDB (Very Large
Data Base Conference) im Herbst 2005 Zukunft: Ausweitung auf Anfragen mit Gruppierungen,
Aggregierungen und verschachtelten Anfragen
Weiterentwicklung seit 2002
05.07.2005 Elena Hensinger 41
Screenshots (1)
05.07.2005 Elena Hensinger 42
Screenshots (2)
05.07.2005 Elena Hensinger 43
Vielen Dank für die Aufmerksamkeit!
Fragen?