Diagnosing MySQL Server Freezes and Stalls, Sometimes Called Crashes

This document is a work in progress with only limited content at this stage. You should open a service request if it does not provide sufficient information to resolve your problem.

This does not cover actual crashes of the MySQL server. Check your running process list using operating system tools like PS or Task Manager. If you see that mysqld is still running this is the guide for you. If it is not still running, check a guide to actual crashes.

Look at the end of the list of transactions for queries that have been running for many tens, hundreds or thousands of seconds. If you see many such queries, decide whether you can safely KILL them and do that until the problem is resolved. Then work on tuning the queries and setting innodb_thread_concurrency and innodb_concurrency_tickets appropriately.

Tell us the symptoms, whether it was sudden and whether it has been happening regularly. We will also need this output:
 

SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS;

Both taken while the problem is happening.
Both a second time a minute or so later, ideally still during the problem. If it is ongoing, repeat a few times with a ten or so minute gap between runs.
 

SHOW VARIABLES;
SHOW GLOBAL STATUS;

Also provide the most recent few thousand lines of the MySQL error log. Do not just start when the problem started, we need at least a  few hundred lines before then, if those lines exist. Going back more than a few days is not necessary.

For longer term monitoring we may ask you to turn on the InnoDB monitor. This regularly writes InnoDB status information to the MySQL Server error log file (usually called <servername>.err in the MySQL data directory. This is useful for monitoring batch jobs or over time to catch periodic problems. To enable it enter this in the client mysql:
 

USE TEST;
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

To disable it after collecting data use:
 

USE TEST;
DROP TABLE innodb_monitor;