This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Oct 15, 2013 2:58 PM by Dude! Go to original post RSS
  • 15. Re: How to get the highest Ave Mem & CPU Usage?
    Dude! Guru
    Currently Being Moderated

    Actually a CPU is pretty much always running at 99 %, even when doing nothing. When a CPU is 99 % idle, it means the Idle process, running at the lowest priority, does not have to wait for any other processes. To my understanding, there is no traffic jam in a CPU, unless there is a hardware error. A CPU can take any number of process within the configured limit. The problem is that each additional process gets a smaller slice of the available CPU time and the result are increased wait times and slow response times. Like I mentioned earlier, if a certain workload is reached, the system will increase CPU voltage to make it faster and "buy" more time, which will increase its heat.

     

    When a CPU reaches 100 % it means the processes on your computer are using the full energy and possible capacity of the CPU. This is rather a good than a bad sign during high demand or peak performance, and it does not necessarily mean that the machine cannot handle any additional tasks in a reasonable or timely manner. A CPU can be 100 % busy with one task, but also with 1000 tasks, or in other words, does not necessarily mean that a slow down when adding more processes is going to be a problem. A problem certainly occurs when the OS scheduler itself uses 100 % or processes are waiting for interrupts, such as disk access, in which case the system cannot continue with other tasks in order to maintain data integrity, and may freeze and may need to be reset to unblock the issue.

  • 16. Re: How to get the highest Ave Mem & CPU Usage?
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi

    Dude        ! wrote:

     

    Capacity planning is a lot more complex than gathering statistics and verifying that some numbers are within a certain limit. If CPU and Memory shows 100 % utilization during peak times, it simply means the system or task is using all available resources to perform what it is doing.

     

    When you reach CPU = 100%, you cannot put any more processes on the runqueue. That means that instead of running, they have to wait to be put on CPU. The more database demand for CPU exceeds capacity, the more time it spends waiting, and less time doing something useful.  Worse yet, CPU starvation affects background processes as well. For example, you can have "log file sync waits" going throug the roof just because LGWR spends large amount of time waiting or CPU, and unless you know about your CPU situation, you can waste lots of time pursuing the wrong trail.  Because of its effect on background processes, CPU starvation can distort other performance metrics.

     

    So knowing whether or not your CPU is saturated is the departure point for any serious performance analysis, not just because availability of CPU is crucial for system's normal performance, but also because it tells you how much you can trust other performance statistics.

     

    And all this stuff you posted here about voltage and frequency increasing to accomodate changes in the workload -- where did you get all this? Can you post a link? Because it sounds a lot like total nonsense.

     

    Best regards,

      Nikolay

  • 17. Re: How to get the highest Ave Mem & CPU Usage?
    Dude! Guru
    Currently Being Moderated

    Did you ever wonder why the CPU becomes hot and the fan's turn faster when you reach a certain system workload? Perhaps it's all like you say "nonsense", but I think its called CPU Frequency Scaling and you can look it up in Google.

     

    When you reach CPU = 100%, you cannot put any more processes on the runqueue.

     

    Interesting idea, but it depends on the CPU scheduler, and last not least the reason for the workload. There are various ways to handle a 100 % CPU situation, such as lowering process priorities. And btw, the average CPU run queue contains processes running, including processes ready or waiting to run.

     

    Regarding links, I don't know how representative the following is, but it pops up in Google:

    http://www.dba-oracle.com/t_high_cpu.htm

     

    vmstat documentation:

     

    Optimum use would have the CPU working 100 percent of the time. This holds true in the case of a single-user system with no need to share the CPU. Generally, if us + sy time is below 90 percent, a single-user system is not considered CPU constrained. However, if us + sy time on a multiuser system exceeds 80 percent, the processes may spend time waiting in the run queue. Response time and throughput might suffer.

     


  • 18. Re: How to get the highest Ave Mem & CPU Usage?
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    Dude        ! wrote:

     

    Did you ever wonder why the CPU becomes hot and the fan's turn faster when you reach a certain system workload? Perhaps it's all like you say "nonsense", but I think its called CPU Frequency Scaling and you can look it up in Google.

    I did look it up in google. Did you? It's a power-saving technique implemented in Linux kernel code. Its goal is to reduce power consumption, not improve performance, so I'm still not sure why you decided to bring it up in this discussion.

     

    Regarding links, I don't know how representative the following is, but it pops up in Google:

    http://www.dba-oracle.com/t_high_cpu.htm

     

     

    As long as we are discussing things that pop up in google -- when I enter "AWR CPU" in google, the number 1 result is a link to an article I posted in my blog last year, and number 2 is a link to an article on Donal Burleson's website that references my article as the only source:

     

    AWR reports: interpreting CPU usage | Oracle Diagnostician

    CPU in AWR Report tips

     

    Best regards,

      Nikolay

  • 19. Re: How to get the highest Ave Mem & CPU Usage?
    Dude! Guru
    Currently Being Moderated

    I agree that Dynamic Frequency Scaling was perhaps a bad example to bring up since it is typically not used for server CPUs and primarily a power saving feature, but it also affects processing power and workload readings. However, it a CPU feature and not nonsense.

     

     

    If you think that 100 % CPU is a bottleneck, by all means. I provided a link that has other links and information showing otherwise. If you do not agree, that's fine, but perhaps you can provide a link other than your own blog.

  • 20. Re: How to get the highest Ave Mem & CPU Usage?
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

     

    If you think that 100 % CPU is a bottleneck, by all means. I provided a link that has other links and information showing otherwise. If you do not agree, that's fine, but perhaps you can provide a link other than your own blog.

    How about the official Oracle Performance Tuning Guide?

     

    Managing Operating System Resources

    Section 9.3, "Understanding CPU"

     

    "Workload is an important factor when evaluating your system's level of CPU utilization. During peak workload hours, 90% CPU utilization with 10% idle and waiting time can be acceptable. Even 30% utilization at a time of low workload can be understandable. However, if your system shows high utilization at normal workload, then there is no room for a peak workload."

     

     

     

    Best regards,

      Nikolay

  • 21. Re: How to get the highest Ave Mem & CPU Usage?
    Alvaro Pro
    Currently Being Moderated

    I think the key word here is PEAK workload.

     

    100% CPU utilization under normal workload does not necessarily mean a problem IF the database is performing as expected. It just means you have no more room to accommodate additional work on your database. You have no more vertical scaling capacity, if you will. You have used all of your resources as far as CPU is concerned, that means if you need some additional CPU for some peak workload period, not only you will not get it, but it's likely it will degrade it since CPU starvation triggers a chain reaction on the response times because it affects background processes.

     

    So as far as the OP question goes: "Can my server handle additional workload?" If CPU is operating at 100% nearly all the time then the answer is likely no. If that 100% CPU means there is already a CPU starvation going on is impossible to tell without knowing the system and having the appropriate baselines to compare against.

     

    My 2 cents.

  • 22. Re: How to get the highest Ave Mem & CPU Usage?
    Nikolay Savvinov Guru
    Currently Being Moderated

    Sorry, but wasn't I saying this all along? Let me quote my earlier post in this thread:

     

    "And about database using 100% of all resources being a good thing (because "otherwise they would've been wasted") -- well, the fact that this is a popular opinion doesn't make it any less wrong. There are always fluctuations in workload. If a system is at 100% of its capacity, then it won't be able to handle such fluctuations adequately. Plus, often people underestimate the danger of CPU starvation because they don't fully appreciate the difference between instantaneous and average values."

     

    BTW I think I finally understand now where all this confusion is coming from. When people talk about whether high CPU usage is a problem, they can mean one of two things:

     

    1. "high CPU usage is bad, your CPU usage is high, ergo your current slowness is explained by high CPU usage". This is WRONG. CPU usage of 80, 90 or even 100% does NOT explain any slowness, unless there is evidence of CPU saturation

    2. "high CPU usage is bad because you need safety margin in order to accomodate potential fluctuations in the server workload". This is correct, not only because database workload doesn't remain constant, but also because the database doesn't own any CPUs. Database instance lives in a server OS which has processes of its own. Even if there are no other databases or application servers running on the host, there are still background processes of various sorts, so it's unsafe to run without some CPU "head room".

     

    Best regards,

      Nikolay

  • 23. Re: How to get the highest Ave Mem & CPU Usage?
    Dude! Guru
    Currently Being Moderated

    A simple practical test is sometimes the best proof of concept:

     

    In the below test I'm maxing out the CPU of my computer to 100 %. However, the system is still responsive and accepts additional tasks. I'm even browsing the web and writing this message while the cpu is as busy as it can be. I notice a slowdown, but it is managable.

     

    So, even though the CPU runs at 100 % it still accepts additional processes. It does not accept additional workload without slowing down other processes, but whether or not the result is still acceptable depends on perception and expected result. One way to find out is to collect statistics and compare them with statistics when performance is not acceptable.

     

    $ yes > /dev/null &

    top reports: CPU usage: 90.90% user, 9.9% sys, 0.0% idle

    2138  yes          88.2

     

    Let's do a few more:

     

    $ yes > /dev/null &

    [2] 2146

    $ yes > /dev/null &

    [3] 2147

    $ yes > /dev/null &

    [4] 2148

    $ yes > /dev/null &

    [5] 2149

    $ yes > /dev/null &

    [6] 2150

     

    CPU usage: 92.80% user, 7.19% sys, 0.0% idle

    2150  yes          11.7

    2149  yes          11.1

    2146  yes          10.5

    2147  yes          10.4

    2151  yes          10.4

    2148  yes          10.4

  • 24. Re: How to get the highest Ave Mem & CPU Usage?
    Nikolay Savvinov Guru
    Currently Being Moderated

    You didn't have to bother -- I never argued that a system at 100% CPU usage can't be responsive. Sure it can. In fact, just a week ago I found that one of databases used by dozens of developers from several different teams had been at CPU=100% since July. All this time, it was responsive enough for no one to notice the CPU problem. At least not until one of the teams started their own batch, which became the proverbial straw that broke camel's back.

     

    Best regards,

      Nikolay

  • 25. Re: How to get the highest Ave Mem & CPU Usage?
    jgarry Guru
    Currently Being Moderated

    There is a third thing.  The optimizer may decide to do full scans in the SGA.  If the plan had previously used an index to get a smaller data set, this can appear to users that "suddenly, the computer is slow."  They see an entire cpu being taken up for a long time, where previously all cpu's were mostly idle.  While we and the cpu may see this as an increase in workload, the users and managers don't.  They just see 100% cpu usage for no reason.  

     

    Also, I'm wondering whether "When you reach CPU = 100%, you cannot put any more processes on the runqueue." is really the way to say it.  I know it's a semantics argument, but isn't "run queue getting longer" the real serious problem?

  • 26. Re: How to get the highest Ave Mem & CPU Usage?
    Dude! Guru
    Currently Being Moderated

    My argument was in response to the original poster asking how to get the highest memory and cpu usage to determine whether the system can take another 10 % workload.

     

     

    I think the misconception is that 100 % CPU does not mean 100 % empty or the end of the rope. From what I understand, 100 % CPU means there is no CPU time left to run any more processes without slowing down other processes, perhaps even regardless of process priorities. Whether performance is still acceptable is a different question.

     

     

    When a CPU runs nearly 100 % most of the time, it is reasonable to assume that the machine is overloaded, although overloading in this respect is really a matter of perceived response times.

     

     

    It seems to be a common myth that once a CPU reaches 100 % something needs to be done in order to fix it. In most, if not all cases, a CPU will always reach 100 % regardless and it is just a question how long it takes to complete a process. One does not necessarily need to buy a bigger car when the gas pedal reaches the floor.

  • 28. Re: How to get the highest Ave Mem & CPU Usage?
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi

    jgarry wrote:

     

    There is a third thing.  The optimizer may decide to do full scans in the SGA.  If the plan had previously used an index to get a smaller data set, this can appear to users that "suddenly, the computer is slow."  They see an entire cpu being taken up for a long time, where previously all cpu's were mostly idle.  While we and the cpu may see this as an increase in workload, the users and managers don't.  They just see 100% cpu usage for no reason.

    What is "a full scan in the SGA"? And since when the optimizer gets to decide whether to access blocks from SGA or from disk? Why would a change in a plan for one query appear as "suddenly, the computer is slow" -- they would be able to notice that other queries are still running as fast as before?

     

    If a query is executed in parallel mode, or if several sessions run the same SQL concurrently, then a plan flip can cause serious performance trouble for the entire database -- is this what you were trying to say? If so, then sure, I won't argue with that. And if this query is CPU intensive for whatever reason, then it would lead to increase in CPU usage (and possibly -- CPU queuing latency). And if this happens, then the root cause needs to be identified and addressed, not just for the sake of the affected query, but also for the sake of other activities on the database that can be seriously affected.

     

    I'm not sure why you consider this a special "third" case. BTW, what were the first two?

     

    jAlso, I'm wondering whether "When you reach CPU = 100%, you cannot put any more processes on the runqueue." is really the way to say it.  I know it's a semantics argument, but isn't "run queue getting longer" the real serious problem?

     

    Yes, I misspoke.

     

    Best regards,

      Nikolay

  • 29. Re: How to get the highest Ave Mem & CPU Usage?
    jgarry Guru
    Currently Being Moderated

    Where you said "...they can mean one of two things:"

     

    I was merely pointing out the difference between what managers see and what we know.  This becomes real obvious on the dbconsole performance screen (on an otherwise lightly loaded system), you see a sea of green for a particular session.

     

    When I say a full scan in the sga, I'm referring to how some table/index can be kept warm by being accessed repeatedly.  People tend to think full scans mean lots of disk i/o - which in many cases, it ought to be, and things are moving towards direct reads - but in many cases the blocks haven't been aged out, and cpu is spending lots of time copying all those blocks memory to memory.  We all know memory is faster than disk - but the problem comes when a lot more is accessed in memory than should be gotten from disk.  It just takes one bad sql to throw the average cpu way up.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points