As it says in the title, I am looking to determine whether MySQL itself will, on its own, issue a FLUSH TABLES
command at any point. I have yet to find anything that points to a specific answer to this particular question.
I am looking to determine a specific issue for one of my clients (Magento 2 database on RDS Aurora). Something issued that command and it caused a series of queries to pile up and overload the database writer instance, which partially brought down the application. In addition, there are four reader instances that have data replicated to them.
In AWS's Performance Insights, I can see FLUSH TABLES
as a "top" query during the time where this was an issue, but I don't know where it came from and that's what gives me cause for concern.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.42, for Linux (x86_64) using EditLine wrapper
Connection id: 45860368
Current database: database
Current user: database@ip
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.12-log MySQL Community Server (GPL)
Protocol version: 10
Connection: rds.host.vpc via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 19 days 23 hours 44 min 28 sec
Threads: 66 Questions: 9419814427 Slow queries: 103646984 Opens: 5194758 Flush tables: 3 Open tables: 57979 Queries per second avg: 5454.223
Additionally, innodb_stats_persistent
is enabled and innodb_stats_persistent_sample_pages
is set to 128.
I can see that tables were flushed three times, according to \s
but what causes them? Is there an internal mechanism that actually issues these commands explicitly?
I found that waiting for table flush
can result from ANALYZE TABLE
or replication, but this hasn't occurred before so I'm concerned it might happen again. And, if possible, I'd like to know how to avoid it occurring, or, if it must, how to control when it happens.
[edit] After reviewing the slow query log, I found the flush table commands, who initiated and from what IP. This looks to have been manual intervention in my particular case. However, I would still like to know if any automatic processes can trigger a flush.
FLUSH TABLE
and increment that counter.