Advanced DBA – Performance Tuning for MUF Part II

World®
’16
Advanced DBA – Performance
Tuning for MUF Part II
Kevin Shuma VP, Product Management
CA Technologies
MAINFRAME AND WORKLOAD AUTOMATION
MFX66EB

2 © 2016 CA. ALL RIGHTS RESERVED.@CAWORLD #CAWORLD
Abstract
This is a two part session that describes the various techniques
used to tune a CA Datacom environment(MUF) for the best
possible performance. Included will be discussions on available
monitoring tools.
The session will cover various techniques to improve
performance within the CA Datacom®/DB environments.
VP, Product
Management
CA
Technologies
Kevin Shuma

Agenda
I TUNED LAST YEAR WHY DO IT AGAIN?
UTILIZING THE IBM ZIIP TO IMPROVE TCO
SUMMARY
REDUCING PHYSICAL IO TO IMPROVE TCO
NEW FUNCTIONALITY AVAILABLE IN V15.0
OTHER CONSIDERATIONS
1
2
3
44
5
6

I Tuned Last Year Why Do It Again?
§ Hardware changes
– System z Integrated Information Processor (zIIP)
– Increased real memory
– Newer faster DASD
§ Software changes
– Exploitation of the hardware
– Increased memory capabilities
– New faster or less resource intensive functionality
How often do I need to tune?

Many sites mainframe software charges are based on
general purpose processor use
§ Remember this graph ?
§ Typical licensing models would charge based on the highest
rolling 4 hour average – not the overall average
– Highest 4 hour 65.93 MSUs
– Average 4 hour 20.14 MSUs

UTILIZING THE IBM zIIP to improve TCO

Utilizing the IBM zIIP
§ System z Integrated Information Processor (zIIP)
– Hardware costs only
– Does not add to MIPs/MSUs – does not affect software licenses
– zIIP CPUs run at full speed – no “reduced capacity” CPUs
– IBM limit to zIIPs per General Purpose (GP) CPU
§ Software vendors like CA can code software to use zIIP as long
as they stay within a given set of rules
– Only certain kinds processing – can not dispatch physical IO
– Only code your own – cannot zIIP other parties code
Available specialty processer on most z boxes

CA Datacom utilizing the IBM zIIP
§ CA Datacom and zIIP Specialty Engine
– zOS release 12.0 and above will offload MUF GP processing to zIIP
– Release 12.0 – offloads ~33% to zIIP
– Release 14.0 – offloads ~50% to zIIP
– Release 14.01 – offloads ~80%+ to zIIP
– Release 15.0 – offloads ~90+ to zIIP

What could zIIP in CA Datacom get me?
Assuming that MUF in 25% of the processing on your box
Without zIIP
Max. MSU 65.93
Avg. MSU 20.14
With r12 zIIP 33% and MUF @ 25%
Max. MSU 60.49
Avg. MSU 18.50
Max. MSU 57.69
Avg. MSU 17.63

What could zIIP in CA Datacom get me? (cont’d)
Assuming that MUF in 25% of the processing on your box
Without zIIP
Max. MSU 65.93
Avg. MSU 20.14
With r14.01 zIIP 85% and MUF @ 25%
Max. MSU 51.92
Avg. MSU 15.86
Max. MSU 50.27
Avg. MSU 15.86
Straight line savings
not typical

CA Datacom utilizing the IBM zIIP
§ Customer on V15.0
– Our license is based on a 55 MSU highest rolling average
– Before r14, we exceeded the 55 MSU 6 out of 12 months incurring
significant upcharges on software
– Most recent contract 55 MSUs per month
§ With a special rate to buy “extra 55 MSUs” for the year
– After r14.01 and now 15.0,
§ We have only exceeded 50 MSUs once during a very heavy startup month
– We will not use many of the “extra MSUs” this year
– Reduces our costs on the mainframe

CA Datacom utilizing the IBM zIIP (cont’d)
§ Start-up option
– SMPTASK 5,3,3,SRB
§ Current default is TCB
§ Code SRB to run SMP tasks as SRBs
§ Main task runs as TCB (as well as other subtasks)
§ First two SMP tasks scheduled to run more aggressively
§ Other SMP tasks scheduled same as when in TCB mode
§ All SMP tasks are either SRB or TCB
Utilizing zIIP in the MUF

§ Each SRB SMP task paired with a DBSMAPR TCB
– Used for things an SRB can’t do
– Or things a zIIP can’t do
– Passed back and forth as synchronous requests
SRB-
SMP1
SMA1
SRB-
SMP2
SMA2
SRBs TCBs

§ Example of SRB/TCB pairs

§ Customer example: Large bank – week of processing
– Running V14.01 zIIP with large buffer pools

§ Any technique that reduces IO reduction can improve TCO by
swapping GP CPU for zIIP-able CPU
– Using more buffers
– Using MRDF covered and virtual data
– Reducing IO
§ Larger block sizes
§ Use of compression (with zIIP present)
§ Anything else
Final thoughts

Reducing physical IO to improve TCO

How to Improve TCO With Datacom – Reduce IO
§ Database engines are physical IO consumers
§ Physical IOs consume significant amounts of CPU
§ Reduction of Physical IO leads to reduction of CPU consumed
– Logical IOs (memory) are cheaper in CPU the physical IO
– Logical IOs are zIIP-able (specialty processor eligible)
§ Remember the bank with 97% zIIP
Typically reducing IO will save GP CPU
May be processed on zIIP

Reduce IO (cont’d)
§ Ensure the buffer pools are large enough to provide high
logical reuse
– IXX/DXX buffers are 64-bit
§ Increase each until 5+ reuse “falls off” (increase in reasonable increments)
– DATA/DATA2 buffers are 31-bit or 64-bit (V15)
§ Increase each until 5+ reuse “falls off” (increase in reasonable increments)
– If using 31-bit storage (do not allow 31-bit to get exhausted)
– Monitor paging to ensure paging remains reasonable
Standard buffers

§ Implement larger block size for selected data areas
– If in use make sure they are being used efficiently
§ Larger block size could reduce IO
– Increase from 4K to 18K does not significantly affect cost of IO retrieval
– The more rows per block, the more opportunity for improved reuse
– Need to validate using BVMs
§ Need to balance DATA/DATA2 buffers to area usage
– Increase DATA2 as workload is moved to the large size
– If using 31-bit storage, do not allow 31-bit to get exhausted
Going to a larger data block size = buffer size

§ Like a private buffer pool for a selected area
– Provides a memory cache to avoid IO when requested block is not in a
buffer
Using MRDF COVERED

§ FIRST uses the least amount of CPU to find a requested block
– Direct mapping (data block 7 is in memory slot 7)
– Does not adjust to activity
– May not work well if area to be covered is too big
§ ACTIVE uses more CPU to find a requested block
– Adjusts to activity, similar to buffering
– LRU is used to determine which memory slots are reused
– Indirect mapping (data block 7 may be anywhere in the memory)
– Works well if the “working set” of blocks fits within the amount of
Covered memory
Using MRDF COVERED

§ Covering is not a “set-once and forget” process
– Needs reviewing on a monthly basis
§ Covering allocations can be changed on the fly to match
processing needs
§ Covering should be targeted to high read areas
– IXX over DATA if same value
§ Can be pre-loaded during non-peak processing
Using MRDF COVERED

§ Review stats below – some good choices, some bad choices
Using MRDF COVERED

§ Sample MRDF query
Using MRDF COVERED

§ Looking for the best areas to cover
Using MRDF COVERED

Comparison buffers versus MRDF COVERED
§ Buffers are the most efficient
use of memory when sized to
match the data, but
– Data buffer pools in v14 are limited
due to 31-bit
– Buffers are reused base on LRU
(Least Recently Used)
– A very active batch report against a
non-essential table could flush a large
portion of the active buffer pool
§ COVERED areas
– Are 64-bit by default
– Covered blocks are specific to a
given area and are not flushed by non
associated activity, but
§ COVERED areas must still funnel their
blocks back through the buffer pools
§ Covering low activity areas uses the
same space that high activity areas uses

§ Build buffer pools to a reasonable size to support typical
“peak activity” within the system
§ Use Covering to target key high-use (read) datasets
§ Always measure every change to make sure it presents
business value!!!
Best Choice – Use both large buffer pools and selective covering

§ Implement compression
– Compression reduces the row image so more rows per block
– Reduces DASD utilization (reduces cost)
– Can improve buffer reuse
– Can improve COVERED efficiency
§ DB compression runs in MUF and is zIIP eligible
– With zIIP processors, the compression CPU costs are shifted to less
costly CPU devices
Using Compression

§ An option that once was considered outdated
– DASD cheap – CPU Expensive
§ May now be back in style – DASD is cheap but zIIP Cheaper
– Always measure with BVMs!!
– New cool option in 14.02
§ Online compress
§ See session MFX94S - Reducing TCO with Datacom Compression (Presspack)
Using Compression

New functionality available in V15.0

New functionality available in V15
§ Data buffer pools now go to 64-bit
– Reduced 31-bit consumption
– Ability to create larger (or more) buffer pools
§ Can be combined with additional buffer pools to build tailored
buffer pools that resemble COVERED areas
– Tailored buffer pools would have some advantage over covered areas
– No requirement to move “through” a buffer pool like COVERED
– Buffer LRU processing is highly efficient
§ Which is better - verdict is still out
Data pools now in 64 bit

New functionality available in V15 (cont’d)
§ Are you seeing lots of “other SRB” with zIIP
– Example MUF with medium buffers and lots of IO activity (V14)
Improved zIIP utilization with reduced GP CPU
Cmd Jobname Jobnr Clocktime CPU-Time SRB-Time IOCount CPTime IIPtime IIPonCP IIPencl
___ SHUKE01 59656 00:10:33 2.473518 0.020703 1062 2.432321 0.041197 0.000037 0.041197
___ @2DBRUP2 59696 00:00:53 0.134624 0.023081 109 0.134624
___ QA14MUF4 59663 00:08:14 5.667642 1.258595 70677 3.145144 2.522497 0.000524 2.522497
___ @3PWRUP2 59700 00:00:46 0.099999 0.017140 109 0.099999
___ @1UNRED1 59686 00:01:06 0.387458 0.079477 109 0.387458
___ @3PWRUP3 59701 00:00:51 0.130862 0.023287 109 0.130862
___ @2DBRUP1 59695 00:00:53 0.372552 0.063932 109 0.372552
___ @2DBRUP3 59697 00:00:53 0.138674 0.024207 109 0.138674
___ @1UNRED2 59687 00:00:56 0.103296 0.019699 109 0.103296
Total CPU 5.667642
zIIP CPU 2.522497 45%
Billable CPU 3.145145 55% GP CPU 1.886550 33%
Other SRB 1.258595 22%

§ Are you seeing lots of “other SRB” with zIIP
– Using JARS to capture other SRB
§ zIIP SRB is not reported since it does not run on a GP CPU
Other SRB

§ V15 reduces “other SRB” with zIIP
Total CPU 5.581476
zIIP CPU 4.248196 76%
Billable CPU 1.333280 24% GP CPU 0.967602 17%
Other SRB 0.365678 07%
Cmd Jobname Jobnr Clocktime CPU-Time SRB-Time IOCount CPTime IIPtime IIPonCP IIPencl
___ SHUKE01 59656 00:54:02 5.729934 0.053566 2102 5.240694 0.489240 0.000089 0.489240
___ @2DBRUP2 60326 00:00:52 0.307367 0.051069 109 0.307367
___ QA14MUF4 60316 00:01:10 5.581476 0.365678 152683 1.333339 4.248196 0.000026 4.248196
___ @3PWRUP2 60323 00:01:05 0.264024 0.044803 109 0.264024
___ @1UNRED1 60325 00:00:52 0.432173 0.066993 109 0.432173
___ @3PWRUP3 60331 00:00:45 0.181963 0.028822 109 0.181963
___ @2DBRUP1 60318 00:01:05 0.426242 0.072268 110 0.426242
___ @2DBRUP3 60330 00:00:51 0.300624 0.045925 109 0.300624
___ @1UNRED2 60317 00:01:10 0.532060 0.088708 109 0.532060

§ V15 reduces “other SRB” with zIIP
Other SRB

New data provided in MUF EOJ and ALL_INFO_REPORT
Limited version of reduced “other-SRB” available for users in V14.02

Other considerations

§ Other considerations
– Are you wasting CPU cycles
§ Are you checking out
– Application errors (DB RCs or SQLCODEs)
– PXX dump activity
– Accounting activity
– CBS temp indexes
– SQL temp tables
– Sequential read ahead
Things you need to check

§ Other considerations
– Checking your activity snapshots
§ Look for different/increasing statistics
– Check your DBMSTLST assembly
§ Make sure you are proving the maximum buffers to DBUTLTY
§ Need to change data buffer sizes if you change block sizes
Things you need to check

Summary
A Few Words to Review
Results – Part I and II
Improving performance and reducing TCO can be accomplished in many ways within CA Datacom
The top techniques revolve around:
Move CPU consumption to inexpensive specialty processors
Reducing IO by using buffering and covering to maximize memory use
Reducing activities that consume CPU without benefit
Programs that get “bad return codes” as a normal mode of operation
Building temporary tables or indexes
Etc.

@CAWORLD #CAWORLD © 2016 CA. All RIGHTS RESERVED.42 @CAWORLD #CAWORLD © 2016 CA. All RIGHTS RESERVED.42
Mainframe and Workload Automation
For more information on Mainframe and Workload Automation,
please visit: http://cainc.to/9GQ2JI

Recommended Sessions
SESSION # TITLE DATE/TIME
MFX92S Strategy and Vision for CA Datacom and CA Ideal 11/16/2016 at 12:45 pm
MFX93S What’s new in CA Datacom 11/16/2016 at 1:45 pm
MFX94S Reducing TCO with Datacom Compression (Presspack) 11/16/2016 at 3:00 pm
MFX95SA Modernizing the CA Datacom data – SQL Access Part I 11/16/2016 at 3:45 pm
MFX95SB Modernizing the CA Datacom data – SQL Access Part II 11/16/2016 at 4:30 pm
MFX96S CA Datacom/AD Installation/Upgrade and Maintenance 11/17/2016 at 12:45 pm
MFX97S
CA Datacom/AD Report Utilization, and
information gather for Support
11/17/2016 at 1:45 pm
MFX98S
Using Sysview to monitor and measure your CA Datacom
environment – AD and DB
11/17/2016 at 3:00 pm
MFX99S Birds of a Feather/Stump the Techie! 11/17/2016 at 3:45 pm

Questions?

Thank you.
Stay connected at communities.ca.com

© 2016 CA. All rights reserved. All trademarks referenced herein belong to their respective companies.
The content provided in this CA World 2016 presentation is intended for informational purposes only and does not form any type of
warranty. The information provided by a CA partner and/or CA customer has not been reviewed for accuracy by CA.
For Informational Purposes Only
Terms of this Presentation

Advanced DBA – Performance Tuning for MUF Part II

Recommended

More Related Content

What's hot (20)

Viewers also liked (18)

Similar to Advanced DBA – Performance Tuning for MUF Part II (20)

More from CA Technologies (20)

Recently uploaded (20)

Advanced DBA – Performance Tuning for MUF Part II