Why is my MySQL server using a single core while my processor has multiple cores ?

I am writting my first ever blog post after four and a half years since I started my career as a MySQL DBA. I used to read lots of blog posts  especially from mysqlperformceblog , but  never really had the interest of writting one.  But this interesting question from a client made me write this blog post.

“Why is my MySQL consuming only “core 0”, while I have 8 cores and there is hardly any usage in the other cores?”

This is a question put forward by one of our clients. Started my investigation by looking into the cpu usage stats. It was true that only “core 0” was getting used and the other cores were rarely getting used.

 Usage of “core 0” was like below

Where as there is hardly any usage in the other cores as shown below

But if we look at the overall cpu usage it rarely crossed 30%. But the client questioned “why can’t MySQL share the usage in ‘core 0’ with other cores?”

In my first look found that the machine is a dedicated for MySQL with multi-thread support, all tables are innodb, and innodb has been configured to use multiple threads.  I even checked the cpu affinity which was set to all cores . So there shouldn’t be any issue in multi-threading.

I started digging further. I issued “show processlist” and “top” command in different terminals.  In the top command I had enabled “show threads “option and ignored idle threads

From the above analysis I found there were no parallel active threads Most of the time MySQL gets a single read query (all other threads are in sleep state).  Since “core 0” is logically the first choice, obviously  that single query was handled by  “core 0”. That’s the reason for the spike in “core 0”.  There is no possibility of multiple cores getting used unless there are more than 1 query running in parallel, since a single query(connection) can use on one core.

By prolonged monitoring of processlist and top I also found that at times there were parallel queries that used of multiple cores.

Also I felt that “core 0” usage reaching 60% was not a big deal . But whenever there is room for improvement we should do it. That 60% usage was caused by a bad query which could be tuned.  

So I conclude we need to have multiple connections running in parallel to make the MySQL use multiple cores.  

Suggestions and discussions are greatly appreciated.