Sep 28, 2011
tom

Which sql queries are using so much cpu?

Question

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?

Answer

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.

Related posts:

  1. Monitoring solution to help fix slow MySQL queries problem
  2. Turn “log slow queries” ON
  3. Can’t start MySql log slow queries ON
  4. A pseudo-Sql server to intercept and see all MySQL queries
  5. MyTop doesn’t display executed queries

Leave a comment