db2 performance tuning - epvtech.com
Post on 14-Mar-2022
11 Views
Preview:
TRANSCRIPT
DB2 Performance Tuning XVI EPV USER GROUP
Rome 11 October 2018
ROBERTO GIOI
Responsabile Capacity ed Ottimizzazione
2
MPS and EPV
• MPS has been using EPV software since 2006. Started as support tool to the
sysprogs, from 2009 in Capacity Management Dept.
• Products installed: EPV zParser, EPV for z/OS, EPV Graph for z/OS and
EPV for Db2
• Custom application developed by EPV - EPVMPS - for business accounting
reports purposes
• All the performance & tuning tasks examined in this document rely on
multiple metrics, most of them provided by EPV products
• The tables presented are snapshots of the standard EPV HTML tables
• All the graphs are created starting from the data collected in EPV DBs. MPS
has built an extensive set of self-updating daily excel reports from EPV DBs
7
Findings on main Production Subsys…
• Increase DB2 address space priority in DBSTC + IRLM in SYSTC
• Reduce service class periods for consistency
• Perform Rebind in order to remove invalid/bypassed S-proc
• Increase Plan and Package Auth Cache (AUTHCACHE, CACHEPAC)
• Increase memory in all systems (several go low)
• Increase CF structures size in order to reduce/eliminate reclaims
• Many Buffer Pools to be Page-fixed
• Huge Buffer Pools (>6M pages) to be split up
• Set TRACKMODE=NO
• Define more/larger 4K and 32K workfiles
8
…and others
• Increase DB2 address space priority in DBSTC + IRLM in SYSTC
• Reduce service class periods for consistency
• Set consistent BP definition within data sharing groups
• Increase log buffers (OUTBUFF)
• Increase EDM_SKELETON _POOL
• Increase MAXDS
• Increase NUMLKTS e NUMLKUS
• Disable REAL_STORAGE_MANAGEMENT in all DB2s (CRIT_SIT)
• Put Workfiles and DGTT in separate BP from other objects
• Set VDWT by number of buffers rather than by % of BP size
9
Deployment schedule for main DB2 subsys
LPAR: OS0A
· 14 January RSU application, ZPARM, Rebind
· 24 January 18:00 High Performance DBAT
LPAR: OS0U
· 28 January RSU application, ZPARM, Rebind, 2 new BPs
· 2 February 18:00 High Performance DBAT
LPAR: CICS e PROD
· 4 February RSU application, ZPARM, Rebind, 2 new BPs
· 18 February 02:00 High Performance DBAT
10
DB2 address spaces findings
LPAR: OS0A
RSU application, ZPARM, Rebind - Prime Time shift:
DB2 Master CPU -20%
High Performance DBAT enablement - Prime Time shift:
average DDF call single-execution CPU -20%
LPAR: OS0U
RSU application, ZPARM, Rebind, 2 new BPs - in Prime Time shift:
DB2 Master CPU -22%
High Performance DBAT enablement - Prime Time shift:
average DDF call single-execution CPU -6,5%
12
DB2 subsys results: CICS LPAR
· RSU application, ZPARM, Rebind - Prime Time shift:
DB2 Master CPU -26% ~ -53 mips/hour usage
13
DB2 subsys results: LPAR CICS
High Performance DBAT enablement - Prime Time Shift:
average DDF call single-execution CPU -10%
on 28 million transactions/hour ~ -250 mips/hour usage
16
SMT CICS and OS0U LPARs
In the 8-18 hour shift (full day), zIIP Eligible average mips usage (aka
IIPCP: processor time used by zIIP eligible transactions running on
general purpose processors) decreased by about 36 mips and the
ratio between zIIP Eligible vs. zIIP Executed dropped from 1,3% to
0,4%
During zIIP peak hours, zIIP Eligible dropped by 140 mips and the
ratio between zIIP Eligible/zIIP Executed dropped from 3,9% to 0,4%
At hour 8, opening time for the Bank’s branches, zIIP Eligible
decreased by about 250 mips on CICS-OS0U LPARs
19
SMT LPAR DBM2
In the 8-18 hours shift (full day), zIIP Eligible average mips usage (aka
IIPCP: processor time used by zIIP eligible transactions running on
general purpose processors) decreased by over 100 mips
The ratio between zIIP Eligible/zIIP Executed dropped from 10% to 2%
22
Conclusions
More than 300 mips consumption reduction in Prime Time Shift
Better and more stable response time and throughput
27
Migration from
DB2 v.10 to
DB2 v.11 CM
Geolocation
SW deployed
Effetti di REAL_STORAGE_MANAGEMENT
28
REALSTORAGE_MANAGEMENT in macro DSN6SPRM
The REALSTORAGE_MANAGEMENT subsystem parameter specifies whether DB2® should manage real storage
consumption.
Acceptable values: ON, OFF, AUTO
Default: AUTO
DSNZPxxx: DSN6SPRM REALSTORAGE_MANAGEMENT
ON
DB2 always discards unused real storage frames. Discarding the frames results in some CPU overhead, and this option
is intended for systems in which the availability of real storage is limited. This value would most likely be appropriate for
LPARs that have many DB2 subsystems, such as a development LPAR.
OFF
DB2 does not discard unused real storage frames until one of the following conditions is met:
The LPAR had reached an auxiliary critical state.
The total real and auxiliary storage has reached 80% of the value of the REALSTORAGE_MAX subsystem parameter.
AUTO
DB2 discards unused real storage frames when a significant amount of paging activity is detected. By discarding frames,
DB2 tries to bring the system to a point where paging is limited or nonexistent. However, it might not be possible to bring
the system to that point if other applications on the same LPAR cause the shortage of real storage frames. AUTO is the
default value.
Spatial function responsible Two PTFs: UI36156 UI37237
Effetti di REAL_STORAGE_MANAGEMENT
top related