3

I have an app with a lot of different queries all over the place.

I'm getting spikes in CPU usage from mysqld which is maxing out my server and I'm not sure which of the many queries is doing this.

Is there a way to log / view all the queries going from the unix level? And perhaps to see what queries are chewing up all the resources?

5

There is a type of query called a "slow query" which is much like it sounds: queries that take a long time to execute. In some cases, these queries are legitimately long but in the vast majority of cases a slow query indicates some poorly written code or a lack of resources on the server causing MySQL to become I/O bound.

In either case, if you're seeing mysql spikes then a very, very good place to start is by identifying your slow queries.

I'm not enough of a MySQL guru to pull this off of the top of my head, but in short you want to:

  • Turn on slow query logging (it is off by default in most distributions)
  • Let it run for a bit
  • Look at the log to see what queries are your culprits

I'm not even going to attempt to pull instructions off the top of my head, but you can start with this link:

http://www.ducea.com/2006/11/06/identifying-mysql-slow-queries/

And if this link expires or becomes otherwise useless, the phrase to search for is something like 'log mysql slow queries'.

Once you have them logging, you are well on your way to sorting them out.

  • works a treat. Although how that link turns on slow error logging didn't work on my machine. I needed to add 'set-variable=long_query_time=1' instead of just 'long_query_time=1' to /etc/my.cnf. Plus it failed to mention that you need to manually create the log file and give mysql permission to use it. But, I'm a newb, so nothing is obvious to me :) Thanks again. – willdanceforfun Sep 29 '11 at 0:49
  • Awesome. Good job sussing through it. – jdw Sep 29 '11 at 0:53
  • Cheers. This helped alot. Once I optimised the various queries that were taking so long however I will still getting max CPU usage. It turns out that what really sped things up was when I cleared up a table that was around 40mb in size which was being queried every 30 seconds or so. – willdanceforfun Sep 29 '11 at 2:53
  • 1
    Great. If that table is going to get big again you might want to investigate what indexes it has on it. Even very large tables can be queried quickly if they have good indexes. – jdw Sep 29 '11 at 2:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.