MariaDB Galera Cluster数据库“彻底死锁”的处理过程

作者:Sender Su  来源:原创内容  发布日期:2021-10-28  最后修改日期:2022-02-19

生产环境是三台服务器,使用MariaDB Galera Cluster,形成三节点集群,实现高可用。只是世事无绝对,对于高度繁忙的数据库来说,依然在极偶然的机会下产生彻底的死锁,也就是除了通过运行KILL -9 参数命令杀掉数据库进程之外无法解决的死锁。就我经验而言,平均1.5年1次吧。

article banner

笔者:国际认证信息系统审计师、软考系统分析师

数据库死锁表征:

所有对数据库的写操作访问都被挂起等待,进而影响到对被写的表的读操作也被挂起等待,最后所有用户的所有连接都是处于等待状态。

接到用户报告后,立即开始处理过程:

1、检查:
  • information_schema.INNODB_LOCKS
  • information_schema.INNODB_LOCK_WAITS

这两个表,可以看到当前的互斥锁的记录,一般来说列出的不止1条,但从经验(所以企业内部具备有经验的DBA人员是多么重要!)可以判断到应该是哪一条是死锁,比如我的情况,找到的是这么一句:

  • INSERT INTO ... SELECT FROM ...

其中,INSERT操作对象是使用MyISAM引擎的临时表。倒是不清楚如果改用Aria引擎会否能避免,毕竟MyISAM确实太旧了。

2、检查:
  • information_schema.INNODB_TRX

通过这个表的内容,以及在第1步获得的锁记录id等参数,在这个表内可以找到对应的CONNECTION ID。

3、尝试按 CONNECTION ID 杀掉访问数据库的连接。

操作方法是使用 KILL CONNECTION 命令。这里存在两种可能:

(1)如果能杀,随后所有被阻塞的操作都得到执行,那么数据库就恢复正常。

(2)如果不能杀,则存在(a)卡死在InnoDB引擎内和(b)卡死在WSREP引擎两种情况。

不同的卡死情况直接关系到后续处理过程的风险程度。

对于(a)卡死在InnoDB引擎内的情况,杀掉几个其它有一定相关性的数据库连接,典型如锁死语句操作的表的读操作连接,一般都能恢复正常。

对于(b)卡死在WSREP引擎的情况,会在尝试杀连接时,数据库返回:

  • ERROR 1095 (HY000): You are not owner of thread 4226581

这样的提示。此时,无论是用 KILL CONNECTON 或者 KILL QUERY ID命令都杀不了。唯一能做的只有杀掉数据库服务。原因是 WSREP PROVIDER 程序为确保实现节点间数据同步,会短暂接管执行写操作的连接,并为连接设置一个比较高级的特权,这个特权是高于数据库的root用户,所以root用户杀不了处在这个状态的连接。

4、应对卡死在WSREP引擎的操作:

(1)首先记录好相关连接所在操作的表名,这些表很容易会被连带破坏掉,要重点检查。

(2)杀掉所有能杀的数据库连接,关闭所有访问数据库的后台服务和前台系统。如果有写入中的连接不能杀,简单忽略之。

(3)通过 systemctl 命令,非卡死连接所在的节点数据库,尝试关闭服务。如果关不了,KILL -9 杀掉数据库进程。

(4)继续通过 systemctl 命令,对卡死连接所在的节点的数据库(以下简称为挂起节点),尝试关闭服务。如果关不了,也是 KILL -9 杀掉数据库进程。注意,此操作会必然导致 Galera Cluster 重启后的SST(State Snapshot Transfer)过程是无法避免的。

通过实践认为,在本文描述的情况下,对数据库进程执行 KILL -9 操作后,对于使用InnoDB引擎存放的数据来说,只要不影响到REDO LOG的完整性就没见到有数据丢失的问题。但对于不是InnoDB引擎的数据,比如MyISAM的,尤其是被写入引起死锁的表,就有可能会被破坏而需要重建。所以结论是只要数据有用,无论数据量多少,都应该使用InnoDB引擎。

对于(2)和(3)两步,如果设置了集群数据库是多写多读模式,则要认真考虑是否放弃挂起节点的数据,在数据库重启服务并进行SST时,选择没有出现挂起情况的节点作为数据的Donor。

但如果挂起节点是全集群的唯一写节点,则要谨慎检查和考虑重启服务进行SST时,究竟是否使用挂起节点作为Donor了。

以下内容对应的是挂起节点为唯一写节点的情况。

(5)重启服务器后,修改配置文件:

  • /etc/my.cnf.d/server.cnf

把配置文件中的 wsrep_on=on 改为off,然后执行

  • systemctl start mariadb

命令以单机模式启动节点的数据库。

注:经验认为重启服务器是必须的,考虑到内存释放重整以及网络资源释放等因素,重启服务器最简单直接和可靠。 

(6)单机模式启动后,持续 tail 命令观察数据库的错误日志。,然后按(1)中记录的和死锁连接相关的表名,执行

  • CHECK TABLE 表名 EXTENDED;

命令进行检查。在此期间,注意观察错误日志提示。

对于检查出现错误的表,如果SELECT数据失败,可以尝试REPAIR或者直接DROP掉重新CREATE。但如果能SELECT,就不要DROP,REPAIR就好。因为极端情况下DROP了之后会CREATE不了,这种情况出现在InnoDB的Catalog有问题,或者这个表的frm(表结构定义)也被破坏掉的时候。

(7)随后对其它数据库的表进行全面的CHECK操作检查是否存在问题。但一般都没有问题,且此过程较花时间。

(8)确认数据无问题后,systemctl 关闭单机,还原配置文件的wsrep_on参数为on,然后按Galera Cluster模式用 galera_new_cluster 命令重新启动集群,等待SST过程重新同步节点,然后恢复集群服务。

后记:

1、据说可以通过设置 max_statement_time 来让数据库主动退出超长时间运行的内部进程,但对于卡死在 WSREP 引擎这种情况尚未知是否有效,要等下一次的问题出现,而且这种设置有可能和实际使用情况,比如用户就是执行了一个较长时间的查询,所冲突。参考:

https://jira.mariadb.org/browse/MDEV-12008

https://mariadb.com/kb/en/aborting-statements

2、KILL QUERY ID 操作所要给出的QUERY ID参数,要通过

  • SHOW ENGINE INNODB STATUS

才能看到。因为 QUERY ID 不是 PROCESS ID。

3、被堵塞的连接,其状态会停留在:Commit、committing、acquiring total order isolation,个别停留在Updating。

本栏目相关
  •  2015-08-26 解决MariaDB使用Percona XtraBackup增量备份出错
  •  2021-10-28 MariaDB Galera Cluster数据库“彻底死锁”的处理过程
  •  2023-08-27 MySQL/MariaDB全量增量备份及合并脚本
  • 本站微信订阅号:

    微信订阅号二维码

    本页网址二维码:

    本栏目热门内容
  • Acrobat虚拟PDF打印机执行打印时挂起,解决办法竟然...
  • LINKSYS交换机登录WEB界面显示不正确的解决方法
  • 又一次RAID 5阵列故障记录
  • 解决VMware vSphere ESXi 5.0 Update 1 中虚机不能...
  • 修改CentOS发行信息以绕过Dell服务器BIOS更新和DSET...
  • 解决虚拟化运行的 Windows Server 2003 标准版出现...
  • Windows Server 2008 重命名域和域控制器
  • Intel Nehalem CPU Errata 导致 VMWare ESXi(vSpher...
  • 一次很精神的电脑组装过程记录(但不是自己的电脑)...
  • 解决MySQL Cluster 备份总是失败,提示文件已存在的...
  • MegaCli安装及使用杂记
  • 解决WSUS显示客户端不全的问题
  • 解决 VMWare vSphere 6 客户端无法修改用户密码
  • 解决Windows Server 2008 R2域控制器显示无法连接到...
  • 本站服务器RAID 5阵列双硬盘失效挽救记录
  • 网站数据库从MySQL 5.0升级到5.6的记录
  • 解决MariaDB使用Percona XtraBackup增量备份出错
  • Linux 下的分区调整工具GParted实战
  • DELL PowerEdge 820 报CPU3 INTERNAL ERROR 的解决...
  • 修改arpwatch使通知邮件主题显示IP地址
  • 程序员漫画:如何用8种不同的编程语言去解救公主
  • 解决Samba WINS服务的错误解释问题
  • 解决很好用的多合一即时通信软件pidgin的崩溃问题
  • 使用 GParted 进行虚拟机硬盘分区调整操作
  • 解决Squid代理HTTP时在浏览器出现Content Encoding ...
  • 用Delphi编写使用到ADO的DLL的一些问题
  • 网站简单改版
  • 索尼系列手提电脑备份失败,出现700错误的解决办法
  • Dell R900服务器 BMC firmware incompatible with C...
  • 更多...