Mysql – Monitoring solution to help fix slow MySQL queries problem

apache-2.2monitoringMySQL

I have two servers: one that runs Apache and another one just for MySQL. I don't have any monitoring tools installed on any of them.

Every once in a while it appears that my SQL queries are running very very slow (queries that would normally run in 0.3 seconds now take 20 seconds). I don't know if this is a MySQL issue, a CPU issue, or even a networking one, as it usually happens when no one is around to check, so I am looking for a monitoring solution that would help me pinpoint the source of the problem.

Thank you.

Best Answer

The first thing to check is your slow query log. You may have to configure it, as I am not sure it is configured by default in all versions of MySQL. The default setting usually is to log all queries that run longer than 3 seconds.

It could well be that you have a particular query that runs very long and locks the tables for a lot of other queries, this should show up in the slow query log very clearly.

If this is the case, then the next steps are:

  1. run the query through EXPLAIN to see what the engine is doing
  2. find out from where the query is run.

If your slow query log doesn't show anything, then it's time to look elsewhere.

You could use nagios, cacti or mrtg to monitor things like data throughput on ethernet interfaces, interface status, disk transfers etc. These tools can easily be configured to either provide graphs (cacti, mrtg) or send alert messages (cacti, nagios) when certain thresholds are exceeded.

Related Topic