MySQL using only one CPU despite thread_concurrency setting

Sheridan · 12-04-2007, 09:36 AM

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

chrism01 · 12-04-2007, 11:09 PM

You might want to ask these guys: http://forums.mysql.com/list.php?35

Sheridan · 12-05-2007, 01:05 AM

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...

trickykid · 12-05-2007, 11:14 AM

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.