Autonomic Database Tuning Microsoft SQL Server (2005) Blockseminar Autonomic Database Tuning Johannes Sternatz, Stephan Arenswald Teile Teil I Stephan Überblick über Features und Konzepte SQL Server 2005 Teil II Johannes Detaillierter Blick auf zwei Komponenten Index Selection Behandlung von Statistiken Inhaltsverzeichnis Teil I Einleitung 2. Self-management 1. 1. 2. Database Tuning Advisor 3. 1. 2. 3. 4. 4. Configuring Optimizing Übersicht Kostenmodell Teile detailliert Verbesserte Skalierbarkeit Statistische Ergebnisse 1. Einleitung Einleitung Heutige Anforderungen Integrierte Auswahl von physischen Design-Features Handhabbarkeit Skalierbarkeit Geringer Overhead bei Performance Scriptability und Anpassung Antwort auf diese Forderungen ist der Database Tuning Advisor (DTA) Einleitung “AutoAdmin”-Projekt unter http://research.microsoft.com/dmx/autoadmin/ Zur Zeit steht das physische Datenbankdesign im Mittelpunkt Materialized Views Index-Erstellung Partitionierungsstrategien 2. Self-management Self-configuring Datenbankkonfigurationsparameter online konfigurierbar Automatisches Speichermanagement Self-configuring Automatisches Speichermgmt. Allokieren und Deallokieren von Speicher auf Anfrage 64 GByte Einschränkung auf 32-bit Betriebssystemen Verwendung der AWE (AdressWindowing Extensions) Spezielle System Vorraussetzungen Windows 2003 >= Ent SQL Server 2005 >= Ent 2-3 GByte AWE Window AWE Memory Mapping Self-optimizing Histogramme Automatisch herausfinden, welche Spalte Histogramme benötigt Verwendung von “Selftuning histograms” Self-optimizing Histogramme SQL-query ST-histogram Optimize Execute online offline Workload Log 3. Database Tuning Advisor Allgemein Neu in Version 2005 Vereinigung von Teils bereits vorhanden Features wie Index Tuning Wizard (ITW) Server Version Features Tuned SQL Server 7.0 Nur Indexe SQL Server 2000 Indexe und Indexed Views SQL Server 2005 Indexe, Materialized Views und horizontale Partitionen Übersicht Databases Workload Storage, Time, Managability DB-Admin Tools, Applications XML-Input Column Group Restriction Candidate Selection Merging Enumeration Physical XML-Output Design Recommend ation Microsoft SQL Server 2005 Query Optimizer Kosten-Model Basis ist die “what-if ”-Analyse Simulation von horiz. Partitionierung, Indexen und materialized Views Nimm die Konfiguration mit den geringsten Kosten Vorteile durch die Benutzung des Query-Optimizers: Die simulierte Konfiguration kann einfach auf den Production- Server übertragen werden Bei einer Optimizer-Verbesserung profitiert der DTA direkt davon Nutzung aller möglichen Aspekte bezüglich der Performance DTA Column Group Restriction Reduzierung der Menge der Column Groups (Menge der Spalten, die für den Workload relevant sind) Output ist die Menge der “interressanten” Columns Column Group Restriction Candidate Selection Merging Enumeration DTA Column Group Restriction Reduzierung der Menge der Column Groups (Menge der Spalten, die für den Workload relevant sind) Output ist die Menge der “interressanten” Columns Candidate Selection Suche nach “sehr guten Konfigurationen” Column Group Restriction Candidate Selection Merging Enumeration DTA Merging Bis jetzt: candidate selection nur auf einzelnen Queries Ausgeben einer neuen physischen Designstruktur aus den gegebenen Kandidaten Column Group Restriction Candidate Selection Merging Enumeration DTA Merging Bis jetzt: candidate selection nur auf einzelnen Queries Ausgeben einer neuen physischen Designstruktur aus den gegebenen Kandidaten Column Group Restriction Candidate Selection Enumeration Ausgabe der Endergebnisse (inklusive der Kandidaten vom Merging) Merging Enumeration Verbesserte Skalierbarkeit Tuning in einem Produktions / Test Server Scenario Production Server Test Server Import metadata and statistics Create statistics DTA DTA recommendations Perform Tuning Screenshots Screenshots Screenshots 4. Statistische Ergebnisse Statistische Ergebnisse Qualitiy of Recommendation of DTA compared to ITW Running time of DTA compared to ITW Inhaltsverzeichnis Teil II Index Selection 2. Statistikpflege 1. I. Index Selection Tool - Einführung • Zielsetzung • Finde über die Tabelle T mit s Spalten die Menge zugriffszeitminimierender Indizes I* Problem ist NP-Schwer • Lösungsansätze i. Auf Basis Semantischer Informationen (Uniqueness, Beziehungen, Tabellengröße) „Textbook Solution“ Workload? ii. Hard-coded rules Optimizer? iii. Auf Basis des Optimizers Kostenvorhersage I. Index Selection Tool - Architektur • • Funktionalität • Input: • Process: • Output: Workload = {SQL-Statements} Heuristik auf Basis der Kostenvorhersage des Opt. Menge an ein- und mehrspaltigen Indizes I* Ablaufplan 0. I* = { } 1. Erfasse indexierbare Spaltenkandidaten Heuristik! 2. Enumeriere eine neue Indexmenge I‘ 3. Berechne Kosten für I‘ 4. I* = arg min(Kosten(I*), Kosten(I‘)) 5. Wenn möglich, GoTo 2. 6. Berechne evlt. mehrspaltige Indizes durch Springen zu 1. I. Index Selection Tool - Architektur (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/vldb97.pdf&pub=VLDB ) I. Index Selection Tool – Candidate Index Selection (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/vldb97.pdf&pub=VLDB ) I. Index Selection Tool - Candidate Index Selection • Aufgabe • Reduziere Enumerationsaufwand durch Beschränkung indexierbarer Spalten • Annahmen 1. Ein Spalte ist nur dann indexierwürdig, wenn sie in der WHERE-/GROUP-BY- oder ORDER-BY-Klausel mind. eines SQL-Statements derWorkload vorkommt I* darf nur Indizes beinhalten, welche indexierwürdige Spalten enthalten („Zulässige Indizes“) I. Index Selection Tool - Candidate Index Selection 2. Ein zulässiger Index i1 ist nur dann Indexkandidat der Workload, wenn i1 in mindestens einem SQL-Statement Q derWorkloadW Element der besten Indexmenge I1* ist. (Heuristik) Spalten ⊃ Indexierwürdige Spalten = Zulässige Indizes ⊃ Indexkandidaten ⊃ I* • Was ist die beste Indexmenge I1* eines SQL-Statements Q1? • Rekursiver Aufruf des Index Selection Tools und indexierwürdige Spalten = Indexkandidaten • I1* = Enumeriere(IndexwürdigeSpalten(Q1), Q1) mit W1 = Q1 I. Index Selection Tool - Candidate Index Selection … am Buchhaltungsbeispiel Gegeben seien folgende Tabellen: RW_Beleg (BuchNr, Geschäftsjahr, BuKr, Nutzer, BuchDat) as B ----------------------------------RW_Position (BuchNr, Geschäftsjahr, BuKr, Zeile, SollHaben, Konto, Betrag) Gegeben sei folgende Select-Workload: Q1 = SF P WHERE Konto = ‚Debitor0815‘ Q2 = SF B WHERE BuKr= ‚IDES1000‘ & Nutzer = ‚HassoP‘ & BuchDat = 6.9.06 Q3 = SF B WHERE BuchDat = 9.6.96 Q4 = SF B WHERE BuKr = ‚IDES1000‘ Q5 = SF P,B WHERE B.BuchNr = P.BuchNr & B.Geschäftsjahr = P.Geschäftsjahr & P.BuKr = B.BuKr =‚IDES1000‘ & Konto = ‚Kreditor4711‘ as P I. Index Selection Tool - Candidate Index Selection … am Buchhaltungsbeispiel Query Indexierwürdig Best (Ann.) Q1 P.Konto P.Konto Q2 B.BuKr, B.BuchDat, B.Nutzer B.Nutzer Q3 B.BuchDat B.BuchDat Q4 B.BuKr B.BuKr Q5 B.BuchNr, P.BuchNr, B.Geschäftsjahr, P.Geschäftsjahr, B.BuKr, P.BuKr, P.Konto B.BuchNr, P.BuchNr, B.Geschäftsjahr, P.Geschäftsjahr Indexkandidaten I. Index Selection Tool – Configuration Enumeration (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/vldb97.pdf&pub=VLDB ) I. Index Selection Tool - Configuration Enumeration • Aufgabe • Berechne bei gegebenen Indexkandidaten die Menge der zugriffszeitminimierenden Indizes I* (= optimale Konfiguration) • Trotz Beschränkung auf Indexkandidaten ist eine reine naive Enumeration langwierig aber lösungsverbessernd (Trade-Off!) Lösung: Teilheuristik mit m naiv enumerierten Indizes I. Index Selection Tool - Configuration Enumeration • • k sei die maximale Kardinalität von I* (Benutzereingabe) Workload W, Indexkandidaten Kand • Ablaufplan Greedy(m,k)-Enumerations-Algorithmus 1. I* = NaiveEnumeration(W,Kand,k) wenn k=m dann exit 2. Wähle neuen Index i* aus Kand mit: Kosten(I*+{i*},W) < Kosten(I*+{i},W) für jedes i <> i* 3. Wenn Kosten(I*+{i*},W) < Kosten(I*,W) Dann I* = I* + {i*} Sonst Exit 4. Wenn #I* = k Dann Exit Sonst GoTo 2 seed hill climbing I. Index Selection Tool – Multi-Column Index Generation (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/vldb97.pdf&pub=VLDB ) I. Index Selection Tool - Multi-Column Index Generation • Index Selection wird wiederholt • pro Wiederholungsstufe steigt die Dimension der berechneten Indizes, welche in I* aufgenommen werden • Annahmen für den 2-dimensionalen, optimalen Index (a,b)*: • MC-LEAD: • a ist ein ein-dimensionaler, optimaler Index, welcher in der vorherigen Iteration enumeriert wurde • b muss nur indexierwürdig sein • MC-ALL: • a und b sind jeweils ein-dimensionale, optimale Indizes, welche in der vorherigen Iteration enumeriert wurden I. Index Selection Tool – Cost Evaluation (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/vldb97.pdf&pub=VLDB ) I. Index Selection Tool – Cost Evaluation • Definition: Configuration C = Menge an Indizes • • Aufgabe: Problem: Berechne Kosten(C,W) mit Hilfe des Optimizers (i.V.m. What-If) Anzahl der Optimizeraufrufe = #W*#MöglicheConfigurations • Lösung: divide et impera • C ist atomar für W, wenn es ein Query Q in W gibt, für den die Query Engine alle Indizes aus C zur Ausführung von Q benutzt I. Index Selection Tool – Cost Evaluation • Divide et impera • Ist C für Q nicht atomar, so ist mindestens ein Index aus C überflüssig • Für Select- und Update-Queries • Aus Kosten(C,Q) = Kosten(C‘,Q) (C‘ ist atomar für Q) folgt Kosten(C,Q) = min(Kosten(C‘‘,Q)| ∀ C‘‘ ⊂ C) = T • Für Insert- und Delete-Queries • Kosten(C,Q) = SelektKosten(C,Q) + IndexUpdateKosten(C,Q) = T + ∑i in C [Kosten({i},Q) – Kosten({},Q)] Für Query Q nicht atomare Konfigurationen brauchen nicht vom Optimizer berechnet werden! I. Index Selection Tool – Cost Evaluation Es müssen nur atomare Konfigurationen vom Optimizer evaluiert werden! Ergebnisse werden im Sinne dynamischer Programmierung in einer Cost-Evaluation-Table verwaltet Gretchenfrage: Wie erkennt das System für Q atomare Konfigurationen? • Annahme • Query Engine nutzt maximal j Indizes pro Tabelle • Query Engine nutzt Indizes von maximal t Tabellen pro Join • Heuristik • Q ist für C dann atomar, wenn C maximal j WHERE-Columns pro Tabelle und maximal t tabellendifferente WHERE-Columns als Indizes enthält • Bewährt: j=t=2 I. Index Selection Tool – Cost Evaluation … am Buchhaltungsbeispiel Sei C Sei Q = {B.BuKr, B.BuchNr, P.BuchNr} = SF RW_Beleg as B WHERE BuKr = ‚IDES1000‘ Kosten(C,Q) = Kosten({B.Bukr},Q) Sei C = {K.Typ, K.Konto, P.Konto} Sei Q= SF Kontenplan as K , RW_Position as P, WHERE K.Typ = ‚Erfolgskonto‘ AND K.Konto = P.Konto t=1, j=2 Kosten(C,Q) = min(Kosten({K.Konto,K.Typ},Q) ; Kosten({P.Konto},Q)) I. Index Selection Tool – „What-if“ Index Creation (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/vldb97.pdf&pub=VLDB ) I. Index Selection Tool – „What-if“ Index Creation • Aufgabe: Überprüfung des Einflusses hypothetischer Konfigurationen (HC) • Zwei Alternativen 1. Hypothetische Konfiguration wird umgesetzt („CREATE INDEX …“) Neue Katalogeinträge Keine Abbildung alternativer DB-Zustände Teuer und Behinderung des operativen Betriebs 2. Hypothetische Konfiguration wird simuliert (durch HCA-Engine) Keine Behinderung, alternative DB-Zustände abbildbar Kein Katalogeintrag Optimizer anpassen I. Index Selection Tool – „What-if“ Index Creation (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/autoadmin_conf_version.pdf&pub=ACM) I. Index Selection Tool – „What-if“ Index Creation • HC-Simulation-Interfaces • • • DEFINE WORKLOAD (aus Workload-File) DEFINE CONFIGURATION SET DATABASE SIZE (mit scaling value mi für Tabelle Ti) • CREATE INDEX … WITH STATISTICS_ONLY = Simuliere hypothetischen Index auf Basis von zu erstellenden Histogrammen • Zur Reduktion der Berechnungskosten 5%-Sampling I. Index Selection Tool – „What-if“ Index Creation • ESTIMATE CONFIGURATION OF <workload> FOR <configuration> • Da keine Katalogeinträge direkte Verbindung zum Optimizer nötig HC_mode_call (HC, base_index, scaling_values) ruft Optimizer im NoExecution-Modus auf I. Index Selection Tool – Fazit … am Buchhaltungsbeispiel • • #RW_Beleg = 150.000, #RW_Position = 600.000 Indizes auf Primärschlüssel vorhanden • Vorgeschlagene Konfiguration I* = {B.BuchDat; (P.Konto, P.Geschäftsjahr, P.BuchNr)} • Erwartete Verbesserung: 96% I. Index Selection Tool – Fazit Vergleich zu einem non-iterativen Baseline-Algorithmus ohne Dynamischem Programmieren und Candidate Selection (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/vldb97.pdf&pub=VLDB ) II. Automated Statistic Management • Optimizer benötigt zur Kostenberechnung eines theoretischen Ausführungs-planes die zu erwartenden Kardinalitäten aller (Zwischen-)Tabellen • Statistiken (vs. Gleichverteilungsannahme) erleichtern die Schätzung eben jener Kardinalitäten, kosten aber auch Erstellungs- und Updateaufwand Tradeoff: Statistiken müssen effizient (billig), aktuell und nützlich sein • Statistiken zumeist in Form von Histogrammen (Häufigkeitsverteilungen) oder in Dichte-/Selektivitätsäquivalenten II. Automated Statistic Management - SITs • Kostenvorhersage beruht bisher auf Statistiken von Basistabellen: Bsp.: result = SELECT * FROM R WHERE R.a > 10. #result = #R * nR.a>10 (Anteile n aus Basisstatistik über R.a) II. Automated Statistic Management - SITs Was macht der Optimizer bei tabellenübergreifenden Queries? Bsp.: result = SF R, S WHERE R.x = S.y AND S.a < 10 1) Naive Lösung: „Same procedure as every time!“ #result = #R_JOIN_S * nS.a<10 (#R_JOIN_S durch Histogrammvgl. R.x vs. S.y, Anteil n aus Statistik über S.a) Ist S.a in R_Join_S gleich verteilt wie in S ??? Annahme der Unabhängigkeit aller WHERE-Bedingungen II. Automated Statistic Management - SITs 2) Nutzung von Statistiken auf Zwischentabellen (SITs) (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/Sig02-SITS.pdf&pub=ACM) II. Automated Statistic Management - SITs • Welche SITs sollen erstellt werden? • Enumeratorische Explosion: Alle möglichen SITs • Workloadabhängig: Alle nützlichen SITs • • • SITs nur über filternde/joinende Attribute nötig SITs nur dann nötig, wenn Unabhängigkeitsannahme stark verletzt wird Heuristik: SIT berechnen, wenn #Best-Case-Szenario << #Worst-Case-Szenarios II. Automated Statistic Management - SITs … ein Beispiel S.y Query: SF R, S WHERE R.x = S.y AND S.a < 10 Ann.: Sel(S.a<10) = 50% 0 5 10 15 20 15 20 R.x 0 5 10 i. Gleichverteilungsannahme II. Automated Statistic Management - SITs … ein Beispiel S.y Query: SF R, S WHERE R.x = S.y AND S.a < 10 Ann.: Sel(S.a<10) = 50% 0 5 10 15 20 15 20 ii. Best case R.x 0 5 10 (qualitative Zeichnung) II. Automated Statistic Management - SITs … ein Beispiel S.y Query: SF R, S WHERE R.x = S.y AND S.a < 10 Ann.: Sel(S.a<10) = 50% 0 5 10 15 20 15 20 iii. Worst case R.x 0 5 10 (qualitative Zeichnung) II. Automated Statistic Management - SITs • Im Operativbetrieb muss Optimizer Query-Pläne unter Nutzung von SITs „geschickt“ enumerieren/umschreiben • Experimentelle Studie (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/Sig02-SITS.pdf&pub=ACM) Fazit Auch Microsoft beschäftigt sich mit Autonomic Database Tuning Vorzeigetool ist der DTA Vorwiegend Beschränkung auf Self Optim. (Statistikpflege, ...) und Self Conf. (DTA, Dynamic Memory Mgmt ...) In Forschung Beschränkung auf physisches Datenbankdesign Benchmark-Ergebnisse zeigen, das es sich lohnt weiter zu forschen Quellen Today‘s DBMSs: How autonomic are they? Elnaffar, Powley, Benoit, Martin; ww.cs.queensu.ca/home/cords/publications/autodbms.pdf http://research.microsoft.com/dmx/autoadmin/ Database Tuning Advisor for Microsoft SQL Server 2005 Agrawal, Chaudhuri et. al. An Efficient, Cost-Driven Index Selection Tool for Microsoft SQL Server 2005 Chaudhuri, Narasayya Self-tuning Histograms: Building Histograms Without Looking at Data Aboulnaga, Chaudhuri AutoAdmin „What-if“ Index Analysis Utility Chaudhuri, Narasayya Vielen Dank für die Aufmerksamkeit ! Fragen?! Back Up II. Automated Statistic Management – ST-Histograms • Problem: „Normale“ Statistiken müssen zum Aufbauzeitpunkt (ad-hoc vs. optimizerinduziert) alle Daten „anfassen“: IO: O(n), Sort: O(n*logn) • Idee: Query-Execution-Engine (QEE) kennt zu jedem Query die Anzahl betroffener Tupel Bau von inkrementellen Histogrammen, welche m.H. des QEE-Feedbacks verfeinert werden Self Tuning Histograms (ST-Histograms) • Vorteil: Einsparnis des Aufbauaufwandes (zu Lasten der Query-Ausführung!) Automatische Verfeinerung II. Automated Statistic Management – ST-Histograms (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/sig99_st.pdf&pub=ACM) II. Automated Statistic Management – ST-Histograms • Update-Modus: • Offline, m.H. eines Logs der QEE • Online, d.h. on-the-fly-Anpassung • Wie baut das System ST-Histograms? dreiphasiges Vorgehen 1. Initialisierung zum Erstellungszeitpunkt • Input: Tabellengröße Anzahl der Bins Grenzen d. Histogramms • Process: aus Systemkatalog aus Schätzung aus Schätzung Annahme der Gleichverteilung zwischen den Grenzen II. Automated Statistic Management – ST-Histograms 2. Verfeinerung bei Queryausführung (Online) • Input: Anzahl der Tupel Bereichsgrenzen Betroffene Bins • Process • • • • von QEE aus Where-Bedingung aus ST-Histogram Verfeinerung nur, wenn ST-Histogram und QEE-Feedback nicht matchen (hoher Schätzfehler) Es werden nur betroffene Bins aktualisiert Bin-Struktur bleibt erhalten Bin-Höhe verändert sich proportional zum Schätzfehler und zur bisherigen Bin-Höhe ( Score) II. Automated Statistic Management – ST-Histograms … ein Beispiel ST-Histogramm nachher ST-Histogramm vorher 80 60 70 50 60 40 50 40 30 30 20 20 10 10 0 0 bis 20 bis 50 bis 70 bis 100 bis 20 R.a • SF R WHERE R.a BETWEEN [20;70] liefere 105 Tupel ST-Histogramm muss um 30 Tupel in [20;70] ergänzt werden Bin[bis50]:Bin[bis70] = 2:1 = Scoreverteilungsschlüssel bis 50 bis 70 R.a bis 100 II. Automated Statistic Management – ST-Histograms 3. Restrukturierung bei Queryausführung (Online) • Process • • Verschmelze benachbarte Bins mit nahezu gleicher Binhöhe Trenne Bins mit (vermutlich) großen Binhöhenunter-schieden II. Automated Statistic Management – ST-Histograms (Quelle: http://research.microsoft.com/copyright/accept.asp?path=ftp://ftp.research.microsoft.com/users/AutoAdmin/sig99_st.pdf&pub=ACM) II. Automated Statistic Management – ST-Histograms • Würdigung: • Geeignet v.a. bei symmetrischen (nicht-schiefen) Verteilungen • Ersparnis von Initialisierungsaufwand zu Lasten der Queryaus-führung Gut für DW • ST-Histograms besser als Gleichverteilungsannahme • Automatische Genauigkeitsanpassung an Bedeutsamkeit