Hello,

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!

You should try to login to MySQL on the command line and run a show processlist command to see what MySQL is doing while this is occurring. That should help you track down where the issue is.

I will try that next time it happens. Thank you!

So I was finally able to see this information during one of the spikes - I now know the query that runs for 300+ seconds. I have been digging everywhere on the site, including a windows grep-powered search through what I believe to be an exact copy of all public_html files on the server, and I can not find that query anywhere.

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).

There's a few things you can do.

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.