How do I find out what's causing processes to wait for table flush?

Question

I'm currently using MariaDB 10.3 and am troubleshooting this case where threads will start to build up until the MySQL service eventually has to be restarted. I was able to capture the process list before restarting MySQL, but I have a few questions that I hope will help me head in the right direction.

In the screenshot below, I've included a portion of the process list, which I sorted by time.

The same thing has happened two different days, and on both days, the output is very similar. From looking at the processes, there are two things I've observed:

1. There is a longer running process in the statistics state.
2. All of the processes are waiting for the table flush after our database backup process, which seems to be waiting for a table flush itself. I need to confirm with the team what tool is being used for backups, but I believe it might be something from Percona.

The main issue I'm trying to solve here is figuring out what's causing all of these processes to wait for a table flush, but I'd also like to understand the output here. Does this output produce something that looks obvious in terms of what's causing it or is there a particular way you would go about figuring this out?

Thanks in advance!

Answer 1

I am going to hazard a guess that the first query at the top, as Rick said, is the cause of it, especially if it is an ANALYZE TABLE query. This is a rather typical cause of the queries piling up in the "waiting for table flush" state.

Another possiblity is that the query in question is a large write that tainted more 10% of the rows in the table - this will trigger an automatic re-analysis of the persistent InnoDB table statistics.

Disclosure: I wrote the referenced article.

Answer 2

Off hand I would say that the first item is the villain. It has the largest "Time". It locked something that all the rest are stuck waiting for.

If you wish to discuss it further, please provide the query, EXPLAIN SELECT ..., and SHOW CREATE TABLE(s).

Is the schema "EAV"? I have a guess as to how this situation could occur.

Have you changed my.cnf from the defaults?

More details:

1. the big query is hanging onto some tables
2. the back up is doing that FLUSH so it can grab all the table without interference. That is, it wants to do a "consistent" dump.
3. the rest are suck behind the FLUSH.

Next time, consider running SHOW FULL PROCESSLIST; and be sure to capture at least the item with the largest Time (other than any system processes).

What Engine(s) are your tables using?

Yes, look into other backup mechanisms, especially MariaDB's version of mysqldump.