Re: linux system load

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 20 Nov 2004 09:23:42 +0000
Message-Id: <1100942622l.12857l.0l@medo.noip.com>

On 11/20/2004 03:20:06 AM, biti_rainy wrote:
> hi,all

>=20
>=20

> one or two days per week,sometimes the run queue size is close to
> 20 , many are close to 1 or 2
> but the load-1 is always close to 6
> i do not know why, i check the OS net/IO/ram/cpu load ,and make
> a lot of statspack report
> all sql do not be slow, the cpu_time/wait_time/Elapsd_time are
> not changed.

Do more statspack reports at level 9. Do it every minute. That will lighten the
load. You never have to actually view the report. What do you expect to find out from statspack? Statspack will give you the immensely useful information with regard to the numbers of db block gets, number of sorts, the hit ratio for your dictionary, library and buffer cache, but will not tell you a thing about your run queue. So, let's stop and think for a moment. Are you using the right tool? Will sar and statspack help you to find out what's happening and will they give the SPIDs of the processes to watch? Methinks not. So, you need a tool which will show you the activity of each process, not a statistical value sampled, chewed and regurgitated and then given to you, after a period of time. This information is worth as much as my description tries to imply. The same applies to statspack. Please, don't let me dissuade you from printing statspack reports. I have few shares of the International Paper (ticker: IP) and I like people who print STATSPACK reports. You must be aware that printing statspack reports will do more good to me then to you. What you want is called "top". NO! Stop! Don't read the source code! Your problem is shortage of CPU resources. Top will tell you the most active process. You must see what is it doing and why is it doing so. CPU exhaustion is frequently a reward for a good BCHR. SQL statement like

select count(*) from emp,emp,emp,emp,emp,emp,emp,emp,emp;

will consume huge amount of CPU and propel your BCHR close to mach 10, before even NASA manages to get there. It is the hardest problem of them all: the method that uses the wait interface tells you to see what are the processes waiting for. Your problem are processes that are NOT waiting. Processes that are spinning like crazy are not waiting - they are running, by the very definition of the term. What you have to do is to analyze what are they doing and is there a method (usually, a change in the algorithm) to do it cheaper. And, please, don't use statspack.

>=20
> i am vary puzzle.

Oh, so am I. Variations are wild.

> i want to know what is the formula of load-1 and load-5

>=20

> i read the sar's source code and find that it read the
> /proc/LOADAVG
> i do not read the linux source code

Reading the whole Linux source code is useful for some things, but tuning oracle database is not one of them. The same applies to sar. Always look at the bright side of life. For life's quite absurd, and manual is the final word, you must always face the curtain with a bow. So, instead of reading the source code, here is what the fine manual says:

-q Report queue length and load averages. The following values are

displayed:

             runq-sz
                    Run queue length (number of processes  waiting  for
run
                    time).

             plist-sz
                    Number of processes in the process list.

             ldavg-1
                    System load average for the last minute.

             ldavg-5
                    System load average for the past 5 minutes.

             ldavg-15
                    System load average for the past 15 minutes.



>=20

> Best regards
> msn: biti_rainy_at_hotmail.com
> a dba from alibaba(china)

--=20
Mladen Gogala
Oracle DBA

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

Received on Sat Nov 20 2004 - 03:23:58 CST