FAQ
I'm not a hardware guy or sys admin person so forgive me if this is a stupid question. Leaving out all other variables(such as IO), should I expect performance to be the same in a databse if the server it is riding at is at 90% cpu usages as opposed to 10%? since there would still be spare cycles? Or is there a declining returns as you get closing to the maximum available cpu usage?

Search Discussions

  • Goulet, Dick at Oct 22, 2004 at 2:55 pm
    Ryan,

    From my Unix SysAdmin once you reach 60% CPU utilization
    you've reached the point of diminishing returns, namely things are going
    to slow down real fast. This is one time I believe him as we've seen
    the results.

    Dick Goulet
    Senior Oracle DBA
    Oracle Certified 8i DBA
    -----Original Message-----
    From: ryan_gaffuri_at_comcast.net =20
    Sent: Friday, October 22, 2004 3:56 PM
    To: oracle-l_at_freelists.org
    Subject: question about cpu usage

    I'm not a hardware guy or sys admin person so forgive me if this is a
    stupid question. Leaving out all other variables(such as IO), should I
    expect performance to be the same in a databse if the server it is
    riding at is at 90% cpu usages as opposed to 10%? since there would
    still be spare cycles? Or is there a declining returns as you get
    closing to the maximum available cpu usage?=20
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Gogala, Mladen at Oct 22, 2004 at 3:03 pm
    Question asked in such generality really doesn't make much sense and can
    only have one
    answer: it depends. Mostly, it depends on what is CPU doing. Well optimized
    queries will
    typically have a short burst or two of intense CPU activity and then will
    finish. Using
    100% of CPU power is, unfortunately, also characteristic for "well cached"
    queries which
    can perform a gazillion logical block gets with no phyisical disk reads. An
    example of
    such query is the following:

    SELECT COUNT(*) FROM EMP,EMP,EMP,EMP,EMP,EMP,EMP,EMP,EMP,EMP;

    Table emp normally has 14 rows so the number of rows to count is POW(14,10)
    and
    RDBMS process will be spinning using 100% of CPU for approximately 30
    minutes.
    What will you get? A worhless number which could have been computed in a
    much, much
    cheaper fashion. So, the answer to your question is: optimize your most
    expensive
    queries and only then try predicting scalability and growth of your
    database.

    --
    Mladen Gogala
    A & E TV Network
    Ext. 1216
    -----Original Message-----
    From: ryan_gaffuri_at_comcast.net
    Sent: Friday, October 22, 2004 3:56 PM
    To: oracle-l_at_freelists.org
    Subject: question about cpu usage


    I'm not a hardware guy or sys admin person so forgive me if
    this is a stupid question. Leaving out all other
    variables(such as IO), should I expect performance to be the
    same in a databse if the server it is riding at is at 90% cpu
    usages as opposed to 10%? since there would still be spare
    cycles? Or is there a declining returns as you get closing to
    the maximum available cpu usage?
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Ryan_gaffuri_at_comcast.net at Oct 22, 2004 at 3:52 pm
    ok i will refine the question.
    Assuming I am at a constant rate X of CPU usage, will there be a declining returns as CPU usage increases to X + n for a constant period of time.

    Assume CPU usage holds steady. CPU u sage is CPU usage. It shouldnt matter what its doing in terms of performance.
    -------------- Original message --------------
    Question asked in such generality really doesn't make much sense and can
    only have one
    answer: it depends. Mostly, it depends on what is CPU doing. Well optimized
    queries will
    typically have a short burst or two of intense CPU activity and then will
    finish. Using
    100% of CPU power is, unfortunately, also characteristic for "well cached"
    queries which
    can perform a gazillion logical block gets with no phyisical disk reads. An
    example of
    such query is the following:
    --
    http://www.freelists.org/webpage/oracle-l
  • Sol beach at Oct 22, 2004 at 6:53 pm
    It is not as simple as you might initially think it is.
    Consider that in reality, saying a CPU is 50% busy/idle is somewhat
    nonsensical.
    Either is it doing useful work (at the request of the OS) [100% busy]
    or it is idle [0% busy].
    At any & EVERY point in time the CPU is either 100% or 100% idle;
    nothing in between.

    Now over a period of time it could be busy half the time (50% busy & 50% idle).

    What happens if 10 different folks concurrently as the CPU to do work for them?
    The answer is that 9 folks go into the CPU-wait queue.

    As system system statistics display the reality the CPU is approaching
    100% busy,
    users may or may not experience a degredation in response time.

    It all depends on the charateristics of the work load.
    It might even depend upon the law of large number.

    HTH & YMMV!

    On Fri, 22 Oct 2004 20:57:15 +0000, ryan_gaffuri_at_comcast.net
    wrote:
    ok i will refine the question.
    Assuming I am at a constant rate X of CPU usage, will there be a declining returns as CPU usage increases to X + n for a constant period of time.

    Assume CPU usage holds steady. CPU u sage is CPU usage. It shouldnt matter what its doing in terms of performance.


    -------------- Original message --------------
    Question asked in such generality really doesn't make much sense and can
    only have one
    answer: it depends. Mostly, it depends on what is CPU doing. Well optimized
    queries will
    typically have a short burst or two of intense CPU activity and then will
    finish. Using
    100% of CPU power is, unfortunately, also characteristic for "well cached"
    queries which
    can perform a gazillion logical block gets with no phyisical disk reads. An
    example of
    such query is the following:
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • DENNIS WILLIAMS at Oct 22, 2004 at 3:58 pm
    Ryan

    Perhaps you can tolerate a mini-O.S. lesson.
    A process can be in several states -- running (only one process/CPU),
    waiting (for I/O, user response, etc.), or ready-to-run (or run queue). The
    O.S. lets each process have the CPU (running) for a short interval of time,
    or until it must wait for something like I/O. Once a process has its I/O
    satisfied, it is put on the ready-to-run queue until the O.S. decides to put
    it back on the CPU.

    Basically there is a queue of processes waiting for the CPU. If the
    system is nearly idle, that queue will be very short. When the process is
    back from the I/O, it is quickly put back on the CPU. If the system is
    heavily loaded, then the queue will get very long. If you are interested and
    on Unix, check out the uptime command, which will show you the length of the
    run queue on your server.

    Many people find the run queue a better indicator of how heavily loaded
    an OLTP server is.

    Dennis Williams
    DBA

    Lifetouch, Inc.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of ryan_gaffuri_at_comcast.net
    Sent: Friday, October 22, 2004 2:56 PM
    To: oracle-l_at_freelists.org
    Subject: question about cpu usage

    I'm not a hardware guy or sys admin person so forgive me if this is a stupid
    question. Leaving out all other variables(such as IO), should I expect
    performance to be the same in a databse if the server it is riding at is at
    90% cpu usages as opposed to 10%? since there would still be spare cycles?
    Or is there a declining returns as you get closing to the maximum available
    cpu usage?
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Ryan_gaffuri_at_comcast.net at Oct 22, 2004 at 5:42 pm
    I'm assuming you mean vmstat? what do you look for?
    Many people find the run queue a better indicator of how heavily loaded
    an OLTP server is.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    --
    http://www.freelists.org/webpage/oracle-l
  • Nelson, Allan at Oct 22, 2004 at 7:49 pm
    Response time for interactive users is non-linear with respect to cpu
    utilization. The curve looks like a hockey stick with the puck striking
    surface pointing up. If you are past the knee of the curve your
    interactive users are receiving unpredictable response times. If you
    are at 90% utilization and you have 4 CPU's or less you are probably
    driving your interactive users mad. Query response time for them will
    swing wildly between "normal" to 10 to 100 times normal. Your batch
    jobs and your total throughput on the other hand might be doing pretty
    well. You can get Cary Millsap and Jeff Holt's Optimizing Oracle
    Performance from O'Reilly. You can go to hotsos.com and register for a
    =66ree acount and get some white papers and an excel spreadsheet that will
    show you where the knee on the curve is relative to the number of CPU's.

    BTW, number of cpu's is the only factor that affects the knee of the
    curve and for most affordable configurations and normal mixes of OLTP vs
    batch you will find the you really want to stay less than 85% and for
    small machines with 4 or less cpus you are best of to stay below 70%.

    Allan

    -----Original Message-----
    =46rom: oracle-l-bounce_at_freelists.org
    On Behalf Of sol beach
    Sent: Friday, October 22, 2004 4:11 PM
    To: ryan_gaffuri_at_comcast.net
    Cc: Gogala, Mladen; oracle-l_at_freelists.org
    Subject: Re: question about cpu usage

    It is not as simple as you might initially think it is. Consider that in
    reality, saying a CPU is 50% busy/idle is somewhat nonsensical. Either
    is it doing useful work (at the request of the OS) [100% busy] or it is
    idle [0% busy]. At any & EVERY point in time the CPU is either 100% or
    100% idle; nothing in between.

    Now over a period of time it could be busy half the time (50% busy & 50%
    idle).

    What happens if 10 different folks concurrently as the CPU to do work
    =66or them=3F The answer is that 9 folks go into the CPU-wait queue.

    As system system statistics display the reality the CPU is approaching
    100% busy, users may or may not experience a degredation in response
    time.

    It all depends on the charateristics of the work load.
    It might even depend upon the law of large number.

    HTH & YMMV!

    On Fri, 22 Oct 2004 20:57:15 +0000, ryan_gaffuri_at_comcast.net
    wrote:
    ok i will refine the question.
    Assuming I am at a constant rate X of CPU usage, will there be a=20
    declining returns as CPU usage increases to X + n for a constant=20
    period of time.
    =20
    Assume CPU usage holds steady. CPU u sage is CPU usage. It shouldnt=20
    matter what its doing in terms of performance.
    =20
    =20
    -------------- Original message --------------
    =20
    Question asked in such generality really doesn't make much sense and
    can only have one
    answer: it depends. Mostly, it depends on what is CPU doing. Well=20
    optimized queries will typically have a short burst or two of=20
    intense CPU activity and then will finish. Using
    100% of CPU power is, unfortunately, also characteristic for "well
    cached"
    queries which
    can perform a gazillion logical block gets with no phyisical disk
    reads. An
    example of
    such query is the following:
    =20
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    ___________________________________________________________________________=
    ___
    This email is intended solely for the person or entity to which it is =
    addressed and may contain confidential and/or privileged information. =
    Copying, forwarding or distributing this message by persons or entities =
    other than the addressee is prohibited. If you have received this email in =
    error, please contact the sender immediately and delete the material from =
    any computer. This email may have been monitored for policy compliance. =
    [021216]

    --
    http://www.freelists.org/webpage/oracle-l
  • Zhu chao at Oct 23, 2004 at 3:47 am
    I am not graduated from Computer Science major, so I only talk about
    my own experience.
    I have seen both cases,
    1. CPU runs at 100% and application performance does not degrade. (We
    run tuxedo middleware and I measured response time via txrpt). This
    application use database heavily and SQL was not good.CPU number was 4
    and load (uptime) was 8-10. The response time was same between CPU
    usage at 60% and 100%. The bottleneck in the database was purely
    Logical IO(no disk IO contention and lock contention etc).

    2. CPU usage rise to 70% and we see a lot of application report error.
    (Time out)This was because business grows and our server reached its
    IO capacity. A lot of disk io wait event and also a lot of Enqueue
    wait contention.
    On Fri, 22 Oct 2004 19:41:22 -0500, Nelson, Allan wrote:
    Response time for interactive users is non-linear with respect to cpu
    utilization. The curve looks like a hockey stick with the puck striking
    surface pointing up. If you are past the knee of the curve your
    interactive users are receiving unpredictable response times. If you
    are at 90% utilization and you have 4 CPU's or less you are probably
    driving your interactive users mad. Query response time for them will
    swing wildly between "normal" to 10 to 100 times normal. Your batch
    jobs and your total throughput on the other hand might be doing pretty
    well. You can get Cary Millsap and Jeff Holt's Optimizing Oracle
    Performance from O'Reilly. You can go to hotsos.com and register for a
    =66ree acount and get some white papers and an excel spreadsheet that will
    show you where the knee on the curve is relative to the number of CPU's.
    \--
    Regards
    Zhu Chao
    www.cnoug.org
  • Mladen Gogala at Oct 24, 2004 at 3:16 am

    On 10/22/2004 08:41:22 PM, Nelson, Allan wrote:
    Response time for interactive users is non-linear with respect to cpu
    utilization. =20
    Of course it's non-linear. If OLTP program is well written, it will mostly=20
    utilize I/O - screen, disk and network. Solving a small 100,000x100,000 lin=
    ear=20
    system by using Gauss-Jordan method is seldomly a part of an OLTP program.
    Also, very few programs are created optimal. If I, for the sake of speed of
    development write a perl/DBI program that utilizes perl "FORMAT" statement,
    I'll sacrifice the speed of the program itself. I'll page in the whole perl
    interpreter, then by executing "use DBI;" I'll load and map an additional d=
    ynamic=20
    library, and I'll do so again when I execute "DBI->connect("dbi:oracle:$db"=,$usr,$pwd)".
    If I start using additional modules like LWP or MIME::Lite it will expand t=
    hat
    perl interpreter to monumental proportions. Address space can easily exceed=
    100MB.
    The same thing will happen if Java is used. Any other application developme=
    nt tool=20
    like ColdFusion or Oracle*Forms is many times as expensive as perl or Java.=
    If=20
    your box is running only a single database, the situation is clear but if t=
    here are
    things like LVM, backup server, print spooler or X11, the situation can be =
    very=20
    different. Sendmail with decent rule set, connecting to SpamAssassin and Cl=
    amAV can
    also waste quite a bit of resources, including the CPU, especially if SpamA=
    ssassin=20
    is version 3.0 equipped with all the submodules (Pyzor, DCC, Spamcop). It i=
    s=20
    multithreaded and humongous and can easily waste a whole SMP system. On the=
    other=20
    hand, if the app system is written by using an efficient TP monitor like Tu=
    xedo, Top End
    or CICS and C/C++, then running out of CPU can mean that you desperately ne=
    ed a HW=20
    upgrade, not just a better system administrator.
    To summarize, high CPU utilization is frequently a sign of suboptimal compo=
    nent. If the=20
    CPU is spent in the system mode, there is a problem with a driver or kernel=
    component,
    like excessive paging. If the CPU is spent in user mode, then it is most l=
    ikely an
    application which shouldn't be on an OLTP system in the first place. Genera=
    l connections
    between OLTP user response time and CPU (ab)use are completely irrelevant a=
    nd make no=20
    sense.
    --=20
    Mladen Gogala
    Oracle DBA
  • Cary Millsap at Oct 24, 2004 at 2:43 pm
    I forgot to cc the list last Friday when I responded to this...

    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *

    Upcoming events:
    - Performance Diagnosis 101: 10/26 Toronto, 1/4 Calgary

    SQL Optimization 101: 10/18 New Orleans, 11/8 Dallas, 12/13 Atlanta
    Hotsos Symposium 2005: March 6-10 Dallas
    Visit www.hotsos.com for schedule details...

    -----Original Message-----
    From: Cary Millsap =20
    Sent: Friday, October 22, 2004 3:05 PM
    To: 'ryan_gaffuri_at_comcast.net'
    Subject: RE: question about cpu usage

    Ryan,

    Good question actually. It depends on how many CPUs are in there. If you
    have a copy of "Optimizing Oracle Performance" handy, check out table =
    9-3 on
    page 260. What it says is that you can expect performance to vary
    unpredictably on a 1-CPU machine when CPU utilization exceeds 50%. On a
    2-CPU machine, the magic number is about 57.7%. On a 4-CPU machine, it's
    66.5%. The table lists "knee in the curve" numbers for CPU counts =
    ranging
    from 1 to 128.

    The whole section called "Behavior of M/M/m Systems" (pp254-266) should =
    be
    of interest to you.

    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *

    Upcoming events:
    - Performance Diagnosis 101: 10/26 Toronto, 1/4 Calgary

    SQL Optimization 101: 10/18 New Orleans, 11/8 Dallas, 12/13 Atlanta
    Hotsos Symposium 2005: March 6-10 Dallas
    Visit www.hotsos.com for schedule details...

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org =

    On Behalf Of ryan_gaffuri_at_comcast.net
    Sent: Friday, October 22, 2004 2:56 PM
    To: oracle-l_at_freelists.org
    Subject: question about cpu usage

    I'm not a hardware guy or sys admin person so forgive me if this is a =
    stupid
    question. Leaving out all other variables(such as IO), should I expect
    performance to be the same in a databse if the server it is riding at is =
    at
    90% cpu usages as opposed to 10%? since there would still be spare =
    cycles?
    Or is there a declining returns as you get closing to the maximum =
    available
    cpu usage?=20

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • DENNIS WILLIAMS at Oct 25, 2004 at 8:31 am
    Ryan - No I meant the "uptime" command. On most Unix systems uptime lists
    the run queue length over several time samples.


    Dennis Williams

    DBA

    Lifetouch, Inc.



    From: ryan_gaffuri_at_comcast.net
    Sent: Friday, October 22, 2004 5:46 PM
    To: DENNIS WILLIAMS; oracle-l_at_freelists.org
    Cc: DENNIS WILLIAMS
    Subject: RE: question about cpu usage





    i'm assuming you mean vmstat? what do you look for?
    Ryan
    Perhaps you can tolerate a mini-O.S. lesson.
    A process can be in several states -- running (only one process/CPU),
    waiting (for I/O, user response, etc.), or ready-to-run (or run queue). The
    O.S. lets each process have the CPU (running) for a short interval of time,
    or until it must wait for something like I/O. Once a process has its I/O
    satisfied, it is put on the ready-to-run queue until the O.S. decides to put
    it back on the CPU.
    Basically there is a queue of processes waiting for the CPU. If the
    system is nearly idle, that queue will be very short. When the process is
    back from the I/O, it is quickly put back on the CPU. If the system is
    heavily loaded, then the queue will get very long. If you are interested and
    on Unix, check out the uptime command, which will show you th e length of the
    run queue on your server.
    Many people find the run queue a better indicator of how heavily loaded
    an OLTP server is.

    Dennis Williams
    --
    http://www.freelists.org/webpage/oracle-l
  • Nicolai Tufar at Oct 28, 2004 at 5:35 pm
    All these talks about why CPU usages should be
    below 100% would make an experienced Mainframe
    system programmer -that is an syste administrator
    in unixspeach for self-respecting system administrator
    *do* write programs in assembly- smile. Everyone knows that a system
    need to be at 100% CPU usage or
    otherwise the system is underutilized.

    The reason why UNIX administrator loses his temper
    when CPU utilisation goes beyond 60% is that UNIX
    monitoring tools do not count pure CPU usage. They
    add system time, time spent in I/O and everything
    else in the count. UNIX task chedulers are so lame
    that they can not properly distribute CPU time
    between interactive processes wen load average
    goes beyond %60.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 22, '04 at 2:51p
activeOct 28, '04 at 5:35p
posts13
users10
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase