master db performance with wlm
TRANSCRIPT
DB2’S GOT
MASTER DB PERFORMANCE WITH WLM
Raja
DB2 V10.1 on AIX
Hybrid multi TB Database
Tables with 3080761021 rows (> 3 Billion)
Failure of reports (36%)
Business user (Sponsor) queries – SLOW
(41%)
Problem Statement
First Impression
INVESTIGATION
H/W as per Firm standards
Load on DB increased
DBA’s killing low priority sessions
Resource hogging on the server
Business users were NOT happy
DB2 WLM is a functionality that o Maintains SLA’so Prevents resource hoggingo Optimizes system to avoid overload
oControls, monitors and analyses DB activity
Workload Manager …… in a nutshell
DB2 WLM introduces the entities called oService Super Class – Logical Grouping of SCoService classo Thresholdo Workloado Work Class Seto Work Action Set
WLM…… continued
Service class - Applies Conditions
WLM…… continued
Threshold – Sets Limits
DATA TAG – Tag for the data (New Feature in 10.1)o CREATE TABLESPACE "VOTE_4RAJA“
.... .... DATA TAG 1...;o ALTER TABLESPACE "VOTE_4RAJA“
... ... DATA TAG 1 ....;
WLM……new functionality
CREATE SERVICE CLASS CLASS1 CREATE SERVICE CLASS “LOW_PRIORITY“ UNDER “CLASS1“
.....
CPU LIMIT 25
PREFETCH PRIORITY LOW
BUFFERPOOL PRIORITY LOW; CREATE SERVICE CLASS “HIGH_PRIORITY“ UNDER
“CLASS1” …..
CPU LIMIT NONE
PREFETCH PRIORITY HIGH
BUFFERPOOL PRIORITY HIGH
WLM DDL
CREATE THRESHOLD "LOW_TO_HIGH"
FOR SERVICE CLASS “LOW_PRIORITY"
WHEN DATATAGINSC IN (1,...)
REMAP ACTIVITY TO “HIGH_PRIORITY“
ALTER THRESHOLD ………
……………………………
WHEN DATATAGINSC IN (1,...)
REMAP ACTIVITY TO “HIGH_PRIORITY“
WLM DDL
WLM……continued
LOW_PRIORITY SC
HIGH_PRIORITY SC
THRESHOLD
NOYES
DATA TAG TBSP
Optimizer
First ImpressionBest Impression
Streamlined resource usage
Sponsor queries given priority – No failures
Reports generation for priority data on time
New Features offer multiple solutions
Happy Customer
Useful Link :http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.wn.doc/doc/c0052271.html
Thank You
Twitter : raja_iafLinkedin : www.linkedin.com/in/lrajasekhar/