Troubleshooting 100% CPU Utilization by mysqld process
I am trying to troubleshoot an issue that causes a random part of my site to become unresponsive (stuck "loading") for 5-15 minutes at a time. I ran the "top" command, and it seems that "mysqld" consumes 100% of CPU power for the duration of this problem. There are no relevant errors in mysql error logs that I can see (no recent errors at all). I ran mysqlcheck –all-databases --repair --optimize successfully and no issues were found ("OK" / "Table already up-to-date" for all tables).
This happens on random pages of the site, so I can't point the finger at any particular query either. Once it happens on page A, it does not happen on that page again for several hours, but it may happen on a different page. There are some pages that are almost guaranteed to have this problem the first time I access them.
Any ideas on how I can troubleshoot this?
Thank you!
4 Replies
Is there any way to see the origin (as in the php file) of the currently active query? A command line parameter in show processlist (I couldn't find any) or a particular log file somewhere?
In other words, any ideas on how to track down a query that seems to be missing from all the php files on the site by a snippet of it obtained through the show processlist command?
Thank you Ghan_04 - you have been a great help already!
p.s. I do not know if this would help, but the "state" of the troublesome query was "copying to tmp table" the whole time it was executing (~5 minutes).
don't try and grep the whole select statement, just pick a few words or so.
so if you saw
select one, two, three, four, five
from a, b, c
where a.id = b.aid
blah blah blah
just
grep -r "select one, two, three, four" /var/www/public_html/
and even then, it might no show up, sometimes there's a library abstracting the sql
also turn on slow query logging in my.cnf
and lastly, if you have enough memory, mount /tmp on tmpfs
but if you're already running out of memory, that might make things worse.