Präsentation

IBM DB2 Analytics Accelerator
Das Beste aus beiden Welten!
IBM Netezza Webcast: 10:00 – 10:45 Uhr
Ralf Götz
IBM Netezza
Systems Engineer
[email protected]
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
1908 x schneller:
2 Stunden 39 Minuten reduziert auf 5 Sekunden!
99% weniger CPU:
24Millionen Sätze: 56.5 CPU Sekunden reduziert
auf 0.4 CPU Sekunden!
…und das in nur 2 Tagen… klingt das interessant für Sie?
2
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
„IBM DB2 Analytics Accelerator“
Was ist der IBM DB2 Analytics Accelerator (IDAA)?
Was macht der IDAA?
Warum kann der IDAA mein System/z für analytische Abfragen so stark
beschleunigen?
IBM Netezza – Kurze Historie
IBM Netezza 1000 Appliance – die Technologie
Erste Kundenbeispiele
Fragen & Antworten
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Was ist der IBM DB2 Analytics Accelerator?
Ein Add-On für DB2 auf System z zur drastischen
Beschleunigung von analytischen Abfragen
DB2/z analysiert die ankommenden Queries
und entscheidet, eine Query weiterzuleiten,
oder selbst auszuführen .
Die clevere Idee: die jeweils am besten
geeignete Technology für die jeweilige
Aufgabe wird genutzt!
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Add-On? Weiterzuleiten? Was? Wohin?
System z196/z114
IBM Netezza 1000
Das Add-On ist ein Stück Software und die Netezza DWH Appliance
5
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
DB2 Analytics Accelerator V2.1
Das Beste beider Welten – System z und Netezza
Was es ist
Der IBM DB2 Analytics Accelerator ist ein lastoptimierte, Appliance Add-On. Dieses Add-On
beschleunigt Select Queries mit atemberaubender
Geschwindigkeit!
Warum ist IDAA so anders:
Performance: Beispiellose NearRealtime Antwortzeiten für Analysen,
die durch schlechte Performance vorher
unmöglich waren.
Integration: Tiefe Integration mit DB2
ermöglicht vollständige Transparenz für
alle Anwender und Anwendungen.
Self-Managed Workloads: Queries
werden immer am richtigen Ort
verarbeitet.
Transparenz: Applikationen, die zur
DB2 verbunden sind, bemerken den
Accelerator gar nicht.
Vereinfachte Administration:
Appliance “hands-free” Betrieb,
eliminiert die meisten Tuning Aufgaben
Breakthrough Technology Enabling New Benchmarks in Analytics
6
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Lösungskomponenten
Netezza
1000
zEnterprise
z196/z114
CLIENT
Primary
OSA-Express3
10 GbE
Private Service Network
10Gb
Backup
Data Studio
Foundation
DB2 Analytics
Accelerator
Admin Plug-in
Users/
Applications
7
BladeCenter
Data Warehouse application
DB2 for z/OS enabled for IBM
DB2 Analytics Accelerator
IBM DB2
Analytics
Acelerator
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Einfachheit, Flexibilität, freie Wahl
Erfüllen von Anforderungen durch vollständige Lösungen
IBM
Netezza
Zweckbestimmte Appliance
Netezza 1000
IBM
System z
Mixed Workload System
PureScale Analytics System 9700
Information Management Portfolio
(Information Server, MDM, Streams, etc)
Einfachheit
Der richtige Mix aus Einfacheit und Flexibilität
Flexibilität
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Z
N
Das Beste beider Welten
IBM DB2 Analytics Accelerator for z/OS
Netezza 1000 System z Appliance
Data Mart Data Mart Data Mart Data Mart
Stark in OLTP
Marktführer in
unternehmenswichtigen
Transaktionalen Systemen
Data Mart Consolidation
Stark in Data Warehousing
Transaction Systems
(OLTP)
Appliance Marktführer bei HighSpeed Analytischen Systemen
Stark in der Konsolidierung
Data Warehouse
Analytics
z/OS:
Netezza:
Recognized leader in
mixed workloads with
security, availability
and recoverability
Recognized leader in
cost-effective high
speed deep analytics
Beispielloser flexibilität bei
einem mixed workload und
Virtualisierung ermöglicht
kosteneffiziente Konsolidierung
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Sie haben die Wahl:
Mehrere DB2 Systeme und ein IDAA
Ein DB2 System und mehrere IDAA
Mehrere DB2 Systeme und mehrere IDAA
Volle Flexibilität für DB2 Systeme:
•Bessere Ausnutzung der IDAA Ressourcen
•Skalierbarkeit
•Hochverfügbarkeit
10
•
•
•
•
•
•
residing in the same LPAR
residing in different LPARs
residing in different CECs
being independent (non-data sharing)
belonging to the same data sharing group
belonging to different data sharing groups
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Netezza 1000 Appliance Skalierbarkeit
1
10
.......
TF3
TF6
TF12
TF24
TF36
TF48
TF72
TF96
TF120
Cabinets
1/4
1/2
1
2
3
4
6
8
10
Processing
Units
24
48
96
192
288
384
576
768
960
Capacity
(TB)
8
16
32
64
96
128
192
256
320
Effective
Capacity
(TB)*
32
64
128
256
384
512
768
1024
1280
List Price
$380k
$730k
$1.39m
$2.78m
$4.17m
$5.56m
$8.34m
$11.12m
$13.9m
Aktuelle IDAA Plattformen
Vorhersagbare, lineare Skalierung über die gesamte Familie
Capacity
= User Data space
Effective Capacity = User Data Space with compression
*: 4X compression assumed
© 2011 IBM Corporation
Warum kann der IDAA mein System/z für analytische
Abfragen so stark beschleunigen?
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Wie funktioniert das Routing der Queries?
13
© 2011 IBM Corporation
Wer ist Netezza? Woher kommen wir?
Ein kurzer Blick zurück…
… und in die Technik unter dem Blech
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Netezza – unter „blauer“ Flagge
Gründung Netezza Corporation 2001
Erstes Produkt 2003
IBM gab am 20.09.2010 die Übernahme von
Netezza bekannt
Seit 01.07.2011 ist die vollständige Integration in
die IBM abgeschlossen
IBM Netezza ist nun Teil der Software Group /
Information Management
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Netezza – unter „blauer“ Flagge
Gründung Netezza Corporation 2001
Erstes Produkt 2003
Y
T
I
IBM gab am 20.09.2010
die Übernahme von
L
Netezza bekannt UA
Q
Seit 01.07.2011 ist die vollständige Integration in
die IBM abgeschlossen
D
E
V
IBM Netezza ist nun Teil der Software
Group /
O
Information Management PR
AP
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Netezza – Marktführer & Innovator bei DWH Appliances
Eine kurze Historie
IBM Netezza High
Capacity Appliance
Erste High Capacity
Appliance weltweit
(>10 PB Storage)
IBM Netezza 1000 with
Advanced Analytics
Erste Analytic DWH
Appliance weltweit
IBM Netezza 1000
Impact
Erste PB DWH
Appliance weltweit
(300X Performance)
(150X
Performance)
NPS®
10000 Series
Erste 100TB DWH
Appliance weltweit
NPS®
8000 Series
Erste DWH Appliance
der Welt
2003
17
2006
3 einfache Paradigmen:
(50X Performance) - Preis/Performance
- Hohe Geschwindigkeit
- einfach zu bedienen
2009
2010
2011
2012
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Digitale
Medien
Finanzdienstleistungen
Öffentlicher
Bereich
Gesundheitswesen
Handel
Telekommunikation
Andere
18
Page 18
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Digitale
Medien
Finanzdienstleistungen
Öffentlicher
Bereich
Gesundheitswesen
Handel
Telekommunikation
Andere
19
Page 19
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Traditionelle Data Warehouse Anwendungen
sind einfach zu komplex
Sie basieren auf Datenbanken die für die Transaktionsverarbeitung
optimiert wurden – NICHT um die Anforderungen von fortschrittlichen
Analysen auf großen Datenbeständen abzubilden
Zu komplexe Infrastruktur
Zu ineffiziente Analysen
Zu komplizierter Einsatz
Zu viel Personal für die Wartung
Zu viel Tuning notwendig
Zu kostspielig im Betrieb
Zu zeitraubend für schnelle Antworten
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Eine einzige
echte
Data Warehouse Appliance:
Gemacht nur für einen Zweck: die Analyse ihrer Daten
Integrierte Datenbank, Server und Speicher
Standard Schnittstellen
Niedrigste TCO für Data Warehouse-Systeme
Geschwindigkeit: 10-100x schneller als traditionelle Systeme
Einfachheit: Minimale Administration und Tuning
Skalierbarkeit: bis zu Petabytes Nutzerdaten
Smart: High-performance advanced Analytics
21
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Die Netezza TwinFin™ (=IBM Netezza 1000) Appliance
Plattengehäuse
SMP Hosts
S-Blades™
(mit FPGA-basierendem
Databankbeschleuniger)
22
Nutzdaten
Swap- und Spiegelpartitionen
Hochgeschwindkeitsdatenstrom
SQL Compiler
Query Plan
Optimize
Admin
Prozessor &
DB- und Datenstrom-Logik
Hochgeschwindigkeitsdatenbank
Im Datenstrom: Joins,
Aggregationen, Sortierung, etc.
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Das Geheimnis hinter der Leistung
Snippet Blades™ (S-Blades™)
Zone Map
Accelaration
Field Programmable
Gate Array (FPGA)
•Keine Wartung notwendig
•Aktualisierung bei jedem Laden
•Automatisierte Konfiguration
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Netezza Data Stream Processing
select DISTRICT,
PRODUCTGRP,
sum(NRX)
from
MTHLY_RX_TERR_DATA
where MONTH = '20091201'
and
MARKET = 509123
and
SPECIALTY =
'GASTRO'
Uncompress
FPGA Core
Project
CPU Core
Restrict,
Visibility
Complex ∑
Joins, Aggs, etc.
Slice of table
MTHLY_RX_TERR_DATA
(compressed)
select DISTRICT,
PRODUCTGRP,
sum(NRX)
where MONTH = '20091201'
and
MARKET = 509123
Page 24
and
SPECIALTY = 'GASTRO'
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Wir bringen die Netezza AMPPTM Architektur zur DB2/z
AMPP = Asymmetric Massively Parallel Processing
CPU
Memory
Advanced
Analytics
BI
Legacy
Reporting
DB2 for
z/OS
•V9
•V10
SMP
Host
CPU
Netezza
•1000/3
•1000/6
•1000/12
FPGA
Memory
CPU
DBA
FPGA
FPGA
Memory
Network
Fabric
S-Blades™
Disk
Enclosures
IBM DB2 Analytics Accelerator
© 2011 IBM Corporation
Erste Kunden- und Einsatzbeispiele
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Erstes Feedback unserer Kunden: Fast Time to Value
IBM DB2 Analytics Accelerator (Netezza 1000-12)
Production ready - 1 person, 2 days
Table Acceleration Setup … 2 Hours
–
–
–
–
–
DB2 “Add Accelerator”
Choose a Table for “Acceleration”
Load the Table (DB2 copy to Netezza)
Knowledge Transfer
Query Comparisons
Initial Load Performance …
5.1 GB in 1 Min 25 Seconds (24M rows)
400 GB in 29 Min (570M rows)
Actual Query Acceleration … 1908x faster
2 Hours 39 Minutes to 5 Seconds
CPU Utilization Reduction … 99% less CPU
24M rows: 56.5 CPU seconds to 0.4 CPU seconds
Actual customer results, October 2011
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
IDAA Beta Customer
Workload characteristics
– Workload already running on DB2 for
z/OS
– Accelerating queries to operational
data store for financial reporting
The challenges
– The
challenges
The DB2
workload on the mainframe is permanently cost
–challenged
The DB2 workload on the mainframe is permanently cost
– Customer
challenged
focus is on CPU savings for long running reports
– Customer
The
facts: focus is on CPU savings for long running reports
– The
facts:
Workload
assessment: turnaround in 1 day
– –Workload
Workload
assessment
assessment:
helped
turnaround
to identify
in 1the
dayopportunity
Workload
assessment
– –Time
to Value:
2 days: helped to identify the opportunity
– Time
to Value:
2 days:
Installation
+ first
tables acceleration
NoInstallation
changes to+ the
firstapplication
tables acceleration
and SQL needed
No changes
theraw
application
and SQL needed
– Compression:
400toGB
data compresses
down to 40 GB
Compression:
400 GB raw data compresses down to 40 GB
– –Load
time 29 minutes
Load time 29
– –Acceleration
for minutes
first customer query
– Acceleration
for→
first
query
720 sec (DB2)
14customer
sec (IDAA)
/ Acceleration factor 51
720 secselling
(DB2) →point
14 sec (IDAA) / Acceleration factor 51
The unique
– The
IDAAunique
Strongly selling
supportspoint
the ongoing active cost management
mainframe
–onIDAA
Strongly supports the ongoing active cost management
on mainframe
Return
on investment
– Return
investment
Fast ROIon
based
on saved mainframe CPU MIPS together
Netezza
price/performance
–with
Fast
ROI based
on saved mainframe CPU MIPS together
with Netezza price/performance
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
The challenge
– The
Long challenge
query execution times
– Long
query execution times
The
facts:
– The
facts:
Workload
assessment: turnaround in 1 day
IDAA Beta Customer
Workload characteristics
– Workload already running on DB2 for
z/OS
– Accelerating queries to data hub for
reporting on medical data and
reporting on claims processing
Workload
assessment:
turnaround
in 1ofday
– –Workload
assessment
showed
3111 out
3464 queries with
offload
–a potential
Workloadfor
assessment
showed 3111 out of 3464 queries with
– POC
a potential
focusingfor
onoffload
the 224 to of those queries
POC
on days:
the 224 to of those queries
– –Time
to focusing
Value: 2.5
– Time
to Value:
2.5tables
days:acceleration
Installation
+ first
221
Installation
out of 224+queries
first tables
were
acceleration
offloaded immediately
221“Loaded”
out of 224
queries
offloaded immediately
– 5.1 GB
in 1
Min 25were
Seconds
5.1query
GB “Loaded”
in 1 1908x:
Min 25 from
Seconds
– –Top
acceleration
159Min → 5 sec
159Min
→ 5 sec
Top
acceleration
1908x:
– –Top
10query
queries:
11 hrs+ →
5 minfrom
(average
139x)
– Top
10 queries:
hrs+ → 5 min (average 139x)
The
unique
selling11point
– The
sellingw/o
point
IDAAunique
implementation
a change to applications and data
IDAA
implementation
w/o
change
applications
and data
– –IDAA
strengthens
the role
ofaDB2
as ato
data
hub
– IDAAon
strengthens
the role of DB2 as a data hub
Return
invesment
– Return
invesment
Fast ROIon
based
on saved mainframe CPU MIPS together
Netezza
price/performance
–with
Fast
ROI based
on saved mainframe CPU MIPS together
with Netezza price/performance
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Erste Kundenkommentare über den IBM DB2
Analytics Accelerator Product Lifecycle
Size
POC
Install
Table Setup
Load
ithin 1 day
w
d
n
u
ro
a
CoE Turn
Build upon Wo
rk
load assessm
ent and subse
quent Nz sizin
”
g
g
in
d
r
a
o
b
n
o
f
o
e
s
2”
ing: “ea
“normal” DB
a
h
it
w
o
d
Best th
ld
hat you wou
w
to
r
a
il
im
ry s
itive and ve
“It was
tu
in
s
a
w
t
“I
very ea
sy to us
"Eas
e”
y to u
s
e
"
“I found ev
erything to
be intuitive
l"
”
in tutoria
m
“Us
5
a
h
it
able
ry easy w
e
v
:
s
with
r
e
s
ary u
out
"For ordin
stud
ying
use
r ma
nua
ls” "Only requires a few seconds to get used to..."
"Intuitive
interactio
n and we
ll-arrange
d layout"
Compress
Accelerate
"The way
we hav
admin tas e seen here is rea
lly easy.
k s ca n b e
Most of th
distribute
d
o
n
e
o
n
ce and
e
d with the
tool"
© 2011 IBM Corporation
Assessment: Ist IDAA eine Option?
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Modernizing your DBMS infrastructure
on System z
Production
Data
Low Latency Data
Transactional
Information
Operational
Applications
32
Operational
Analytics
DB2
Analytics
Accelerator
Enhancing the operational
systems by bringing business
insights pervasively across the
organization embedded in the
operational applications
Passt IDAA für Sie?
• Do you have long running
DB2 for z/OS queries that
could provide business
value if they could be run in
seconds vs hours ?
• Do you face performance
challenges with complex
and ad hoc queries ?
• Extending the use of
operational platform data to
perform business analysis
and daily reporting
• The forgotten query: Have
you elected to set aside
some queries due to
performance challenges?
• Have you exhausted all
efforts to tune the system ?
• Organized to a support high speed transaction environment with historical and analytic information
• Increase agility by rapidly responding with immediate, accurate information, eliminating the need to search for answers
with analysis that is timely
• Enables the organization to become more nimble by placing fact-based information into the hands of decision makers
• Removes the never-ending search for the right information
• Delivers new insights that help business users discover opportunities to maximize customer and product profitability,
minimize customer churn, detect fraud, improve patient care, increase campaign effectiveness and more.
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Erster Schritt: Ein schneller Workload
Test
Customer
Report
Report for
for aa first
first assessment:
assessment:
Acceleration
Acceleration potential
potential for
for
Queries
Queries
Estimated
Estimated time
time
CP
CP cost
cost
Collecting information from dynamic
statement cache, supported by step-by-step
instruction and REXX script (small effort for
customer)
Uploading compressed file (up to some MB)
to IBM FTP server
IBM / Center of Excellence
Importing data into local database
Quick analysis based on known DB2
Analytics Accelerator capabilities
1
Customer
Customer
Database
Database
33
Documentation
Documentation
and
and REXX
REXX procedure
procedure
2
Data
Data package
package
(mainly
(mainly unload
unload
data
sets)
data sets)
Pre-process
Pre-process and
and
load
load
3
IBM
IBM lab
lab
Database
Database
Quick
Quick Workload
Workload
Test
Test Tool
Tool
Report
Assessment
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Business Case
Approach
1
2
Look at the number of reports
eligible for IDAA. Use the
workload assessment tool
Sort by CPU consumption
4
5
IDAA target
3
Combine the best of both worlds
-Use DB2 for short running queries
-Use IDAA for long running queries
6
Calculate MIPS savings from the
offloadable queries
Calculate effort savings for needless
DB2 performance tuning efforts for
offloadable queries
Compare with IDAA investments
ROI within a few months
© 2011 IBM Corporation
Information Management
Smart Business Analytics on System z
Fragen?
35
© 2011 IBM Corporation