MySQL MyISAM multi-core optimization

innodbmulti-coreMySQLoptimization

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?

Best Answer

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.

Related Topic