View
1
Download
0
Category
Preview:
Citation preview
Self Tuning in DBMS -Techniken und aktuelle Systemunterstützung
Prof. Dr.-Ing. habil. Kai-Uwe Sattler
TU IlmenauFG Datenbanken & Informationssystemewww.tu-ilmenau.de/dbis
K. Sattler 1
Überblick
MotivationAdministration & TuningKostenperspektiveVision
GrundlagenState of the ArtAusblick & Zusammenfassung
K. Sattler 2
Aufgaben eines Administrators
SpeicherverwaltungFestplatten,
Verteilung der Daten, ...
SpeicherverwaltungFestplatten,
Verteilung der Daten, ...
Verwaltung der System-ressourcen
CPU, Speicher, Prozesse
Verwaltung der System-ressourcen
CPU, Speicher, Prozesse
Backup & RecoveryArchivierung auf Bändern/CD
Wiederherstellung
Backup & RecoveryArchivierung auf Bändern/CD
Wiederherstellung
Anwendung & AnfragenDB-Schema, Anfragetuning,
Antwortzeit, Durchsatz
Anwendung & AnfragenDB-Schema, Anfragetuning,
Antwortzeit, Durchsatz
K. Sattler 3
Tuning-Aufgaben
PhysischeEbene
Indexauswahl,Partitionierung, Clustering,Hardware
SystemebeneStatistiken: Auswahl, AktualisierungMemory: Gesamtgröße, AufteilungBufferpool: Größe, StrategieLocking
Externe(Anwendungs-)
Ebene
Anfragetuning (Hints, Rewriting)Anwendungs- bzw. C/S-SchnittstelleTransaktions-Chopping
KonzeptuelleEbene
Schema, Denormalisierung,Materialisierte Sichten
K. Sattler 4
DBS - die Kostenperspektive
16%
28%
37%
3%4%
8% 4% Schulung
Administration
Implementierung
Client-Zugriff
Entwicklungswerkzeuge
Softwarelizenzen
Upgrades
81% Personalkosten81% Personalkosten
[The Aberdeen Group, 1998]
K. Sattler 5
DB-Administratoren ...
sind teuer...durchschnittlich 80.000 $ in den USASpitze bis 124.000 $www.salary.com
sind gesucht...14 Angebote in den letzten 14 Tagenwww.monster.de
verbringen...55% ihrer Zeit damit, das System am Laufen zu halten[IOUG 2001 DBA Survey]
K. Sattler 6
Aber ohne Administrator??Juni 1999:
22h Ausfall durch Betriebssystemfehler 3-5 Mill. $ Umsatzverlust und 26% Kursverlust
Juni 1999: 22h Ausfall durch Betriebssystemfehler 3-5 Mill. $ Umsatzverlust und 26% Kursverlust
April 1998:26h Ausfall wegen Systemupgrade40 Mill $ Schaden
April 1998:26h Ausfall wegen Systemupgrade40 Mill $ Schaden
August 1996: 24h Ausfall durch menschliches Versagen3 Mill $ Schaden wegen Rückzahlungen
August 1996: 24h Ausfall durch menschliches Versagen3 Mill $ Schaden wegen Rückzahlungen
K. Sattler 7
Die Vision: Selbstverwaltende Systeme
Zur Anzeige wird der QuickTime™ Dekompressor „TIFF (Unkomprimiert)“
benötigt.
Statt...
besser so....
Zur Anzeige wird der QuickTime™ Dekompressor „TIFF (Unkomprimiert)“
benötigt.
oder so!
K. Sattler 8
Überblick
MotivationGrundlagen
Autonomic Computing: EinordnungGrundprinzip von Self-TechnikenSpektrumParadigmen
State of the ArtAusblick und Zusammenfassung
K. Sattler 9
Aspekte der Selbstverwaltung
IBM-Vision des Autonomic Computing
Zur Anzeige wird der QuickTime™ Dekompressor „TIFF (LZW)“
benötigt.
Zur Anzeige wird der QuickTime™ Dekompressor „TIFF (LZW)“
benötigt.
Zur Anzeige wird der QuickTime™ Dekompressor „TIFF (LZW)“
benötigt.Zur Anzeige wird der QuickTime™
Dekompressor „TIFF (LZW)“ benötigt.
Erkennung und Behandlung
von Ausfällen
Erkennung und Behandlung
von Ausfällen
Erkennung und Abwehr von Angriffen
(Einbrüche, Viren, ...)
Erkennung und Abwehr von Angriffen
(Einbrüche, Viren, ...)Optimierung der
Ressourcennutzung(CPU, Speicher)
Optimierung der Ressourcennutzung
(CPU, Speicher)
Automat. Anpassungan veränderte Umgebung
(Hard-, Software)
Automat. Anpassungan veränderte Umgebung
(Hard-, Software)
K. Sattler 10
Spektrum des Self Tuning
Integrationin DBMS
Zeitfenster füEntscheidungEchtzeit
lbst-tunendeAlgorithmen
System-getriggertes
Management
Externe Feedback-Schleife
Seiten-ersetzung,Prefetching
Seiten-ersetzung,Prefetching
MemoryManagementMemory
ManagementVerwaltungvon
Statistiken
Verwaltungvon
StatistikenPhysischerEntwurf
PhysischerEntwurf
Konfiguration,Installation
Konfiguration,Installation
LängerfristigRegelmäßig,bedarfsgesteuert
[Chaudhuri, Weikum]
K. Sattler 11
MAPE-Zyklus
Monitor
Analyze Plan
Execute
ElementElement
EffektorEffektor
Knowledge
SensorSensor
Regelkreis zur Überwachung und kontinuierlichen Anpassung an UmgebungLernen aus der VergangenheitWissen über sich selbst und die Umgebung
K. Sattler 12
Paradigmen: Überblick
Parametrisierung / Tradeoff-EliminierungSeitengröße, Bufferpool-Größe
Entwurfsunterstützung / Statische OptimierungKonfiguration, Physischer Datenbankentwurf
Online-OptimierungAuswahl und Pflege von Statistiken, Memory Management
K. Sattler 13
Parametrisierung /Tradeoff-Eliminierung
Aufgabe: Bestimmung von ParameterwertenProbleme:
Viele Parameter (z.B. DB2 V.8 ca. 150 Parameter)Welcher Parameter? Abhängigkeiten?Änderung ist aufwändig (stop+restart) Häufig Kompromiss
Beispiele:Seitengröße
IO-Effizienz vs. SpeichernutzungParameter für Bufferpool (Größe, Strategie)
LFU: besser bei statischen ZugriffsmusternLRU: besser, wenn sich aktuelles Zugriffsmuster zukünftig fortsetzt; jedoch Probleme bei sequenziellem Scan
K. Sattler 14
Beispiel: Adaptive Algorithmen
Bufferpool: Seitenersetzungsstrategien ARC, 2QAusgleich zwischen Aktualität und Häufigkeit der Zugriffe; Scan-ResistenzPrinzip: 2 LRU-Listen L1 und L2, Buffergröße c
L1: kürzlich einmal genutzte SeitenL2: kürzlich mind. zweimal genutzte Seiten|L1| ≤ p < c und |L1|+|L2| ≤ 2c
Ziel: dynamische Anpassung der Buffer-Partitionierung über pBei Seitenzugriff auf x
Wenn x ∈ L1 ∪ L2, dann zur MRU-Position von L2, sonst zu MRU von L1
p++ bei L1-Hit, p-- bei L2-Hit
[IEEE Computer 4/2004]
K. Sattler 15
Statische Optimierung / Entwurfsunterstützung
Physischer Datenbankentwurf: Indexe, mat. Sichten, Partitionierung, MDC-Tabellen, ...
SELECT l_orderkey,sum(l_extendedprice * (1 - l_discount)) AS revenue,o_orderdate, o_shippriority
FROM customer, orders, lineitemWHERE c_mktsegment = 'MACHINERY'
AND c_custkey = o_custkeyAND l_orderkey = o_orderkeyAND o_orderdate < DATE '1995-03-26'AND l_shipdate > DATE '1995-03-26'
GROUP BY l_orderkey, o_orderdate,o_shippriority
ORDER BY revenue DESC, o_orderdate;
Materialisierte Sicht?
Index?
K. Sattler 16
Statische Optimierung / Entwurfsunterstützung
Problem: pro Tabelle mit n Spalten
verschiedene Indexe!1.000 Tabellen, je 20 Spalten = ????außerdem: Kosten-Nutzen-Verhältnis
Speicherplatz, Aktualisierungsaufwand
K. Sattler 17
Statische Optimierung / Entwurfsunterstützung
Monitor & Analyze:(repräsentative) Anfragen oder Objekte (Indexe, MVs)?Über welchen Zeitraum?Bestimmung des potentiellen Gewinns?
Plan:Auswahl der besten Objekte unter ConstraintsEinmalig oder kontinuierlich?
WorkloadWorkload
Schema, Statistiken,Kosten
AnalyzeAnalyze ExecuteExecuteMonitorMonitor
PlanPlan
K. Sattler 18
Monitoring
Workload-RepräsentationSammeln von repräsentativen Anfragen, z.B. Top-SQL in OracleWorkload-Kompression: Random Sampling, k-Median-Clustering
IndexempfehlungenErweiterung des Optimierers um Berücksichtigung virtueller bzw. hypothetischer IndexeIdentifikation potenzieller Indexkandidaten durch Analyse der Anfrage (Attribute in SELECT, WHERE, ORDER BY, GROUP BY)Im Plan verwendete virtuelle Indexe = Indexempfehlung
K. Sattler 19
Plan: Auswahl von Indexen
Greedy-Verfahren, Dynamic Programming
Rucksack-ProblemRucksack-Problem
Menge von Anfragen Q1, ..., QmIndexkandidaten I1, ..., In mit Größe und VerwaltungskostenProfit von Ii bzgl. QkAufgabe: Finde Indexkonfiguration C ⊆ { I1, ..., In }
die Größenbeschränkung einhält undGesamtprofit (unter Berücksichtigung der Verwaltungskosten) maximiert
K. Sattler 20
Online-Optimierung
Memory TuningBestimmung der Größe und Aufteilung des DBMS-HauptspeichersProblem:
Verschiedene dedizierte Bereiche: Bufferpool, Sortierbereich, Compiled SQL Cache, Procedure Cache, ...Unterschiedlichen Nutzen, verschiedene MetrikenAbhängig vom Workload
Verwaltung von Statistikenzur Kostenabschätzung von Plänen: Kardinalitäten, häufige Werte, Werteverteilung, ...Typischerweise keine inkrementelle Aktualisierung
Fehlende, veraltete Statistiken → fehlerhafte Kostenberechnung →ungünstige Planauswahl
K. Sattler 21
Memory Tuning
Kosten-Nutzen-Metrik für SpeicherkonsumentenTrefferrate von Bufferpool vs. SQL Cache
Kontinuierliche Überwachung (Aktivitätsfenster)Lang laufende OLAP-Anfragen vs. kurze Transaktionen
Feedback-SchleifeDynamische Aufteilung eines vorgegebenen Speicherlimits (global bzw. für einzelne Operatoren - PGA in Oracle)MIMO-Regelkreis (DB2)
Bestimmung der SpeicherobergrenzeWeiterhin durch DBA (Unterstützung durch Configuration Advisor)Automatisch in Abhängigkeit vom verfügbaren Speicher
K. Sattler 22
Automatische Verwaltung von Statistiken
Überwachung von ÄnderungenAnzahl der Änderungen auf der Tabelle
≥10% geänderte Tupel → AktualisierungSignifikante Änderung der Werte
Vergleich von Stichproben der Verteilung: bei größeren Differenzen → Aktualisierung
Query FeedbackMonitoring der Anfrageausführung
Vergleich der tatsächlichen mit der erwarteten KardinalitätWenn Fehler zu groß → Aktualisierung
K. Sattler 23
Überblick
MotivationGrundlagenState of the Art
ÜberblickSQL Server 2005DB2 V.9Oracle10g
Ausblick und Zusammenfassung
K. Sattler 24
Klassifikation der SystemunterstüzungSQL Server 2005 DB2 V. 9.1 Oracle10g
Konfiguration Configuration Manager, Management Studio
Configuration Advisor Database ConfigurationAssistant
Physischer Entwurf Database Tuning Advisor (Indexe, Indexed Views, Clustered Index, Partitionen), Missing Indexes
Design Advisor (Indexe, MVs, Partitionen)
SQL Tuning + Access Advisor (Indexe etc. + Anfragemodifikationen)
Memory Tuning Dyn. Verteilung zwischen Min/Max, Wartemodell für Operatoren
STTM (MIMO-Regelkreis für Aufteilung und Bestimmung der Gesamtgröße)
ASMM (Aufteilung innerhalb eines vorgeg. Limits)
Integr. Performance Mgmt.
Dynamic Management Views Health Center AWR+ADDM
Autom. Statistiken Autom. Aktualisierung durch Überwachung der Änderungen (asynchron)
ASC: Update- und Feedback-getriebene Überwachung und Aktualisierung
Automatic Tuning Optimizer: Erkennung fehlender oder falscher Statistiken
K. Sattler 25
SQL Server: Database Tuning AdvisorWorkload,
ConstraintsWorkload,
Constraints
EmpfehlungEmpfehlung
Pruning,Workload-Kompression
Pruning,Workload-Kompression
Kandidaten-Auswahl
Kandidaten-Auswahl
EnumerationEnumeration
Optimierer
SQL Server
„What if“API
[VLDB 00, SIGMOD 04, VLDB 04]
K. Sattler 26
SQL Server: DTA
„What If“-Indexe / Indexed ViewsVirtuelle / hypothetische Objekte nur für OptimiererKorrespondierende Statistiken durch Abschätzung und Stichproben
Merging von Kandidaten zur ReduktionVon Indexen (gem. Präfix, Spaltenvereinigung) und materialisierten Sichten
Nutzung des Optimierer-Kostenmodells für Abschätzung des Gewinns einer KonfigurationOptimierung / Suche innerhalb vorgegebener Zeitgrenzen
K. Sattler 28
DB2: Configuration Advisor
Initialisierung verschiedener Performance-relevanter Parameter beim Anlegen einer DB
Memory-Größen, Verbindungen, Logging/Recovery, Parallelität, etc. (~ 36 Parameter in V. 8.1)
Einbeziehung vonNutzereingabenautomatisch ermittelten SystemparameternHeuristiken zur DB-Konfiguration („Expertenwissen“)
Berechnungsvorschriften für ParameterwerteUnabhängige ModellparameterAbhängigkeiten zwischen Modellparametern„Null-Summen“-Beziehungen
K. Sattler 30
DB2: LEarning Optimizer
Nutzung von Ausführungsergebnissen zur
Valdidierung von StatistikenErkennung notwendiger AktualisierungenSammlung und Pflege von Statistiken
[VLDB 2001]
OptimiererOptimierer
PlanPlan
AusführungAusführung
ErwarteteKardinalitätErwartete
Kardinalität
RealeKardinalität
RealeKardinalität
AnpassungAnpassung
Statistiken
K. Sattler 31
DB2: LEO
LEO-Analysekomponente realisiert als HintergrundprozessDurch Vergleich der Kardinalitäten → Anpassungsfaktor für SelektivitätsabschätzungFür Prädikat A > k
Wenn
Dann
Verwaltung der Anpassungsfaktoren im SystemkatalogBrücksichtigung der Anpassungsfaktoren bei späteren Selektivitätsabschätzungen
K. Sattler 32
Oracle10g: AWR+ADDM
Integrierter Ansatz:Workload Repository (AWR) + Self-Diagnostic Engine (ADDM)
Umfasst:Zeitmodell, DB-Aktivitäten, Top SQL, Top Objekte, ...
Genutzt von:DB-Advisor(historische) Performance-Analyse
In Memory Statistics:Zeitmodell v$sys_time_model, SQL Statistiken v$sql, Betriebssystem v$osstat, ...Detailwerte und kumulierte Werte (v$...metric)
Snapshots:Stündliches Erstellen von Snapshots; 7 Tage Aufbewahrung
K. Sattler 33
Oracle10g: AWR
In MemoryStatistics
SGA
mmonmmonAWR
SnapshotsAWR
Snapshots
v$v$ dba$dba$
Self TuningComponent
Self TuningComponentADDMADDM ...
K. Sattler 34
Oracle10g: ADDM
Proaktive Diagnose unter Verwendung der AWR-SnapshotsAutomatischer Lauf nach Snapshot-ErstellungAnalyse zeitintensiver DB-Tasks (DB-Zeitmodell)
Symptom (z.B. hohe CPU-Last) → Ursache (z.B. fehlender Index)Entscheidungsbaum mit hinterlegtem Expertenwissen (u.a. aus Statspack-Analysen)
...
CPU-KapazitätCPU-Kapazität
I/O-KapazitätI/O-Kapazität
DB-LocksDB-Locks
Buffer zu kleinBuffer zu klein
I/O-Bandbreite zu geringI/O-Bandbreite zu gering
...
SymptomeUrsachen
[CIDR 05]
K. Sattler 35
Oracle10g: ADDM
Zur Anzeige wird der QuickTime™ Dekompressor „TIFF (Unkomprimiert)“
benötigt.
K. Sattler 36
Überblick
MotivationGrundlagenState of the ArtAusblick
Autonomes Tuning & Soft Index ManagementZusammenfassungLiteratur
K. Sattler 37
Soft Index Management
Änderung des WorkloadsÄnderungen am Schema, am Datenvolumen, der Datencharakteristika, ...Interferenzen mit anderen Tuning-Maßnahmen...
WorkloadWorkloadOptimiert für
∆ ∆
K. Sattler 38
Soft Index Management
Warum überhaupt: CREATE INDEX & friends?Soft Index: autonom vom DBMS verwalteter Index
wird bei Bedarf materialisiert / dematerialisiert
AnfrageAnfrage PlannerPlanner
Soft Index MgrSoft Index Mgr
Index AdvisorIndex Advisor
ExecutorExecutor
IndexBuildScan, DROPIndexBuildScan, DROP
Index-EmpfehlungenIndex-EmpfehlungenIndex-Pool
Soft-Index-Konfiguration
K. Sattler 39
Soft Index Management
Implementierung der Soft Indexe in PostgreSQLIndex AdvisorMonitoring und Verwaltung der Soft IndexeOn-the-Fly-Erzeugung durch IndexBuildScans im Rahmen vonAnfragenDirekte Nutzung der erzeugten Indexe innerhalb der Anfrage
NLJoinNLJoin
TableScan(r)TableScan(r)TableScan(s)TableScan(s)n=|r|
n
NLJoinNLJoin
TableScan(r)TableScan(r) SwitchPlanSwitchPlan
IndexScan(i,s)IndexScan(i,s)i := IndexBuildScan(s)i := IndexBuildScan(s)
1 n-1
K. Sattler 40
Zusammenfassung & Ausblick
Moderne Informationssysteme mithohen Anforderungen (Performanz, Verfügbarkeit, ...)hoher Komplexität
Self-*-Features als wichtiges MerkmalAktuelle Arbeiten in Industrie und Forschung mit Fokus auf Tuning, Konfiguration
Zunächst Tool-Lösungen für Einzelprobleme (physischerEntwurf)Aktuell zunehmend integrierte Ansätze (SQLCM, AWR+ADDM)
Noch viel zu tun...besseres Verständnis der ZusammenhängeTuning ist nur ein AspektAkzeptanz
K. Sattler 41
Literatur (Auszug)
S. Chaudhuri, G. Weikum: Foundations of Automated Database Tuning (Tutorial), SIGMOD 2005.S. Chaudhuri, B. Dageville, G. Lohmann: Self-Managing Technology in Database Systems (Tutorial), VLDB 2004.Special Issues on Self-Managing Database Systems, Bulletin of the TC on Data Engineering, IEEE Computer Society, September 2006.Special Issue Autonomic Computing, IBM Systems Journal, Vol. 41, No. 1, 2003.
K. Sattler 42
Mehr Details?
Datenbank-Tutorientage @BTW 2007
Kai-Uwe Sattler, Eike Schallehn:Self-*-Techniken in DBMS: Grundlagen, Techniken, Systemüberblick
Wo? Wann? Aachen, 6. März 2007 09:00-12:30 Uhr
Zur Anzeige wird der QuickTime™ Dekompressor „TIFF (Unkomprimiert)“
benötigt.
Zur Anzeige wird der QuickTime™ Dekompressor „TIFF (Unkomprimiert)“
benötigt.
Recommended