MySQL / MariaDB: Waiting for table flush

Our MySQL support engineers recently had a customer contact us about an interesting problem. Their MySQL server’s performance getting intermittently degraded. Their queries were getting stuck in the “Waiting for table flush” state. They were seeing this on a regular basis, but there was no obvious root cause. Unfortunately, transparency of their database setup was very poor because they were on a “managed service” database managed by their hosting provider. They were not granted sufficient privileges to see all running queries, only the ones executed by their application users. They could also not access the details about all running queries using the performance_schema or the slow log.

Typical causes of queries stuck in “Waiting for table flush state”

Our first thought was that somebody misguidedly decided to regularly run ANALYZE TABLE on their database tables. Unfortunately, all we could establish with certainty was that if such a thing was running, it was not running from any of the database accessing processes they wrote themselves.

Other causes of queries stuck in “waiting for table flush” state can be backups taking place (they have to briefly flush tables to get a consistent starting point state), or explicit FLUSH TABLES statements. We also found none of those in their processes. We also looked at whether this could be a manifestation of the InnoDB table DROP / TRUNCATE buffer pool stall bug, but found no evidence of DROP or TRUNCATE statements or temporary table usage during the problematic periods.

Eventually, their managed database service provider (whom we shall not name) finally owned up to their standard managed database setup being behind this. It transpired that on all their managed database servers, they had a cron job that regularly ran ANALYZE TABLE on all of the database servers they were managing.

Best Practices for running ANALYZE TABLE

Running ANALYZE TABLE automatically is a bad practice that causes problems like this. You should only run it on a table when you have clear, repeatable and consistent evidence that your index statistics on a table are sufficiently out of sync with the data distribution to cause the query optimiser to come up with poor execution plans for queries accessing the table in question.

Conclusion

Be careful out there with buying into managed database service offerings from providers who very obviously do not have sufficient knowledge or experience to be running managed database services.