LinuxQuestions.org
Visit the LQ Articles and Editorials section
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 12-04-2007, 09:36 AM   #1
Sheridan
Member
 
Registered: Aug 2007
Location: Hungary
Distribution: Fedora, CentOS
Posts: 89

Rep: Reputation: 21
Smile MySQL using only one CPU despite thread_concurrency setting


Hi guys...

I have a - seemingly - very big problem with our MySQL server.

There's MySQL 4.1, downloaded from MySQL.com running on it (we cant upgrade it because of severe software issues it would cause).

It's running a basic installation of Fedora 7 with SMP kernel, without graphic interface. It has 2 Quad core Xeon processors.

There's a particularly large, difficult query which takes about 15 minutes to execute on that machine. This is not the worst problem. You see, when I look at the output of SAR, I see that during the 15 minutes only One core is utilized out of the eight. See:

Code:
05:26:11 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
05:26:12 PM     all      7.43      0.00      3.59      0.00      0.00     88.99
05:26:12 PM       0      0.00      0.00      0.00      0.00      0.00    100.00
05:26:12 PM       1      0.94      0.00      0.94      0.00      0.00     98.11
05:26:12 PM       2      0.00      0.00      0.00      0.00      0.00    100.00
05:26:12 PM       3     59.41      0.00     26.73      0.00      0.00     13.86
05:26:12 PM       4      0.00      0.00      0.00      0.00      0.00    100.00
05:26:12 PM       5      0.00      0.00      0.00      0.00      0.00    100.00
05:26:12 PM       6      0.00      0.00      0.00      0.00      0.00    100.00
05:26:12 PM       7      0.00      0.00      0.00      0.00      0.00    100.00
...and even that is not on 100%. I looked at disk usage also, but no, there's only moderate HDD activity and even that is generated by some other process or so.

I've tried setting the "thread_concurrency" to 8 and 16 also, but no good. The server is at a 0.5 load, serving other requests on other processor cores rapidly when needed. But that one core is occupied with that one query and the "computing" is not distributed between cores.

Can you suggest something that would help me?

The server's exact params otherwise are the following:

Code:
HP DL380R05 CTO Chassis

HP X5355 DL380G5 FIO Kit

HP X5355 DL380G5 Kit

HP 4GB FBD PC2-5300 2x2GB Kit (x4)

HP 146GB 10K SAS 2.5 Hot Plug Hard Drive (x8, in RAID6)

HP SA Cache Battery Kit

HP NC110T PCIe Gigabit Server Adapter (x2)

HP Smart Array P400/256 DL38x LP FIO Ctr

HP RPS 350/370/380G5/385G2 Kit
Levente
 
Old 12-04-2007, 11:09 PM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,287

Rep: Reputation: 2034Reputation: 2034Reputation: 2034Reputation: 2034Reputation: 2034Reputation: 2034Reputation: 2034Reputation: 2034Reputation: 2034Reputation: 2034Reputation: 2034
You might want to ask these guys: http://forums.mysql.com/list.php?35
 
Old 12-05-2007, 01:05 AM   #3
Sheridan
Member
 
Registered: Aug 2007
Location: Hungary
Distribution: Fedora, CentOS
Posts: 89

Original Poster
Rep: Reputation: 21
OK I will!

Btw...

Just a thought here, but maybe my problem really lies within the query code. I don't know exactly how they (the developers) wrote the query to the database, but IMHO it's possible that the query is constructed in a way so that it cannot be, or will not be broken into threads by the engine, proper.

Now, in this case: Would it be possible for me to install some sort of virtualization software on top of the OS or inside the OS (Like Vmware, XEN, etc), to "hide" that many processors from the MySQL and effectively have it see all the 8 cores as one CPU - and so it would use all of them?

Or, going further, could I just merge with some virtualization technique the two identical MySQL server HW into one Linux virtual machine and have that VM run the MySQL server software? Is this thing even possible or it's just my sick, desperate fantasy? :-)

Anyway...

I will ask the MySQL guys now...
 
Old 12-05-2007, 11:14 AM   #4
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 199Reputation: 199
Well, if you have one query running, it's not going to split that across CPU's. Try running two queries and see if it only tries to utilize one CPU or others. It should be an indication that you said that other smaller queries are coming thru and getting processed rather fast, that should indicated they are using the other CPU's.

If you're complicated query takes 15 minutes to run, that's not that long, I've seen some longer, hours, days. When it goes beyond several hours, that's when you need to re-evaluate your sql query being used.
 
  


Reply

Tags
dl380, mysql, performance, quadcore, smp, xeon


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
Sendmail / MySQL CPU Time Question mcupples Linux - Server 0 03-30-2007 11:51 AM
setting CPU voltage with cpufreq/cpufreqd pafrica Linux - Laptop and Netbook 2 09-03-2005 03:04 AM
MySQL on SMP. CPU stats max at 99.9% quill18 Linux - Software 2 05-26-2005 10:22 AM
limit cpu usage by mysql eduac Linux - Software 3 06-08-2004 05:08 PM
How to setting the correct value in BIOS about CPU speed? explorer1979 General 1 09-09-2001 05:35 AM


All times are GMT -5. The time now is 11:59 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