Is CPU usage 100% really okay?

Even the great ones do misspeak from time to time. And when they do, this can lead to a terrible mess in people’s heads.

Here is one example:

Are you at 100% utilization? If not, you haven’t accomplished your job yet. You cannot
put CPU in the bank and save it for later. So, if you are running with idle cycles you
should be looking for more ways to use it.

This statement is, at best, very confusing (and you can see some examples of this confusion in this recent OTN thread).

Let’s apply it to a specific example:

~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                2.80      1.03      12.8       6.0       3.0      81.2

Instance CPU
~~~~~~~~~~~~
              % of total CPU for Instance:       7.6
              % of busy  CPU for Instance:      40.1
  %DB time waiting for CPU - Resource Mgr:       0.0

So, what do we see here? According to the quote above, the system is wasting more than 80% of it’s CPU cycles. And we should be looking for more ways to use it. Or should we? Let’s take a closer look:

Statistic                                  Value        End Value
------------------------- ---------------------- ----------------
...
BUSY_TIME                              9,773,733
IDLE_TIME                             42,171,849
...
OS_CPU_WAIT_TIME                      13,525,800

The system spends more time WAITING for CPU than running on it!

Apparently, the system is doing nothing throughout most of the observation period — but when it does, demand for CPU exceeds the supply,and processes have to get in line. Hardly an optimal situation (and much less, a situation when we should be looking to put more load on CPU).

Nevertheless, Tom Kyte’s statement isn’t entirely wrong. It’s true under a number of conditions: for example, for a single CPU, in absence of CPU queuing, and for instantaneous values (not for averages!) CPU usage of 99.99% is not only okay, but optimal, because it’s using CPU cycles to do work. But even in this case it doesn’t mean that it’s advisable for a real-life system to stay at 99.99% mark for an extended period of time, because in real life workload always fluctuates, and by running at 99.99% you eliminate the necessary “head room” to handle such fluctuations.

In case of multiple CPUs (and nowadays that’s the standard for any decent database) the statement doesn’t hold any more. Consider a system with 4 CPU’s. If the total CPU usage is above 75%, that means that at least 3 CPUs are already “full” and processes running on them are subject to CPU scheduling latency. The fact that there is a fourth CPU with some time available on it doesn’t always help, because switching a process from one CPU to another (“context switching”) is expensive (or if CPU affinity is set for the process, then it’s impossible altogether).

Another important point is that 99.99% might be okay if it’s a peak value (although e.g. official Oracle documentation is more conservative about that and recommends peak values not to exceed 90%). But more often, we are looking at averages (e.g. AWR reports provide average values for intervals between snapshots, i.e. 1 hour averages by default). And when you see an average of X%, there is no way of knowing whether was at X% 100% of the time or at 100% X% of the time. So in this case what you should really focus on, is not CPU usage per se, but rather, time spent waiting for CPU (available as OS_CPU_WAIT_TIME in V$OSSTAT and AWR).

Failure to ensure enough available CPU for the system can lead to things far worse than everything running somewhat slower than usual. When the system is approaching the CPU limit, its processes at CPU scheduler’s mercy. And while you know that LGWR or DBWR is more important than a session running some background batch job, CPU scheduler doesn’t. The CPU scheduler may also be unaware of dependencies between processes, so nasty things like priority inversion can happen.

Worse yet, in addition to causing a performance issue, CPU starvation also is likely to affect the diagnostic information you need in order to tackle this performance issue. Since all computer subsystems need CPU to run normally, when there is not enough of it, all subsystems are affected, so it can look like a log file sync issue, and I/O issue or virtually anything. I even saw one system which was almost completely paralyzed, but looked as if it was idle.

To sum up:

1) The threat of CPU starvation is not to be taken lightly
2) While in theory a system can have 99.99% CPU usage and not be in trouble, in practice you need to have enough free CPU to accomodate possible fluctuations in the workload
3) CPU usage figures cannot tell you whether or not CPU is already a bottleneck but they can warn you that it’s approaching
4) CPU queuing stats, like OS_CPU_WAIT_TIME, can tell you whether or not CPU is already a bottleneck
5) Low average CPU usage doesn’t mean that CPU is not a problem — you need to look at peak values
6) Monitoring and understanding CPU usage, its short-time and long-time periodicity cycles, trends, fluctuations and anomalies, is a must for any responsible DBA and SA

P.S.  After I already finished the first draft, I stumbled upon an excellent post by Charles Hooper on the same subject, with far more quotes and references, and a more technical post by Cary Milsap. Another interesting piece of reading on the subject is this OTN discussion between Jonathan Lewis, Charles Hooper and Donald Burleson.

P.P.S. Thanks to Swapnil Rane for providing the AWR report.