Enter Date in Title Master

Workload Management HBC Case Study

IRMAC, January 2008

Shelley Perrior -DBA team lead

Agenda

Agenda:

Corporate and presenter backgroundWLM case study

System profile Business goals WLM configuration

Other points

About HBC

About HBC:Hbc – the Hudson’s Bay Company

Canada’s oldest and largest diversified general merchandise retailer

Incorporated on May 2, 1670 More than 580 Stores in all 10 Provinces across

Canada 5 Retail formats, along with an eRetail portal

The Bay – Department Zellers – Mass Home Outfitters – Specialty home Designer Depot – Deals outlet Fields – Discount

70,000 associates focused on exceptional customer service

Privately owned as of 2006

Database Group - HBC

Presenter Background:

• Supervisor database administration for all databases

• Teradata technical lead, Teradata Certified master

• Liaison with business and application teams

• Extensive experience in system and application performance tuning on Teradata

Configuration Of Data Warehouse:

• Database is Teradata V2R6.1.1• BI reporting tool is Microstrategy 8.0• Current production system is a 12 node 5380

system with 7.5 TB of data• Dev system is a 4 node 5350 system.• Both Prod and Dev databases are at V2R6.1.1• Current workload consists mainly of a Business

Intelligence application using Microstrategy for reporting and our Fraud Control application. Also have limited adhocs running as well.

• We use TDWM and Priority scheduler to monitor and control workload.

Business goals to address with WLM

Business goals to address with WLM

Goals in priority order: Goal 1: Ensure a response time of less than 10

seconds from the POS for Returns transaction validation

Goal 2: Ensure 95% of all Microstrategy queries finish in under 10 minutes

Goal 3: Ensure ETL processing completes within specified batch window

Goal 4: Accommodate effectively, emergency requests and changes in workload (daily, monthly, weekly etc)

Problems if WLM not used:

Heavy workload from Microstrategy can threaten the response times for the higher priority work.

Potential runaway queries may monopolize system resources

Changes in workload may not be handled effectively (i.e.. batch runs too long and impacts business reporting)

WLM Configuration:

Work categories (with different WLM controls) Transaction load and verification from POS Microstrategy Reporting ETL Other

WLM controls: Resource allocation & priority controls CPU allocation: Ensure a percentage reserved for

high priority work (expedited workload) Use milestones to push short queries through faster Spool space usage limits Allocations change by time of day (for ETL) controls set up by user account code

WLM Configuration cont’d:

Individual user request controls Max allowable spool priority of query by user account code

Concurrency controls Limit number of reports per individual, total number

on system, no batch in certain windows.

Emergency Query privileges Allow for special situations when queries may need a

higher priority.

Other points:WLM implementation process:

Work with application to identify business requirements

Identify query grouping (i.e.. application, type of query, etc)

Collect and analyze data on resource usage implement WLM controls and monitor adjust as required until desired results are met Set up SLA set up monitoring and alerts

Lessons Learned:

- Workload management is an ongoing process

- Good communication is necessary between applications and database team

- SLA’s are necessary

- Monitoring critical

- Historical data important