1 / 79

Datenbankauswertungen in großen Datenmengen - Spaltenorientierte Datenbank

Datenbankauswertungen in großen Datenmengen - Spaltenorientierte Datenbank. Einführung: Probleme und Herangehensweise Sybase Adaptive Server IQ und IQM Prinzip-Überblick Speicherungsstruktur und Indextypen IQ Multiplex Beispiele, Ergebnisse Jürgen Bittner.

albert
Download Presentation

Datenbankauswertungen in großen Datenmengen - Spaltenorientierte Datenbank

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Datenbankauswertungen in großen Datenmengen- Spaltenorientierte Datenbank • Einführung: Probleme und Herangehensweise • Sybase Adaptive Server IQ und IQM • Prinzip-Überblick • Speicherungsstruktur und Indextypen • IQ Multiplex • Beispiele, Ergebnisse Jürgen Bittner

  2. Das „gewöhnliche“ Performance-Problem Ein Select braucht zu viel Zeit,... was tun ? • Schnellere Hardware ? • Überprüfen des Kommandos • Prüfen des Datenbank-Servers • Prüfen der Datenbank

  3. Ein Select braucht zu viel Zeit,... was tun ? • Überprüfen des Kommandos • Liegt eine ungünstige (evt. vermeidbare) Formulierung vor ? • Besonderheiten der Hersteller sind zu beachten

  4. Anfragebeispiel Wieviele Gastronomie-Einrichtungen in Sachsen haben kein „Radeberger“ ? SELECT COUNT (DISTINCT Einr) FROM Absatz WHERE Land = ‘SA‘ AND Typ = ‘G‘ AND Einr = IS NOT IN (SELECT DISTINCT Einr FROM Absatz WHERE Land = ‘SA‘ AND Typ = ‘G‘ AND Prod = ‘Radeb‘) SELECT COUNT (DISTINCT Einr) - AnzRadeb FROM Absatz, (SELECT COUNT(DISTINCT Einr) AS AnzRadeb FROM Absatz WHERE Land = ‘SA‘ AND Typ = ‘G‘ AND Prod = ‘Radeb‘) WHERE Land = ‘SA‘ AND Typ = ‘G‘ SELECT COUNT (DISTINCT Einr) FROM Absatz WHERE Land = ‘SA‘ AND Typ = ‘G‘ AND Einr = IS NOT IN (SELECT DISTINCT Einr FROM Absatz WHERE Land = ‘SA‘ AND Typ = ‘G‘ AND Prod = ‘Radeb‘)

  5. Ein Select braucht zu viel Zeit,... was tun ? • Überprüfen des Kommandos • Liegt eine ungünstige (evt. vermeidbare) Formulierung vor ? • Besonderheiten der Hersteller sind zu beachten • Prüfen des Zugriffsplans: Wurde ein nicht erwarteter Ablauf generiert ? • Index-Benutzung: • Wurde ein wirkungsvoller Index nicht ausgewählt ? • Fehlt ein Index ? • Reihenfolge der Joins Maßnahmen: Diverse Eingriffe wie • Hints (Force Index, Parallelization, number of pages per read,...) • Zerlegung der Query in mehrere Schritte mit Hilfe temporärer Tabellen • Update statistics, u.ä. • Prüfen des Datenbank-Servers • Einschalten eines Performance-Monitors

  6. Ein Select braucht zu viel Zeit,... was tun ? • Prüfen des Datenbank-Servers • Einschalten eines Performance-Monitors • Index-Benutzung • Prozessaktivität • Sperren • Cache-Benutzung • Task switches • Prüfen der Datenbank • Modifikation des Datenbank-Schemas • Anlegen weiterer Indizes • Einbauen von Aggregaten und anderen Redundanzen • Partitionierung Häufig ergibt sich neues Konfliktpotential !

  7. Tuning stößt häufig an Grenzen Beispiele: • „Spezial-Queries“ legen das komplette System lahm. • Die Kapazität des Systems ist bereits bei irgendeiner Benutzer-Anzahl ausgeschöpft, es sollen aber zusätzliche, z.B. auch Intranet-Anwender unterstützt werden. • Die Datenmengen sind sehr groß. • Das Select wird von einem Endbenutzer-Werkzeug generiert.

  8. Die grundlegende Entscheidung:Isolieren der Anfragen von den Transaktionen OLTP Server Query Server Enterprise Connect Replication Server REP Agent Stable Device Daten Log Daten Log

  9. Data Warehouse Architektur Datamart Datamart Datamart Benutzer- Tool RDBMS Relationale DB Data Staging (ETL) Benutzer- Tool Enterprise Data Warehouse SW-Pakete RDBMS Benutzer- Tool Altdaten ROLAP Benutzer- Tool Externe Quellen Daten- Bereinigungs- Tool Warehouse Admin. Tools MOLAP unternehmen- weites/ zentrales Data Warehouse Daten-Extraktion, Transformation und Laden neu strukturierte (‘Architected’) Data Marts Quell- daten

  10. Bei sehr großen Datenmengen – prinzipielle Performanceprobleme Beispielsituationen: • „Das Analysesystem steht erst ab 11:00 Uhr morgens zur Verfügung.“ • „Die Informationen sind immer auf dem Stand vom Vortag, benötigt werden aber Informationen, die max. 60 Minuten alt sind.“ • „Das Data Warehouse speichert die Geschäftsvorgänge der letzten 6 Monate, benötigt werden aber die Trends über die letzten 2 Jahre oder mehr.“

  11. (Häufige) Probleme in Business Intelligence Anwendungen • Antwortzeiten - sind zu lang • Flexibilität und komplexe Abfragen - mit ständiger Erweiterung der Anforderungen (Ad-Hoc SQL) sind sehr problematisch • Wachsende Nutzerzahl/ Datenmenge – Performance sinkt und genügt nicht mehr den Anforderungen • Analyse auf Detaildatenebene - nicht alle Daten werden abgespeichert aufgrund der Größe des Datenbestandes  Arbeit mit verdichteten Daten • Speicherung und Analyse von (sehr) großen Datenbeständen – zu teuer in Speicher, Administration und Antwortzeit • Online-Loads - parallel zum Auswerten nicht (immer) möglich

  12. Hohe Performance bei Datenbankauswertungen • Einführung: Probleme und Herangehensweise • Sybase Adaptive Server IQ und IQM • Prinzip-Überblick • Speicherungsstruktur und Indextypen • IQ Multiplex • Beispiele, Ergebnisse

  13. Der traditionelle RDBMS-Ansatz Traditioneller Ansatz: • Benutze einen Index wenn verfügbar • benötigt normalerweise Table Scan • Gehe zu den ausgewählten Datenseiten und addiere die Zahlen • Zufällige Verteilung der Daten führt dazu, daß fast alle Seiten gelesen werden müssen. • Auf jeder Seite müssen alle - auch die irrelevanten - Daten gelesen werden. Berechne den durchschnittlichen Absatz von „Radeberger“ in Gastronomie-Einrichtungen in Sachsen je Monat der letzten 3 Jahre SELECT AVG (Abs), SUM(Abs)/AnzGSA/36 FROM Absatz, (SELECT COUNT(DISTINCT Einr) AS AnzGSA FROM Absatz WHERE Land = ‘SA‘ AND Typ = ‘G‘) WHERE Land = ‘SA‘ AND Typ = ‘G‘ AND Prod = ‘Radeb‘

  14. Das Problem: Große Datenmengen Berechne den durchschnittlichen Absatz von „Radeberger“ in Gastronomie-Einrichtungen in Sachsen je Monat der letzten 3 Jahre • 360 Millionen Zeilen • 200 Bytes pro Zeile • 16K Seitengröße • 4.500.000 I/O’s pro Table Scan werden benötigt, mit schneller Platte, d.h. 40MB/sec 30 Minuten !!! Sehr teuer und unflexibel bei Ad-hoc-Anfragen

  15. Vorteile: • Es werden nur die relevanten Daten gelesen • Einheitliche Datentypen deshalb Komprimierung möglich • Datenbank ist einfach zu ändern und zu verwalten Vertikale Partitionierung Sybase IQ:Daten sind in Spalten statt in Zeilen gespeichert.

  16. Vorteile: Ohne weitere Techniken kann IQ den Disk-I/O sehr stark reduzieren Vertikale Partitionierung Berechne den durchschnittlichen Absatz von Radeberger in Gastronomie-Einrichtungen in Sachsen je Monat der letzten 3 Jahre Sybase IQ:Es werden nur die relevanten Spalten gelesen Ergebnis im Beispiel: Reduzierung des Disk-I/O auf maximal 5% (ohne einen Index zu benutzen)

  17. Komprimieren der Daten OLTP Engine IQ SQL:Select sum (red) from ABC 1 2 3 4 ….. 100 1 2 3 4 …. 100 SQL: Create table ABC yellow, blue, red..magenta • Komprimieren in Zeilen bringt wenig wegen wechselnder Datentypen, sehr wirkungsvoll innerhalb einer Spalte • Dekomprimieren von Zeilen ist ineffizient (CPU overhead) weil meist nur ein Teil benötigt wird • Relative kleine Seitengröße bei OLTP bewirkt ungenutzten Platz • Bit-wise and bit-mapped sehr platzgünstig • Null values benötigen viel Platz in zeilen-orientierten DBMS • Zeilen-orientierte DBMS benötigen 4 - 10 mal mehr Speicherplatz als IQM Db page 2-32KB DB Page bis 2048 KB

  18. Platten-Laufwerke OLTP Engine IQ SQL:Select sum (red) from ABC 1 2 3 4 ….. 100 1 2 3 4 …. 100 SQL: Create table ABC yellow, blue, red..magenta • Problem • kleine I/O Größe der zeilen-orientierten DBMS • +90% braucht die Platte zum Suchen • random I/O der zeilen-orientierten DBMS • +90% braucht die Platte zum Suchen • Suchzeiten verbessern sich nur langsam, CPUs schneller => mehr Laufwerke pro CPU • Zeilen-orientiertesDBMS: 10 Laufwerke pro CPU (bevorzugt kleine Platten: 18-36GB) • IQ : 0.5 -1 Laufwerke pro CPU (bevorzugt große Platten: 73-180-320GB) • Zeilen-orientierte DBMS benötigen 10 – 20 mal mehr Laufwerke als IQMpro CPU Db page 2-32KB Db page bis 2048 KB

  19. Datenkompression - Radikale Senkung von Speicherbedarf und Wartung Herkömmliche DBMS Summaries Aggregates 1 – 2 TB Gleiche INPUT-Daten: “Konventionelles DW”ist 6x-10x größer als Sybase IQ DW Indexes 0,5 – 3 TB 2.4-6 TB Base table “RAW data” no indexes 0,9 – 1,1 TB 0.25 - 0.9 TB Aggr/Summ: 0 – 0,1 TB LOAD LOAD Indexes: 0,05 – 0,3 TB Base table: 0,2 – 0,5 TB INPUT DATA: 1 TB Source: Flat Files, ETL, Replikation, ODS

  20. Sybase IQ – PraxisergebnissePerformance vs. Oracle - (Kundenbeispiel Citibank) Oracle Sybase IQ Durchschnittl. 3.1 Std. Antwortzeit Ladezeit 8.4 Std. Plattenplatz 47 GB Plattform 2-CPU Ausführen von sechs komplexen Anfragen - Bankenanwendung (select customer ID, group by product and account) 6.9 Min. 3.1 Std. 8 GB 1-CPU

  21. Hohe Performance bei Datenbankauswertungen • Einführung: Probleme und Herangehensweise • Sybase Adaptive Server IQ und IQM • Prinzip-Überblick • Speicherungsstruktur und Indextypen • IQ Multiplex • Beispiele, Ergebnisse

  22. 4 Basis-Index-Typen und weitere Spezial-Typen Abkürzung Bezeichnung FP Wird für jede Spalte grundsätzlich Verwendet, Default Index Fast Projection HG Für UNIQUE und PRIMARY KEY notwendig High Group LF Low Fast HNG High Non Group Comparison Index CMP Word Index WD Join Index JI Date-, Time-, Datetime Date,TIME,DTTM

  23. Fast Projection (FP) Die Daten einer Spalte werden komprimiert gespeichert, abhängig von Datentyp und Kardinalität. • Default Speicherung, die automatisch durch IQ realisiert wird und nicht entfernt werden kann • für alle Spalten: notwendig für select list Spalten, string Suche, ad-hoc joins SELECT Land FROM Landtabelle WHERE Land LIKE ‘Sa%‘

  24. Fast Projection (FP) • Häufig wird dieser Default Index mit einem oder mehreren Indizes anderer IQ Index Typen verbunden. • benutzt bei wildcard string Suche—z.B., LIKE ’%sys%’ • Günstig für Berechnungen — z.B. SUM (A + B) • Einzige Möglichkeit für Datentyp BIT • Spaltenbeispiele: • Addresse • Name • Texte

  25. Fast Projection (FP) Subtype: FP(1)

  26. Fast Projection (FP) • Falls die Werteanzahl der Spalte < 256 ist, werden die Daten der Spalte als Fast Projection FP(1) anstelle von FP gespeichert • 1-Byte look-up table • Der Server versucht beim Laden FP(1) • Setzt auf FP(2) nachdem 256 Werte erkannt wurden • Der Datenbank-Administrator kann die Kardinalität der Spalte in der create table syntax durch Benutzung des UNIQUE Parameters angeben

  27. Fast Projection (FP) Subtype: FP(2)

  28. Fast Projection (FP) • Falls die Werteanzahl der Spalte > 256 und < 65.536 ist, werden die Daten der Spalte als FP(2) anstelle von FP gespeichert • 2-Byte look-up table • Setzt auf FP(3) nachdem 65.536 Werte erkannt wurden • Der Datenbank-Administrator kann die Kardinalität der Spalte in der create table syntax durch Benutzung des UNIQUE Parameters angeben

  29. Low Fast (LF) Bitmap Index einschl. B-tree, der für Spalten mit kleiner Kardinalität benutzt wird • Für jeden Spaltenwert ein Bitmap • Menge solcher Bitmaps für Bearbeitung fast aller Anfragen angewendet • Ideal für Spalten mit einer Kardinalität <1500 SELECT * FROM Absatz WHERE Prod = ‘Radeberger‘

  30. Low Fast (LF) • wird angewendet bei folgenden Anfrageoperationen: • Suchargumente in where-Klauseln • Joins • GROUP BY • ORDER BY • Spaltenbeispiele: • Geschlecht • Ja/nein • Produktname • Land • Datum (falls < 1500 verschiedene Werte)

  31. Dramatische I/O-Reduzierung 800 Bytes x 20M 16K Seite RDBMS = 1.000,000 I/Os Geschlecht M M W M M W Staat CACANYCA MA CT Versichert JNJ N J N • Verarbeitet grosse Mengen nicht benötigter Daten • Erfordert oft “Full Table Scan” 20M Sätze 800 Bytes/Satz 20M Bits x 3 Spalten / 8 16K Seite = 470 I/Os Versichert Geschlecht Staat 1 M CA J M CA N W NY J M CA N 0 1 0 1 1 1 0 1 1 1 0 1 2 2 + + 20M Bits = 3 4 “Wieviele Männer sind in Kalifornien nicht versichert?“

  32. High Non Group (HNG) Bit-weiser Index, optimiert für Bereichs-Suche und Aggregations-Funktionen • Beispiel: SELECT SUM(Abs) FROM Absatz (1 * 64) + (0 * 32) + (1 * 16) + (6 * 8) + (4 * 4) + (3 * 2) + (4 * 1) = 154

  33. High Non Group (HNG) • Nicht-werte-basierter Bitmap-Index • Ideal für Spalten, die benutzt werden in: • Ranges • BETWEEN • SUM( ) und AVG( ) Funktionen • Spaltenbeispiele: • Datum (falls > 1500 verschiedene Werte) • Beträge • Mengen

  34. High Group (HG) Index für Daten mit hoher Kardinalität

  35. High Group (HG) • Verbesserter B-tree Index zur Ausführung von = und GROUP BY Operationen auf Spalten mit hoher Kardinalität • Für Spalten mit großer Anzahl eindeutiger Werte (>1500) • Wird benutzt, wenn die Spalte an einem Join beteiligt ist • Spaltenbeispiele: • Produkt Id • Mitarbeiter ID

  36. Prinzipielle Herangehensweise bei derIndexierung von Tabellen

  37. Prinzipielle Herangehensweise bei derIndexierung von Tabellen (Forts.)

  38. 4 Basis-Index-Typen und weitere Spezial-Typen Abkürzung Bezeichnung FP Wird für jede Spalte grundsätzlich Verwendet, Default Index Fast Projection HG Für UNIQUE und PRIMARY KEY notwendig High Group LF Low Fast HNG High Non Group Comparison Index CMP Word Index WD Join Index JI Date-, Time-, Datetime Date,TIME,DTTM

  39. Optimierte Speicher - / Indexstrukturen Beispiel – Abfrage: Berechne die Summe des Umsatzes, den durchschnittlichen Wert eines Verkaufs und die Anzahl der Verkäufe je Monat und Kunde für eine spezielle Produktart SELECT Kunde.Name, Verkauf.Monat, SUM(Verkauf.Wert), AVG(Verkauf.Wert), Count(Verkauf.Verkauf_id) FROM Kunde, Verkauf Where Kunde.Kunde_id = Verkauf. Kunde_id AND Verkauf.Produkt_Name LIKE “%anzug%” AND Verkauf.Jahr = 2000 GROUP BY Verkauf.Monat, Kunde.Name

  40. Optimierte Speicher - / Indexstrukturen SELECT Kunde.Name, Verkauf.Monat, SUM(Verkauf.Wert), AVG(Verkauf.Wert), Count(Verkauf.Verkauf_id) FROM Kunde, Verkauf Where Kunde.Kunde_id = Verkauf. Kunde_id AND Verkauf.Produkt_Name LIKE “%anzug%” AND Verkauf.Jahr = 2000 GROUP BY Verkauf.Monat, Kunde.Name 2 “Fast Projection” Indizes für die Projektion 1 “High Non Group” Index für die Aggregatbildung 4 “High Group” Indizes für die Aggregatbildung, die Join-Verarbeitung und das Gruppieren pro Kunde 2 “Low Fast” Indizes für die Suchbedingung und das Gruppieren auf Monatsebene 1 Word Index für Zeichenkettensuche

  41. Beispiel SELECTAVG (Abs), SUM(Abs)/AnzGSA/36 FROM Absatz, (SELECTCOUNT(DISTINCT Einr)AS AnzGSA FROM Absatz WHERELand = ‘SA‘AND Typ = ‘G‘) WHERELand = ‘SA‘AND Typ = ‘G‘ AND Prod = ‘Radeb‘ 1 “High Non Group” Index für die Aggregatbildung 1 “High Group” Index für die Aggregatbildung 3 “Low Fast” Indizes für die Suchbedingung

  42. Vorteile: Ohne weitere Techniken kann IQ den Disk-I/O sehr stark reduzieren Vertikale Partitionierung Berechne den durchschnittlichen Absatz von Radeberger in Gastronomie-Einrichtungen in Sachsen je Monat der letzten 3 Jahre Sybase IQ:Es werden nur die relevanten Spalten gelesen Ergebnis im Beispiel: Reduzierung des Disk-I/O auf maximal 5% (ohne einen Index zu benutzen)

  43. Vertikale Partitionierung Berechne den durchschnittlichen Absatz von Radeberger in Gastronomie-Einrichtungen in Sachsen je Monat der letzten 3 Jahre SELECTAVG (Abs), SUM(Abs)/AnzGSA/36 FROM Absatz, (SELECTCOUNT(DISTINCT Einr)AS AnzGSA FROM Absatz WHERELand = ‘SA‘AND Typ = ‘G‘) WHERELand = ‘SA‘AND Typ = ‘G‘ AND Prod = ‘Radeb‘ Sybase IQ:Es werden nur die relevanten Spalten gelesen Ergebnis im Beispiel: Reduzierung des Disk-I/O auf max. 2%

  44. Eurostat : wide table – 10 Mio rows

  45. Eurostat : Horizontale Partitionierung

  46. Eurostat : Vertikale Partitionierung

  47. Eurostat : In IQ-M In IQ : 757 FP, 45 HG, 512 LF, 103 HNG = 1417 index

  48. Sybase IQ und überprüfte Einsparungen bei Plattenspeicher

  49. Hohe Performancebei Datenbankauswertungen • Einführung: Probleme und Herangehensweise • Sybase Adaptive Server IQ und IQM • Prinzip-Überblick • Speicherungsstruktur und Indextypen • IQ Multiplex • Beispiele, Ergebnisse

More Related