2

I've noticed that our app does not respond to requests when a demanding request is in execution. Running top seems to identify the source of the problem:

top - 13:54:25 up 1 day, 13:43,  2 users,  load average: 1.02, 0.98, 0.83
Tasks: 110 total,   1 running, 109 sleeping,   0 stopped,   0 zombie
Cpu(s): 11.9%us,  1.1%sy,  0.0%ni, 86.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.2%st
Mem:   3145728k total,  2329220k used,   816508k free,        0k buffers
Swap:   131072k total,   128164k used,     2908k free,  1585060k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
26073 mysql     20   0  397m 209m 3452 S 99.1  6.8   3:02.49 mysqld             
16419 mailnull  20   0  9848 3288 2664 S  2.3  0.1   1:17.63 exim               
 2085 nobody    20   0 44312  10m 3436 S  1.3  0.3   4:50.98 litespeed          
24727 nobody    20   0  320m  50m  41m S  0.3  1.7   0:06.86 lsphp5             
26314 root      20   0  2428 1104  832 S  0.3  0.0   0:00.36 top                

It appears to me that mysql is hogging the entire CPU that it is running on (99.1%). This means that one of our cores is pegged at 100% while the other 7 (seven!) sit idle at 0%.

I understand that if our tables were InnoDB, the load would be distributed amongst the cores. Is this correct?

Is there any way to distribute the workload amongst the cores our tables are using MyISAM?

Am I looking in the wrong place entirely? While the resource-heavy query is hogging one CPU, shouldn't MySQL be able to utilize the other CPU's for separate queries? Or, is this limited by our usage of MyISAM?

1 Answer 1

3

Your understanding of InnoDB and the load being distributed among more cores is incorrect, but close.

MyISAM tables do 'table based locking', meaning that any query that locks the table necessarily blocks all other queries that would need to grab a lock.

InnoDB uses 'row based locking' for most operations which allows other queries that do not need to lock that exact row to continue in parallel.

One single large query is still going to only use one core whether it's MyISAM or InnoDB, but multiple queries hitting the same table should be able to execute simultaneously on separate cores, provided they're not being blocked by row-level locks.

2
  • Thanks for your answer. The strange thing is that when the server is performing this resource-intensive task/query, nearly all requests/queries are blocked. In other words, the web app slows to a crawl. The only explanation I can think of is that this resource-intensive query locks enough important tables to render the web app effectively 'frozen' until the query is complete. Can you think of anything else that could be causing the issue?
    – rinogo
    Jun 14, 2013 at 21:43
  • 1
    I think you're on the right path with regard to the MyISAM tables. You can get more insight when this is occurring by connecting to the mysql server with a client and issuing the 'SHOW FULL PROCESSLIST' command when you are experiencing this slowdown. I haven't used MyISAM in quite awhile, but if I remember correctly you should clearly see queries with a state saying they're waiting on a lock. It may just be faster to alter your tables into InnoDB tables and see if the problem goes away if your data size isn't very large.
    – user143703
    Jun 14, 2013 at 21:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .