执行analyze table意外导致waiting for table flush.pdf

张鹏

179

6页

0次

2023-05-24

5墨值下载

囧...执行analyze table意外导致waiting for table flush

原创高鹏老叶茶馆 2月7日

作者：八怪（高鹏）中亦科技数据库专家

一、问题描述

问题现场如下：

mysql> show processlist;

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

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

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

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

sleep(1000) from test.e01 |

ocesslist |

* from test.e01 |

* from test.e01 |

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

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

乍一看来，很是奇怪，这里没有出现我们经常遇到的 flush table/flush table with read lock 堵塞，直

接出现了 Waiting for table flush的堵塞，有点像

https://www.jianshu.com/p/b141585cd844

以前记录的文章中的案例2，但是其实并不一样，这里是由于analyze table语句造成的。

复现过程非常简单（必须是社区版本，我使用的8.0.21），如下：

session 1

mysql> select sleep(1000) from test.e01;（要有几条数据）

这条语句肯定结束不了

session 2

mysql> analyze table test.e01;

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

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

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

session 3

mysql> select * from test.e01;

# 被堵塞了

此时堵塞的情形就是Waiting for table flush

二、analyze触发了什么

analyze table 除了更新我们的统计数据，实际上最后做了一个操作如下（栈）：

#0 TABLE_SHARE::clear_version (this=0x7ffeec00ee38) at /opt/mysql/mysql-

8.0.21/sql/table.cc:518

#1 0x0000000003680a4d in <lambda(std::unordered_map<std::__cxx11::basic_string<char>, std::uni

que_ptr<TABLE_SHARE, Table_share_deleter>, std::hash<std::__cxx11::basic_string<char> >, std::e

qual_to<std::__cxx11::basic_string<char> >, Malloc_allocator<std::pair<const std::__cxx11::basi

c_string<char>, std::unique_ptr<TABLE_SHARE, Table_share_deleter> > > >::iterator)>::operator()

(std::unordered_map<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<cha

r> >, std::unique_ptr<TABLE_SHARE, Table_share_deleter>, std::hash<std::__cxx11::basic_string<c

har, std::char_traits<char>, std::allocator<char> > >, std::equal_to<std::__cxx11::basic_string

<char, std::char_traits<char>, std::allocator<char> > >, Malloc_allocator<std::pair<std::__cxx1

1::basic_string<char, std::char_traits<char>, std::allocator<char> > const, std::unique_ptr<TAB

LE_SHARE, Table_share_deleter> > > >::iterator) const (__closure=0x7fff600ce8a0,

my_it=...) at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:9831

#2 0x0000000003680d0c in tdc_remove_table (thd=0x7ffed8094550, remove_type=TDC_RT_REMOVE_UNUSE

D, db=0x7ffed80c4a08 "test", table_name=0x7ffed80c4a20 "e01", has_lock=false)

at /opt/mysql/mysql-8.0.21/sql/sql_base.cc:9844

#3 0x0000000003d890a9 in mysql_admin_table (thd=0x7ffed8094550, tables=0x7ffed80c4cb8, check_o

pt=0x7ffed80ba370, operator_name=0x61c06e7 "analyze", lock_type=TL_READ_NO_INSERT,

open_for_modify=true, repair_table_use_frm=false, extra_open_options=0, prepare_func=0x0, o

perator_func=

(int (handler::*)

(handler * const, THD *, HA_CHECK_OPT *)) 0x3accc64 <handler::ha_analyze(THD*, HA_CHECK_OPT*)>,

check_view=0, alter_info=0x7ffed80c4b28,

need_to_acquire_shared_backup_lock=true) at /opt/mysql/mysql-8.0.21/sql/sql_admin.cc:1296

大概看一下做了什么，如下：