Transcript Document

DB2 UDB ESE
Performance
Management
Tradeoffs
Ohio Valley CMG
September, 15 2004
Dr. Boris Zibitsker
BEZ Systems
www.bez.com
Agenda
• Challenges of planning and managing DB2 UDB
ESE applications during different phases of the
application life cycle
• How to evaluate performance management
tradeoffs
• Continuous process of setting and managing
expectations
2
Strategic Performance Management
Business Demand
DB2 Application Performance Management
SPM
Performance
Resources
Data
Resources
+
Business
Processes
+
Workloads
+
Data
3
Global View on Business Processes and IT
Resource Utilization Model
Business Processes Model
User
…
User
Disk
Disk
CPU
CPU
Disk
Disk
Workloads Model
Data Model
User
Appl
User
User
User
SQL
Appl
4
A.
Feasibility Study
8. Control
7. Predict
6. Plan
5. Report
4. Advice
3. Analyze
2. Characterize
1. Measure
Continuous Process of Performance Management
New DBMS, HDW & Architecture Justification. The
value is in justification of platform for new
System Life Cycle
applications
B.
Performance Management and Capacity Planning
C.
New Application Certification
How new application will perform in production
environment, and what should be done proactively
D.
Active Data Warehouse
Proactive optimization and managing mixed
workloads
E.
Application & DBMS Servers
Planning and managing multi tier environment
F.
Disaster Recovery
DB objects for DR process and Capacity Planning
for DR site
G.
New Releases of OS/DBMS/Applications
How new release of DBMS or OS will affect
current workload performance
H.
Server Consolidation
Justify data, applications and server consolidation
Comparing alternatives, setting
expectations, reducing the risk of surprises
5
How to Evaluate Tradeoffs









Know your Business Plan, Goals and
Role of DB2 UDB Applications
Identify Major Criteria
Perform Workload Characterization
Use DB2 Design Adviser and other
wizards
“Gut feeling” or vendors
recommendations
Use Benchmarking
Trend analysis
Performance prediction based on
analytical models
Multicriterion Optimization and adaptive
real-time management
6
Use Models to Evaluate Tradeoffs
Options
Input
Performance Prediction
Workload
Hardware
Software
Model
Response Time
Throughput
Utilization
Cost
Plan
Workload Growth
Database Size Growth
New Applications Implementation Plan
Hardware Upgrade Plan
Virtual Configuration Plan
Priority Change Plan
7
Open and Closed Queueing Network Models
Open
Closed
Departing Requests
Disk
Arriving Requests
Users
Throughput
…
CPU
Disk
CPU
Disk
Disk
Z,
Think Time
Utilization Law:
U=A*S
Response Time Law:
R=S / (1-U)
Where:
A
U
S
R
Z
N
X
Arrival Rate
Utilization
Service Time
Response Time
Think Time
Number of Users
Throughput
8
Tradeoffs Analysis
During Feasibility
Study
Architecture
SMP/MPP/Cluster/GRID
DBMS platform
Hardware platform
Challenges of Parallel Processing
Parallel Processing Affects
SMP System
CPU
Lo cking
Paging
I nterpro cessor
Com m unicat io n
MPP System
I/O
Query
Web Server
Bro ws er
Inter - and intra Partition Parallelism
CPU
Each SQL Request is Processed by Multiple
Database Partitions and Multiple Nodes in Parallel
Applicat ion Server
Challenges
of Parallel Processing
Performance Management
SMP System
CPU
CPU
Shared Memory
Database partition 1
Disks
Disks
Disks
Disks
CPU
CPU
CPU
CPU
CPU
Shared Memory
Database partition 2
Disks
Disks
Disks
Disks
How to Reduce Response Time?
How to
Improve
balance?
10
Throughput
Minimum Three Nodes with 16 CPUs per Node are
Required to Satisfy Sales Workload’s Throughput
SLO
Load
Marketing
Sales SLO
Order
Tracking
Sales
32
24
16
12
Order
Processing
Number of CPUs per Node
11
Workload Growth
Relative Response Time
Minimum 16 CPUs per Node are Required to Support
Sales Workload Response Time SLO with 50%
Workload and Data Base Size Increase
Current Level of
Workload and DB Size
Load
50% Workload
and DB Size Growth
Sales
Order
Tracking
Marketing
Order
Processing
SLO
Other
32
24
16
12
32
24
16
12
Number of CPUs per each of Three Node
12
Tradeoffs Analysis
During Performance
Management and
Capacity Planning
Index Strategy
Index Strategy
New Index Candidates
Unreferenced Indexes
2003 SBC Performance Management Review
19
14
Data Clustering




In order to improve
performance, and enforce
clustering, DB2 UDB DBA can
create a CLUSTERING index
Clustering will force placement
of inserted rows in a specific
order.
Since indexes contain
information from the table,
every time the table is changed,
it requires modification of the
index.
In order to be able to cluster
future Inserts, each page of the
table should have a significant
amount of free space.
Single Dimensional
Data Clustering
Clustering
Index
On State
Table
Index on
Year
15
Multi-Dimensional Clustering (MDC)

Product
List of all customers who
bought specific product
during several Years
Ye
ar

Customer

Dimension is an Axis Along Which Data
is Physically Organized in MDC Table
Ye
ar

Multi-Dimensional Clustering
(MDC) provides automatic
clustering of data along multiple
dimensions.
Each distinct value of a dimension
with high cardinality will waste
disk space. It also significantly
increases the load time.
MDC load organizes data into
blocks based on dimensions
values.
Each distinct value of a dimension
points to a block.
Blocksize of a tablespace and
cardinality of dimension are
important from point of view of
possibility wasting significant
amount of disk space by MDC.
Customer

Product
List of all Products which
were purchased by specific
customer during specific
Year
Dimension, Slice and Cell
Block/Extend Size, Page Size
Block Index
16
MQT Tradeoffs



Materialized Query Table (MQT)
contains the results computed
from the common parts of the
queries sets
If tables have a lot of updates,
the cost of MQT maintenance can
offset the benefits.
Take into consideration the
additional disk space required
and overhead associated with
maintenance of MQT
Materialized Query Table
Reuse Many Times
Aggregate and
Precompute Once
Join
Table 3
Table 1
Table n
Table 2
17
Partitioning Tradeoffs





Table space is assigned to the database
partition group
Tablespaces exist within partition
groups.
It makes sense to place large, frequently
accessed tables with partition group 2
Small, infrequently accessed tables can
be placed within partition group 1
Using the DB2 replicate feature, DBA can
replicate the small table’s rows across all
DB2 partitions in order to improve
performance of joins.
Evaluate Partitioning Tradeoffs
When to Replicate Small Tables?
Group 1
Part 1
Part 2
Part 3
Part 4
Group 2
Small Table
Big Table
IBM Corporation 2003
IBM Data Management Techn
18
Identify Changes in Pattern of Data Accesses
SQL Type Distribution
9%
1%
7%
Select
Insert
Delete
Other
83%
Join Distribution
15%
1%
0%
30%
2 Way
3 Way
4 Way
5 Way
38%
6 Way
16%
7 Way
About 7% SQL are Inserts, 9% Deletes and 16%
of SQL Join 7 tables!
19
Summarization Candidates
Summarization
Candidate
Summarization
Candidates
Analysis is based on captured SQL only
2003 SBC Performance Management Review

BEZ Systems, All Rights Reserved
The Atlanta DB2 Users Group
20
Tuning







OS Tuning
 Paging space on 2 or more separate
disks with 2x real memory
 Use vmstat/iostat
Database Manager Config Parms



INTRA_PARALLEL
NUM_INITAGENTS/NUM_POOLAGENTS
SHEAPTHRES




DFT_DEGREE
DFT_QUERYOPT
APPLHEAPSZ
SEQDETECT

Use just a few


Small for OLTP
For DSS Sort/HashJoin /Table in memory

Log disks should be on own disks
Database Configuration Parameters
Bufferpools
SORTHEAP
Logging
I/O Configuration Parameters
21
Balance Affects Performance
Response time depends on
utilization of the slowest device.
If one of the devices of MPP
system is consistently over
utilized it can lead to poor
performance of all applications
Unbalanced
How to correct? Consider data
redistribution
Well Balanced
Potential Benefits of Balancing
Resource Utilization – significant
improvement in response time
and throughput
22
SMP, MPP, Cluster, GRID Planning Tradeoffs
UP
# Nodes = 4
OUT
24
Multi-tier Architecture and Federated DBMS
Tradeoffs
Web Server
Application Server
Database Server
Application Server
Database Server
Database Server
25
Federated Access





Independent interface for end users
Combine data from multiple operational systems, data marts or data
warehouses
Add historical perspective to operational data
User or application perform operations with the data or records, rather than
a copy of data, because there is a time lag between current data and records
Saving by reduction of data movement
DB2 Family
IMS/VSAM
DB2
Federated Support
Informix
Teradata
MS SQL Srvr
Sybase
Oracle
26
Factors affecting configuration sizing









Workload, applications and SQL
Load requirements
Number of concurrent users
Table’s sizes
Performance expectations: responsiveness and throughput
Expected growth
Platform options: pSeries, Sun, HP
Memory (1-2 GB per processor, 32 or 64bit)
Disk




Tables, Indexes, MQT, Tempspace, etc in TB and number of spindles
(6-10 for each CPU plus drives for Logs)
Raw dara * 4 (unmirrored)
Raw data * 5 (RAID5)
Raw data * 8 (mirrored)
27
Evaluate Partitioning Tradeoffs
LPAR Structure at Night
During ETL Process
LPAR Structure
During Day time
PX
PX
PX
PX
AIX LPAR 2 4 CPUs
PX
PX
PX
PX
PX
PX
PX
PX
PX
PX
PX
PX
AIX LPAR 2 4 CPUs
AIX LPAR 2
28 CPUs
AIX LPAR 2
28 CPUs
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
DB2
AIX LPAR 1 4 CPUs
AIX LPAR 1 28CPUs
AIX LPAR 1 4 CPUs
Tradeoff
Increase in number of
partitions and tailoring
partitioning to workload
requirements can affect
performance.
What is the optimum
partitioning scheme for
specific workload.
AIX LPAR 1 28 CPUs
DB
DB
ETL
ETL
DB
DB
ETL
ETL
28
Evaluate Software Configuration Parameters




Over 50 parameters can be changed
without stopping the database or the
database manager
Buffer pool sizes can be changed
dynamically without stopping and
starting the database.
Automatic Connection Pooling with DB2
UDB ESE
For decision support workloads, the level
of intra- parallelism should be set as
maximum, and for OLTP workload it is
better to set it OFF.
29
Disk Storage Configuration Tradeoffs

You have to decide how much disk
storage will be required to support
growing business needs.
 raw data, working space, temporary
space, indexes, materialized query tables,
summary tables, multi dimensional cubes,
etc.
 RAID technology needs disk space as well.

You have to configure disks to satisfy
performance requirements.
 Disk capacity should be balanced with CPU
capacity.

Capacity planners typically select at least
6 disks to store table data per CPU in an
MPP environment.
 Systems and log files should be placed on
separate disks.
 The number of adapters, storage
controllers, and channels should be
sufficient to support the expected
workload and database size growth.
30
New Application
Implementation
New Application Implementation Concerns
• Risk that new application will not perform as
expected
• Risk that new application implementation will
increase existing data warehouse application
response time
• Uncertainty if data warehouse will be able to
satisfy business needs without unplanned
additional upgrade
32
Major Steps in New Application Certification
Step 1.
Model of the New Application on Development Machine Before
Implementation in Production
Step 2.
Model Existing Applications on Production Machine
Step 3.
Copy New Application Workload to Model of the Production
Machine
Step 4.
Predict New Application Implementation Impact
Step 5.
Justify Hardware Upgrade
Step 6.
On going Measurement and Validation of Actual Results Against
Forecast
33
New Application Profile
Name
Profile
Month 1
Month 2
…
Month 12
Arrival Rate (Req K / hour)
20
50
60
160
Avg Response Time (sec)
8
20
21
62
CPU (MIPS)
50
200
220
420
I/O Rate (1/sec)
30
150
170
480
ITR (Req/CPU sec)
100
300
330
630
SOB (IO/CPU sec)
20
100
100
100
%S
70
70
70
70
%I
10
10
10
10
%U
10
10
10
10
%D
10
10
10
10
%Join
30
30
30
30
%Join 2 Tables
80
80
80
80
%Join 3 Tables
10
10
10
10
%Join 4 Tables
2
2
2
2
10
10
10
10
100
300
400
1400
%SUM
Sum Table Size (GB)
34
Existing Applications/Workloads Profile
35
Data Use Profile




Frequency of
accessing different
tables and views
Distribution of the
volume of data
accessed and
retrieved by
application requests
Identification of the
critical tables and
tuning opportunities
Table sizes and disk
space utilization
36
Distribution of SQL Types
•
•
•
Frequency of
Select/Insert/Update/Del
ete operations
Frequency of Join
operations
Frequency of
summarization and
aggregation functions
performed with data,
Identification of the
critical SQL and tuning
opportunities
37
New Application
Implementation
Predicted Impact of Implementing a New Application Example
38
Comparing Actual Results with Expected
Workload
Name
Sales
# Req /
CPU sec
Before
# I/Os /
CPU sec
Before
# Req / CPU
sec
After
# I/Os /
CPU sec
After
Estimated Savings
Per Year ($)
118.8
2287.3
126.5
3185.7
5,400
2276.1
9223.7
2382.7
9495.3
3,000
Finance
4.7
15717
6.4
16540.6
16,200
HR
0.3
8.9
1.9
13.9
600
Batch
2.7
4602.5
2.7
5446.3
1,200
System
6.3
140.5
7.7
58.4
134
Marketing
39
Summary
Development
Design
Setting
Expectation
Managing
Expectations
Response Time
Managing Expectations Reduces Uncertainty and Risk
Predicted New
Application
Impact
Actual
Results
SLO
Production
Workload
Expectations
Date
40
Additional
Resources, White
Papers, Case Studies,
Presentations,
Webinars, URLS
References

BEZ Presentations


Customer Case Studies


BEZPlus DB2 UDB
Education and Consulting


BEZ Performance User Group www.bpug.org
Software


www.bez/com/bezcustomers/customercasestudies
BPUG


www.bez.com/spotlight/spm
Performance Management and Capacity Planning for DB2 UDB
www.bez.com/services
Meta Group on Strategic Performance Management and BEZ
www.bez.com/spotlight/spm
42
Interesting URLS







www.bez.com
www.software.ibm.com/data

http://www-3.ibm.com/software/data/db2/benchmarks



http://www.almaden.ibm.com/
http://www.almaden.ibm.com/software/dm/SMART/leo.shtml (Learning Optimizer)
http://www.almaden.ibm.com/software/dm/SMART/pa.shtml (Partitioning Advisor)

www.tpc.org

www.top500.com

http://www.adtmag.com/article.asp?id=7179


http://www-3.ibm.com/software/data/pubs
http://www-3.ibm.com/software/data/pubs/papers/ White papers
IBM Almaden Research center
DB2 UDB ESE TPC-C and TPC-H
Top 500 Computers in the world
DB2 scales to 1000 nodes on LINUX
DB2 Pub Center, includes ONLINE Technical library
43