What is MySQL currently doing?

When the MySQL database suddenly becomes unresponsive, the most important question: What is MySQL currently doing?  One way to answer this question is with the following SQL:

SHOW FULL PROCESSLIST;

You should be able to run this command from your MySQL Workbench or a MySQL prompt.  Much like the linux 'top' command it shows the current processes and what they are doing. Here is an example of the call:

In the above example, the database is fairly idle as most of the processes are sleeping (Command=Sleep) and have been for awhile.  When the database is busy executing long running queries they will show up with a Command=Query.  The 'Time' column tells you how long the query has been running in seconds.

Historically: What has been slow?

When the database has been sporadically slow, or seems to be getting slower the slow query log can be a useful tool in debugging the problem.  By default, MySQL does not have the slow query log turned on, but on all of our AWS RDS instances we should have turn on this feature.  At the time of this writing our RDS instances are configured to captures any query that takes longer than one second.  The slow query log can be accessed through MySQL Workbench or a MySQL command prompt by querying the 'slow_log' table in the 'mysql' schema.  You must connect with a user that has read permission to the 'mysql' schema (or database).  Here is an example:

select * from mysql.slow_log where start_time > '2012-06-01 00:00:00'  and db = 'prodC' order by start_time desc;

Here is an example of the above query:

In this example you can see we were having some problem with some very basic queries taking crazy amounts of time.