MySQL using only one CPU despite thread_concurrency setting
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:
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:
You might want to ask these guys: http://forums.mysql.com/list.php?35
OK I will!
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? :-)
I will ask the MySQL guys now...
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.
|All times are GMT -5. The time now is 03:40 PM.|