DB2負載管理(WLM)(二)
與前一測試不同之處在於,在影射DML操作到服務類時,根據估計成本進行調整。
1,建立服務類,層次關係如下:
------------WLM_TIERS
-- +----------+-----------+
-- | | |
--WLM_SHORT WLM_MEDIUM WLM_LONG
-- Create service superclass WLM_TIERS
CREATE SERVICE CLASS WLM_TIERS@
-- Create service subclasses WLM_SHORT, WLM_MEDIUM, WLM_LONG
CREATE SERVICE CLASS WLM_SHORT UNDER WLM_TIERS@
CREATE SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS@
CREATE SERVICE CLASS WLM_LONG UNDER WLM_TIERS@
2,建立服務類重新影射閾值
--WLM_SHORT --> WLM_MEDIUM --> WLM_LONG
-- Create thresholds to remap activities from WLM_SHORT to WLM_MEDIUM
-- to WLM_LONG service subclasses based on processor time used in service class
CREATE THRESHOLD WLM_TIERS_REMAP_SHORT_TO_MEDIUM FOR
SERVICE CLASS WLM_SHORT UNDER WLM_TIERS ACTIVITIES
ENFORCEMENT DATABASE PARTITION WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS
REMAP ACTIVITY TO WLM_MEDIUM@
CREATE THRESHOLD WLM_TIERS_REMAP_MEDIUM_TO_LONG FOR
SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS ACTIVITIES
ENFORCEMENT DATABASE PARTITION WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS
REMAP ACTIVITY TO WLM_LONG@
-- Create work class set WLM_TIERS_WCS
CREATE WORK CLASS SET WLM_TIERS_WCS
( WORK CLASS WLM_SHORT_DML_WC WORK TYPE DML,
WORK CLASS WLM_MEDIUM_DML_WC WORK TYPE DML,
WORK CLASS WLM_LONG_DML_WC WORK TYPE DML,
WORK CLASS WLM_CALL_WC WORK TYPE CALL,
WORK CLASS WLM_OTHER_WC WORK TYPE ALL)@
3,建立工作類集,工作動作集
-- Create work action set WLM_TIERS_WAS to map activities grouped under
-- each work class in work class set WLM_TIERS_WCS to the corresponding
-- service subclass.
CREATE WORK ACTION SET WLM_TIERS_WAS FOR SERVICE CLASS WLM_TIERS
USING WORK CLASS SET WLM_TIERS_WCS
( WORK ACTION WLM_SHORT_DML_WA ON WORK CLASS WLM_SHORT_DML_WC
MAP ACTIVITY TO WLM_SHORT,
WORK ACTION WLM_MEDIUM_DML_WA ON WORK CLASS WLM_MEDIUM_DML_WC
MAP ACTIVITY TO WLM_MEDIUM,
WORK ACTION WLM_LONG_DML_WA ON WORK CLASS WLM_LONG_DML_WC
MAP ACTIVITY TO WLM_LONG,
WORK ACTION WLM_CALL_WA ON WORK CLASS WLM_CALL_WC
MAP ACTIVITY TO WLM_SHORT,
WORK ACTION WLM_OTHER_WA ON WORK CLASS WLM_OTHER_WC
MAP ACTIVITY TO WLM_MEDIUM )@
4,設定不同服務類代理優先順序
-- Create a stored procedure to determine operating system and to set service
-- class agent priorities. The operating system must be determined before
-- setting agent priority because the range of values for agent
-- priority differs between Windows and UNIX operating systems.
CREATE PROCEDURE checkPlatformAndSetAgentPriority()
LANGUAGE SQL
--------------------------------------------------------
-- SQL Stored Procedure checkPlatformAndSetAgentPriority
--------------------------------------------------------
BEGIN
DECLARE isWindows VARCHAR(256);
DECLARE stmt VARCHAR(300);
SELECT os_name INTO isWindows
FROM SYSIBMADM.ENV_SYS_INFO
WHERE UCASE(os_name) LIKE '%WIN%' OR UCASE(os_name) LIKE '%NT%';
-- *PROPERTY* (UNIX) Set agent priority for service classes on UNIX
-- operating systems. A lower numeric value means higher priority. A higher
-- numeric value means lower priority. The valid range for agent priority on
-- UNIX is -20 to 20.
IF isWindows IS NULL
THEN
SET stmt = 'ALTER SERVICE CLASS WLM_SHORT UNDER WLM_TIERS AGENT PRIORITY -10';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS AGENT PRIORITY 0';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS WLM_LONG UNDER WLM_TIERS AGENT PRIORITY 10';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS SYSDEFAULTSYSTEMCLASS AGENT PRIORITY -15 ';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS SYSDEFAULTMAINTENANCECLASS AGENT PRIORITY 15';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
-- #PROPERTY# (Windows) Set agent priority for service classes on Windows
-- operating systems. A higher numeric value means higher priority. A lower
-- numeric value means lower priority. The valid range for agent priority on
-- Windows is -6 to 6.
ELSE
SET stmt = 'ALTER SERVICE CLASS WLM_SHORT UNDER WLM_TIERS AGENT PRIORITY 3';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS AGENT PRIORITY 0';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS WLM_LONG UNDER WLM_TIERS AGENT PRIORITY -3';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS SYSDEFAULTSYSTEMCLASS AGENT PRIORITY 5 ';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
SET stmt = 'ALTER SERVICE CLASS SYSDEFAULTMAINTENANCECLASS AGENT PRIORITY -5';
PREPARE s1 FROM stmt;
EXECUTE s1;
COMMIT;
END IF;
END@
-- Call the checkPlatformAndSetAgentPriority stored procedure to
-- set agent priorities for service classes
CALL checkPlatformAndSetAgentPriority()@
-- Drop the checkPlatformAndSetAgentPriority stored procedure
DROP PROCEDURE checkPlatformAndSetAgentPriority@
5,設定不同服務類預取(PREFETCH)優先順序
-- #PROPERTY# Set prefetch priority for service classes. Valid values for
-- prefetch priority are HIGH, MEDIUM, LOW or DEFAULT (MEDIUM).
ALTER SERVICE CLASS WLM_SHORT UNDER WLM_TIERS PREFETCH PRIORITY HIGH@
ALTER SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS PREFETCH PRIORITY MEDIUM@
ALTER SERVICE CLASS WLM_LONG UNDER WLM_TIERS PREFETCH PRIORITY LOW@
ALTER SERVICE CLASS SYSDEFAULTSYSTEMCLASS PREFETCH PRIORITY HIGH@
ALTER SERVICE CLASS SYSDEFAULTMAINTENANCECLASS PREFETCH PRIORITY LOW@
6,建立服務類重新影射閾值並記錄事件監視器日誌
-- #PROPERTY# Set the maximum in service class processor time before
-- remapping and the checking period. The maximum in service class processor
-- time determines how much processor time an activity can consume in
-- a service class before being remapped to the target service class.
-- For example, if you want an activity to remain in service class
-- WLM_SHORT for a shorter period before being remapped to WLM_MEDIUM,
-- decrease the CPUTIMEINSC threshold value for WLM_TIERS_REMAP_SHORT_TO_MEDIUM.
-- The checking period determines how long to wait between checks for threshold
-- violation. For serial ESE instances, set the checking period to be
-- the same as the processor time before remap. For DPF or SMP instances,
-- set a lower value for the checking period than the processor time
-- before remap.
--
-- When one of these thresholds is violated and an activity is remapped
-- to the next service subclass, an event monitor record is written to
-- the threshold violations event monitor. This way, you can see
-- how many activities are moved between the tiers service subclasses.
-- Logging an event monitor record incurs a small performance cost. Once
-- the system is tuned and the threshold violation event monitor records
-- are no longer needed, simply remove the 'LOG EVENT MONITOR RECORD'
-- clause from the ALTER THRESHOLD statements.
ALTER THRESHOLD WLM_TIERS_REMAP_SHORT_TO_MEDIUM WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS
REMAP ACTIVITY TO WLM_MEDIUM LOG EVENT MONITOR RECORD@
ALTER THRESHOLD WLM_TIERS_REMAP_MEDIUM_TO_LONG WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS
REMAP ACTIVITY TO WLM_LONG LOG EVENT MONITOR RECORD@
7,設定工作類集參考估算成本
-- #PROPERTY# Set work class properties for work class set WLM_TIERS_WCS.
-- This setting determines the initial mapping of DML activites to the
-- service subclasses based on estimated cost. For example, if you want
-- DML activities with a higher estimated cost to map to service class
-- WLM_SHORT instead of WLM_MEDIUM initially, increase the TO value of
-- WLM_SHORT_DML_WC and decrease the corresponding FROM value of WLM_MEDIUM_DML_WC.
-- Work class set properties:
--
-- Work Class Estimated Cost in
-- Timerons (From/To)
-- ------------------------------------
-- WLM_SHORT_DML_WC 0/1000
-- WLM_MEDIUM_DML_WC >1000/100000
-- WLM_LONG_DML_WC >100000/infinity
-- WLM_CALL_WC *see note below*
-- WLM_OTHER_WC *see note below*
--
-- Note:
-- Work classes WLM_CALL_WC and WLM_OTHER_WC contain CALL activities and other
-- activities that do not have cost estimates. Estimated cost is available
-- only for DML statements. Non-DML activities such as DDL and LOAD will fall
-- under the WLM_OTHER_WC work class. Activities grouped under WLM_CALL_WC are
-- mapped to service class WLM_SHORT initially. Activities grouped under
-- WLM_OTHER_WC are mapped to service class WLM_MEDIUM and will not get
-- remapped.
ALTER WORK CLASS SET WLM_TIERS_WCS
ALTER WORK CLASS WLM_SHORT_DML_WC FOR TIMERONCOST FROM 0 TO 1000
ALTER WORK CLASS WLM_MEDIUM_DML_WC FOR TIMERONCOST FROM 1000 TO 100000
ALTER WORK CLASS WLM_LONG_DML_WC FOR TIMERONCOST FROM 100000 TO UNBOUNDED@
8,啟用工作負載管理器
-- Alter SYSDEFAULTUSERWORKLOAD to map workload to WLM_TIERS service class
ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD SERVICE CLASS WLM_TIERS@
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18922393/viewspace-702326/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- DB2負載管理(WLM)(一)DB2負載
- DB2負載管理(WLM)(三)DB2負載
- DB2 V9.5工作負載管理(二)DB2負載
- DB2 WLMDB2
- DB2 V9.5工作負載管理(五)DB2負載
- DB2 V9.5工作負載管理(六)DB2負載
- DB2 V9.5工作負載管理(七)DB2負載
- 負載均衡技術(二)———常用負載均衡服務介紹負載
- Nginx 學習系列(二) ------------- 負載均衡Nginx負載
- Nginx 學習系列(二) ————- 負載均衡Nginx負載
- springboot+zuul(二)------智慧負載Spring BootZuul負載
- SpringCloud(二):服務呼叫與負載均衡SpringGCCloud負載
- RAC負載均衡的簡單測試(二)負載
- Nginx 高階篇(二)什麼是負載均衡Nginx負載
- NLB網路負載均衡管理器詳解負載
- 負載均衡負載
- linux負載均衡總結性說明(四層負載/七層負載)Linux負載
- gRPC負載均衡(客戶端負載均衡)RPC負載客戶端
- gRPC負載均衡(自定義負載均衡策略)RPC負載
- 2.8 使用資料庫服務管理應用負載資料庫負載
- Azure Front Door(二)對後端 VM 進行負載均衡後端負載
- 粘性會話負載均衡 - MQTT Broker 叢集詳解(二)會話負載MQQT
- php列印負載函式、Linux awk列印負載PHP負載函式Linux
- NGINX 負載均衡Nginx負載
- WebSocket負載均衡Web負載
- IP負載均衡負載
- 【Nginx】負載均衡Nginx負載
- nginx負載均衡Nginx負載
- Nginx負載配置Nginx負載
- Camel:軟負載管理中介軟體 通過介面及介面管理Nginx叢集負載Nginx
- 負載均衡技術(一)———負載均衡技術介紹負載
- 解密負載均衡技術和負載均衡演算法解密負載演算法
- JMeter分散式壓測/JMeter負載新增/jmeter負載均衡/jmeter Windows系統壓測負載新增JMeter分散式負載Windows
- Azure Application Gateway(二)對後端 VM 進行負載均衡APPGateway後端負載
- 資料庫負載急劇提高的應急處理(二)資料庫負載
- 常用的DB2管理命令DB2
- 淺析基於雲的DNS管理與負載均衡技術DNS負載
- 動態質量管理之二(轉載)