Workload management in data warehouses:

basesprocketData Management

Oct 31, 2013 (3 years and 9 months ago)

90 views

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

© 2008 IBM Corporation

®


WLM methodology

Paul McInerney

IBM Canada

paulmci@ca.ibm.com

IBM Software Group | Information Management software

January 2008

4

WLM in data warehouses | Overview and case studies

IBM Toronto Lab

Home of DB2 LUW (Linux, Unix, and Windows) product development …

… and much else …

IBM Software Group | Information Management software

January 2008

5

WLM in data warehouses | Overview and case studies

User
-
Centered Design (UCD) team


Hands
-
on usability testing, customer visits, design protoyping, web surveys, etc.

IBM Software Group | Information Management software

January 2008

6

WLM in data warehouses | Overview and case studies

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)


IBM Software Group | Information Management software

January 2008

7

WLM in data warehouses | Overview and case studies

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…


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

IBM Software Group | Information Management software

January 2008

8

WLM in data warehouses | Overview and case studies

Aspects of WLM

Type of control

Scope of work to control

Goals, needs, planning

(logical design)

Types of WLM
control needs &
goals

Types of work
categories to control

Design & implementation

(vendor specific physical
design)

Types of control
mechanisms

Vendor
-
specific control
mechanisms and their
settings

Vendor
-
specific objects
for defining work
categories

IBM Software Group | Information Management software

January 2008

9

WLM in data warehouses | Overview and case studies

Major categories of WLM 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:

Set an operating
envelope that defines what types of
requests are allowed to run and
how much work is allowed to run
concurrently.

Protection goals


Protect from individual "bad" requests


Protect from overload

Business value goals


Resource sharing goals


Priority goals

IBM Software Group | Information Management software

January 2008

10

WLM in data warehouses | Overview and case studies

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


Seasonal/holiday retail peaks


Queues for any service

IBM Software Group | Information Management software

January 2008

11

WLM in data warehouses | Overview and case studies

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.


Customer account balance lookup vs. weekly management report of customer balances


Post office: first class vs. third class

IBM Software Group | Information Management software

January 2008

12

WLM in data warehouses | Overview and case studies

Aspects of WLM
-

recap

Type of control

Scope of work to control

Goals, needs, planning

(logical design)

Types of WLM
control needs &
goals

Types of work
categories to control

Design & implementation

(vendor specific physical
design)

Types of control
mechanisms

Vendor
-
specific control
mechanisms and their
settings

Vendor
-
specific objects
for defining work
categories

IBM Software Group | Information Management software

January 2008

13

WLM in data warehouses | Overview and case studies

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

IBM Software Group | Information Management software

January 2008

14

WLM in data warehouses | Overview and case studies

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

IBM Software Group | Information Management software

January 2008

15

WLM in data warehouses | Overview and case studies

Summary

Type of control

Scope of work to control

Goals, needs,
planning

(logical design)

Protection goals:

Protect from "bad" requests

Protect from overload

Business value goals:

Resource sharing goals

Priority goals

Work source categories:

application

business group

Work type categories:

e.g., large query

Design &
implementation

(vendor specific
physical design)

Map to product control mechanisms
and their settings.
Generic categories:

Resource allocation & priority
controls

Individual user request controls

Concurrency controls

Map to product objects for
defining work categories