MySQL Troubleshooting workflow In a couple of interviews i have had recently, i have been asked to explain my workflow when troubleshooting or resolving an incident with MySQL. Here are ways they have asked the question:


Explain the steps or measures you take to respond to, troubleshoot and resolve a database issue.
Do you use monitoring and graphing tools? Explain in what circumstances you use them?


Personally, if there is an incident, like too many threads or whatever issue is with our database that is having an impact on performance or service (not a db crash), the first thing i do is to check the running processes on that DB using show processlist or using mytop. That normally gives me an idea of the activities going on on that db server. Then i would also look at our xymon monitoring system to see if it detects any issues with that server, e.g., disk issue, Raid issue, memory issue (e.g., swapping), high CPU usage etc. So this is somewhat my workflow when dealing with my first question.

I would like to get some good ideas from other DBAs on their workflows on dealing with database incidents in their production environments. So please help answer my two questions above with your own workflows.
For discovering performance issues and getting started on them, my favorite tool is the SlowLog.  By keeping it turned on and a low value for long_query_time, I can look back in time to see what might have caused a slowdown in the past.  Plus it will point to the specific query (or queries) that should be investigated.

I feel that most non-crash issues will be reflected in some way in the slowlog.

Sometimes there are things that are challenging to investigate.  One Slave was running significantly slower than other Slaves, even though nothing indicated any spike in traffic, etc.  Even the slowlog was more confusing than helpful.  After a while, the problem went away.  Several people were looking into it.  The problem turned out to be the RAID controller 'cycling' its battery, during which it disabled the its "Battery Backed Write Cache".

Monyog and MEB are a tools ($$) that provide a lot of continual monitoring of a lot of metrics.  Then the problem becomes knowing what to look at.

Many times, I have encountered clients that happily make lots of connections to MySQL.  (Eg: Apache with a high value of MaxClients.)  Usually this is not a problem because MySQL finishes things rapidly.  But sometimes MySQL gets overwhelmed.  The cure is (in my opinion) to throttle how many connections the client will make to MySQL.  After only a few dozen active connections, MySQL starts stumbling over itself (locks, mutexes, I/O, CPU, etc).  The solution may be to speed up the queries (see SlowLog), or it may be to throttle the clients.  Threads_running (at the moment) and Max_used_connections (after the fact, but with no time given) are the clues that this scenario may be happening.

In that case, the SlowLog would show slow queries that are normally fast.  And it would show a lot of slow queries at a specific time -- coming from a lot of different connections (clients).

I have a couple hundred formulas (of STATUS and VARIABLES) with recommended ranges.  This comes in handy once in a while.  Some of them say whether caches (Query, Buffer_pool, Key_buffer, Table_open, etc) are being effective or inefficient.  Unfortunately, they rarely say "change xx to yy".

One project had spikes at the start of the hour.  Well, the obvious answer was 'cron'.  Sure enough, there were over a hundred cron jobs, each hitting at the top of the your, and possibly at other times.  Solution:  Instead of 

*/5 * * * * do_something.sh


do

(3-59)/5 * * * * do_something.sh


(and change the '3' to various values for different cron jobs.)  MySQL EVENTs can also be a spike-maker.

Many monitoring tools can discover such an hourly spike.  Even an alert human can notice such.  But few, if any, monitoring tools will tell you what to do.

The real problem is that the tools give you clues, but not answers.  That is where a human needs to step in.  That human needs a knowledge of the 'system', the tunables, the queries, the schema, how to create good indexes, etc.