Monitoring solution to help fix slow MySQL queries problem

Question

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.

Answer 1

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.

Answer 2

Analyzing the slow query log is a good idea.

My guts tells me though that you are using MyISAM storage engine for your tables and suddenly there's a spike in delete/update/insert activity combined with some long-running selects ... that leads to all kind of funky stuff with MyISAM, whereas with InnoDB everything stays steady.

Answer 3

While the recommendations provided by others are rock solid, diagnosing while ongoing is better done using different methods than the slow query log. This is a tool best used to monitor for slow queries for tuning as part of normal application maintenance.

It sounds like you have a query running that is inefficient or simply dealing with a large data-set. When the queries become slow, run show full processlist; and sort by length of time running. This should allow you to determine the query that is causing others to take longer to execute.

If you do not have a tool like mytop installed, you can use this command to sort based on the length of time running:

mysql -e 'show full processlist;' |sort -n -k 6

Once you determine the query at fault, you can consider the best method of remedy be it simply tuning the query, creating an index or perhaps even pruning the data set.

Architecture has a significant play on this type of scenario as well. OLAP queries are best done on secondary databases and not the OLTP database.

Answer 4

You can use mk-query-digest to parse MySQL logs and aggragate them.
Also having proper monitoring solution (like Nagios) will help you greatly (with MySQL plugin).

Answer 5

I've used mytop to get slow queries very often. When our webmasters start complaining about the performance of the (virtual) mysql server, I ask them politely to come and watch the output of mytop together. We find the culprit query very easily like this and from there you know what the next step can be.

Answer 6

Mytop and the slow query logs are great places to start. Combined with a system monitoring / logging / graphing (delete as appropriate) solution such as OpenNMS / Nagios / Cacti you can start to get a better insight into what is going on when you get problems.

It's also worth setting up Apache mod_status to show you which requests Apache is handling when the slowdown occurs but you'll have to be watching and see it happen. The output of this and also MySQL runtime variables can also be integrated into OpenNMS graphing to help improve this insight further.

Answer 7

Performance Tuning

* change parameters in /etc/mysql/my.cnf
      o thread_stack=196K
      o query_cache_limit=2M 
      o query_cache_size=64M

try this according to your needs

if queries are very slow try (disable the reverse dns look-up)

 skip-name-resolve

if it works you should consider to check DNS configuration instead using this option