Skip to main content
Database Administrators

Return to Question

added additional context
Source Link
Marisa
Marisa
  • 23
  • 5

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.

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.

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.

Source Link
Marisa
Marisa
  • 23
  • 5

MySQL - Can FLUSH TABLES Be Triggered Automatically by MySQL Itself?

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.