Question: The Queries are hung and Process list shows: “Waiting for table flush”. How to troubleshoot the issue?
There are two main causes of this problem. The most obvious and easiest to detect is a pending FLUSH TABLES WITH READ LOCK. It is obvious because the pending FLUSH can be seen in the process list.
The less obvious is an internal table flush caused by performing ANALYZE, OPTIMIZE, RENAME, ALTER, REPAIR, or any other command that changes the underlying table’s stats or definition. After such a command, a FLUSH TABLE is issued internally to re-read the table definition and stats. If a query is running that accesses that table at the time the the FLUSH TABLE is issued, the FLUSH TABLE will be blocked until the query completes. While the FLUSH TABLE is blocked, no new queries accessing the affected tables can start running and their state in the process list will show “Waiting for table flush“.
The original command that caused the FLUSH TABLE will end successfully, and will not appear in the process list, so it can be difficult to know exactly what happened.
For queries that are blocked by a pending FLUSH TABLES WITH READ LOCK, the simple fix to get back in business is to simply kill the process that issued the FLUSH TABLES WITH READ LOCK.
For queries that are blocked by a pending FLUSH TABLE, the solution is not as straightforward. Killing the queries that are in the state “Waiting for table flush” won’t fix the issue. There are three ways to fix the problem:
- Wait for the long-running queries which are blocking the FLUSH TABLE to complete;
- Identify the long-running queries and kill them;
- Restart the server
Note that the three options mentions are options, not steps. It is not necessary to do all three.