110 likes | 129 Views
Workload Management HBC Case Study IRMAC, January 2008. 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
E N D
Workload Management HBC Case StudyIRMAC, January 2008 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