SQL Projekt AG FSU 290610, 1 Datenbankauswertungen in großen Datenmengen - Spaltenorientierte Datenbank Einführung: Probleme und Herangehensweise Sybase

SQL Projekt AG FSU 290610, 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


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



Überprüfen des Kommandos Liegt eine ungünstige (evt. vermeidbare) Formulierung vor ?

Besonderheiten der Hersteller sind zu beachten


Anfragebeispiel

Mon Einr Typ Land Prod Abs

0105 32 G SA Werne 12

0105 36 G MV Becks 9

0105 38 G SA Radeb 28

0105 41 K NS Jever 11

0105 43 G SA Radeb 9

0105 46 G BY Paula 3

0105 47 M NW Dortm 70

0105 49 K SA Lands 12

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


Typ = ‘G‘ AND

Prod = ‘Radeb‘)

SELECT COUNT (DISTINCT Einr)

FROM Absatz


Typ = ‘G‘ AND

Einr = IS NOT IN (SELECT DISTINCT Einr

FROM Absatz


Typ = ‘G‘ AND

Prod = ‘Radeb‘)

SELECT COUNT (DISTINCT Einr) - AnzRadeb

FROM Absatz,

(SELECT COUNT(DISTINCT Einr) AS AnzRadeb

FROM Absatz


Typ = ‘G‘ AND

Prod = ‘Radeb‘)


Typ = ‘G‘



Ü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



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 !


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.


Die grundlegende Entscheidung: Isolieren der Anfragen von den Transaktionen

Daten Log

QueryServerEnterprise

ConnectReplicationServer

Stable Device

REP Agent

Daten Log

OLTPServer


Data Warehouse Architektur

Relationale DB

SW-Pakete

Altdaten

Externe Quellen

Quell-daten

DataStaging

(ETL)

WarehouseAdmin. Tools

Enterprise DataWarehouse

Daten-Extraktion,Transformationund Laden

Datamart

Datamart

unternehmen-weites/zentrales DataWarehouse

ROLAP

RDBMS

RDBMS

Datamart

MOLAP

neu strukturierte(‘Architected’)Data Marts

Benutzer-Tool

Benutzer-Tool

Benutzer-Tool

Benutzer-Tool

Daten-Bereinigungs-Tool



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


(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


Hohe Performance bei Datenbankauswertungen

•Einführung: Probleme und Herangehensweise




• IQ Multiplex



Der traditionelle RDBMS-Ansatz


0105 32 G SA Werne 12


0105 38 G SA Radeb 28

0105 41 K NS Jever 11



0105 47 M NW Dortm 70

0105 49 K SA Lands 12

Berechne den durchschnittlichen Absatz von „Radeberger“ in Gastronomie-Einrichtungen in Sachsen je Monat der letzten 3 Jahre

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

Traditioneller Ansatz:

SELECT AVG (Abs), SUM(Abs)/AnzGSA/36

FROM Absatz,

(SELECT COUNT(DISTINCT Einr) AS AnzGSA

FROM Absatz


Typ = ‘G‘)


Typ = ‘G‘ AND

Prod = ‘Radeb‘


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


0105 32 G SA Werne 12


0105 38 G SA Radeb 28

0105 41 K NS Jever 11



0105 47 M NW Dortm 70

0105 49 K SA Lands 12

Berechne den durchschnittlichen Absatz von „Radeberger“ in Gastronomie-Einrichtungen in Sachsen je Monat der letzten 3 Jahre

Das Problem: Große Datenmengen


Vorteile:

• Es werden nur die relevanten Daten gelesen

• Einheitliche Datentypen deshalb Komprimierung möglich

• Datenbank ist einfach zu ändern und zu verwalten

Sybase IQ: Daten sind in Spalten statt in Zeilen gespeichert.


9805 32 G SA Werne 6



9805 41 K NS Jever 11



9805 47 M NW Dortm 7

9805 49 K SA Lands 12


0105 32 G SA Werne 12


0105 38 G SA Radeb 28

0105 41 K NS Jever 11



0105 47 M NW Dortm 70

0105 49 K SA Lands 12

Vertikale Partitionierung


Sybase IQ: Es werden nur die relevanten Spalten gelesen

Vorteile: Ohne weitere Techniken kann IQ den Disk-I/O sehr stark reduzieren


9805 32 G SA Werne 12


9805 38 G SA Radeb 28

9805 41 K NS Jever 11



9805 47 M NW Dortm 70

9805 49 K SA Lands 12

Berechne den durchschnittlichen Absatz von Radeberger in Gastronomie-Einrichtungen in Sachsen je Monat der letzten 3 Jahre

Ergebnis im Beispiel:Reduzierung des Disk-I/Oauf maximal 5% (ohne einen Index zu benutzen)



Komprimieren der Daten

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 bis2048 KB

Db page2-32KB

1 2 3 4 ….. 100

OLTPEngine

1 2 3 4 …. 100

IQSQL: Create table ABC

yellow, blue, red..magenta

SQL:Select sum (red) from ABC


Platten-Laufwerke

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-orientiertes DBMS: 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 IQM pro CPU

Db page bis2048 KB

Db page2-32KB1 2 3 4 ….. 100

OLTPEngine

1 2 3 4 …. 100

IQSQL: Create table ABC

yellow, blue, red..magenta

SQL:Select sum (red) from ABC


Herkömmliche DBMS

SummariesAggregates1 – 2 TB

Indexes0,5 – 3 TB

Base table“RAW data”no indexes0,9 – 1,1 TB

2.4-6TB

Gleiche INPUT-Daten: “Konventionelles DW”ist 6x-10x größer als Sybase IQ DW

LOAD

LOAD

Base table: 0,2 – 0,5 TB

Indexes: 0,05 – 0,3 TB

Aggr/Summ: 0 – 0,1 TB0.25 -0.9 TB

INPUT DATA:1 TBSource: Flat Files,ETL, Replikation, ODS

Datenkompression - Radikale Senkung von Speicherbedarf und Wartung


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 GB1-CPU

Sybase IQ – Praxisergebnisse

Performance vs. Oracle - (Kundenbeispiel Citibank)







• IQ Multiplex



4 Basis-Index-Typen und weitere Spezial-Typen

Bezeichnung Abkürzung

Fast Projection

High Group

Low Fast

High Non Group

FP

HG

LF

HNG

CMPComparison Index

Word Index

Join Index

Date-, Time-, Datetime

WD

JI

Date,TIME,DTTM

Wird für jede Spalte grundsätzlich Verwendet, Default IndexFür UNIQUE und PRIMARY KEY notwendig


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

Fast Projection (FP)

LandSachsenSachsen-AnhaltThüringenNiedersachsenHessenBrandenburgSachsenHessen

SELECT LandFROM LandtabelleWHERE Land LIKE ‘Sa%‘



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



Subtype: FP(1)

LandSachsenSachsen-AnhaltThüringenNiedersachsenHessenBrandenburgSachsenHessen

Sachsen 1Sachsen-Anhalt 2Thüringen 3Niedersachsen 4Hessen 5Brandenburg 6

12345615


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




Subtype: FP(2)

ProdRadebergerWernesgrünerRadebergerLandskronBecksRadebergerPaulanerWernesgrünerKlosterbräu

Radeberger 1 1

Wernesgrüner 2 2

Landskron 3 3

Becks 4 4

Paulaner 5 5

Klosterbräu 6 6

1 12 21 13 34 41 1

5 52 2

6 6


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



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

ProdRadebergerWernesgrünerRadebergerLandskronBecksRadebergerPaulanerWernesgrünerKlosterbräu

Radeberger

Wernesgrüner

Landskron

1 0 00 1 01 0 00 0 10 0 01 0 00 0 00 1 00 0 0

SELECT *FROM AbsatzWHERE Prod = ‘Radeberger‘


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)


“Wieviele Männer sind in Kalifornien nicht versichert?“

GeschlechtMMWMMW

800 Bytes/Satz

20MSätze

StaatCACANYCAMACT

RDBMSRDBMSVersichert

JNJNJN

800 Bytes x 20M 16K Seite = 1.000,000 I/Os

Verarbeitet grosse Mengen nicht benötigter Daten

Erfordert oft “Full Table Scan”

M CA JM CA NW NY JM CA N

12

43

Geschlecht Staat Versichert

= 2+ +1101

0101

1101

20MBits

20M Bits x 3 Spalten / 816K Seite

= 470 I/Os

Dramatische I/O-Reduzierung


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

Binäre Darstellung HNG-Index für Abs

Abs 64 32 16 8 4 2 112 0 0 0 1 1 0 0 0 0 0 1 1 0 09 0 0 0 1 0 0 1 0 0 0 1 0 0 1

28 0 0 1 1 1 0 0 0 0 1 1 1 0 011 0 0 0 1 0 1 1 0 0 0 1 0 1 19 0 0 0 1 0 0 1 0 0 0 1 0 0 13 0 0 0 0 0 1 1 0 0 0 0 0 1 1

70 1 0 0 0 1 1 0 1 0 0 0 1 1 012 0 0 0 1 1 0 0 0 0 0 1 1 0 0

1 0 1 6 4 3 4


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

High Non Group (HNG)


High Group (HG)

Index für Daten mit hoher Kardinalität


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

High Group (HG)


Prinzipielle Herangehensweise bei derIndexierung von Tabellen


Prinzipielle Herangehensweise bei derIndexierung von Tabellen (Forts.)


4 Basis-Index-Typen und weitere Spezial-Typen

Bezeichnung Abkürzung

Fast Projection

High Group

Low Fast

High Non Group

FP

HG

LF

HNG

CMPComparison Index

Word Index

Join Index

Date-, Time-, Datetime

WD

JI

Date,TIME,DTTM

Wird für jede Spalte grundsätzlich Verwendet, Default IndexFür UNIQUE und PRIMARY KEY notwendig


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, VerkaufWhere Kunde.Kunde_id = Verkauf. Kunde_id

AND Verkauf.Produkt_Name LIKE “%anzug%”AND Verkauf.Jahr = 2000GROUP BY Verkauf.Monat, Kunde.Name

Optimierte Speicher - / Indexstrukturen


SELECT Kunde.Name, Verkauf.Monat, SUM(Verkauf.Wert), AVG(Verkauf.Wert), Count(Verkauf.Verkauf_id)FROM Kunde, VerkaufWhere Kunde.Kunde_id = Verkauf. Kunde_id

AND Verkauf.Produkt_Name LIKE “%anzug%”AND Verkauf.Jahr = 2000GROUP BY Verkauf.Monat, Kunde.Name

Optimierte Speicher - / Indexstrukturen

2 “Fast Projection” Indizes für die Projektion1 “High Non Group” Index für die Aggregatbildung4 “High Group” Indizes für die Aggregatbildung, die Join-Verarbeitung und das Gruppieren pro Kunde2 “Low Fast” Indizes für die Suchbedingung und das Gruppieren auf Monatsebene

1 Word Index für Zeichenkettensuche


Beispiel


FROM Absatz,


FROM Absatz


Typ = ‘G‘)


Typ = ‘G‘ AND

Prod = ‘Radeb‘

1 “High Non Group” Index für die Aggregatbildung1 “High Group” Index für die Aggregatbildung3 “Low Fast” Indizes für die Suchbedingung



Vorteile: Ohne weitere Techniken kann IQ den Disk-I/O sehr stark reduzieren


9805 32 G SA Werne 12


9805 38 G SA Radeb 28

9805 41 K NS Jever 11



9805 47 M NW Dortm 70

9805 49 K SA Lands 12


Ergebnis im Beispiel:Reduzierung des Disk-I/Oauf maximal 5% (ohne einen Index zu benutzen)





9805 32 G SA Werne 12


9805 38 G SA Radeb 28

9805 41 K NS Jever 11



9805 47 M NW Dortm 70

9805 49 K SA Lands 12


Ergebnis im Beispiel:Reduzierung des Disk-I/Oauf max. 2%



FROM Absatz,


FROM Absatz


Typ = ‘G‘)


Typ = ‘G‘ AND

Prod = ‘Radeb‘


Eurostat : wide table – 10 Mio rows


Eurostat : Horizontale Partitionierung


Eurostat : Vertikale Partitionierung


Eurostat : In IQ-M

In IQ : 757 FP, 45 HG, 512 LF, 103 HNG = 1417 index


Sybase IQ und überprüfte Einsparungen bei Plattenspeicher

Sybase IQ DATA COMPRESSION Beispiele

GeladeneRohdaten

Sybase IQ komprimiert

Erwartete Datenexplosion bei anderen Anbietern

Sun DWH Reference Architecture(InfoSizing – August 2007)

1 PB 260 TB 3 PB bis 7 PB

Sun DWH Reference Architektur(InfoSizing – June 2004)

155 TB 55 TB 500 TB bis 1,000 TB

Telefonica 70TB 15 TB 210 TB bis 490 TB

comScore Networks 40 TB 16 TB 120 TB bis 280 TB

Health Insurance Review Agency 27 TB 12 TB 81 TB bis 189 TB

Samsung Card 15 TB 7 TB 45 TB bis 105 TB

Nielsen Media Research 12 TB 12 TB 36 TB bis 84 TB

Large Credit Card Company 10 TB 4 TB 30 TB bis 70 TB


Hohe Performancebei Datenbankauswertungen





• IQ Multiplex



Skalierbarkeit

IQ-M

CPU

CPU

CPU

Starten mit einem Server Hinzufügen von CPUs u.

Speicher nach Bedarf

IQ-M

CPU CPU

CPU CPU

CPU CPU

IQ-M

CPU CPU

CPU CPU

CPU CPU

IQ-M

CPU CPU

CPU CPU

CPU CPU

IQ-M

CPU CPU

CPU CPU

CPU CPU

Multiplexing ermöglicht es,weitere Server und CPUs hinzuzufügen dabei kein bis minimaler

Verlust an Skalierbarkeit; die 1000ste CPU wird

so gut wie die erste CPUperformen

Terabytes an Festplatten können ins SAN eingefügt werden IQ-M wird diese effektiv nutzen

Skaliert wie ein Grid

CPU

CPU

CPU

Fiber Channel Storage Area Network

“Starte klein und wachse” mit Sybase IQ Multiplex Konfiguration


Workload: Each user executing random sequence of (TPC/H-like) queries (Source : HP Lab in San Bruno, CA)

80

120

160200

240

280

320

360

40

400

0

100

200

300

400

500

1 2 3 4 5 6 7 8 9 10

Knoten31 sec31.6 sec

400 User Antw-Zeit = 31.6 secErhöhung : 1.9% (0.6 sec)

Anwender

40 User Antw-Zeit: 31 sec

Users

98%

Sybase IQ Multiplex Test der Skalierfähigkeit

Nachgewiesen im Labor und bei Kunden

Skalierbarkeit


ReadKnoten

IQ-M

CPU

CPU

CPU

SKALIERBARKEIT

Nach hinzufügen eines Knotens KEIN globaler Lock Manager nötig KEINE Datenumverteilung erforderlich KEINE Änderungen im Schema SEHR geringe I/O Contention

IQ-M

CPU CPU

CPU CPU

CPU CPU

IQ-M

CPU CPU

CPU CPU

CPU CPU

IQ-M

CPU CPU

CPU CPU

CPU CPU

IQ-M

CPU CPU

CPU CPU

CPU CPU

Skaliert wie ein Grid

CPU

CPU

CPU

Fiber Channel Storage Area Network

Read/ Write Knoten

HOCHVERFÜGBARKEIT

- Keine Unterbrechung des Datenbankzugriffs für andere Knoten- Andere Knoten werden durch Ausfall nicht beeinflußt--Anwender können Queries nach Start des Knotens einfach wiederholen oder automatisch auf anderen Knoten ausweichen (Sybase Open Switch, HW, …)-Bei gespiegelten Platten “no single point of failure” Architektur

Einfache Administration und implizite Hochverfügbarkeit

Skalierbarkeit

Read/ Write Knoten

ReadKnoten

Read/ Write Knoten


Technische Limits

Datenbankgröße: Betriebssystemabhängig Maximal 192 PByte

Anzahl Tabellen pro Datenbank: 4.293.918.719Zeilen pro Tabelle: 248

Tabellen/ Views in einer Query: 512

Feldgröße für “Long Varchar” oder “Long Binary”IQ Page Größe 128K: bis zu 512 TBIQ Page Größe 512K: bis zu 1 PB

Größe einer Page: 64 KB bis 512 KBAnzahl Spalten pro Tabelle: 45.000

Geeignet für sehr große Datenbanken


Speicherung ALLER relevanten Daten in EINEM System

Partner-lösung

SybaseIQ

Internet(E-mail und Dokumente)

Vor-handenerE-mailServer

Anwender können weiter ihren bisherigen Email Client nutzen – können aber auch auf das System zugreifen

PartnerlösungDokumente und E-mail Clients

•Dokumente•Bilder•Video•Audio•Fax•Datei und DB Backup•Andere Daten

( Optional )•DW•Transaktionen Weitere Daten

können in der Lösung nach Bedarf hinzugefügt werden


Sun Reference Warehouse ArchitectureJuli 2007 - weltgrößtes DWH

Die Sun Data Warehouse Referenz Architektur, bestehend aus SolarisTM 10 OS, Sybase® IQ und BMMsoft DataFusionTM mit einem Sun SPARC® Enterprise M9000 Server erbrachte folgende wichtige Ergebnisse:

Es wurden ein 1 PByte transaktionale Rohdaten (6 Trillionen Sätze mit Kursdaten von Börsen) in ein voll indexiertes Starschema geladen

Es wurde eine Ladegeschwindigkeit von 285 Milliarden Sätze pro Tag (3 Millionen Sätze pro Sekunde) erzielt

Es wurde eine 85%-ige Datenkompression bei der Speicherung von einem PByte transaktionaler Rohdaten erreicht – diese Daten belegten weniger als 260 TByte Plattenplatz im System

Es zeigte eine durchschnittliche „Ready-Time“ von kleiner zwei Sekunden nach dem Hinzufügen von neuen Daten in das Data Warehouse

Es wurde die Hälfte der „T“ (Transaktional) Daten mit über 72 Terabyte an “EDM” (Emails, Dokumente und Multimedia) Daten ersetzt – Aufbau eines 572 TByte großen Data Warehouse mit „EDMT“ Daten

Es wurde eine Ladegeschwindigkeit von 26 TByte pro Tag beim Aufbau eines Data Warehouse mit 185 Millionen Dokumenten (Emails, Attachements und andere unstrukturierte Dokumente) erreicht

Es wurde eine Ladegeschwindigkeit von zwei Millionen Emails pro Stunde und 6 Millionen Dokumente pro Stunde erreicht – dabei wurden weniger als 7% der verfügbaren CPU Leistung benötigt

Audit show show


Sun DWH Reference Architecture

Reference ArchitectureEin Sun SPARC® Enterprise M9000 Server mit Solaris™ 10

Drei Sun StorageTek™ 6540 Storage Arrays verbunden mit dem Server über Fiber Channel

Sybase® IQ 12.7 Enterprise Edition

BMMsoft DataFusion für die Verwaltung unstrukturierter Daten und Emails Sybase IQ Writer nutzte 64 Cores (mit zusammen 128 Threads) und 100 GB

Hauptspeicher

45 GB Hauptspeicher für den Sybase IQ Ladeprozess und als Cache für Teile der geladenen Dateien

Der BMMsoft DataFusion Ladeserver nutzte 64 Cores (mit zusammen 128 Threads) und 40 GB Hauptspeicher

20 GB Hauptspeicher für Solaris 10 zur Optimierung von Swapping und Paging

Hauptspeichernutzung

Quelle: Sun Data Warehouse Reference Architecture for Structured and Unstructured Data, InfoSizing, August 20, 2007







• IQ Multiplex



ODBC AS/400ODBC AS/400 Informix Informix Microsoft Microsoft OracleOracle DB2/UDBDB2/UDB

ASECIS

IBM MVS (z/OS)IBM MVS (z/OS)DB2(CICS)DB2(CICS)DB2(IMS)DB2(IMS) DB2(DRDA)DB2(DRDA)IDMSIDMS IMSIMS

VSAMVSAM

DirectConnect

DirectConnect

Applikation

Technologische Grundlagen: Component Integration Services von Sybase ASE Proxy Tabellen Union in Views Instead-of-Trigger (ASE 15.0.2)

Transparent für SQL Archiv: Sybase IQArchiv: Sybase IQ

KUNDE

ORDER

VERTRAG

ORDER_HISTORY

PLACE_ORDER

TABLE

TABLE

TABLE

TABLE

CICS Trx

Physik. Speicherung/ Logik

Proxy Tabelle

VERTRAG

PLACE_..

ORDER_HISTORY

Sybase ASE und heterogene Umgebungen

Online-Archiv auf Basis Sybase IQ


Partnerlösungen (Auswahl)

PBS (Deutschland) SAP BI Archivlösung

Rent-a-Brain (Deutschland) iMarc-Emailarchivierung Dokumentenarchivierung

BMMSoft (USA) Email/-/ Dokumentenarchivierung


PBS CBW NLS IQ Introduction

PBS CBW NLS IQ for Sybase IQ is a powerful and completeNearline Storage Solution for SAP Business Intelligence

SAP BI

SAP NLS Data ArchivingProcess (DAP)

Administration/Monitoring

Load Data

Read DataAccess: Queries, Reload, ...

PBS CBW NLS IQInterface

Sybase IQ

server

CBW NLS IQ Infrastrutcure (without adk components)


CBW-Architektur mit NLS und Sybase IQ

SAPSAPNearlineNearlineProviderProvider

InfoCubeDataStore Objekt

DB und Nearline lesen(in Query-Attributen aktivieren)

SAP BWDatenbank

SAP BW Query

PBS Nearline Servicesfür Sybase IQ

Spalten-basierteData Warehouse DB,Kompression bis 1:10

Sybase IQ


Kompressionen InfoCubes - KundenbeispielInfoCubeInfoCube Größe arch. DatenGröße arch. Daten Größe Daten in Sybase IQGröße Daten in Sybase IQ Kompression aufKompression auf

INDIA03 9.042.943.028 Bytes 691.322.880 Bytes 8 %

INDIA21 1.944.346.768 Bytes 81.305.600 Bytes 4 %

FAKT01 59.532.053.152 Bytes 6.299.435.008 Bytes 11 %

FAKT21 4.617.352.608 Bytes 247.070.720 Bytes 5 %

FAKTP02 831.113.280 Bytes 63.963.136 Bytes 8 %

ERG002 19.097.371.560 Bytes 1.046.978.560 Bytes 5 %

0FIAR_C02 480.045.888 Bytes 45.178.880 Bytes 9 %

0FIAR_C03 7.932.706.440 Bytes 963.600.384 Bytes 12 %


Query „Markthierarchie“ – Speed (I)

QueryQuery

M_INDIA01/WEB1_M_INDIA01_MARHIE_ZJVJB Kundenhierarchie über Attribut KDUNIQUE 2003 – 2007Anzahl Datensätze: 17 Mio.

ZugriffsartZugriffsart Sybase IQSybase IQ Oracle DBOracle DBmit Aggregatenmit Aggregaten

Oracle DBOracle DBohne Aggregateohne Aggregate

Primärliste 16 s 71 s 416 s

Zeit [s]

Sybase IQ (16s)Oracle mit Aggregaten (71s -> Faktor 4)

Oracle ohne Aggregate (416s -> Faktor 26)


Query „Fakturen“ – Speed (I)

QueryQuery

Query M_FAKT01/STD_M_FAKT01_ASS_PC Fakturaauswertung 12.2007Anzahl Datensätze: 57 Mio.

ZugriffsartZugriffsart Sybase IQSybase IQ Oracle DBOracle DBmit Aggregatenmit Aggregaten

Oracle DBOracle DBohne Aggregateohne Aggregate

Primärliste 12 s 164 s nach 2000 s abgebrochen

Zeit [s]

Sybase IQ (12s)Oracle mit Aggregaten (164s -> Faktor 14)

Oracle ohne Aggregate (abgebrochen)


Erfahrungsbericht – Fazit Kundeninstallation

Speed

Bis zu 14 x schnellere Antwortzeiten

Kompression

Kompression der Archivdaten bis zu 95 %

Administration

Keine Index- keine Aggregat- Modellierung


Mehr als 1500 Kunden

Erfolgreich etablierte und schnell wachsende Kundenbasis Mehr als 3000 Kundenprojekte bei mehr als 1500 Kunden

weltweit

http://www.itg.com/index.php


Analysten

GartnerGartner Data Warehouse Magic Quadrant Position: Challenger

IDC“Wir haben beobachtet und darauf gewartet, dass Firmen, die Datenbanken implementieren, sich vermehrt für Sybase IQ und seine einzigartige Tabellen- und Indexstruktur entscheiden. Denn diese sichert eine beeindruckende Performance bei komplexen Abfragen auf großen Data Warehouses. Gemessen an den Markterfolgen der letzten Jahre scheint es so, dass der Markt endlich ‘begriffen‘ hat.”

Carl Olofson, Research Vice President Information Management and Data Integration Software Research

IDC 2007


FK_TELCO_FA_REFERENCE_MONTH FK_TELCO_FA_REFERENCE_RESIDENT

FK_TELCO_FA_REFERENCE_SERVICE

FK_TELCO_FA_REFERENCE_STATUS

month

month_keymonth_textmonth_numberfiscal_periodyearperiod_and_yearmonth_and_year

integerchar(9)integerchar(2)integerchar(7)char(7)

<pk>

residential_customer

customer_keycustomer_first_namecustomer_last_namecustomer_genderstreet_addresscitystatepostal_codephone_number

integerchar(11)char(15)charchar(18)char(20)char(2)char(9)char(10)

<pk>

service

service_keycall_waiting_flagcaller_id_flagvoice_mail_flagcellular_flaginternet_flagisdn_flag

integercharcharcharcharcharchar

<pk>

status

status_keynew_customernew_addresscall_waiting_statuscaller_id_statusvoice_mail_statuscellular_statusinternet_statusisdn_statusclosed_this_period

integercharcharchar(10)char(10)char(10)char(10)char(10)char(10)char

<pk>

telco_facts

month_keycustomer_keyservice_keystatus_keycombined_revenuenumber_of_lineslocal_call_countlocal_call_minuteslong_distance_call_countlong_distance_call_minutesminutes_online

integerintegerintegerintegernumeric(19,4)integerintegerintegerintegerintegerinteger

<pk,fk1><pk,fk2><pk,fk3><pk,fk4>

Telekommunikations-DB

1.200.000 Zeilen

100.000

1272

24


Merkmal ASE ASIQ Bemerkung Version ASE12.5.0.

1 12.4.3

Hardware PC 333 MHZ ,128 MB, Platte Toshiba MK2018GAS

OS Windows 2000 Rohdaten (MB) 54 54 Daten (MB) geladen u. ind.

271,9 66,8 Bei ASIQ einschließlich Indexe

Daten (MB) ( Index)

78,8 (189)

Zusatzindexe Ladezeit (min) bcp / load table

4 4 Bei ASIQ Laden und Indizieren

Indizierungszeit (min)

40

Voraussetzungen und Laden


Merkmal ASE ASIQ Bemerkung Query1 (ms)

2.033 1.120 .993 1.003

1.362 .200 .150 .160

Query displays service types by month Valid values are 1- 12 for month_key

select month_key ,service_key,count(*) from telco_facts where month_key = 1 group by month_key,service_key

Query2 (ms)

372.116 359.960 359.896 338.756

9.975 .841 .741 .721

A look at customers who have the following

service call waiting , caller id, and voice mail by

-- fiscal period i.e. Q1,Q2,Q3,Q4 for year= 1998 select service_key,fiscal_period,count(*) from telco_facts T,month M where T.month_key=M.month_key and service_key = 4 group by fiscal_period,service_key order by fiscal_period,service_key

Anfragebeispiele



295.553 168.803 215.466 234.336

2.784 .952 .931 .871

-- A count of customers who have dropped call -- waiting from their service palns by Quarter select service_key,fiscal_period,count(*) from telco_facts T,month M,status S where T.month_key=M.month_key and S.status_key = T.status_key and call_waiting_status='Dropped' group by fiscal_period,service_key order by fiscal_period,service_key

Query4 (ms)

195.470 191.816 192.996 192.036

8.352 .761 .771 .892

-- female Customers in Massachusetts that do not -- have caller id select distinct(C.customer_key), C.Customer_first_name, C.customer_last_name, C.phone_number from residential_customer C,service,telco_facts where C.customer_key = telco_facts.customer_key and telco_facts.service_key = service.service_key and caller_id_flag = 'N' and state = 'MA' and customer_gender = 'F'

Anfragebeispiele



444.800 437.720 421.606 460.183

5.288 3.425 3.405 3.395

-- Find prospects for voice mail based on the criteria -- that customers with call waiting and caller id are -- good prospects for call Waiting select state, count(*) from telco_facts T,service S, residential_customer C, month M where T.service_key = S.Service_key and T.customer_key = C.customer_key and T.month_key = T.month_key and call_waiting_flag = 'Y' and caller_id_flag = 'Y' and voice_mail_flag = 'N' and state in ('NY','NJ','PA') and fiscal_period = 'Q1' group by state

Anfragebeispiele



17.576 15.803 15.423 15.763

6.849 6.079 6.129 6.069

-- Find customers that had ISDN service in February -- 1998 select customer_last_name ,customer_first_name from residential_customer R ,telco_facts T, service S, month M where M.month_text = 'February ' and M.year = 1998 and s.isdn_flag = 'Y' and M.month_key = T.month_key and S.service_key = T.service_key and R.customer_key = T.customer_key

Anfragebeispiele



468.433 470.726 468.143 466.470

2.544 1.292 1.272 1.262

-- Look at the local call minutes to see if they -- have increased after adding call waiting select fiscal_period,count(*),sum(local_call_minutes) from residential_customer R ,telco_facts T, status S, month M where s.call_waiting_status='Added' and state = 'OH' and M.month_key= T.month_key and S.status_key = T.service_key and R.customer_key = T.customer_key group by fiscal_period order by fiscal_period

Anfragebeispiele



629.456 629.656 627.180 627.933

2.303 1.862 1.863 1.883

-- Look at the call usage for customers with call -- waiting "service type 2" compared with customers -- with both call waiting and Caller id for Q4 for -- customers in CA select fiscal_period, T.service_key, sum(local_call_minutes), sum(local_call_count), count(*) from telco_facts T,residential_customer C,service S, month M where T.customer_key = C.customer_key and T.service_key = S.service_key and T.month_key = M.Month_key and fiscal_period = 'Q4' and T.service_key in (02,03) and state = 'CA' group by fiscal_period,T.service_key

Anfragebeispiele


EDS Report: IQM vs “konventionelles” RDBMS

Major DBMS (not NCR)

Sybase IQ Multiplex 12.4.3

ratio

#CPUs in Sun SF 6800 (750 MHz) 8 8 DB cache size 4 GB 4 GB

DW installation time (ready for data) 5-10 days table partitioning & table placement

1 day 5x-10x

#Records 880 Million Input data size (total size of input files) 270 GB

Loading speed (records/hour) 45 M/ hour 208 M/ hour 4.5x

Storage efficiency Base table (NO indexes) 300 GB 63 GB 4.7x Base table+indexes 550 GB 128 GB 4.3x

Base table+index+MVs+aggr. 900 GB 128 GB 7x Query speed

50 sequential queries 114 min 50 min (HDS) 2.3x 50 concurrent queries 180 min 13 min (HDS) 14x 50 concurrent queries against tables being loaded on the same SMP server. IQM slowdown (28%) due to CPU (and not db) contention

180 min (NO DW load)

18 min (HDS) (with DW Load)

10x

50 concurrent queries 180 min 13 min (* ) (T3) 14x


Sun‘s iForce Enterprise Data Warehouse Reference Architecture

Basiert auf Sybase Adaptive Server IQ Multiplex mit 156 CPUs und 160 GB RAM

Ergebnisse:

48,2 Terabyte Rohdaten korrespondieren mit 22 Terabyte Speicherverbrauch

5-160 Millionen Records werden täglich geladen in < 1h Konkurrenz zwischen Laden und Anfragen der gleichen

Tabelle bringt nur 6,9 % Verlangsamung Bis zu 1000 x schnellere Analyse-Laufzeiten 80% weniger Installationsaufwand Unterstützt Tausende Anwender gleichzeitig


1&1 Internet AGBertelsmann Music GroupEMI ElectrolaRTL TelevisionAllianz-Dresdner BausparkasseDresdner BankVodafone D2 GmbHDekaBankDeutsche BankCitibankDEVK Allgemeine Versicherungen AGRisk ConsultingRaiffeisen Hauptgenossenschaft NordMüller (Drogeriemärkte)European Southern Observatory …

Kunden in Deutschland (Auszug)

Documents

SQL Projekt AG FSU 290610, 1 Datenbankauswertungen in großen Datenmengen - Spaltenorientierte Datenbank Einführung: Probleme und Herangehensweise Sybase