MySQL · 答疑解惑 · 物理备份死锁分析

背景

本文对 5.6 主备场景下,在备库做物理备份遇到死锁的case进行分析,希望对大家有所帮助。

这里用的的物理备份工具是 Percona-XtraBackup(PXB),有的同学可能不清楚其备份流程,所以这里先简单说下,PXB的备份步骤是这样的:

  1. 拷贝 InnoDB redo log,这是一个单独的线程在拷,直到备份结束;
  2. 拷贝所有InnoDB ibd文件;
  3. 加全局读锁,执行 FLUSH TABLES WITH READ LOCK(FTWRL);
  4. 拷贝 frm、MYD、MYI 等文件;
  5. 获取位点信息,执行 show slave status 和 show master status;
  6. 解锁,UNLOCK TABLES;
  7. 做一些收尾处理,备份结束。

如果 MyISAM 表很多话,全局读锁的持有时间会比较长,所以一般都在备库做备份。

另外 FLUSH TABLE WITH READ LOCK 这条命令会获取2个MDL锁,全局读锁(MDL_key::GLOBAL)和全局COMMIT(MDL_key::COMMIT)锁,MDL锁详情可以参考之前的月报MDL 实现分析

死锁分析

CASE 1

我们先看一下死锁时的现场是怎样的:

mysql> show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------------+-------------------+
| Id | User        | Host            | db   | Command | Time | State                                  | Info              |
+----+-------------+-----------------+------+---------+------+----------------------------------------+-------------------+
| 1 | root        | 127.0.0.1:53309 | NULL | Query   | 278 | init                                   | show slave status |
| 2 | system user |                 | NULL | Connect | 381 | Queueing master event to the relay log | NULL |
| 3 | system user |                 | NULL | Connect | 311 | Waiting for commit lock                | NULL |
| 4 | root        | 127.0.0.1:53312 | NULL | Query   | 0 | init                                   | show processlist  |
+----+-------------+-----------------+------+---------+------+----------------------------------------+-------------------+

可以看到 show slave status 被堵了很久,另外 SQL 线程在 Waiting for commit lock,说明在等待 COMMIT 锁。

这时候如果我们再连接进去执行 show slave status 也会被堵,并且即使 Ctrl-C kill 掉线程,线程依然还在。

mysql> show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------------+-------------------+
| Id | User        | Host            | db   | Command | Time | State                                  | Info              |
+----+-------------+-----------------+------+---------+------+----------------------------------------+-------------------+
| 1 | root        | 127.0.0.1:53309 | NULL | Query   | 753 | init                                   | show slave status |
| 2 | system user |                 | NULL | Connect | 856 | Queueing master event to the relay log | NULL |
| 3 | system user |                 | NULL | Connect | 786 | Waiting for commit lock                | NULL |
| 4 | root        | 127.0.0.1:53312 | NULL | Killed  | 188 | init                                   | show slave status |
| 5 | root        | 127.0.0.1:53314 | NULL | Query   | 0 | init                                   | show processlist  |
| 8 | root        | 127.0.0.1:53318 | NULL | Killed  | 125 | init                                   | show slave status |
| 11 | root        | 127.0.0.1:53321 | NULL | Killed  | 123 | init                                   | show slave status |
| 14 | root        | 127.0.0.1:53324 | NULL | Query   | 120 | init                                   | show slave status |
+----+-------------+-----------------+------+---------+------+----------------------------------------+-------------------+

pstack 看下相关线程的 backtrace,show slave status 线程的 backtrace 如下,非常明显是在等mutex,对应代码为 mysql_mutex_lock(&mi->rli->data_lock):

#0 __lll_lock_wait #1 _L_lock_974 #2 __GI___pthread_mutex_lock #3 inline_mysql_mutex_lock #4 show_slave_status #5 mysql_execute_command #6 mysql_parse #7 dispatch_command #8 do_command #9 do_handle_one_connection #10 handle_one_connection ...

SQL 线程的 backtrace 如下,在等 COMMIT 锁:

#0 pthread_cond_timedwait #1 inline_mysql_cond_timedwait #2 MDL_wait::timed_wait #3 MDL_context::acquire_lock #4 ha_commit_trans #5 trans_commit #6 Xid_log_event::do_commit #7 Xid_log_event::do_apply_event #8 Log_event::apply_event #9 apply_event_and_update_pos #10 exec_relay_log_event #11 handle_slave_sql ...

如果我们gdb进去,去调试SQL线程,在 MDL_context::acquire_lock中:

(gdb) p (MDL_key::enum_mdl_namespace)lock->key->m_ptr[0] $24 = MDL_key::COMMIT (gdb) p ((THD*)lock->m_granted.m_list.m_first->m_ctx->m_owner)->thread_id $25 = 1

可以看到 COMMIT 锁被线程 1 持有。

SQL线程在 Xid_log_event::do_commit 之前会持有 rli_ptr->data_lock。

所以现在就清楚了,是线程1(备份线程)和线程3(SQL线程)死锁了,还原下死锁过程:

  1. 备份线程执行 FTWRL,拿到 COMMIT 锁;
  2. SQL线程执行到Xid event,准备提交事务,请求 COMMIT 锁,被备份线程阻塞;
  3. 备份线程为了获取 slave 执行位点,执行 show slave status,需要获取 rli->data_lock,被 SQL 线程阻塞。

就这样2个线程互相持有等待,形成死锁。

我们知道,MDL 是有死锁检测的,为什么这里没有检测到呢?因为rli->data_lock是一个mutex,不属于MDL系统的,在这个死锁场景中,MDL锁系统只能检测到对 COMMIT 锁的请求,是不存在死锁的。

之后的 show slave status 都被堵,是因为在执行 show slave status 前,会请求一个mutex:

mysql_mutex_lock(&LOCK_active_mi); res= show_slave_status(thd, active_mi);
mysql_mutex_unlock(&LOCK_active_mi);

之前死锁的 show slave status 没有退出,后面的 show slave status 自然堵在这个 mutex 上,并且因为无法检测 thd->killed,所以一直无法退出。

死锁的原因是SQL线程在提交的时候,持有 rli->data_lock 锁,其实这个是不需要的,MySQL 官方在这个 patch 中修复。

CASE 2

在上面的bug修复后,又出现了死锁,但死锁的情况却不一样,show processlist 结果如下:

mysql> show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------+-------------------+
| Id | User        | Host            | db   | Command | Time | State                            | Info              |
+----+-------------+-----------------+------+---------+------+----------------------------------+-------------------+
| 2 | system user |                 | NULL | Connect | 436 | Waiting for master to send event | NULL |
| 3 | system user |                 | NULL | Connect | 157 | Waiting for commit lock          | NULL |
| 6 | root        | 127.0.0.1:42787 | NULL | Query   | 86 | init                             | show slave status |
| 7 | root        | 127.0.0.1:42788 | NULL | Query   | 96 | Killing slave                    | stop slave        |
| 8 | root        | 127.0.0.1:42789 | NULL | Query   | 0 | init                             | show processlist  |
+----+-------------+-----------------+------+---------+------+----------------------------------+-------------------+

依然是 SQL 线程在等待commit锁,然后 show slave status 被堵住没有返回,不同的是多了一个 stop slave; 我们来看下 stop slave 的backtrace:

#0 pthread_cond_timedwait #1 inline_mysql_cond_timedwait #2 terminate_slave_thread #3 terminate_slave_threads #4 stop_slave #5 mysql_execute_command #6 mysql_parse #7 dispatch_command #8 do_command #9 do_handle_one_connection #10 handle_one_connection ...

对应代码,可以发现 stop slave 正在等待 SQL 线程退出,而SQL线程此时正在等待备份线程(id=6)持有的 COMMIT 锁。整个死锁过程是这样的:

  1. 备份线程执行 FTWRL,拿到 COMMIT 锁;
  2. SQL线程执行到Xid event,准备提交事务,请求 COMMIT 锁,被备份线程阻塞;
  3. 用户执行 stop slave,准备停掉备库复制线程,等待 SQL 线程退出;
  4. 备份线程为了获取 slave 执行位点,执行 show slave status,需要获取 LOCK_active_mi 锁,被用户线程(stop slave)阻塞。

这次是备份线程、SQL 线程、用户线程3个线程互相持有等待,形成死锁。

这次并不是代码bug,算是一个用法问题,因此我们在运维过程中,如果发现 SQL 线程在 Waiting for commit lock,就不要 stop slave。

死锁解决

如果不可避免出现了死锁,该怎么解决呢?

通过上面的分析可以看到,不管是在 case 1 还是 case 2,备份线程和用户线程都不再接受响应了,要解决死锁的话,只能 kill 掉 SQL 线程了,那么直接 kill 是否有风险呢?

SQL 线程能执行 Xid event,说明是在更新事务引擎表,kill 掉应该没问题(事务可以回滚,之后可以重做),但是5.6有这样的一个bug,会导致SQL线程在等待 COMMIT 锁的时候被kill,直接跳过事务,这样备库会比主库少一个事务,因此 kill 后需要对比主备数据,把少的事务补上。

如果你使用的 MySQL 版本已经修掉这个bug,也就是在 5.6.21 版本及之后,那么 kill SQL 线程是安全的。

死锁重现

如果为了测试或研究代码,要想复现死锁该怎么办呢?如果直接在备库执行一个 FTWRL,很可能是复现不了的,因为FTWRL是获取2个锁,全局读锁和全局 COMMIT 锁,SQL 线程非常可能被全局读锁堵到(Waiting for global read lock),而不是被 COMMIT 锁堵(Waiting for commit lock)。

一种方法是写 testcase,用 dubug sync 功能设置同步点,让线程停在指定的地方,但这要求 mysqld 跑在deubg模式下,并且要求有一定的MySQL 源码开发基础;
另一种方法是改代码,延长do_commit的时间,比如 sleep 一段时间,这样就给我们足够的时间让 FTWRL 在 SQL 线程请求 COMMIT 锁前执行完成,但是这需改代码,然后重新编译安装;
如果我们不会用debug sync,又不想改代码重新编译安装,就想在已有的环境测,改怎么办呢?SYSTEMTAP!

systemtap 起初只支持在内核空间进行探测,0.6 版本之后可以在用户空间进行探测,使用 systemtap 需要程序中包含 debug 信息(程序编译时加上 -g 选项)。

列出所有我们可以对 mysqld 进行探测的地方。

sudo stap -L 'process("/usr/sbin/mysqld").function("*")'

列出所有可以对 Xid_log_event 类进行探测的地方。

sudo stap -L 'process("/usr/sbin/mysqld").function("*Xid_log_event::*")'

如果我们想让 Xid_log_event::do_commit 执行有点延迟,可以这样做:

sudo stap -v -g -d /usr/bin/mysqld --ldd -e 'probe process(16011).function("Xid_log_event::do_commit") { printf("got it\n")  mdelay(3000) }'

16011 是正在跑的备库进程PID,执行上面的 stap 命令后,每当备库执行到 Xid_log_event::do_commit 时,stap 就会打出个 “got it”,然后 SQL 线程暂停3s,这就给了我们充足的时间去执行 FTWRL,在SQL线程 commit 前拿到 COMMIT 锁。

转自:http://www.kancloud.cn/taobaomysql/monthly/117960




本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
链接: http://blog.itpub.net/28602568/viewspace-2123386/ 标题: Oracle not exist子查询全扫的优化   作者: lōττéry ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.] 前言:   之前写过  Oracle 针对子查询里有group by 表全扫的优化 ( 子查询和外层关系是left join);   本次介绍 子查询与外层是not exists的关系是如何优化子查询全扫的;    优化前: SQL SET
【故障处理】 ORA-12545: Connect failed because target host or object does not exist 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  错误 ORA-12545: Connect failed because target host or object does not exis
今天写了个脚本,虽然实现的功能不多,但是个人感觉是一个好的开始,架子出来了,后面要补充的细节加进来就逐步完善了。 这个脚本的运行效果如下: OS     Version  is :[ RHEL_6.3 ] Oracle Version  is :[ 11.2.0.3.0] Oracle Instance is :[ dgtest ] dgtest ORACLE_HOME     is :[ /U01/app/oracle/product/11.2.0.2/db_1  ] Oracle  status  is

DUAL系列 - 2016-08-12 17:08:18

DUAL 系列   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① DUAL遭到破坏后的重建(重点) ②  关于参数replication_dependency_tracking简介 ③ DUAL简介     Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.
今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式 数据库中存在一个属主用户,表,存储过程等对象都创建在这个用户上,而另外有一些连接用户,根据业务和功能可能访问的对象权限也有所不同。所以就会出现一个owner,多个connect user的情况。这种方式可以减少很多误操作,权限控制更为细粒度。 现在的问题是在owner用户上创建存储过程,存储过程会引用若干张表,都在owner用户下,而connect user下则没有这些表相关的任何同义词。看起来好像是不大合

Oracle如何删除表中重复记录 - 2016-08-11 14:08:09

Oracle如何删除表中重复记录 1    引言 在对数据库进行操作过程中我们可能会遇到这种情况,表中的数据可能重复出现,使我们对数据库的操作过程中带来 读诸 多不便,那么怎么删除这些重复没有用的数据呢 ? 平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时,系统提示 ORA-01452  :不能创建唯一索引,发现重复记录。 2    处理过程 重复的数据可能有这样两种情况 : 第一种 是 表中只有某些字段一样,第二种是两行记录完全一样 。删除重复记录后的结果也分为 2 种, 第一种 是重复的

外连接转换为内连接的情况 - 2016-08-10 04:08:06

一般的情况下外连接如下a right join b on a.id=b.id 那么b一定要作为驱动表,原因在于只有b作为驱动表才能得到完整的结果集,如果a作为驱动,那么返回的结果集 可能不完整,但是在特殊的情况的,可能将外连接转换为内连接 考虑如下的情况 b    id  name   1   g1   1   g2   2   g3   2   g4 a   id name   2  gname2 使用如下语句: select b.id,a.id from  a right join b on a.id=
今天总算抽了些时间把半自动化的脚本完成了大半,目前还缺少两部分的脚本,一部分是安装前的检查脚本,可以做一个预检查。虽然目前来看还不是必须,但是这些是标准和规范的地方,这些条件不满足,失败的概率会加大。另外一部分是安装后的补充脚本,其实安装后还有很多需要注意的地方。 大体想了下,补充的脚本包含下面的部分。 配置crontab,目前的常用job是定期删除归档,定期检查监听的情况 配置iptables ,把主库的防火墙信息拷贝过来,或者作为静态备份,需要是启用 配置大页,这个可以在优化的基础上进行计算,在内核参数

MongoBD 日常操作小节 - 2016-08-09 17:08:09

一、开启Mongodb 密码验证功能 默认安装完mongodb是不用密码验证的,直接输入mongo就可以登入数据库进行相关操作,设置参数auth=true启动mongodb密码验证功能,开启改功能步骤如下: ①、 修改参数文件auth=false,并重启mongodb ②、登入数据库,创建管理员用户(默认是没有管理员账户的) [ root@mon godb ~]  # mongo user admin   db.createUser(     {       user: "admin",       pw

在Buffer Cache中自动大表缓存 - 2016-08-09 17:08:09

约翰,在A银行做DBA。凯特琳,新的首席技术官,她正在为数据库性能问题由于长的I/O响应时间而沮丧。为了提高性能,她希望增加数据库实例的缓冲区缓存,以便在缓存中缓存更多的数据,从而减少了频繁需要去读磁盘。戴比,系统开发工程师,经历了很多这样的问题在她的职业生涯中,她解释说,“我们的应用有很多全表扫描,数据库的全表扫描使用的是直接路径读取(DPR)在表中的数据块,不做缓冲区高速缓存。”因此,添加物理内存到服务器后,增加缓冲区缓存,是不会帮助,因为缓冲区没有用。应用程序可以通过加hits跳过DPRs,使用缓冲