DB2 for z / OS Virtual Storage Tuning

DB2 for z/OS Virtual
Storage Tuning
John J. Campbell
IBM Distinguished Engineer
DB2 for z/OS Development
[email protected]
© 2010 IBM Corporation
Disclaimer/Trademarks
The information contained in this document has not been submitted to any formal IBM test and is distributed AS IS. The use of this information or the
implementation of any of these techniques is a customer responsibility and depends on the customer’s ability to evaluate and integrate them into the
customer’s operational environment. While IBM may have reviewed each item for accuracy in a specific situation, there is no guarantee that the same
or similar results will be obtained elsewhere. Anyone attempting to adapt these techniques to their own environments do so at their own risk.
Any performance data contained in this document were determined in various controlled laboratory environments and are for reference purposes
only. Customers should not adapt these performance numbers to their own environments as system performance standards. The results that may be
obtained in other operating environments may vary significantly. Users of this document should verify the applicable data for their specific
environment.
Trademarks
IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions
worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the
Web at “Copyright and trademark information” at www.ibm.com/legal/copytrade.shtml.
© 2010 IBM Corporation
2
Why is storage tuning important?
Leading cause of DB2 outages
If storage is limited, extra DB2 members may be required in the
datasharing group
© 2010 IBM Corporation
3
Fitting DB2 in the DBM1 Address Space
16EB
The beam
DB2 DBM1 address space now has an addressing
range of 16EB (“the beam”) based on 64-bit
addressing but
–
Maximum of 16MB available “below the 16MB line”
–
Maximum of 2032MB available “above the 16MB
line” and “below the 2GB bar”
Practical maximum available to DB2 and
specifically DBM1 AS is much less
–
Typical 7-9MB available “below the line”
–
Typical 800-1900MB available “above the line” and
“below the 2GB bar”
2GB
The bar
2032MB
800-1900MB
(typical values)
16MB
The line
16MB
© 2010 IBM Corporation
7-9MB
(typical values)
0
4
What is the Problem?
Storage is allocated into different subpools which have unique characteristics
–
Storage acquired via MVS GETMAIN
–
Storage released by MVS FREEMAIN
GETMAIN processing by DB2 components using DB2 storage manager
–
Requests may be conditional or unconditional to DB2 storage manager
–
"Short on Storage" condition can occur for both
–
DB2 recovery routines may be able to clean up
–
Individual DB2 threads (allied, DBAT) may abend with 04E/RC=00E200xx when insufficient storage
available
–
–
e.g. 00E20003 & 00E20016
Eventually DB2 subsystem may abend with abend S878 or S80A due to non DB2 subsystem component
(e.g DFP) issuing unconditional MVS getmain
–
DB2 getmains are MVS conditional getmains so are converted to DB2 abends e.g. 00E20016
© 2010 IBM Corporation
5
Tracking DB2 Storage
DB2 storage is mostly allocated in SP229 Key 7
RMF for high level
–
Virtual Storage (VSTOR) Private Area Report
–
Interval data collected in SMF Type 78-2
–
Collected by RMF Monitor I session option: VSTOR(D,xxxxDBM1)
–
Produced by RMF Post Processor option: REPORTS(VSTOR(D,xxxxDBM1))
IFC Records
–
–
IFCID 225
–
Storage Summary
–
Snapshot value as each DB2 Stats interval comes due (ZPARM = STATIME)
–
Now included in Statistics Trace Class 1
IFCID 217
–
Storage Detail Record at thread level
–
Effectively a dump SM=1 report but in IFC form
–
Available through Global Trace Class 10
© 2010 IBM Corporation
6
Tracking DB2 Storage /
IFC Records I
– First class support provided by OMEGAMON XE for DB2 PM/PE, DB2 PM and DB2 PE
– Statistics Trace | Report
–
Includes FILE and LOAD data base table support as well as upgrade (ALTER TABLE ....) of already
installed table DB2PM_STAT_GENERAL
– Record Trace Report
– New SPREADSHEETDD subcommand option
– Both DB2PE V2.1 & DB2PM V8.1 via APAR PK31073
– OMEGAMON XE for DB2 PE V3 & V4 via APARs PK33395 & PK33406
– REXX Tools (MEMU2, MEMUSAGE)
– Available for download from Developer Works
http://www.ibm.com/developerworks/exchange/dw_entryView.jspa?externalID=1494&categoryID=29
© 2010 IBM Corporation
7
Tracking DB2 Storage /
V8 APAR PK20800 8/07
– DISPLAY THREAD(*) SERVICE(STORAGE)
– DSNV492I message that can be used by DB2 service for diagnostics
V91A
N *
0 003.RCRSC 02 SYSOPR
0067
0
V490-SUSPENDED 07213-09:59:18.02 DSNRCRSC +00000230 01.51
V492-LONG 252 K VLONG 40 K 64 1028 K
– Includes Agent Local Non-System Storage usage
– Does not include Getmained Stack Storage usage
– The key values are the LONG storage pool and the VLONG storage pool values (252KB +
40KB = 292KB in previous example)
– Reflect virtual storage consumption below the 2GB bar
– May be used to identify poorly behaved applications or DB2 code issues
8© 2010 IBM Corporation
8
MVS Storage Overview
EXTENDED REGION SIZE (MAX) – QW0225RG
– Total theoretical amount DB2 has access to
31 BIT EXTENDED LOW PRIVATE – QW0225EL
– DB2 uses a small amount of Low private (bottom up storage)
– DB2 code itself / reservation for pageset storage
31 BIT EXTENDED HIGH PRIVATE – QW0225EH
– DB2 mostly uses subpool 229 Key 7 (top down storage)
Other products also use address space storage
– Dataset opens / DFP
– SMF
DBM1 AND MVS STORAGE BELOW 2 GB
CONTINUED
-------------------------------------------24 BIT LOW PRIVATE
(MB)
24 BIT HIGH PRIVATE
(MB)
31 BIT EXTENDED LOW PRIVATE
(MB)
31 BIT EXTENDED HIGH PRIVATE
(MB)
EXTENDED REGION SIZE (MAX)
(MB)
EXTENDED CSA SIZE
(MB)
9© 2010 IBM Corporation
QUANTITY
-----------------0.14
0.47
130.86
859.40
1296.00
632.48
9
MVS Storage Overview /
ECSA – QW0225EC
– Common storage area across all address spaces for a given LPAR
– Large ECSA size would be 1GB with typical sizes being 300-500MB
– Affects maximum available Extended Region
– Biggest factor
– Some customers due to the needs of other products have huge ECSA leading to very small
extended region size
– Extensive use of ECSA by IMS across dependent regions
– Mostly buffer pools, control blocks, data are in ECSA
– Sizes are at user choice – For best performance they tend to be large
– Not exploiting VSCR features of recent IMS releases
– Generous over allocation for safety of ECSA and other common areas
– Common LPAR image for Sysplex (best practice)
© 2010 IBM Corporation
10
MVS Storage Overview /
REGION parameter in JCL for ASID
– MVS rule is high private cannot grow into low private
– Region parameter controls how high low-private can grow
– 0M means all available region can be allocated, could be limited by exit
– No effect on getmained DB2 storage since DB2 only allocates high private storage
– Region parameter can be used to save a few MB by forcing some low private growth into 24-bit
low private (QW0225LO)
– Some dataset open activity can be in trouble with a low REGION= parm
– Usually REGION=0M is preferred
MEMLIMIT
– Not observed by DB2 DBM1
– Overridden by DB2 to 4TB
© 2010 IBM Corporation
11
DB2 DBM1 Address Space Storage
DBM1 AND MVS STORAGE BELOW 2 GB
31-bit / 24-bit DB2 storage
TOTAL DBM1 STORAGE BELOW 2 GB
– Getmained
TOTAL GETMAINED STORAGE
– Variable
TOTAL VARIABLE STORAGE
– Fixed Storage
QUANTITY
-------------------------------------------(MB)
616.67
(MB)
137.96
(MB)
120.00
(MB)
379.23
EDM POOL
TOTAL AGENT LOCAL STORAGE
TOTAL AGENT SYSTEM STORAGE
(MB)
279.39
(MB)
187.10
NUMBER OF PREFETCH ENGINES
– Stack storage
Non-DB2 getmained
– SMF
– Dataset / pageset
600.00
NUMBER OF DEFERRED WRITE ENGINES
300.00
NUMBER OF CASTOUT ENGINES
300.00
NUMBER OF GBP WRITE ENGINES
300.00
NUMBER OF P-LOCK/NOTIFY EXIT ENGINES
202.00
TOTAL AGENT NON-SYSTEM STORAGE
(MB)
TOTAL NUMBER OF ACTIVE USER THREADS
92.29
101.12
RID POOL
(MB)
1.13
PIPE MANAGER SUB POOL
(MB)
7.97
LOCAL DYNAMIC STMT CACHE CNTL BLKS
(MB)
0.99
THREAD COPIES OF CACHED SQL STMTS
(MB)
16.24
IN USE STORAGE
(MB)
STATEMENTS COUNT
HWM FOR ALLOCATED STATEMENTS
0.61
82.77
(MB)
STATEMENT COUNT AT HWM
0.78
122.00
DATE AT HWM
02/02/09
TIME AT HWM
10:24:37.33
TOTAL FIXED STORAGE
(MB)
2.61
TOTAL GETMAINED STACK STORAGE
(MB)
96.87
(MB)
49.35
(MB)
130.32
TOTAL STACK STORAGE IN USE
STORAGE CUSHION
© 2010 IBM Corporation
---------------
12
DB2 DBM1 Address Space Storage /
Getmained - QW0225GM
–
Compression Dictionaries and Bufferpools
are now above the bar
–
EDM pool still getmained
Fixed Storage - QW0225FX
–
–
–
–
Variable Storage - QW0225VR
–
Most interesting from a tuning perspective
–
Variable length blocks
–
Thread pools (AGL)
–
–
Used by both System and User
Small change in the great scheme of things
Stack Storage - QW0225GS
–
–
–
–
Local Dynamic Statement Cache
–
–
© 2010 IBM Corporation
High performance storage
Fixed length blocks
Not usually so interesting from a tuning
perspective
Save areas
Working program variables
Small amounts of high speed storage allocations
Cached in the DB2 address space to allow
greater performance
Compressed only at full system contraction
Stacks are bigger in V8 (64K vs 20K)
13
Non-DB2 Storage
Not tracked by DB2
Non-DB2 storage is high private storage
– TOTAL DBM1 STORAGE = TOTAL GETMAINED STORAGE QW0225GM + TOTAL GETMAINED
STACK STORAGE QW0225GS + TOTAL FIXED STORAGE QW0225FX + TOTAL VARIABLE
STORAGE QW0225VR
– NON-DB2 STORAGE= MVS 31 BIT EXTENDED HIGH PRIVATE QW0225EH – TOTAL DB2 DBM1
STORAGE
Used usually by MVS functions such as SMF
Parameter DETAIL in SMFPRMxx can cause storage to creep and become very
large
–
The big hit to DB2 in this area is the DDNAME tracking: allocation does not realise that we
have closed off a page set and reallocated it again
–
SMF Type 30 subtype 4 and 5 will track all the DDNAMES
–
Most environments do not need SMF Type 30 subtype 4 and 5
–
Recommend NODETAIL
© 2010 IBM Corporation
14
DB2 DBM1 Address Space Storage /
64-bit DB2 storage
DBM1 STORAGE ABOVE 2 GB
-------------------------------------------– Fixed
FIXED STORAGE
(MB)
– Variable
GETMAINED STORAGE
(MB)
– Compression Dictionaries
COMPRESSION DICTIONARY
(MB)
IN USE EDM DBD POOL
(MB)
– DBD Pool
IN
USE
EDM
STATEMENT
POOL
(MB)
– Dynamic Statement Cache
IN USE EDM RDS POOL
(MB)
– RDS Pool Above (V9)
IN
USE
EDM
SKELETON
POOL
(MB)
– Skeleton Pool (V9)
VIRTUAL BUFFER POOLS
(MB)
Buffer Control Blocks
VIRTUAL POOL CONTROL BLOCKS
(MB)
CASTOUT BUFFERS
(MB)
Castout Buffers
VARIABLE STORAGE
(MB)
64-bit Shared Private Storage (V9)
THREAD COPIES OF CACHED SQL STMTS
(MB)
IN USE STORAGE
(MB)
Virtual Buffer Pools are not managed by DB2
HWM FOR ALLOCATED STATEMENTS
(MB)
storage manager (not part of Getmained storage).
In V7 and below they were managed by DB2
SHARED MEMORY STORAGE
(MB)
storage manager
TOTAL FIXED VIRTUAL 64BIT SHARED
(MB)
TOTAL GETMAINED VIRTUAL 64BIT SHARED (MB)
TOTAL VARIABLE VIRTUAL 64BIT SHARED
(MB)
– Getmained
–
–
–
–
© 2010 IBM Corporation
QUANTITY
------------25.99
3951.66
48.38
255.69
93.98
0.09
96.61
3460.16
0.91
37.50
1255.45
126.59
1.08
1.40
2579.48
15.50
1194.41
1369.57
15
10
20 -01
10 - 2
4
20 -01 -20
10 - 2 . 0
5
20 -01 -00 2.0
10 - 2 . 5 4. 0
20 -01 5-0 2.4 35
10 - 2 5. 4 5. 0 6 8
20 -01 5-1 2.3 62 2
10 - 2 0. 3 6. 6 0
8
5
0
20 -01 -15 2.2 07
10 - 2 . 2 8. 5 1 0
5
20 -01 -20 2.2 84 7
10 - 2 . 1 0. 3 9 6
20 -01 6-0 2.1 48 2
10 - 2 1. 0 2. 1 6 6
5
6
20 -01 -05 2.0 529
10 - 2 . 5 3. 8 4
6
1
20 -01 -10 2.4 94
10 - 2 . 4 4. 9 7 5
20 -01 6-1 2.3 26 4
10 - 2 5. 3 6. 6 0 9
20 -01 6-2 2.2 76 4
10 - 2 0. 2 8. 0 8
4
7
2
20 -01 -01 2.2 53
10 - 2 . 1 0. 2 3 1
7
20 -01 -06 2.1 81 5
10 - 2 . 0 2. 0 9 2
20 -01 7-1 2.0 23 0
10 - 2 0. 5 3. 7 5 1
20 -01 7-1 2.4 58 9
10 - 2 5. 4 4. 0 8
7
7
3
20 -01 -20 2.3 89
10 - 2 . 3 6. 5 0 3
8
20 -01 -01 2.2 96 0
10 - 2 . 2 8. 3 6 6
20 -01 8-0 2.2 75 7
10 - 2 6. 1 0. 1 8 2
8
9
20 -01 -11 2.1 207
10 - 2 . 0 1. 8 3
8
9
20 -01 -15 2.0 72
10 - 2 . 5 3. 6 4 2
20 -01 8-2 2.4 18 4
10 - 2 0. 4 4. 6 4 8
20 -01 9-0 2.3 68 4
10 - 2 1. 3 6. 6 0
5
5
9
20 -01 -06 2.2 72
10 - 2 . 2 8. 3 5 8
9
20 -01 -11 2.2 40 8
10 - 2 . 1 0. 0 9 8
20 -01 9-1 2.1 78 5
10 - 2 6. 0 1. 8 0 0
9
0
20 -01 -20 2.0 219
10 - 3 . 5 3. 6 4
0
4
20 -01 -01 2.4 46
10 - 3 . 4 4. 7 6 1
20 -01 0-0 2.3 44 5
10 - 3 6. 3 6. 4 7 5
20 -01 0-1 2.2 73 5
10 - 3 1. 2 8. 4 4
2
0
3
20 -01 -16 2.1 23
10 - 3 . 1 9. 9 2 3
0
20 -01 -21 2.1 65 1
10 - 3 . 0 1. 7 2 1
20 -01 1-0 2.0 04 9
10 - 3 1. 5 4. 4 1 0
20 -01 1-0 2.4 88 9
10 - 3 6. 4 3. 1 3
4
1
5
20 -01 -11 2.3 26
10 - 3 . 3 6. 2 9 5
1
20 -01 -16 2.2 23 5
10 - 3 . 2 9. 0 1 5
-0 1-2 2.2 20 2
2- 1
01 . 1 0. 7 8 18
- 0 2. 1 5 8
2. 2 6 6
0 2 .5
0
. 0 0 65
4.
8
24 8
52
18
20
MB
1000
700
600
CTHREAD+MAXDBAT
Type 1 = 361
Type 2 = 370
TOTAL FIXED STORAGE
© 2010 IBM Corporation
200
500
400
150
300
0
TOTAL GETMAINED STORAGE
TOTAL GETMAINED STACK STORAGE
TOTAL VARIABLE STORAGE
Thread Count
Basic graphing of storage - leaky subsystem?
7 days data (Mon-Sun)
DB2D Total Storage vs Threads
350
900
800
300
250
100
200
100
50
0
Total threads
16
Basic graphing
Check the major components of DB2 storage
– Fixed
– Getmained
– Stack
– Variable
– Thread counts to give an idea of workload
Previous graph shows a leak?
– Or does it?
© 2010 IBM Corporation
17
10
20 -02
10 - 0
1
20 -02 -02
10 - 0 . 0
1
20 -02 -04 1.0
1 0 - 0 . 0 3 .4
1
20 -02 -06 8.0 273
1 0 - 0 1 . 1 8 .7 3
0
20 -02 -08 5.1 106
1 0 - 0 . 2 5 .0 7
4
20 -02 1-1 2.2 30
1 0 - 0 0 . 2 0 .2 5 2
1
8
20 -02 -12 9.2 991
1 0 - 0 . 3 6 .6 9
0
1
20 -02 -14 6.3 30
1 0 - 0 . 4 1 .9 2 1
20 -02 1-1 3.3 03 3
1 0 - 0 6 . 5 7 .1 8 7
1
8
20 -02 -18 0.4 904
1 0 - 0 . 5 3 .5 5
1
6
20 -02 -21 7.4 61
1 0 - 0 . 0 8 .8 7 8
6
1
20 -02 -23 4.0 679
1 0 - 0 2 . 1 5 .9 2 0
20 -02 -01 1.1 573
1 0 - 0 . 1 1 .2 3
2
20 -02 2-0 8.1 28
1 0 - 0 3 . 2 7 .5 0 3
2
3
20 -02 -05 6.2 502
1 0 - 0 . 3 3 .6 1
9
2
20 -02 -07 3.2 40
1 0 - 0 . 4 8 .9 5 5
20 -02 2-0 0.3 09 8
1 0 - 0 9 . 4 5 .2 4 7
2
6
20 -02 -11 7.4 300
1 0 - 0 . 5 0 .5 2
2
0
20 -02 -14 4.4 14
1 0 - 0 . 0 6 .8 3 5
2
2
20 -02 -16 1.0 520
1 0 - 0 2 . 0 2 .8 1 1
20 -02 -18 8.0 664
1 0 - 0 . 1 9 .2 8
7
20 -02 2-2 5.1 23
1 0 - 0 0 . 2 4 .5 9 8
2
1
20 -02 -22 2.2 223
1 0 - 0 . 2 0 .8 9
9
3
20 -02 -00 9.2 52
1 0 - 0 . 3 6 .1 1 2
20 -02 3-0 6.3 28 2
1 0 - 0 2 . 4 1 .3 2 3
3
7
20 -02 -04 4.3 996
1 0 - 0 . 5 8 .5 8
3
8
20 -02 -06 1.4 32
1 0 - 0 . 5 3 .8 2 5
9
3
20 -02 -09 8.5 194
1 0 - 0 3 . 0 0 .1 7 3
20 -02 -11 5.0 524
1 0 - 0 . 1 6 .1 2
3
20 -02 3-1 2.1 48
1 0 - 0 3 . 1 2 .4 2 6
3
3
20 -02 -15 9.1 751
1 0 - 0 . 2 7 .9 8
1
3
20 -02 -17 6.2 62
1 0 - 0 . 3 3 .4 3 8
20 -02 3-1 3.2 11 8
1 0 - 0 9 . 4 9 .7 1 9
3
7
20 -02 -21 0.3 656
1 0 - 0 . 4 5 .1 8
7
3
20 -02 -23 7.4 16
1 0 - 0 . 5 0 .3 3 3
8
4
20 -02 -02 4.4 869
1 0 - 0 4 . 0 6 .7 7 5
20 -02 -04 2.0 060
1 0 - 0 . 0 3 .5 6
-0 4-0 9.0 37 6
2- 6
0 4 . 1 9 .8 9 7 7
- 0 6 .1 5 6
8. 5 7 1
2 3 .1
1
.2 2 9 4
1.
6
44 3
98
18
20
MB
1000
700
600
CTHREAD+MAXDBAT
Type 1 = 342
Type 2 = 352
TOTAL FIXED STORAGE
© 2010 IBM Corporation
200
500
400
150
300
0
TOTAL GETMAINED STORAGE
TOTAL GETMAINED STACK STORAGE
TOTAL VARIABLE STORAGE
Thread Count
Basic graphing – what happened next
Mon-Wed This DB2 took a full week to “warm up”
DB2D Total Storage vs Threads
350
900
800
300
250
100
200
100
50
0
Total threads
18
Lessons to be learned
Wrong data time can lead to erroneous conclusion
– Full week showed a possible leak, 10 days showed DB2 stable
storage usage
Ideal data is from DB2 startup to DB2 shutdown
– If not possible get as much as you can
Do not think you know where the maximum usage is
– You may be way off the mark
More data will lead you to a better result
© 2010 IBM Corporation
19
How much is left available in the address space
QW0225AV – DB2 running total
– Possibly inaccurate since DB2 storage manager has no idea about storage
obtained by other products directly from z/OS
What’s really left
– QW0225RG – (QW0225EL + QW0225EH)
– Numbers directly obtained from z/OS
© 2010 IBM Corporation
20
Storage Overuse: DB2 Storage Contraction
When ‘running low’ on extended virtual, DB2 begins system contraction process
which attempts to freemain any available segments of storage
– Contraction can be
– Normal
– A sign of a poorly tuned system
3 critical numbers for contraction
– Storage reserved for must complete (e.g. ABORT, COMMIT) – QW0225CR
– = (CTHREAD+MAXDBAT+1)*64K (Fixed, real value) +25M
– Storage reserved for open/close of datasets – QW0225MV
– = (DSMAX*1300)+40K (space reserved in low private)
– Warning to contract – QW0225SO
– = Max (5% of Extended Region Size, QW0225CR -25M)
– Storage Cushion = QW0225CR + QW0225MV + QW0225SO
– Note: QW0225MV will decrease as pagesets are opened
© 2010 IBM Corporation
21
Storage Overuse: DB2 Storage Contraction
Examples:
Case 1
Case 2
Case 3
CTHREAD
2000
400
400
MAXDBAT
2000
2000
150
15000
15000
15000
1700
1000
1000
275
175
63
19
19
19
250
150
50
DSMAX
MVS extended region size (MB)
Storage reserved for must complete (MB)
Storage reserved for datasets (MB)
Warning to contract (MB)
**WARNING** DO NOT SPECIFY CTHREAD + MAXDBAT
TOO344
HIGH
544
IN DB2 V8 OR THE CUSHION WILL BE VERY LARGE
Storage Cushion (MB)
© 2010 IBM Corporation
132
22
Storage cushion
QW0225AV reports how much storage DB2 thinks is available
Extended Region Size (QW0225RG)
Storage Critical (QW0225CR)
Thread abends start to occur like 00E20003,
00E20016
Storage Warning (QW0225SO)
IF QW0225AV < (QW0225SO + QW0225MV +
QW0225CR
Contraction starts to occur
0
© 2010 IBM Corporation
23
Storage Overuse: Large Contributors
Stack use (QW0225GS)
–
–
Normal range is typically 100-300MB
Compressed only at full system contraction
System agents (QW0225AS)
–
Some agents once allocated are never deallocated
– For example: P-lock engine, prefetch engine
–
# engines: QW0225CE, QW0225DW, QW0225GW, QW0225PF, QW0225PL
– If these counts are very low and system is on the brink of storage overuse, it is possible that the
allocation of more engines could send the system into contraction
User threads (QW0225VR-QW0225AS)
–
Typical user thread storage footprint can be 500KB to 10MB per thread depending on
thread persistence, variety and type of SQL used
– SAP Threads 10MB
– CICS Threads 500KB
– Number of threads obtained via QW0225AT + QDSTCNAT
© 2010 IBM Corporation
24
CONTSTOR
Thread storage contraction turned on by ZPARM CONTSTOR=YES
–
Online changeable with immediate effect
–
Only compresses LONG storage (as per SERVICE(STORAGE))
–
Maximum of 1 compress every 5 commits (so very cheap to implement)
Associated CPU overhead
–
Benefit should be carefully evaluated before enabling
–
Ineffective for long-running persistent threads with use of RELEASE(DEALLOCATE)
Compresses out part of Agent Local Non-System storage
–
–
Agent Local System
–
Getmained Stack Storage, LDSC
Controlled by two hidden zparms
–
Does not compress
SPRMSTH @ 1048576 and SPRMCTH @ 10
Triggers
–
No. of Commits > SPRMCTH, or
–
Agent Local Non-System > SPRMSTH and No. of Commits > 5
© 2010 IBM Corporation
25
MINSTOR
Best fit algorithm for thread storage turned on by ZPARM MINSTOR=YES
– Online changeable, may not have an effect due to already cached pools
– Restart recommended if this parm changed
Changes the storage management of the user AGL POOL to “Best fit” rather than
“First fit”
– In order to find the best fit piece of storage, CPU cycles are used to scan and maintain
ordered storage
– In a POOL with low fragmentation, MINSTOR may not have a great effect but will cost CPU
Only enable if fragmentation is a big issue
– Only the SM=4 option of the DB2 Dump Formatter and a dump will really give you the
definitive answer
© 2010 IBM Corporation
26
Protecting the System
Plan on a ‘Basic’ storage cushion (free)
– To avoid hitting short on storage and driving Full System Contraction
– To provide some headroom for
– Tuning, some growth, Fast Log Apply, abnormal operating conditions
– Basic cushion = Storage cushion + 100M
– The Basic cushion should be less than 10% of the Extended Region Size, otherwise CTHREAD and/or MAXDBAT are
probably set too high
Case 1
Case 2
Case 3
CTHREAD
2000
450
450
MAXDBAT
2000
2000
200
MVS extended region size (MB)
1700
1000
1000
Storage Cushion (MB)
544
344
132
Basic Cushion (MB)
644
444
232
37%
44%
23%
% of Extended Region Size
© 2010 IBM Corporation
27
Protecting the System /
Estimate the maximum number of threads that can be supported
– Assuming the storage is proportional to the amount of threads, it is possible to predict a
theoretical max. number of concurrent threads
– It may be possible to run the system with more threads than the formula dictates, but there
is the danger that the large threads may come in and cause out of storage conditions
Set ZPARMS CTHREAD and MAXDBAT to protect the system
– CTHREAD and MAXDBAT are the brakes on the DB2 subsystem
– Theoretical maximum: CTHREAD+MAXDBAT = 2000
– Practical maximum is much less (typical range 300-850)
– Avoid over committing resources
– Deny service and queue work outside the system to keep system alive
© 2010 IBM Corporation
28
Estimating Maximum Number of Threads
Collect IFCID 225 since the start of DB2, Statistics class 1 (SMF100 and 102 in
V8, SMF100 in V9)
–
Month end processing
–
Weekly processing
–
Utilities processing
–
Try to use a full application mix cycle
Focus on time periods with
–
Increasing number of allied threads + active DBATs
–
Increasing use of getmained stack storage
–
Increasing use of AGL non-system
Adjust the formula based on workload variations
Protect the system by always using a pessimistic approach to formulating the
numbers
–
Optimistic may mean a DB2 outage
Always recalculate on a regular basis as new workloads and/or parameters are
changed
© 2010 IBM Corporation
29
Estimating Maximum Number of Threads/
Remember to use the MAX impact value across all available data, e.g. MAX system storage
‘Basic’ storage cushion (BC)
–
Calculate Max non-DB2 storage (ND)
–
(TS) = (AS) – MAX(TOTAL AGENT SYSTEM STORAGE QW0225AS + TOTAL FIXED STORAGE QW0225FX +
TOTAL GETMAINED STORAGE QW0225GM + MVS 31 BIT EXTENDED LOW PRIVATE QW0225EL)
Average thread footprint (TF)
–
(AS) = QW0225RG – (BC) – (ND)
Max. allowable storage for thread use (TS)
–
(ND)= MAX(MVS 31 BIT EXTENDED HIGH PRIVATE QW0225EH – TOTAL GETMAINED STORAGE QW0225GM –
TOTAL GETMAINED STACK STORAGE QW0225GS – TOTAL FIXED STORAGE QW0225FX – TOTAL VARIABLE
STORAGE QW0225VR )
Max. allowable storage (AS)
–
(BC) = QW0225CR + QW0225MV + QW0225SO + 5% of QW0225RG
(TF) = (TOTAL VARIABLE STORAGE QW0225VR – MAX(TOTAL AGENT SYSTEM STORAGE QW0225AS) +
TOTAL GETMAINED STACK STORAGE QW0225GS ) / (Allied threads QW0225AT + DBATs QDSTCNAT)
Max threads supported = (TS) / (TF)
© 2010 IBM Corporation
30
Virtual vs. REAL Storage
Important subsystems such as DB2 should not be paging IN from auxiliary storage
(DASD)
–
Recommendation to keep page in rates low (near zero)
–
Monitor using RMF Mon III
V8 introduces very large memory objects that may not be backed by REAL
storage frames
–
Virtual storage below 2GB bar is usually densely packed (as before in V7)
– VIRTUAL=REAL is a fair approximation
–
Virtual storage above the bar number may be misleading
– Backing rate is low for 64-bit storage
– No need to back until first reference
–
For an LPAR with greater than 16GB of defined real storage, DB2 will obtain a minimum
starting memory object above the bar of 16GB
– This memory is sparsely populated
– Virtual will not equal REAL
© 2010 IBM Corporation
31
Monitoring REAL Storage
REAL AND AUXILIARY STORAGE
--------------------------------------REAL STORAGE IN USE
(MB)
AUXILIARY STORAGE IN USE
(MB)
QUANTITY
--------------5958.66
0.00
Real storage needs to be monitored as much as Virtual storage
–
Need to pay careful attention to QW0225RL (Real frames in use by DBM1) and QW0225AX
(Auxiliary frames)
– Ideally QW0225RL should be significantly less than the amount of virtual consumed
An indication of either (a) a DB2 code error or (b) an under provisioned system will see
–
100% real frames consumed
– It will be important to know how much real is dedicated to a given LPAR
– Although a physical machine may have 30GB real, a given LPAR may only have a fraction of this real
dedicated
–
An extensive number of auxiliary frames in use
–
Performance degradation
V9 – Shared object storage can only be monitored at the LPAR level so is only accurate
for a single DB2 LPAR assuming no other exploiters of shared storage
© 2010 IBM Corporation
32
Monitoring REAL Storage - Warning
Excessive amounts of storage on AUX may cause long DUMP times and
severe performance issues.
– Paging may become severe
Make sure enough REAL storage is available in case DB2 has to take a
DUMP
– DUMP should complete in seconds to make sure no performance problems
ensue. Once paging begins it is possible to have the DUMP take 10s of
minutes
© 2010 IBM Corporation
33
How to Limit REAL Storage
New Hidden ZPARM SPRMRSMX
Causes a DB2 outage when the limit hits
Delivered in APAR PK18354
Not widely broadcast
Preferable to monitor the REAL storage numbers in IFCID 225 and
generate alerts when large increase in AUX or REAL approaches
max available
© 2010 IBM Corporation
34
DB2 Service Monitor (V9 CM)
Automatically issues console messages when DBM1 virtual storage below
the 2GB bar reaches critical usage thresholds
– 88, 92, 96, or 98 percent of available storage
Identifies the agents that consume the most storage
DSNV508I -SE20 DSNVMON - DB2 DBM1 BELOW-THE-BAR
STORAGE NOTIFICATION
91% CONSUMED
87% CONSUMED BY DB2
DSNV510I -SE20 DSNVMON - BEGINING DISPLAY OF LARGEST
STORAGE CONSUMERS IN DBM1
DSNV512I -SE20 DSNVMON - AGENT 1: 094
NAME
ST A REQ
ID
AUTHID PLAN
----- - --------- ----SERVER RA * 18461 SE2DIA004 R3USER DISTSERV
LONG 1720K VLONG 388K 64BIT 2056K
DSNV512I -SE20 DSNVMON - AGENT 2: 095
NAME
ST A REQ
ID
AUTHID PLAN
----- - --------- ----SERVER RA * 9270 SE2DIA001 R3USER DISTSERV
LONG 1672K VLONG 388K 64BIT 2056K
© 2010 IBM Corporation
35
New CPU AND STORAGE Metrics (V8 and V9)
Introduced by PK62116
–
–
–
–
APAR PK66373 must be applied
APAR OA24404 for RMF must be applied
ZPARM ZOSMETRICS must be set to YES (default is NO)
RMF Monitor Type III must be running
Adds the following metrics to IFCID 001 (QWOS)
–
–
–
–
Number of CPs
CPU utilisation
Storage values
Unreferenced Interval Count (UIC)
– Indicator for real-storage constraints
Support in Omegamon XE for
DB2 PM/PE V4.20
© 2010 IBM Corporation
CPU AND STORAGE METRICS
--------------------------CP LPAR
CPU UTILIZATION LPAR
CPU UTILIZATION DB2
CPU UTILIZATION DB2 MSTR
CPU UTILIZATION DB2 DBM1
QUANTITY
---------------4.00
39.20
0.00
0.00
0.00
UNREFERENCED INTERVAL COUNT
65535.00
REAL STORAGE LPAR
(MB)
FREE REAL STORAGE LPAR (MB)
USED REAL STORAGE DB2 (MB)
3071.00
1537.10
194.65
VIRTUAL STORAGE LPAR
(MB)
FREE VIRTUAL STOR LPAR (MB)
USED VIRTUAL STOR DB2 (MB)
10269.35
8936.10
194.65
36