0

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.

7
  • Can FLUSH TABLES Be Triggered Automatically by MySQL Itself? You may execute this operation from Event procedure.
    – Akina
    Commented Feb 7 at 19:45
  • @Akina I want to know if MySQL will do it automatically after some operation, or if some condition is met.
    – Marisa
    Commented Feb 7 at 21:59
  • Analyze the slowlog -- see SlowLog
    – Rick James
    Commented Feb 7 at 23:44
  • The documentation says that some "internal" things cause the equivalent of FLUSH TABLE and increment that counter.
    – Rick James
    Commented Feb 7 at 23:45
  • @RickJames Do we have an idea of what things specifically, or does the documentation leave it vague?
    – Marisa
    Commented Feb 8 at 17:40

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.