Self Tuning in DBMS - Techniken und aktuelle

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....


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.


benötigt.


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


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


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 27

SQL Server: DTA

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 29

DB2: Configuration Advisor

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


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


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


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


benötigt.


benötigt.

Documents

Self Tuning in DBMS - Techniken und aktuelle