Transcript Current State of the Hbc Brand

Workload Management
HBC Case Study
IRMAC, January 2008
Enter Date in Title Master
Shelley
Perrior -DBA team lead
Agenda
Agenda:
Corporate and presenter background
WLM 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