LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 09-12-2011, 02:17 PM   #1
baffy
LQ Newbie
 
Registered: Sep 2011
Location: London
Posts: 2

Rep: Reputation: Disabled
Question High CPU load, but low CPU usage (high idle CPU)


All,

I'm relatively new to Linux. I moved from a DBA role to a role where I now combine DBA duties with Linux admin work and therefore will need help with the following Linux question:
I'm in a situation where my developers run a particular SQL about 200 millions times a day (very unnecessary), this SQL is run between 10pm and 2 am everyday. At the time when this SQL is being run, load average is very high and everything becomes slower on the database. I'm trying to get the developers to tune this process but they say it's going to take too much effort, but most importantly, they keep pointing to 'idle time' on the CPU which could be as high as 72% (idle) even when the load is 50 (on a sixteen CPU box).

My question: Since the DB becomes much slower when the load average is high, how do I explain that the 72% idle time is a little misleading and the load average is very important in this instance (rem that all other quesries/SQL run slowly during the period explained above)
 
Click here to see the post LQ members have rated as the most helpful post in this thread.
Old 09-12-2011, 02:40 PM   #2
PTrenholme
Senior Member
 
Registered: Dec 2004
Location: Olympia, WA, USA
Distribution: Fedora, (K)Ubuntu
Posts: 4,153

Rep: Reputation: 331Reputation: 331Reputation: 331Reputation: 331
Have you looked at the I/O usage whilst the SQL task is running? Perhaps you could tune your system to use more "idle" RAM to cache the tables being accessed, or use a larger RAID array for the data base so the access could be distributed over many different disk drives. Have you considered high efficiency disk drive replacements? (e.g., RAM disks, bubble memory drives, etc.)

Have you asked the developers why they're running 200,000,000 queries a day in a production data base instead of a development copy of the data base? It seems like a very strange organizational structure that mixes development and production. (And where's QA in the mix? QA should, at a minimum, have tested any development work before it impacts a production system.)
 
3 members found this post helpful.
Old 09-12-2011, 07:28 PM   #3
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,311

Rep: Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040
As with above (and your statement) 200M sounds definitely wrong. I'd also be surprised if its that difficult to fix; sounds more like laziness to me (& I've worked with RDBMSes for many yrs).
In any case, the DB is likely IO bound as mentioned above. check the process statuses.

You can use top to get page fault cnts http://linux.die.net/man/1/top or use iostat http://linux.die.net/man/1/iostat for more IO specific details.
Given the scenario described, I'd also have a look at the SQL code; they may not be using the correct indexes or there may not be an index that matches their needs.
How much RAM do you have; a large SGA may help to cache the data the they keep asking for.
If its a lot of large queries over many new rows, a re-write sounds good as you won't be able to take advantage of caching.
 
3 members found this post helpful.
Old 09-12-2011, 07:47 PM   #4
syg00
LQ Veteran
 
Registered: Aug 2003
Location: Australia
Distribution: Lots ...
Posts: 12,425

Rep: Reputation: 1055Reputation: 1055Reputation: 1055Reputation: 1055Reputation: 1055Reputation: 1055Reputation: 1055Reputation: 1055
In Linux (high) loadavg has usually nothing to do with CPU%. (Almost) all the articles you see merely carry forward an old Unix mantra about loadavg. Linux defines it differently.

Has to be I/O waits - caused most probably by poor application design, but I'll leave that to the guys above as I don't "do" databases.
Run the following to determine what is probably contributing to the loadavg count - your mate might recognise the tasks listed. Post the output file here if you like.
Code:
top -b -n 1 | awk '{if (NR <=7) print; else if ($8 == "D") {print; count++} } END {print "Total status D (I/O wait probably): "count}' > topsave.txt
 
2 members found this post helpful.
Old 09-13-2011, 12:41 PM   #5
baffy
LQ Newbie
 
Registered: Sep 2011
Location: London
Posts: 2

Original Poster
Rep: Reputation: Disabled
Thumbs up

All,

Fantastic replies. Thanks to you all. Load averages vs CPU usage is now clearer to me. (I'll run the top script during the period this SQL is being run and see what comes up. Note that these SQLs are not as a result of developers testing scripts, but these have been put into production and come in via the app severs). Thanks all.
 
Old 03-13-2013, 10:24 AM   #6
LittleMaster
Member
 
Registered: Jun 2012
Posts: 98
Blog Entries: 1

Rep: Reputation: Disabled
Hi,
First of all sorry for high jacking the thread.

I read the post of syg00 .In below post .I gone through few article all the things says increase of loadavg leads to high cpu utilization.



But above post of sygoo really suprise me .Sygoo can u share your knowledge, how could load does not reflect cpu utilization.

@syg00 .In Linux (high) loadavg has usually nothing to do with CPU%. (Almost) all the articles you see merely carry forward an old Unix mantra about loadavg. Linux defines it differently.

Last edited by LittleMaster; 03-13-2013 at 10:28 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
High load and high cpu kernel usage enid Linux - Server 8 09-30-2010 04:33 AM
High load average, low cpu usage on CentOS 5.4 64-bit lancherider Linux - Server 4 06-01-2010 05:08 PM
high load average, low cpu usage ! jimmyjiang Red Hat 8 02-08-2008 01:28 AM
High load - but CPU 99% idle? Boss Hoss Linux - Hardware 6 05-24-2004 05:39 AM
High idle cpu load in 2.6.4? geekzen Linux - General 4 04-10-2004 12:54 PM


All times are GMT -5. The time now is 08:19 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration