Transcript IBM Workplace - Productivity Best Practices

Workload management in data warehouses:
Methodology and case studies
presentation to
Information Resource Management Association of Canada
January 2008
Paul McInerney, IBM, DB2 LUW product developer
Shelley Perrior, HBC, Data warehouse team lead
Sorina Faur, BMO Financial Group, Database Development Manager
Agenda
• Purpose
– Share lessons and experiences related to workload
management in data warehouses
• Agenda Items
–
–
–
–
WLM methodology
HBC experience
BMO experience
Panel discussion/Q&A
®
WLM methodology
Paul McInerney
IBM Canada
[email protected]
© 2008 IBM Corporation
IBM Software Group | Information Management software
IBM Toronto Lab
Home of DB2 LUW (Linux, Unix, and Windows) product development …
… and much else …
January 2008
WLM in data warehouses | Overview and case studies
4
IBM Software Group | Information Management software
User-Centered Design (UCD) team
Hands-on usability testing, customer visits, design protoyping, web surveys, etc.
January 2008
WLM in data warehouses | Overview and case studies
5
IBM Software Group | Information Management software
Background on presenter and presentation
 Presenter experience relevant to WLM
• Development of DB2 workload manager (DB2 9.5) and Query
Patroller (DB2 version 8)
• Work with customers to optimize their use of WLM features
• Publishing of best practices
 Basis of this presentation
Best Practices for WLM Control Configuration
(a white paper; ~ 60 pages)
January 2008
WLM in data warehouses | Overview and case studies
6
IBM Software Group | Information Management software
A definition of WLM
 Workload management
– WLM is mostly concerned with processing user requests to maximize
business value. That is, WLM functionality considers the business
value of each user request and handles it accordingly.
 Examples
– Protect the system from “runaway” queries
– Achieve a response time objective (SLA) for a critical transaction
– Allocate resources among business groups
– Lots more…
•
•
•
•
•
•
January 2008
Run short queries with a more consistent elapsed time
Prevent particular users from monopolizing computing resources
Prevent peak workload times from overloading computing resources
Run costly queries at a scheduled time
Hold back resources for use in a future phase of system rollout
Allow emergency rush jobs to commandeer resources
WLM in data warehouses | Overview and case studies
7
IBM Software Group | Information Management software
Aspects of WLM
Goals, needs, planning
(logical design)
Design & implementation
(vendor specific physical
design)
January 2008
Type of control
Scope of work to control
Types of WLM
control needs &
goals
Types of work
categories to control
Types of control
mechanisms
Vendor-specific objects
for defining work
categories
Vendor-specific control
mechanisms and their
settings
WLM in data warehouses | Overview and case studies
8
IBM Software Group | Information Management software
Major categories of WLM goals
Business value goals
 Resource sharing goals
 Priority goals
Business value: Optimize quality of
service (usually measured by
response time) by considering the
business value of each work
request. This category addresses
WLM within the confines of the
operating envelope set by
protection management polices.
Protection goals
 Protect from individual "bad" requests
 Protect from overload
January 2008
Protection: Set an operating
envelope that defines what types of
requests are allowed to run and
how much work is allowed to run
concurrently.
WLM in data warehouses | Overview and case studies
9
IBM Software Group | Information Management software
WLM protection goals
 Protect from individual "bad" requests
– Conditions addressed & actions taken
• Some user requests are “bad”
• Stop such requests from starting or from proceeding or starve them
– Examples & analogies
• Query that scans a fact table due to a typo in the query
• Gardening: weeds in a garden
•
•
User ignores policy to NOT run large query during “rush hour”.
Transportation: speeding car in a school zone -> speed bumps
 Protect from overload
– Conditions addressed & actions taken
• Too much work can be submitted simultaneously; trying to process all requests
concurrently can degrade system efficiency or stability
• Queue excess requests or triage requests based on size or priority
– Examples & analogies
• Monday morning rush hour
• Gardening: Limiting the # of flowers planned based on the garden area
•
•
January 2008
Seasonal/holiday retail peaks
Queues for any service
WLM in data warehouses | Overview and case studies
10
IBM Software Group | Information Management software
WLM business value goals
 Resource sharing goals
– Conditions addressed & actions taken
• Multiple requestor categories (business groups, applications) use the warehouse.
• Allocate resources to requestor categories (min, max, target share).
– Examples & analogies
• The marketing dept. pays for 20% of the system but submits 80% of the workload.
• Gardening; Allocate finite garden space to flowers vs. vegetables
•
•
When Joe from marketing runs his monster queries, other requests suffer.
Admin assistant: Time share requests from multiple executives
 Priority goals
– Conditions addressed & actions taken
• Some work requests are more important and urgent than others
• Provide multiple levels of service, consider priority in resource allocation or queue
management
– Examples & analogies
• Emergency user request
• Gardening: Give your favorite plants the premium location in the garden.
•
•
January 2008
Customer account balance lookup vs. weekly management report of customer balances
Post office: first class vs. third class
WLM in data warehouses | Overview and case studies
11
IBM Software Group | Information Management software
Aspects of WLM - recap
Goals, needs, planning
(logical design)
Design & implementation
(vendor specific physical
design)
January 2008
Type of control
Scope of work to control
Types of WLM
control needs &
goals
Types of work
categories to control
Types of control
mechanisms
Vendor-specific objects
for defining work
categories
Vendor-specific control
mechanisms and their
settings
WLM in data warehouses | Overview and case studies
12
IBM Software Group | Information Management software
Types of WLM control mechanisms
 Resource allocation & priority controls
• CPU allocation (min, max, share)
• Priority-based engine queues (e.g. I/O prefetcher)
• Etc.
 Individual user request controls
• Predictive vs. reactive controls
• Attributes to control
– General: Overall cost, elapsed time, executed time
– Specific request attributes: e.g., rows read, rows returned
 Concurrency controls
• Limit the amount of concurrent work activities
– Across user work requests or within a single request
– At various levels/abstractions: connections, statements executing, etc.
• Queue items when requests exceed concurrency limit
January 2008
WLM in data warehouses | Overview and case studies
13
IBM Software Group | Information Management software
WLM work categories
 Work source categories
• application, e.g., Sales App vs. Business Objects
• business group, e.g., Marketing Dept. vs. Finance Dept.
 Work type categories
• size: Large query vs. short-running query. vs. regular query
• other characteristics
January 2008
WLM in data warehouses | Overview and case studies
14
IBM Software Group | Information Management software
Summary
Goals, needs,
planning
(logical design)
Type of control
Scope of work to control
Protection goals:
Work source categories:
Protect from "bad" requests
application
Protect from overload
business group
Work type categories:
Business value goals:
e.g., large query
Resource sharing goals
Priority goals
Design &
implementation
(vendor specific
physical design)
Map to product control mechanisms
and their settings. Generic categories:
Map to product objects for
defining work categories
Resource allocation & priority
controls
Individual user request controls
Concurrency controls
January 2008
WLM in data warehouses | Overview and case studies
15