datenmanagement für sap applikationendbst/material/20071024_129_munz.pdf · ¤sap ag 2007,...
TRANSCRIPT
Datenmanagement fürSAP Applikationen
Rudolf MunzSAP AG
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 2
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 3
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 4
Frontend
1992: SAP Introduces the 3-tier Architecture
LAN required, many roundtrips,data volume about 20 KB
WAN-enabled, few roundtrips,data volume < 10 KB
ApplicationServer
DatabaseServer
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 5
Database Server
Application Server
Frontends
. . .. . .. . .. . . Scalability
SAP Application Server Scalability
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 6
SAP Philosophies and Successes
First in ’real time’ applications
First in application integration via single shared database
First in Unix and Windows
First in SQL DBMS
First in Graphical User Interfaces
First in Virtual Machine concepts
for commercialapplications
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 7
Size of SAP ERP Data Model (Part of Business Suite ’05)
67.000 tables
700.000 columns
10.000 views
13.000 indexes
100.000.000 rows (initial size)
57 GB disk footprint (initial size)
270 millions lines of code
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 8
Published Results for SD Benchmarks
0
20,000
40,000
60,000
80,000
100,000
120,000
140,000
160,000
180,000
200,000
120 300 6,03014,400
19,36026,000
47,528
100,000
1,400
168,300
Number of SD Benchmark Users
20051995 1996 1997 1998 1999 2000 2001 2002 20031994 20041993
SD Benchmark (three-tier): Highest number of users
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 9
Typical OLTP CPU Load Distribution
SAP Standard Benchmarks
Rel
. CPU
-Usa
ge /
Dia
log
Step
0.00
1.00
2.00
3.00
4.00
5.00
6.00
FI SD PP MM ATO
10%
8%
Platform & Release dependent
84% 8% 9% 7% 7%
9% 14% 11%22%
81%
79%
82%
68%
= Database Server+ = Application Server
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 10
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 11
Table Buffer Design Rationale
Optimize read performance of stable or nearly stable dataGranules are tables or primary key ranges
Only primary key or key prefix accesses supported
Stored in shared memory of application serverReplicated in all application servers
No transactional consistency for data in table bufferInvalidation and refresh of buffered tables and key ranges
Async propagation of changes to other application servers
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 12
Database ServerApplication Server
WorkProcess
SELECT *FROM ...
Key
Native SQL
Table Buffer in Application Server
DBInterface
SQL Data
SQL Data
OpenSQL
Catalog
TableBuffer
Open SQL
SQL Data
DBMS
DB
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 13
Performance of Table Buffer vs. DBMS (PK Access)
0
50
100
150
200
250
300
µs
Table Buffer
Local DBMS
ABAP VM
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 14
Database ServerApplication Server
WorkProcess
SELECT *FROM ...
Key
Native SQL
Typical OLTP Traffic Distribution
DBInterface
80%
20%
DBMS
Cache
98%
SQL Data 2%Disks70% reads
80% primary key20% medium complex
30% writes
SQL Data
TableBuffer
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 15
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 16
Content Server Design Rationale
Documents are attachments to SAP Business Objects
Separation of document content from OLTP data– Read and write traffic of documents offloaded from OLTP database– Insert/update/delete of documents not recorded in OLTP database log
– Improved cache utilization in OLTP database server– Reduced size of OLTP database– Direct content delivery to SAP Frontend (Viewer)
Application server sessions use two database sessions,dual session support in database abstraction layer– OLTP DBMS– Content Server (based on MaxDB)
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 17
Content Server
Ap plication Server
Frontends
. . .. . .. . .. . .
Content Server Architecture
. . .. . .
Database Server
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 18
Content Server Consistency
Documents are never updated,Update = delete + insert
References to documents (DocID) stored in OLTP data
Two-phase commit is avoided by write discipline1. Insert new document into Content Server and commit2. Insert or update Business Object in OLTP DBMS and commit
If step 2 succeeds, we are done– Normal case
If step 1 or 2 fail, garbage in the Content Server may be left– Exception– Steps can be repeated
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 19
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 20
liveCache Features
Main memory-based object management system (OMS)– Persistent and shared C++ objects– Favors modeling of tree- or network-like complex object structures– Mainly used for SAP’s supply chain management (planning & optimization)
Data-intensive application logic is executed as Stored Procedures– Application coding and data management in same address space– Navigations on shared data are nearly as fast as on private data– Navigations are 50 to 100 times faster compared to SQL
Reader isolation with respect to concurrent writers (multi-version read)
Writer isolation with respect to concurrent writers (versioning)
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 21
MaxDB(SQL)
liveCache = MaxDB + MoreDB
OMS
liveCache Applications
Applications
MoreDB(OMS)
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 22
liveCache Architecture
Record & Page Manager
SQL OMS
liveCache Applications
SQL Packets
ApplicationServer
liveCacheServer
StoredProceduresin C++
ObjectManagementSystem
ABAP Applications
Record & Page Manager
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 23
Shared and Private Data in liveCache
Main-memory Database(Persistent C++ Objects)
Session A Session B Session C Session D
TransientC++Objects
Object Cache Object Cache
TransientC++Objects
Object Cache
TransientC++Objects
Object Cache
TransientC++Objects
SharedData
PrivateData
(Heap)
Data Cache
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 24
Navigational Performance (in µs)
0 50 100 150 200 250 300 350 400
C++ Pointer
Object Cache
OMS OID
OMS Key
SQL Key SP
SQL Key
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 25
liveCache Statistics (Customer Scenario)
Database size (in GB) 110
Transactions / sec 33
liveCache roundtrips / sec 1.160
Object reads / sec 223.000
Object writes / sec 114.000
Log in KB / sec 660
Log in pages /sec 82
Average load in a 24x7 environmentPeak load is factor 2 higher
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 26
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 27
Business Intelligence Waves
Wave 1: Reporting is part of the OLTP system (past)Unpredictable query load
Reporting on flat OLTP tables
No support of multi-dimensional data, no OLAP, no history
Wave 2: Dedicated Data Warehouse (now)Separation of OLTP systems and Data Warehouse (DWH)
Periodic extracts of OLTP data to DWH (ETL)
OLAP on multi-dimensional data, history
Wave 3: Realtime Analytics (future)Separation of OLTP and OLAP systems
Transactional consistency between OLTP and OLAP data
Driver: SOA and BI functionality as part of transactional applications
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 28
BI Accelerator
Query Performance Booster
BI Accelerator
Storageon disk
Query processingin main memorySAP NetWeaver BI
DBMS
BI Tools or Applications
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 29
Column-Wise Storage
BI Acceleratorstores tables column-wise
OLTP DBMSstore tables row-wise
Att1
Col1 Att2
Col2 Att2
Row2
Row1
...
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 30
Data Compression
Column-wise StorageDictionary
– Sorted array of all used values– Values stored with front compression
Column values– Array of dictionary indexes– Minimal number of bits used
to represent values
Compression rate– Factor 3 - 6 RowID ValueID
1 172 23 74 175 2
Column Dictionary
ValueID Value1 IBM2 Microsoft… SAP17 SAP Press19 SAP SI
Column Values
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 31
Partitioning of Columns into Main Memory of Blades
BladeServer
ColumnsPart 2
BladeServer
ColumnsPart ...
BladeServer
ColumnsPart N
BladeServer
ColumnPart 1
ColumnStorage
Part N
...
Part 2
Part 1
ColumnsFact TableFact TableFact TablePart 1Part 2
...Part N
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 32
Incremental Data Loads
Static Part
Delta Part
Delta PartSupports fast loadsHolds data until they are mergedFast merge
Queries DataLoads
BI AcceleratorEngine
Merge of delta part triggered bySizeScheduleManually
Queries runagainst both
parts
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 33
Query Execution Times (Sample Queries)
3233 801 5132242.3516.4Query 10
262348 95701.91.2Query 13
6 28088 435 77394.137.5Query 12
3233 801 5134112.1865.7Query 11
14967 318 17620082.04015.3Query 9
28167 318 17615381.92924.9Query 8
15 80359 87024.28.2Query 7
27 798590 784153.246.1Query 6
209348 95722.45.3Query 14
27 798590 784113.436.3Query 5
5 77196 71212.32.6Query 4
126283 02031.85.3Query 3
1 32213 434 508845.2435.3Query 2
102 54061.59.1Query 1
Rows afteraggregation
Rows afterfiltering
Improvement(factor)
BI Accelerator(sec)
DBMS(sec)Query
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 34
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 35
Summary: Special Data Containers
OLTP dataTransactional SQL engineRow-wise storageAdditional caching of stable data in main memory of application server
OLAP dataSQL engine without transactions and loggingColumn-wise storageMain memory databaseMPP approach (data partitioning into blades)
Objects (C++, Java, ABAP)Transactional Object Management SystemMain memory databaseData-intensive application logic executed as Stored ProceduresAlternative to OLTP data management based on SQL
DocumentsDocument repositoryDocuments attached to Business Objects (DocID in OLTP data)Separation of document and OLTP workload
SQL DBMS
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 36
Scenario: RFID-Tagged Products in a Supply Chain
EventsProducts with an RFID tag are moved in a global supply chain
All movements are detected as an RFID event by RFID sensors
Customer specific RFID tag and event data (e. g. XML)
Extremely high volume of RFIDs and associated RFID events
Customer scenario– 1KB / event– 120 TB / year = 337 GB / day = 14 GB / hour = 3.9 MB / sec
(best case, 10x peaks)
Challenging write workload
Challenging read workload with search capabilities on all attributes
Distributed event capturing, storing, and retrieval
Infrastructure to capture, store, and retrieve (RFID) events?
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 37
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 38
Invisible DBMS
Many DBMS instances in embedded systems
Many DBMS instances in an enterprise IT landscape– Central administration– Central user and role management
We will run short of DBAs– DBA-free operation required
Self-management = self-tuning + self-administration– Implicit reorganization– Implicit Update Statistics– Implicit index tuning
- Determine unused indexes- Propose / create useful indexes
Workload analysis and adaptation (self-tuning)– ”Online everything” for automatic configuration changes– Adapt to new system quotas
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 39
Continuous Availability
High Availability configurations are in placeProtection against unplanned downtimes (hardware, system software, desaster)Hot-standby with failover from master to slave (OS cluster)
Customers want business continuity24 x 365 instead of 24 x 7Applications facing customers or partnersThink of Google, Ebay, Amazon, ... in the consumer space
Continuous Availability addresses planned downtimeConfiguration changes (”online everything”)Patches for the current release (rolling patch services in a cluster)Upgrades to the next application release (to be solved)– Migration to new application coding and an extended persistency layer– Old and new persistency layer run in parallel (during upgrade phase)– Changes get propagated from old to new persistency layer– Application server and DBMS involved
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 40
Unlimited Scalability
Nearly unlimited main memoryActive part of an OLTP database can be kept in main memoryEffects of all open transactions can be kept in main memoryOptimization potential: dedicated main memory data structures vs.serializable data structures for logging and checkpointing
Nearly unlimited CPU power (multi-cores)Eliminate reader/writer synchronization (= multi-version concurrency control)Reduce low-level writer/writer synchronization– Context switches are expensive and should be avoided– Fast synchronization techniques (compare and swap)– Differentiate between safe and unsafe phases of changes– Differentiate between extensions and structural changes– Look for collision-free algorithms
Cache misses determine CPU performance (level 2 cache vs. main memory)– Locality of coding (profile-based optimization)– Locality of main memory data (e. g. for scanning)
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 41
Tenant-aware DBMS
New software delivery model: Software-as-a-ServiceHosted applications for many (small) tenantsInteresting for small and medium enterprises with little IT skillsAll tenants run the same application, data are tenant-specificData isolation between tenants is a mustDBMS instance per tenant is too expensive (admin and system costs)
Solution 1: Tenant-aware data modelRequires discipline in application development and additional QA effortsNo programming access to the persistency layer by tenants
Solution 2: Tenant-aware DBMSImplicit tenant-enabling by the DBMS: DBMS virtualization– Resource sharing for caches, data volumes, log
Each tenants gets an own (virtual) DBMS instanceNo changes in the application codingAllows for tenant-specific extensions
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 42
Agenda
SAP Architecture
Table Buffer
Content Server
liveCache
BI Accelerator
Summary Special Data Containers
Future DBMS Requirements
Summary
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 43
Summary
Table BufferContent ServerliveCacheBI Accelerator?
Invisible DBMSContinuous AvailabilityUnlimited ScalabilityTenant-aware DBMS
Specialized data containers forStable OLTP dataDocumentsObjectsOLAP dataEvents
New operational requirementsEliminate DBMS administrationAlways upExploit hardware trendsVirtualization at the DBMS level
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 44
Copyright 2007 SAP AG. All Rights Reserved
No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may bechanged without prior notice.
Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.
Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.
IBM, DB2, DB2 Universal Database, OS/2, Parallel Sysplex, MVS/ESA, AIX, S/390, AS/400, OS/390, OS/400, iSeries, pSeries, xSeries, zSeries, System i, System i5, System p,System p5, System x, System z, System z9, z/OS, AFP, Intelligent Miner, WebSphere, Netfinity, Tivoli, Informix, i5/OS, POWER, POWER5, POWER5+, OpenPower and PowerPC aretrademarks or registered trademarks of IBM Corporation.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries.
Oracle is a registered trademark of Oracle Corporation.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.
HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
Java is a registered trademark of Sun Microsystems, Inc.
JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.
MaxDB is a trademark of MySQL AB, Sweden.
SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registeredtrademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies.Data contained in this document serves informational purposes only. National product specifications may vary.
The information in this document is proprietary to SAP. No part of this document may be reproduced, copied, or transmitted in any form or for any purpose without the express priorwritten permission of SAP AG.
This document is a preliminary version and not subject to your license agreement or any other agreement with SAP. This document contains only intended strategies, developments,and functionalities of the SAP® product and is not intended to be binding upon SAP to any particular course of business, product strategy, and/or development. Please note that thisdocument is subject to change and may be changed by SAP at any time without notice.
SAP assumes no responsibility for errors or omissions in this document. SAP does not warrant the accuracy or completeness of the information, text, graphics, links, or other itemscontained within this material. This document is provided without a warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability,fitness for a particular purpose, or non-infringement.
SAP shall have no liability for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the use of these materials. Thislimitation shall not apply in cases of intent or gross negligence.
The statutory liability for personal injury and defective products is not affected. SAP has no control over the information that you may access through the use of hot links contained inthese materials and does not endorse your use of third-party Web pages nor provide any warranty whatsoever relating to third-party Web pages.