CPU utilization 50% on Dual Core Xeon and Windows

I am running a MySQL 5.0 dedicated server on a Windows 2003 machine (Xeon 5130, 2GHz, two cores).

The problem is that MySQL process will utilize just 50%. Normally, I would presume that the MySQL thread uses just one core, but then, if you look at the graph more closely you can see that one core is used more then the other, and then they switch. Not like just one is utilized while the other stands by.

You can see the graph here:

[20kb]

I have read that it is a normal MySQL behavior: to utilize just one CPU (Core) per thread (Process?) and that is all fine because there is another point of my concern! While the query (which takes 50% of CPU resources) is running I cannot issue another query (even the simplest query) towards the server until the first one has finished…

Don’t you think is very odd that my server cannot get the load past 50%?

I opened two browser windows on two different computers and request the data in the same time… It behaved the way I described before.

However, i connected to MySQL server using terminal on one machine and using web page on the other… After I requested some long-lasting query over the web page I could quite freely use terminal to request data using SQL and utilize more then 50% out of the CPU…

Does this make any sense? Could it be that PHP is locked to just one thread / process? How do I overcome this?

I tried running two different mysql console clients with same user and it utilizes 100%.

I still CANNOT make MySQL utilize more then one processor with PHP…

Say if one web user issues a long query all the other web users must wait for it to finish even to issue the simplest query!

At least point me in some direction.

Another thing… I have had that long query ran on my server while issuing two more simple requests and what I got is this picture (from MySQLAdmin’s Server Connections TAB):

[3kb]

As you can see, the first query is running for 141 seconds and two more are SLEEPING even tho 50% of processor (one core) is unutilized.

I just can’t figure it out… confused:

Here goes my.cnf

[client]port=3306[mysql]default-character-set=latin2[mysqld]skip-lockingport=3306basedir="C:/mysql5/"datadir="C:/mysql5/Data/"ft_min_word_len=3ft_stopword_file="c:/sphinx/stopwords.txt"memlockthread_concurrency = 4thread_cache_size=16default-character-set=latin2default-storage-engine=MYISAMsql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"max_connections=30key_buffer_size=50Msegment.key_buffer_size=950Minit_file="c:/mysql5/init.sql"join_buffer_size=10Mread_buffer_size=128kskip-innodb

One query in MySQL can not be split on several CPU’s.

Which means that if you have one query that is badly written and is CPU intensive that query will only be able to use 50%( 1 CPU ) on your box.
And the problem with the sleeping queries can be that the one CPU intensive query is locking the tables so that the other two queries have to wait for that query to finnish, hence they are sleeping.

So start by optimizing the CPU intensive query and things should clear up for you.

How can one SELECT query lock tables for other SELECT queries? o.O
I tried to optimize the query the best I can but the FULLTEXT is giving me hard time. I am using sphinx for some of the queries but I cannot use it for all since there are no wildcards in it and I badly need it.

Anyways… If you read my post carefully you can see that the “LOCKING” or “SLEEPING” or WHATEVER is happening when I issue queries from (say) web app. If I issue one on web app and one in console the “WHATEVER” does not occur.