Wednesday, October 30, 2019

Db2 for z/OS: The Potential Performance Impact of 100% CPU Utilization

Twice during the current calendar year, I've worked on a Db2 for z/OS performance issue that I've encountered at various times over the past couple of decades: a transactional Db2 for z/OS workload (versus batch - more on that in a moment) has periods characterized by major increases in response times, with related symptoms being high in-Db2 not-accounted-for time and high - sometimes very high - in-Db2 latch wait times (more on those metrics to come). Analysis of z/OS monitor data shows that the general-purpose "engines" (processors) of the z/OS system in question are running at or near 100% busy during the time of the performance slow-down, and then a disagreement might arise, with some people (me among them) saying that the 100% busy CPU situation is the root cause of the response time elongation, and others saying that this is not the case because a z/OS system, as they see it, should be able to deliver good application performance even with the general-purpose engines running at or near 100%.

In this blog entry, I am aiming to explain why you do not want to have your z/OS system's general-purpose engines running at 100% busy for a Db2 for z/OS transactional workload (zIIP engine utilization should be managed differently versus general-purpose engine utilization, as noted in an entry I posted to this blog a few years ago).

First, let's distinguish between "transactional" and "batch" workloads

When I refer to a transactional workload in this blog entry, I most fundamentally mean, "not batch," and with "batch" I am referring to a traditional (in a z/OS system) JES batch workload. This workload distinction is important for the following reason: in a situation in which a Db2 for z/OS workload is entirely (or almost entirely) of a batch nature, as might be the case during certain overnight hours for certain organizations, it can be OK to try to drive general-purpose engine utilization towards 100%. Why? Because for a traditional z/OS batch workload, maximizing general-purpose engine utilization can be a key factor in getting the batch jobs done within a certain time window (and that can be beneficial in a financial sense: your organization might have service level agreements with clients that stipulate that files sent to you by those clients will be processed before a certain time, with failure to achieve that objective potentially triggering financial penalties).

The transactional workloads to which I refer in this blog entry are those that have two main characteristics: 1) the units of work are relatively small in scale, versus the often-large units of work that are common for batch jobs; and 2) response time for individual transactions is of prime importance. For a batch workload, "response time" is not often part of the conversation - throughput is key (referring to getting a certain amount of work done within a certain time frame). For a transactional workload, it is often very important that individual transactions have very good response time, and when that is a performance goal then you do NOT want the general-purpose engines in your z/OS system running at or near 100% busy.

Second, there is 100% busy, and there is 100% busy

OK, so what am I saying with that odd-looking phrase? I'm saying that there may be a particular level of transactional work on your system that would take general-purpose engine utilization for the LPAR just to 100% busy, and it may be that performance for the transactional workload will be OK in that situation. Trouble is, you're quite likely to blow right past that workload level, and general-purpose engine utilization is still at 100% (it can't go higher) and performance (elapsed time for individual transactions) starts to get pretty bad.

Consider this example: suppose that, at 1000 transactions per second, the general-purpose (GP) engines of the z/OS LPAR are running at 90% busy, and performance is good. Suppose that the transaction rate goes to 1070 per second and GP utilization goes to 98% and performance is still OK. Maybe a transaction rate of 1090 per second takes GP utilization right to 100%, and performance is still OK. What happens when the transaction arrival rate (meaning the rate at which transactions come into the system) goes to 1100 per second, or 1150 per second, or 1200 per second? GP utilization is still 100% (again, it can't go higher), but performance (response times for individual transactions) is likely degrading, and it will keep getting worse as the transaction arrival rate continues to climb. The point is this: to say that a Db2 for z/OS transactional workload can get good performance with GP engines running at 100% is often, in a practical sense, what you might call an academic point, versus a real-world truism, because it is quite unlikely that a transaction arrival rate that just takes GP engine utilization to 100% will stay constant - either it will soon decrease to a level that takes GP utilization below 100% or it will soon increase to a level that will cause performance problems.

What does the performance data tell you?

If you encounter a Db2 transactional application performance problem (unacceptably high response times), and you think that an overloaded system might be the cause, check the utilization of GP engines during the period of the performance problem. This can be done using your z/OS monitor. If that monitor is IBM's RMF product, look at an RMF CPU Activity Report for the z/OS LPAR in which the Db2 subsystem is running (you can see an example of this report on a page in the z/OS Knowledge Center on IBM's Web site). The report typically shows activity in 15-minute intervals of time. For a given 15-minute interval, find the section with CPU utilization numbers. The processors identified as "CP" under the "TYPE" heading are general-purpose engines (zIIP engines are identified by "IIP" under the "TYPE" heading). For the general-purpose engines (TYPE=CP), look at the figure under the heading  "MVS BUSY" in the "TOTAL/AVERAGE" row. See if that value is at or near 100%.

If you see that GP utilization was at or close to 100% during the period of the Db2 transactional performance problem, look next at figures in an accounting report generated by your Db2 monitor. If your monitor is IBM's OMEGAMON for Db2, the report is called an Accounting Long report. You can see an example of that report on this page in the Db2 for z/OS Knowledge Center. [A quick word about this example: it shows the standard aggregation level for data summarization, which is primary authorization ID within plan name. That aggregation level can be useful at times, but my preference is aggregation at the connection type level, which you get when you specify ORDER (CONNTYPE) in the report control statement. When data is ordered by connection type, you get, within the larger report, a sub-report showing all activity in the FROM and TO time period for each connection type used during the period - for example, one sub-report showing all CICS-Db2 activity for the subsystem, one showing all call-attach activity for the subsystem, one showing all DRDA activity (the DDF workload) for the subsystem, etc. I like that aggregation level because if I'm investigating, say, a DDF workload performance problem then I look at the sub-report for the DRDA connection type.] Anyway, if you're looking at the example report pointed to by the preceding link, go to page 1-5 of that sample report. You'll see, at the top of that page, a couple of sideways bar charts. One of the fields of the right-side bar chart is NOTACC. That's in-Db2 not-accounted-for time. For the transactional workload for which there was a performance problem, check that value. If it's greater than 10%, that's an indication of an elevated level of wait-for-dispatch time in the z/OS system, and that, in turn, is an indication of overly-busy GP engines (for more information on in-Db2 not-accounted-for time, you can look at an entry I posted to the Db2 blog I maintained while working as an independent consultant, prior to rejoining IBM in 2010).

Something else to check in that accounting report: Db2 latch wait time, one of the so-called "class 3" suspension times provided in a Db2 monitor accounting report. Normally, latch wait time is a very small percentage of in-Db2 elapsed time for a transactional workload, and a big increase in latch wait time is an indicator of an overloaded z/OS system. In my experience, when highly elevated latch wait times are seen during a period in which Db2 transactional performance is degraded due to an overloaded z/OS system, the data is often misinterpreted. People will see, in a Db2 monitor-generated statistics long report (that may be called a statistics detail report, depending on the monitor used), a big increase in the number of what is labeled "latch counts" for one or more latch classes during the period of elongated transactional response times, and they'll say, "Wow - why are we getting so many more latch class 14 (or 23 or 25 or whatever) events during this period?" and they'll look for things that might be done to reduce those seemingly high latch counts. The thing is, what's being shown in the statistics report is not the count of latch requests, per se - it's the count of latch suspends, meaning, the number of times that a process was suspended while waiting to get a required latch (Db2 uses what are called latches internally to ensure serialization of certain activities that have to be serialized - an example is the latch required when Db2 splits an index page). See, when a z/OS system is not overloaded, a required Db2 latch is often acquired right away, with no need to suspend the associated application process. Conversely, when a z/OS system is overloaded (GP engines running at or near 100%), a single latch request can lead to several suspensions; thus, a big increase in "latch counts" does NOT necessarily mean that more latch requests occurred - it could well be that about the same amount of latch activity was accompanied by many more latch-related suspensions. The problem, in other words, is not more latch activity - it's more suspends related to latch activity, and the increase in latch suspends can very much be a result of an overloaded z/OS system.

Dealing with an overloaded z/OS system

I'd like you to be in a situation in which utilization of the GP engines of your z/OS system doesn't exceed 90% during periods of peak Db2 for z/OS transactional traffic, but maybe that's not the case at your site. What if you are dealing with GP engine utilization that gets to 100% or very close to that when you have a lot of transactional work going through the system, and you see during those times the often-related Db2 performance symptoms: elongated transaction response times, high in-Db2 not-accounted-for time, high Db2 latch suspension time and high "latch count" numbers? What can be done? Some thoughts:

  • Maybe you can reduce CPU consumption through performance tuning actions. Do you have a lot of memory on the system? Maybe you can reduce CPU by enlarging and page-fixing buffer pools, and backing those pools with large page frames. Maybe you can utilize high-performance DBATs via the Db2 profile tables. Maybe other performance tuning actions would get GP engine utilization below 100%. Worth a try.
  • Maybe you can keep transactions flowing quickly through the system by inducing some degree of transaction queuing during peak processing times. One way to do that would be to set MAXDBAT to a value that will keep the Db2 system from getting flooded with DDF transactions (that can be done in a more granular fashion - for particular DDF-using applications - by way of the Db2 profile tables).
  • Maybe you can adjust some dispatching priorities. Getting a z/OS system's workload manager (WLM) policy right is especially important when the system is really busy. I have some advice for priorities that pertain to a DDF workload.
  • Maybe you can add processing capacity. It's likely that, at various times in the past, your organization has increased mainframe processing capacity as application workloads have increased. Maybe it's time to do that again.

Can you run a transactional Db2 workload on a z/OS system on which GP engine utilization is at or near 100%? Yes, in the sense that the system won't fall over. Do you want to run a transactional Db2 workload on a z/OS system on which GP utilization is at or near 100%? I would say, "No." Tuning actions, efficient application program code, proper workload prioritization - all are important for good performance. So is processing capacity - have enough of it.