The Invaluable MySQL Query Log

I was trying to track down a “query storm” on Syndic8 earlier today. At irregular intervals, the query rate would jump up from 10,000 queries per minute to somewhere between 100,000 and 300,000 queries per minute.

Oddly, I could not correlate these with any particular activity on the machine. The number of pages served, the number of web service calls handled, and the number of XML feeds generated did not rise to astronomical levels to match these sudden spikes.

After a bit of investigation, I found out that I could get MySQL to log every query. Full details on how to do this can be found in Section 5.9.2 of the manual, in a section titled “The General Query Log.”

In a nutshell, all I had to do was to add the following line to my.cnf: ` log=/tmp/mysql_query.log `

I made the edit, restarted the MySQL server, and waited for the next storm. Sure enough, it hit within 5 minutes.

I examined the log, and found the offending queries. Each line in the log was time-stamped, so it was easy to correlate the query storm with the actual queries.

I found the problem, adding a very simple application-level cache, and the storms went away.