MySQLFLTWL的堵塞和被堵塞分析-乐山大橙子建站

新闻中心

这里有您想知道的互联网营销解决方案

MySQLFLTWL的堵塞和被堵塞分析

这篇文章主要介绍“MySQL FLTWL的堵塞和被堵塞分析”，在日常操作中，相信很多人在MySQL FLTWL的堵塞和被堵塞分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL FLTWL的堵塞和被堵塞分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

创新互联公司主营息县网站建设的网络公司,主营网站建设方案,成都APP应用开发,息县h5微信小程序开发搭建,息县网站营销推广欢迎息县等地区企业咨询

一、FTWRL的作用

总的说来flush tables with read lock多用于备份的时候对全局表进行锁定来获取binlog信息。虽然持有时间很短，单容易被堵塞造成备份不能完成。那么flush tables with read lock到底做了什么事情呢如下：

获取MDL GLOBAL的S锁，直到unlock tables释放。
获取MDL COMMIT的S锁，直到unlock tables释放。
关闭所有的表重新打开，先释放table cache(包含TABLE_SHARED)，然后重新加载生成table cache。

（下面的信息是在源码函数 acquire_lock和release_lock增加输出达到的）
下面的信息可以看到flush tables with read lock获取MDL LOCK的信息：

2018-08-07T08:03:59.272547Z 7 [Note] [Call Acquire_lock] THIS MDL LOCK acquire [OK]:2018-08-07T08:03:59.272583Z 7 [Note] (>MDL PRINT) |Thread id is 7|Current_state: starting| 
2018-08-07T08:03:59.272599Z 7 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2018-08-07T08:03:59.272613Z 7 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S) 
2018-08-07T08:03:59.272627Z 7 [Note] (------>MDL PRINT) Mdl  duration is:MDL_EXPLICIT 
2018-08-07T08:03:59.272642Z 7 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2018-08-07T08:03:59.292471Z 7 [Note] [Call Acquire_lock] THIS MDL LOCK acquire [OK]:
2018-08-07T08:03:59.292522Z 7 [Note] (>MDL PRINT) |Thread id is 7|Current_state: starting| 2018-08-07T08:03:59.292538Z 7 [Note] (--->MDL PRINT) Namespace is:COMMIT 
2018-08-07T08:03:59.292551Z 7 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S) 
2018-08-07T08:03:59.292564Z 7 [Note] (------>MDL PRINT) Mdl  duration is:MDL_EXPLICIT 
2018-08-07T08:03:59.292580Z 7 [Note] (------->MDL PRINT) Mdl  status is:EMPTY

下面是unlock tables释放MDL LOCK的信息：

2018-08-07T08:05:43.520540Z 7 [Note] [Call release_lock] this MDL LOCK will [RELEASE]:2018-08-07T08:05:43.520571Z 7 [Note] (>MDL PRINT) |Thread id is 7|Current_state: starting| 
2018-08-07T08:05:43.520597Z 7 [Note] (--->MDL PRINT) Namespace is:COMMIT 
2018-08-07T08:05:43.520609Z 7 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S) 
2018-08-07T08:05:43.520620Z 7 [Note] (------>MDL PRINT) Mdl  duration is:MDL_EXPLICIT 
2018-08-07T08:05:43.520634Z 7 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2018-08-07T08:05:43.520658Z 7 [Note] [Call release_lock] this MDL LOCK will [RELEASE]:2018-08-07T08:05:43.520671Z 7 [Note] (>MDL PRINT) |Thread id is 7|Current_state: starting| 
2018-08-07T08:05:43.520682Z 7 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2018-08-07T08:05:43.520693Z 7 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S) 
2018-08-07T08:05:43.520704Z 7 [Note] (------>MDL PRINT) Mdl  duration is:MDL_EXPLICIT 
2018-08-07T08:05:43.520727Z 7 [Note] (------->MDL PRINT) Mdl  status is:EMPTY

如下是关于范围 MDL LOCK的兼容性：

          | Type of active   |
  Request |   scoped lock    |
   type   | IS(*)  IX   S  X |
 ---------+------------------+
 IS       |  +      +   +  + |
 IX       |  +      +   -  - |
 S        |  +      -   +  - |
 X        |  +      -   -  - |

二、常见操作关于和FTWRL MDL 相关锁。

对于DML\FOR UPDATE：需要获取GLOBAL的IX锁持有到语句结束，但是TABLE MDL 持有到事物结束一般为(MDL_SHARED_WRITE(SW) )，DML提交的时候会持有COMMIT的IX锁。
SELECT：不需要GLOBAL的IX锁，但是TABLE级别的MDL 需要持有到事物事物结束一般为(MDL_SHARED_READ(SR))。
DDL：需要获取GLOBAL的IX锁到语句结束，TABLE MDL多变。

三、FTWRL的被什么堵塞

1、长时间的DDL\DML\FOR UPDATE堵塞FTWRL，因为FTWRL需要获取 GLOBAL的S锁，而这些语句都会对GLOBAL持有IX（MDL_INTENTION_EXCLUSIVE）锁，根据兼容矩阵不兼容。

等待为：Waiting for global read lock
堵塞栈帧：

#0  0x0000003f7480ba5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0#1  0x00000000018e7f33 in native_cond_timedwait (cond=0x7fff28009688, mutex=0x7fff28009640, abstime=0x7ffff0318a00)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#2  0x00000000018e82a2 in safe_cond_timedwait (cond=0x7fff28009688, mp=0x7fff28009618, abstime=0x7ffff0318a00, 
    file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", line=1899) at /root/mysql5.7.14/percona-server-5.7.14-7/mysys/thr_cond.c:88#3  0x00000000014a0149 in my_cond_timedwait (cond=0x7fff28009688, mp=0x7fff28009618, abstime=0x7ffff0318a00, 
    file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", line=1899) at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:180#4  0x00000000014a06ac in inline_mysql_cond_timedwait (that=0x7fff28009688, mutex=0x7fff28009618, abstime=0x7ffff0318a00, 
    src_file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", src_line=1899)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_thread.h:1229#5  0x00000000014a192b in MDL_wait::timed_wait (this=0x7fff28009618, owner=0x7fff28009510, abs_timeout=0x7ffff0318a00, set_status_on_timeout=true, 
    wait_state_name=0x2d132c0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:1899#6  0x00000000014a38a7 in MDL_context::acquire_lock (this=0x7fff28009618, mdl_request=0x7ffff0318a70, lock_wait_timeout=31536000)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:3714#7  0x00000000017629ef in Global_read_lock::lock_global_read_lock (this=0x7fff2800b300, thd=0x7fff28009510)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/lock.cc:1131#8  0x00000000015ec19a in reload_acl_and_cache (thd=0x7fff28009510, options=16388, tables=0x0, write_to_binlog=0x7ffff03199fc)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_reload.cc:222#9  0x00000000015a9c27 in mysql_execute_command (thd=0x7fff28009510, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4379#10 0x00000000015adcd6 in mysql_parse (thd=0x7fff28009510, parser_state=0x7ffff031a600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#11 0x00000000015a1b95 in dispatch_command (thd=0x7fff28009510, com_data=0x7ffff031ad70, command=COM_QUERY)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#12 0x00000000015a09c6 in do_command (thd=0x7fff28009510) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#13 0x00000000016e29d0 in handle_connection (arg=0x33d01c0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#14 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x38424d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#15 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#16 0x0000003f740e8bcd in clone () from /lib64/libc.so.6

2、长时间的select堵塞FTWRL，因为FTWRL要关闭所有的表，如果有活跃的语句正在执行table cache不能清空，因此需要等待。实际上即便是flush tables也不能在有语句执行的时候执行同样需要等待。

等待为：Waiting for table flush
堵塞栈帧：

#0  0x0000003f7480ba5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0#1  0x00000000018e7f33 in native_cond_timedwait (cond=0x7fff28009688, mutex=0x7fff28009640, abstime=0x7ffff0318be0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#2  0x00000000018e82a2 in safe_cond_timedwait (cond=0x7fff28009688, mp=0x7fff28009618, abstime=0x7ffff0318be0, 
    file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", line=1899) at /root/mysql5.7.14/percona-server-5.7.14-7/mysys/thr_cond.c:88#3  0x00000000014a0149 in my_cond_timedwait (cond=0x7fff28009688, mp=0x7fff28009618, abstime=0x7ffff0318be0, 
    file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", line=1899) at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:180#4  0x00000000014a06ac in inline_mysql_cond_timedwait (that=0x7fff28009688, mutex=0x7fff28009618, abstime=0x7ffff0318be0, 
    src_file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", src_line=1899)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_thread.h:1229#5  0x00000000014a192b in MDL_wait::timed_wait (this=0x7fff28009618, owner=0x7fff28009510, abs_timeout=0x7ffff0318be0, set_status_on_timeout=true, 
    wait_state_name=0x2d09d00) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:1899#6  0x00000000016a48ca in TABLE_SHARE::wait_for_old_version (this=0x7fff58984190, thd=0x7fff28009510, abstime=0x7ffff0318be0, deadlock_weight=100)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table.cc:4585#7  0x0000000001513e3a in close_cached_tables (thd=0x7fff28009510, tables=0x0, wait_for_refresh=true, timeout=31536000)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:1289#8  0x00000000015ec1d8 in reload_acl_and_cache (thd=0x7fff28009510, options=16388, tables=0x0, write_to_binlog=0x7ffff03199fc)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_reload.cc:224#9  0x00000000015a9c27 in mysql_execute_command (thd=0x7fff28009510, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4379#10 0x00000000015adcd6 in mysql_parse (thd=0x7fff28009510, parser_state=0x7ffff031a600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#11 0x00000000015a1b95 in dispatch_command (thd=0x7fff28009510, com_data=0x7ffff031ad70, command=COM_QUERY)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#12 0x00000000015a09c6 in do_command (thd=0x7fff28009510) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#13 0x00000000016e29d0 in handle_connection (arg=0x33d01c0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#14 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x38424d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#15 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#16 0x0000003f740e8bcd in clone () from /lib64/libc.so.6

3、长时间的commit(如大事物提交)也会堵塞FTWRL，因为FTWRL需要获取COMMIT的S锁，而commit语句会对commit持有IX（MDL_INTENTION_EXCLUSIVE）锁，根据兼容矩阵不兼容。

等待为Waiting for commit lock

基本我们看到所有的语句都会堵塞FTWRL。

四、FTWRL堵塞什么

1、FTWRL会堵塞DDL\DML\FOR UPDATE操作

等待为：Waiting for global read lock
堵塞栈帧：

#0  0x0000003f7480ba5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0#1  0x00000000018e7f33 in native_cond_timedwait (cond=0x7fff58000ee8, mutex=0x7fff58000ea0, abstime=0x7ffff0359660)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#2  0x00000000018e82a2 in safe_cond_timedwait (cond=0x7fff58000ee8, mp=0x7fff58000e78, abstime=0x7ffff0359660, 
    file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", line=1899) at /root/mysql5.7.14/percona-server-5.7.14-7/mysys/thr_cond.c:88#3  0x00000000014a0149 in my_cond_timedwait (cond=0x7fff58000ee8, mp=0x7fff58000e78, abstime=0x7ffff0359660, 
    file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", line=1899) at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:180#4  0x00000000014a06ac in inline_mysql_cond_timedwait (that=0x7fff58000ee8, mutex=0x7fff58000e78, abstime=0x7ffff0359660, 
    src_file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", src_line=1899)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_thread.h:1229#5  0x00000000014a192b in MDL_wait::timed_wait (this=0x7fff58000e78, owner=0x7fff58000d70, abs_timeout=0x7ffff0359660, set_status_on_timeout=true, 
    wait_state_name=0x2d132c0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:1899#6  0x00000000014a38a7 in MDL_context::acquire_lock (this=0x7fff58000e78, mdl_request=0x7ffff03596e0, lock_wait_timeout=31536000)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:3714#7  0x0000000001517a08 in open_table (thd=0x7fff58000d70, table_list=0x7fff58006a70, ot_ctx=0x7ffff0359b00)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:3227#8  0x000000000151b24f in open_and_process_table (thd=0x7fff58000d70, lex=0x7fff58003350, tables=0x7fff58006a70, counter=0x7fff58003410, flags=0, 
    prelocking_strategy=0x7ffff0359c30, has_prelocking_list=false, ot_ctx=0x7ffff0359b00) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5171#9  0x000000000151c3ab in open_tables (thd=0x7fff58000d70, start=0x7ffff0359bf0, counter=0x7fff58003410, flags=0, prelocking_strategy=0x7ffff0359c30)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5789#10 0x000000000151d7e5 in open_tables_for_query (thd=0x7fff58000d70, tables=0x7fff58006a70, flags=0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:6564#11 0x00000000015acb58 in execute_sqlcom_select (thd=0x7fff58000d70, all_tables=0x7fff58006a70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5362#12 0x00000000015a5320 in mysql_execute_command (thd=0x7fff58000d70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:2889#13 0x00000000015adcd6 in mysql_parse (thd=0x7fff58000d70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#14 0x00000000015a1b95 in dispatch_command (thd=0x7fff58000d70, com_data=0x7ffff035bd70, command=COM_QUERY)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#15 0x00000000015a09c6 in do_command (thd=0x7fff58000d70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#16 0x00000000016e29d0 in handle_connection (arg=0x346f2b0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#17 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x38424d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#18 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#19 0x0000003f740e8bcd in clone () from /lib64/libc.so.6

2、FTWRL会堵塞commit操作

等待为Waiting for commit lock
堵塞栈帧：

#0  0x0000003f7480ba5e in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0#1  0x00000000018e7f33 in native_cond_timedwait (cond=0x7fff58000ee8, mutex=0x7fff58000ea0, abstime=0x7ffff03599a0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:129#2  0x00000000018e82a2 in safe_cond_timedwait (cond=0x7fff58000ee8, mp=0x7fff58000e78, abstime=0x7ffff03599a0, 
    file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", line=1899) at /root/mysql5.7.14/percona-server-5.7.14-7/mysys/thr_cond.c:88#3  0x00000000014a0149 in my_cond_timedwait (cond=0x7fff58000ee8, mp=0x7fff58000e78, abstime=0x7ffff03599a0, 
    file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", line=1899) at /root/mysql5.7.14/percona-server-5.7.14-7/include/thr_cond.h:180#4  0x00000000014a06ac in inline_mysql_cond_timedwait (that=0x7fff58000ee8, mutex=0x7fff58000e78, abstime=0x7ffff03599a0, 
    src_file=0x20013b8 "/root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc", src_line=1899)
    at /root/mysql5.7.14/percona-server-5.7.14-7/include/mysql/psi/mysql_thread.h:1229#5  0x00000000014a192b in MDL_wait::timed_wait (this=0x7fff58000e78, owner=0x7fff58000d70, abs_timeout=0x7ffff03599a0, set_status_on_timeout=true, 
    wait_state_name=0x2d13380) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:1899#6  0x00000000014a38a7 in MDL_context::acquire_lock (this=0x7fff58000e78, mdl_request=0x7ffff0359a10, lock_wait_timeout=31536000)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/mdl.cc:3714#7  0x0000000000f63bd9 in ha_commit_trans (thd=0x7fff58000d70, all=true, ignore_global_read_lock=false)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1783#8  0x00000000016b36bb in trans_commit (thd=0x7fff58000d70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:239#9  0x00000000015aa1f1 in mysql_execute_command (thd=0x7fff58000d70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:4472#10 0x00000000015adcd6 in mysql_parse (thd=0x7fff58000d70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836#11 0x00000000015a1b95 in dispatch_command (thd=0x7fff58000d70, com_data=0x7ffff035bd70, command=COM_QUERY)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447#12 0x00000000015a09c6 in do_command (thd=0x7fff58000d70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010#13 0x00000000016e29d0 in handle_connection (arg=0x346f2b0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312#14 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x38424d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#15 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#16 0x0000003f740e8bcd in clone () from /lib64/libc.so.6

3、FTWRL不会堵塞SELECT操作

五、percona对xtrbackup的改进

对于FTWRL percona做了改进应该是backup lock和binlog lock来代替。

到此，关于“MySQL FLTWL的堵塞和被堵塞分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！

网页名称：MySQLFLTWL的堵塞和被堵塞分析
当前URL：http://lswzjz.com/article/jodijd.html