Datenmanagement für SAP Applikationen

Datenmanagement für
SAP Applikationen
Rudolf Munz
SAP AG
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 / 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
1992: SAP Introduces the 3-tier Architecture
Frontend
WAN-enabled, few roundtrips,
data volume < 10 KB
Application
Server
LAN required, many roundtrips,
data volume about 20 KB
Database
Server
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 4
SAP Application Server Scalability
Frontends
Application Server
...
Database Server
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 5
Scalability
...
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
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 6
for commercial
applications
Size of SAP ERP Data Model (Part of Business Suite ’05)
67.000 tables
100.000.000 rows (initial size)
700.000 columns
57 GB disk footprint (initial size)
10.000 views
270 millions lines of code
13.000 indexes
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 7
Published Results for SD Benchmarks
SD Benchmark (three-tier): Highest number of users
200,000
1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005
180,000
168,300
160,000
140,000
Number of SD Benchmark Users
120,000
100,000
100,000
80,000
60,000
47,528
40,000
19,360
20,000
0
120
300
1,400
14,400
6,030
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 8
26,000
Typical OLTP CPU Load Distribution
Rel. CPU-Usage / Dialog Step
6.00
Platform & Release dependent
5.00
4.00
68%
3.00
82%
79%
2.00
81%
10%
1.00
84%
0.00
8%
8%
FI
9%
9%
SD
7%
7%
14%
11%
PP
MM
SAP Standard Benchmarks
+
= Application Server
= Database Server
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 9
22%
ATO
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 / 10
Table Buffer Design Rationale
Optimize read performance of stable or nearly stable data
Granules are tables or primary key ranges
Only primary key or key prefix accesses supported
Stored in shared memory of application server
Replicated in all application servers
No transactional consistency for data in table buffer
Invalidation 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 / 11
Table Buffer in Application Server
Database Server
Application Server
Work
Process
SELECT *
FROM ...
DB
Interface
Table
Buffer
Key
DBMS
SQL Data
Open SQL
SQL Data
Native SQL
Open
SQL
Catalog
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 12
SQL Data
DB
Performance of Table Buffer vs. DBMS (PK Access)
300
Local DBMS
250
200
150
100
50
Table Buffer
ABAP VM
0
µs
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 13
Typical OLTP Traffic Distribution
Database Server
Application Server
Work
Process
SELECT *
FROM ...
DB
Interface
Table
Buffer
Key
DBMS
80%
SQL Data
Native SQL
98%
SQL Data
70% reads
Cache
20%
80% primary key
20% medium complex
30% writes
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 14
2%
Disks
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 / 15
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 / 16
Content Server Architecture
Frontends
Ap plication Server
...
Content Server
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 17
...
...
Database Server
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 discipline
1. Insert new document into Content Server and commit
2. 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 / 18
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 / 19
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 / 20
liveCache = MaxDB + MoreDB
Applications
liveCache Applications
MoreDB
(OMS)
OMS
MaxDB
(SQL)
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 21
liveCache Architecture
Application
Server
ABAP Applications
SQL Packets
liveCache Applications
liveCache
Server
SQL
OMS
Record & Page Manager
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 22
Stored
Procedures
in C++
Object
Management
System
Shared and Private Data in liveCache
Session A
Session B
Session C
Session D
Transient
C++
Objects
Transient
C++
Objects
Transient
C++
Objects
Transient
C++
Objects
Object Cache
Object Cache
Object Cache
Object Cache
Private
Data
(Heap)
Data Cache
Main-memory Database
(Persistent C++ Objects)
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 23
Shared
Data
Navigational Performance (in µs)
SQL Key
SQL Key SP
OMS Key
OMS OID
Object Cache
C++ Pointer
0
50
100
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 24
150
200
250
300
350
400
liveCache Statistics (Customer Scenario)
Database size (in GB)
Transactions / sec
liveCache roundtrips / sec
110
33
1.160
Object reads / sec
223.000
Object writes / sec
114.000
Log in KB / sec
Log in pages /sec
660
82
Average load in a 24x7 environment
Peak load is factor 2 higher
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 25
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 / 26
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 / 27
BI Accelerator
Query Performance Booster
BI Tools or Applications
Storage
on disk
SAP NetWeaver BI
DBMS
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 28
BI Accelerator
Query processing
in main memory
Column-Wise Storage
OLTP DBMS
store tables row-wise
Row1
Row2
...
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 29
BI Accelerator
stores tables column-wise
Att
1
Col1
Col2
Att
2
Att
2
Data Compression
Column-wise Storage
Dictionary
– 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
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 30
Column Dictionary
ValueID
1
2
…
17
19
Value
IBM
Microsoft
SAP
SAP Press
SAP SI
Column Values
RowID
1
2
3
4
5
ValueID
17
2
7
17
2
Partitioning of Columns into Main Memory of Blades
Columns
Part 1
Part 2
...
FactTable
Table
Fact
Fact
Table
Part 1
Part 2
...
Part N
Part N
Column
Part 1
Columns
Part 2
Columns
Part ...
Columns
Part N
Blade
Server
Blade
Server
Blade
Server
Blade
Server
Column
Storage
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 31
Incremental Data Loads
Queries
Data
Loads
BI Accelerator
Engine
Delta Part
Supports fast loads
Holds data until they are merged
Fast merge
Queries run
against both
parts
Delta Part
Static Part
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 32
Merge of delta part triggered by
Size
Schedule
Manually
Query Execution Times (Sample Queries)
Query
DBMS
(sec)
BI Accelerator
(sec)
Improvement
(factor)
Rows after
filtering
Rows after
aggregation
Query 1
9.1
1.5
6
2 540
10
Query 2
435.3
5.2
84
13 434 508
1 322
Query 3
5.3
1.8
3
283 020
126
Query 4
2.6
2.3
1
96 712
5 771
Query 5
36.3
3.4
11
590 784
27 798
Query 6
46.1
3.2
15
590 784
27 798
Query 7
8.2
4.2
2
59 870
15 803
Query 8
2924.9
1.9
1538
67 318 176
281
Query 9
4015.3
2.0
2008
67 318 176
149
Query 10
516.4
2.3
224
33 801 513
32
Query 11
865.7
2.1
411
33 801 513
32
Query 12
37.5
4.1
9
88 435 773
6 280
Query 13
1.2
1.9
0
348 957
262
Query 14
5.3
2.4
2
348 957
209
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 33
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 / 34
Summary: Special Data Containers
OLTP data
Transactional SQL engine
Row-wise storage
SQL DBMS
Additional caching of stable data in main memory of application server
OLAP data
SQL engine without transactions and logging
Column-wise storage
Main memory database
MPP approach (data partitioning into blades)
Objects (C++, Java, ABAP)
Transactional Object Management System
Main memory database
Data-intensive application logic executed as Stored Procedures
Alternative to OLTP data management based on SQL
Documents
Document repository
Documents attached to Business Objects (DocID in OLTP data)
Separation of document and OLTP workload
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 35
Scenario: RFID-Tagged Products in a Supply Chain
Events
Products 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 / 36
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 / 37
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 / 38
Continuous Availability
High Availability configurations are in place
Protection against unplanned downtimes (hardware, system software, desaster)
Hot-standby with failover from master to slave (OS cluster)
Customers want business continuity
24 x 365 instead of 24 x 7
Applications facing customers or partners
Think of Google, Ebay, Amazon, ... in the consumer space
Continuous Availability addresses planned downtime
Configuration 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 / 39
Unlimited Scalability
Nearly unlimited main memory
Active part of an OLTP database can be kept in main memory
Effects of all open transactions can be kept in main memory
Optimization 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 / 40
Tenant-aware DBMS
New software delivery model: Software-as-a-Service
Hosted applications for many (small) tenants
Interesting for small and medium enterprises with little IT skills
All tenants run the same application, data are tenant-specific
Data isolation between tenants is a must
DBMS instance per tenant is too expensive (admin and system costs)
Solution 1: Tenant-aware data model
Requires discipline in application development and additional QA efforts
No programming access to the persistency layer by tenants
Solution 2: Tenant-aware DBMS
Implicit tenant-enabling by the DBMS: DBMS virtualization
– Resource sharing for caches, data volumes, log
Each tenants gets an own (virtual) DBMS instance
No changes in the application coding
Allows for tenant-specific extensions
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 41
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 / 42
Summary
Specialized data containers for
Table Buffer
Stable OLTP data
Content Server
Documents
liveCache
Objects
BI Accelerator
OLAP data
?
Events
Invisible DBMS
New operational requirements
Continuous Availability
Eliminate DBMS administration
Unlimited Scalability
Always up
Tenant-aware DBMS
Exploit hardware trends
Virtualization at the DBMS level
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 43
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 be
changed 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 are
trademarks 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 registered
trademarks 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 prior
written 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 this
document 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 items
contained 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. This
limitation 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 in
these materials and does not endorse your use of third-party Web pages nor provide any warranty whatsoever relating to third-party Web pages.
SAP AG 2007, Datenmanagement für SAP Applikationen / Rudolf Munz / 44