• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Troubleshooting MySQL Query hung – “Waiting for Table Flush”

By admin

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.

Filed Under: mysql

Some more articles you might also be interested in …

  1. How To Create a Local Yum Repository for MySQL Enterprise Packages
  2. How to Backup a MySQL Cluster
  3. Configuring mysqld to log slow queries
  4. Beginners Guide to Management of MySQL Cluster Log Files
  5. Recommended Configuration of the MySQL Performance Schema
  6. Counting Rows Of A Table In MySQL Server
  7. MySQL: How to Set Account Resource Limits
  8. MySQL: how to figure out which session holds which table level or global read locks
  9. How To Purge Audit Logs in MySQL
  10. How to Troubleshoot InnoDB Lock Issues

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary