首页 / MYSQL / MySQL线程处于Waiting for table flush的分析

MySQL线程处于Waiting for table flush的分析

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL线程处于Waiting for table flush的分析，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12389字，纯文字阅读大概需要18分钟。

内容图文

最近遇到一个案例，很多查询被阻塞没有返回结果，使用show processlist查看，发现不少MySQL线程处于Waiting for table flush状态，查询语句一直被阻塞，只能通过Kill进程来解决。那么我们先来看看Waiting for table flush的官方解释：https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

Waiting for table flush

The thread is executing FLUSH TABLES and is waiting for all threads to close their tables, or the thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

那么我们接下来模拟一下线程处于Waiting for table flush状态的情况，如所示：

在第一个会话连接（connection id=13）中，我们使用lock table 锁定表test。

mysql> use MyDB;

Database changed

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|              13 |

+-----------------+

1 row in set (0.00 sec)

mysql> lock table test read;

Query OK, 0 rows affected (0.00 sec)

mysql>

在第二个会话连接（connection id=17）中，我们执行flush table 或 flush table test 皆可。此时你会发现flush table处于阻塞状态。

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|              17 |

+-----------------+

1 row in set (0.00 sec)

mysql> flush table test;

MySQL线程处于Waiting for table flush的分析 - 文章图片

在第三个会话/连接中，当你切换到MyDB时，就会提示“You can turn off this feature to get a quicker startup with -A” ，此时处于阻塞状态。此时你退出会话，使用参数-A登录数据库后，你如果查询test表，就会处于阻塞状态（当然查询其它表不会被阻塞）。如下所示：

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

mysql> use MyDB;

Database changed

mysql> select * from test;

MySQL线程处于Waiting for table flush的分析 - 文章图片

在第四个会话/连接，我们用show processlist查看到当前数据库所有连接线程状态，你会看到17、18都处于Waiting for table flush的状态。如下截图所示：

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| Id | User | Host      | db   | Command | Time | State                   | Info               |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep   |   90 |                         | NULL               |

| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |

| 17 | root | localhost | MyDB | Query   |   52 | Waiting for table flush | flush table test   |

| 18 | root | localhost | MyDB | Query   |    9 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

4 rows in set (0.00 sec)

mysql>

MySQL线程处于Waiting for table flush的分析 - 文章图片

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| Id | User | Host      | db   | Command | Time | State                   | Info               |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep   |   90 |                         | NULL               |

| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |

| 17 | root | localhost | MyDB | Query   |   52 | Waiting for table flush | flush table test   |

| 18 | root | localhost | MyDB | Query   |    9 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

4 rows in set (0.00 sec)

mysql>

mysql>

mysql>

mysql>

mysql> show open tables where in_use >=1;

+----------+-------+--------+-------------+

| Database | Table | In_use | Name_locked |

+----------+-------+--------+-------------+

| MyDB     | test  |      1 |           0 |

+----------+-------+--------+-------------+

1 row in set (0.00 sec)

mysql> kill 17;

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| Id | User | Host      | db   | Command | Time | State                   | Info               |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep   |  442 |                         | NULL               |

| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |

| 18 | root | localhost | MyDB | Query   |  361 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

3 rows in set (0.00 sec)

mysql> kill 13;

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host      | db   | Command | Time | State | Info             |

+----+------+-----------+------+---------+------+-------+------------------+

| 14 | root | localhost | NULL | Query   |    0 | init  | show processlist |

| 18 | root | localhost | MyDB | Sleep   |  427 |       | NULL             |

+----+------+-----------+------+---------+------+-------+------------------+

2 rows in set (0.00 sec)

mysql>

| MySQL线程处于Waiting for table flush的分析 - 文章图片

注意：我们需要Kill线程13， Kill掉线程17是解决不了问题的。

生产环境中，很多时候可能不是lock table read引起的阻塞，而是由于慢查询，导致flush table一直无法关闭该表而一直处于等待状态，例如下面测试案例中，我使用同一张大表做笛卡尔积模拟一个慢查询，其它操作相同，如下所示，你会看到同样产生了Waiting for table flush

mysql> SELECT T.* FROM TEST1 T, TEST1 L;

MySQL线程处于Waiting for table flush的分析 - 文章图片

另外，网上有个案例，mysqldump备份时，如果没有使用参数—single-transaction 或由于同时使用了flush-logs与—single-transaction两个参数也可能引起这样的等待场景，这个两个参数放在一起，会在开始dump数据之前先执行一个FLUSH TABLES操作。

解决方案：

出现Waiting for table flush时，我们一般需要找到那些表被lock住或那些慢查询导致flush table一直在等待而无法关闭该表。然后Kill掉对应的线程即可，但是如何精准定位是一个挑战，尤其是生产环境，你使用show processlist会看到大量的线程。让你眼花缭乱的，怎么一下子定位问题呢？

对于慢查询引起的其它线程处于Waiting for table flush状态的情形：

可以查看show processlist中Time值很大的线程。然后甄别确认后Kill掉，如上截图所示，会话连接14就是引起阻塞的源头SQL。有种规律就是这个线程的Time列值必定比被阻塞的线程要高。这个就能过滤很多记录。

对于lock table read引起的其它线程处于Waiting for table flush状态的情形：

对于实验中使用lock table read这种情况，这种会话可能处于Sleep状态，而且它也不会出现在show engine innodb status \G命令的输出信息中。即使show open tables where in_use >=1;能找到是那张表被lock住了，但是无法定位到具体的线程（连接），其实这个是一个头痛的问题。但是inntop这款利器就可以定位到，如下所示，线程17锁住了表test，在innotop里面就能定位到是线程17。所谓工欲善其事必先利其器！

MySQL线程处于Waiting for table flush的分析 - 文章图片

另外，在官方文档中ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE都能引起这类等待，下面也做了一些简单测试，如下所示：

Waiting for table flush的另外一个场景

会话连接（connection id=18)执行下面SQL语句，模拟一个慢查询SQL

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|              18 |

+-----------------+

1 row in set (0.00 sec)

mysql> select name, sleep(64) from test;

会话连接（connection id=6)执行下面SQL语句，分析表test

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               6 |

+-----------------+

1 row in set (0.00 sec)

mysql> analyze table test;

+-----------+---------+----------+----------+

| Table     | Op      | Msg_type | Msg_text |

+-----------+---------+----------+----------+

| MyDB.test | analyze | status   | OK       |

+-----------+---------+----------+----------+

1 row in set (0.04 sec)

mysql>

会话连接（connection id=8)执行下面SQL语句

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               8 |

+-----------------+

1 row in set (0.00 sec)

mysql> select * from test;

查看线程的状态，你会发现被阻塞的会话处于 Waiting for table flush状态。因为当对表做了ANALYZE TABLE后，后台针对该表的查询需要等待，因为MySQL已经检测到该表内部变化，需要使用FLUSH TABLE关闭然后重新打开该表，所以当你查询该表时，就会处于 Waiting for table flush

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+----------------------------------+

| Id | User | Host      | db   | Command | Time | State                   | Info                             |

+----+------+-----------+------+---------+------+-------------------------+----------------------------------+

|  6 | root | localhost | MyDB | Sleep   |   22 |                         | NULL                             |

|  8 | root | localhost | MyDB | Query   |   14 | Waiting for table flush | select * from test               |

| 15 | root | localhost | NULL | Sleep   |    3 |                         | NULL                             |

| 16 | root | localhost | NULL | Query   |    0 | init                    | show processlist                 |

| 18 | root | localhost | MyDB | Query   |   46 | User sleep              | select name, sleep(64) from test |

+----+------+-----------+------+---------+------+-------------------------+----------------------------------+

5 rows in set (0.00 sec)

mysql>

MySQL线程处于Waiting for table flush的分析 - 文章图片

Waiting for table metadata lock

会话连接（connection id=17)执行下面SQL语句，模拟一个慢查询SQL

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|              17 |

+-----------------+

1 row in set (0.00 sec)

mysql> select name, sleep(100) from test;

会话连接（connection id=6）执行下面SQL语句，修改表结构操作

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               6 |

+-----------------+

1 row in set (0.00 sec)

mysql> alter table test add tname varchar(10); // rename table test to kkk 同样会引起Waiting for table metadata lock

会话连接（connection id=8）执行下面SQL语句，查询表test

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               8 |

+-----------------+

1 row in set (0.00 sec)

mysql> select * from test;

查看线程的状态，你会发现被阻塞的会话处于 Waiting for table metadata lock状态。

mysql> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+

| Id | User | Host      | db   | Command | Time | State                           | Info                                   |

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+

|  6 | root | localhost | MyDB | Query   |   19 | Waiting for table metadata lock | alter table test add tname varchar(10) |

|  8 | root | localhost | MyDB | Query   |    6 | Waiting for table metadata lock | select * from test                     |

| 15 | root | localhost | NULL | Sleep   |    8 |                                 | NULL                                   |

| 16 | root | localhost | NULL | Query   |    0 | init                            | show processlist                       |

| 17 | root | localhost | MyDB | Query   |   55 | User sleep                      | select name, sleep(100) from test      |

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+

5 rows in set (0.00 sec)

mysql>

MySQL线程处于Waiting for table flush的分析 - 文章图片

参考资料：

https://www.percona.com/blog/2013/02/27/mysql-optimizer-analyze-table-and-waiting-for-table-flush/

http://www.cnblogs.com/jackhub/p/3841004.html

http://myrock.github.io/2014/11/20/mysql-waiting-for-table-flush/

http://mysql.taobao.org/monthly/2016/03/10/

MySQL线程处于Waiting for table flush的分析

标签：uri command https 实验 body exe init 分享案例

本文系统来源：http://www.cnblogs.com/xiamibk/p/7390841.html

内容总结

以上是互联网集市为您收集整理的MySQL线程处于Waiting for table flush的分析全部内容，希望文章能够帮你解决MySQL线程处于Waiting for table flush的分析所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/488873.html

来源：【匿名】

【上一篇】数据查询优化之mysql索引【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL线程处于Waiting for table flush的分析】教程文章相关的互联网学习教程文章

MySQL 重要参数 innodb_flush_log_at_trx_commit 和 sync_binlog

innodb_flush_log_at_trx_commit 该参数控制重做日志写入磁盘的过程。我们知道 InnoDB 使用“Write Ahead Log”策略来避免数据丢失问题，即依靠重做日志来保证数据能在丢失后进行恢复。因此，InnoDB 重做日志的持久化非常重要。这个参数的默认值为1首先需要大致了解一下mysql日志操作步骤： log_buff --》 mysql写 (write) --》 log_file --》 OS刷新 (flush) --》 diskinnodb_flush_log_at_trx_commit 参数解释： 0（延迟写）： l...

MySQL 清理缓存—flush tablesFlush tables的影响

摘自：http://blog.chinaunix.net/uid-31401119-id-5781305.html 1 Flush tables简介官方手册中关于Flush tables的介绍， Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.其解析就是关闭所有已打开的表对象，同时将查询缓存中的结果清空。就是说Flush tables的一个效...

什么是最具侵略性的FLUSH / RESET mysql命令来清除查询缓存(以及其他任何东西……)【代码】

我正在mysql.com上阅读,这个FLUSH / RESET命令有多种变体. 什么是从mysqld中冲洗一切可能的(缓存,缓冲区,一切)最积极的方法？我们希望尽可能接近“刚开始”,而不关闭守护进程. 谢谢！解决方法:我猜这些应该做：RESET QUERY CACHE; FLUSH STATUS, TABLES WITH READ LOCK;请阅读相应的手册部分,确保您知道自己在做什么;) http://dev.mysql.com/doc/refman/5.1/en/reset.html http://dev.mysql.com/doc/refman/5.1/en/flush.html

OracleASH内存强制Flush日志解决一例

Oracle ASH（Active Session History）是作为细粒度的AWR报告，经常在我们进行性能调优过程中被应用到。和所有的监控手段一样，A Oracle ASH（Active Session History）是作为细粒度的AWR报告，经常在我们进行性能调优过程中被应用到。和所有的监控手段一样，ASH是建立在定时性能数据采样收集，最后集中汇总分析的基础上。ASH和AWR相比，采样频率更加密集，数据以活跃会话active session为中心。在实际中，我们也可能会遇到与ASH有...

alert日志中出现PrivateStrandFlushNotComplete的处理方法

还是南京那个客户的库，alert.log日志还报了如下的错误： Fri Oct 17 19:59:51 2014Thread 1 cannot allocate new log, sequence 4722Private strand flush not complete Current log# 1 seq# 4721 mem# 0: /oradata/sgomp5/redo01.logThread 1 advanced to l还是南京那个客户的库，alert.log日志还报了如下的错误： Fri Oct 17 19:59:51 2014 Thread 1 cannot allocate new log, sequence 4722 Private strand flush not complete...

mysql出现大量FLUSH TABLES WITH READ LOCK

解决办法故障总结系统中的global read lock被其它线程获取了（被加上了S锁），因此DML在grl上面加IX锁时会出现Waiting for global read lock，而set read_only在grl上加的是S锁，因此此时是可以grant的，而此时系统中存在大查询，set global read_only=1需要等待表被关闭，因此状态是：Waiting for table flush因此原因可以确定了：系统中存在flush tables with read lock但是没有unlock tables小技巧在手动做FLUSH TABLES WITH RE...

MySQL事物系列：3：innodb_flush_log_at_trx_commit小实验【代码】

1：创建表和存储过程mysql> create database trx; Query OK, 1 row affected (0.02 sec)mysql> USE trx Database changed mysql> CREATE TABLE test_trx(-> id int(5),-> uname char(30))-> engine =innodb; Query OK, 0 rows affected (0.01 sec)mysql> DROP PROCEDURE IF EXISTS pro_trx $$ Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER $$ mysql> DROP PROCEDURE IF EXISTS pro_trx $$ Query OK, 0 rows affected, 1 ...

MySql Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' 解决方法

② 修改max_connection_errors的数量为1000： set global max_connect_errors = 1000;③ 查看是否修改成功：show variables like ‘%max_connection_errors%‘; 2、使用mysqladmin flush-hosts 命令清理一下hosts文件（不知道mysqladmin在哪个目录下可以使用命令查找：whereis mysqladmin）；① 在查找到的目录下使用命令修改：/usr/bin/mysqladmin flush-hosts -h192.168.1.1 -P3308 -uroot -prootpwd;备注：其中端口号，用户名，...

MySQL flush privileges ???

[? ???? ?? ?][root@mail ~]# mysqladmin reload[root@mail ~]# mysqladmin flush-privileges [? ???? ????? ?? ?? ?? ?][root@mail ~]# mysqladmin -u root -p reloadEnter password: MySQL flush privileges ???标签：本文系统来源：http://www.cnblogs.com/jweb/p/4612887.html

MySQL · 性能优化· InnoDB buffer pool flush策略漫谈【代码】

背景我们知道InnoDB使用buffer pool来缓存从磁盘读取到内存的数据页。buffer pool通常由数个内存块加上一组控制结构体对象组成。内存块的个数取决于buffer pool instance的个数，不过在5.7版本中开始默认以128M（可配置）的chunk单位分配内存块，这样做的目的是为了支持buffer pool的在线动态调整大小。 Buffer pool的每个内存块通过mmap的方式分配内存，因此你会发现，在实例启动时虚存很高，而物理内存很低。这些大片的内存块又...

MySQL参数：innodb_flush_log_at_trx_commit 和 sync_binlog

innodb_flush_log_at_trx_commit 和 sync_binlog 是 MySQL 的两个配置参数，前者是 InnoDB 引擎特有的。之所以把这两个参数放在一起讨论，是因为在实际应用中，它们的配置对于 MySQL 的性能有很大影响。 1. innodb_flush_log_at_trx_commit 简而言之，innodb_flush_log_at_trx_commit 参数指定了 InnoDB 在事务提交后的日志写入频率。这么说其实并不严谨，且看其不同取值的意义和表现。当 innodb_flush_log_at_trx_commit 取值为 0...