Yet Another Nice Myth

A while ago I explained Nice, what it can do and what it can’t. From this point, whenever someone misunderstood nice, I just pointed at my post.

But yesterday I ran into a misunderstanding that was not covered in the first post:

Boss: Server X is at 100% cpu. Can you renice process 4096?
Me: Sure thing boss. *clickety-click* Done
Boss: Why is cpu still at 100%?

Renice will not lower CPU utilization because:
1. It does not kill processes. After renicing the process will have lower priority, but it will still need the same amount of CPU it did before, and so do all the other processes in the system. At any given moment, our nice process will either wait for CPU or use CPU. Either way, we are still at 100% CPU.
2. You can’t lower the priority to a point that the CPU will prefer to sit idle instead of running that process. Which is exactly what you ask for if you expect renice to affect CPU utilization.

The Linux OS will not let the CPU run on IDLE if someone wants it. It makes absolutely no sense to make processes wait while the CPU is not doing anything.

Boss: Sysadmin types always say that 100% CPU is not necessarily a bad thing, but we are DBAs and we know that if a machine is at 100% CPU it means trouble.

I hope we can all agree that what we want to prevent is processes having to wait for CPU. Ideally, every process that needs CPU will get it immediately. In this sense, 100% CPU is indeed a bad thing, because either processes are already waiting for CPU (look at load average!) or it can happen any second. But it is not because 100% cpu is the problem, it is because waiting processes are the problem.

Suppose you have a classic OLTP system – lots of tiny, fast, transactions. Usually the system runs at around 60% CPU on its single CPU server, but this morning someone ran a huge cpu-intensive report on this system. You know that this report runs for 60 minutes on an empty system. He begs you not to kill the process, but he doesn’t mind waiting. Now you have two options:

1. Let this process run as usual. Since it is a large report, it could be scheduled for more than its fair share of CPU. Taking maybe 80% of cpu time, leaving 20% for our tiny transactions. Process will finish in 75 minutes. On average each tiny transaction will take three times longer than usual. 75 of 100% cpu, bad performance and complaints.
2. Renice the process. Now it will get 40% of CPU time. Tiny transactions get 60% (which is all they need). Report takes 150 minutes, tiny transactions take as usual. 150 minutes of 100% cpu, no performance issues.

Obviously the second scenario is much better. Even if you spend twice as much time in 100% CPU. So maybe 100% cpu is not always evil, and renice is useful even when it doesn’t lower the cpu utilization.


Boss: But look at Top! Things look terrible! How do we know that everything is OK?

It is important to remember that viewing the CPU world through “top” tends to skew our perspective a bit. Top gives CPU utilization aggregated according to its refresh rate – 3 seconds by default. Now during these 3 seconds, 1.2 seconds were used by huge report (40%!) and 2.8 seconds used by 20 processes pooling all those tiny 2 ms transactions. Each process will show close to 0.05% CPU. It looks scary – big bad report taking so much CPU leaving almost nothing for other processes. But each time a transaction wanted to run it got 100% of the CPU. It just finished very very fast, and the big report stayed.

How do we REALLY know that everything is OK?
I trust Oracle on this and go to Active Session History tables (or EM report), find the transaction processing sessions, check out the wait events and make sure CPU is reasonable.

Boss: But what do we do about CPU?

So, 100% CPU is not good, but we can’t use renice to solve this. What can we do?
Killing the offending process is a crude but effective solution. Rumor says SQL Profiles that will allow you to alter the execution plan of the query, maybe making it easier on the CPU. I never tried it. Of course, the ultimate solution is to optimize the report to use less CPU, and in my experience it is easier to convince developers to optimize after you killed the report several times. In worst case, buying more CPU power is always an option.

Advertisements

13 Comments on “Yet Another Nice Myth”

  1. Freek says:

    Nice article

    šŸ˜‰

  2. Noons says:

    One of the things that tells you if you *really* have a CPU resource issue is the run queue size.

    When *n*x has processes ready to use cpu but it’s not available for any reason – busy with other processes, for example – it then increases the run queue process counter.

    nmon and some flavours of the sar command will show you the run queue size.

    If it is less than the number of CPUs in your system, it is very likely that you may not have a CPU resource problem even if they are all going flat out at 100%.

  3. Brian Tkatch says:

    Nice writeup.

  4. Mark J. Bobak says:

    Interesting post. The way I look at it, in a perfect, ideal world, 100% CPU utilization is nirvana. That’s cause in the perfect, ideal world, the system is operating like a well-choreographed ballet. One process steps off the CPU to wait on I/O just as another process receives it’s I/O and steps on the CPU. And on and on.

    However, in the real world, it doesn’t work that way. In the real world, workloads vary, users are logging in and out, some times long running intensive reports are running, sometimes not, etc. In this real world, the closer you are to 100% CPU utilization, the less ability your system has to react to a sudden (or not so sudden) surge in usage. So, if you’ve read Cary Millsap’s book, this is where the “hockey-stick” graph comes in to play. Depending on how many CPUs (service channels) your system has, determines how high you can take your CPU utilization without spinning out of control.

    So, yeah, I’ve seen systems running at 100% and doing it well. But, I wouldn’t want to expose that system to any uncertainty or volatility in workload.

    -Mark

  5. prodlife says:

    Hi Noons,

    I agree that run queue is a good indicator. I’ve been trying to find out how run queue is different than load average. Maybe you know?

    Anyway, if all CPUs are flat at 100%, the next process that will arrive will increase your sun queue, so you can start preparing now.

  6. prodlife says:

    Mark,

    This is exactly why I wrote:
    “100% CPU is indeed a bad thing, because either processes are already waiting for CPU (look at load average!) or it can happen any second”

    The problem is that once the processes are there, and they need the CPU and you need them to run, there is very little you can do about it. You want them to use as much CPU as possible so the users will wait as little as possible.

  7. Noons says:

    Load average is usually the run queue length plus the number of processes actually executing, averaged over three periods of time.

    Whereas the run queue itself is just a point in time measurement over the sampling period.

    As such, the run queue value tends to be “peaky”, whereas the load average is less volatile.

    I prefer to watch the run queue length over a period of time as it tends to highlight peaks of CPU usage while the other one “smoothes the curve”, so to speak.

  8. prodlife says:

    Thanks Noons!

  9. Chris says:

    resource manager is another way to control people who want to run reports on OLTP systems during the day. The advantage over nice is for us poor bods with really strict *nix admins who won’t give us access to nice (or even top on some boxes) we can control the cpu available to different users/services/programs etc.
    this does require some up front planning, but reduces the requirement to re-nice stuff on the fly also for support users (typically having read access to lots of tables in production) ad-hoc sql doesn’t kill us the way it would without resource manager.

  10. prodlife says:

    Chris,

    Yes, I love resource manager, and we are now in the process of implementing it. Of course, resource manager won’t let cpu go below 100% on a busy system either.

    Nice on Linux still has the advantage of being open-source, so you can know exactly what the man behind the curtain is doing. With resource manager, it is back to trusting Oracle šŸ™‚

  11. Chris says:

    If you don’t trust oracle why put all your data in there?

    Seriously though most of the issues I’ve had with the ‘OMG the processor is at 100%, do something to reduce it’ type conversations have been because there is no performance SLA with the customer/business unit using the database. simple stuff like the application should process a ‘standard’ transaction in x amount of time when y users are active on the system.

    I always try to ask the question what is acceptable performance, and try to get people to rmember you can’t save memory/cpu for later so if its 100% used and you have acceptable response times there is no performance problem.

  12. […] 30, 2008 After I published “Yet Another Nice Myth”, my friend MosheZ sent me an angry email: ” STOP looking at top. You need actual application […]

  13. Hello Oracle experts,

    This and the another blog about 100% CPU reminded me of some experiences I had a few years ago. My comments started to be a little long, so I made a post here:

    http://rodgersnotes.blogspot.com/2010/02/oracle-database-tuning-and-being-nice.html

    Hope it’s useful.

    Do the experiment!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s