Re: Innodb bogs down on complicated query(ies)
Next time the CPU is pinned, you could fire up OProfile to collect samples of CPU_CLK_UNHALTED (or equivalent) events. This will slow down the system by a few percent, depending on the desired accuracy. After the load has eased, do "opcontrol --stop" and examine the logs, e.g., "opreport --global-percent" to view the executables and libraries consuming most CPU cycles and "opreport -l /path/to/executable_or_library" to view the bottleneck functions, provided that the executable or library has not been stripped of symbols.
I don't know if OProfile is safe to use in a production environment. I have used it occasionally to find performance bottlenecks, and it has worked well for me.
Good luck,
Marko Mäkelä
Innobase Oy/Oracle Corp.