迁移zabbix引发的一次MySQL锁阻塞

最新推荐文章于 2025-05-26 15:10:45 发布

dumb_man

最新推荐文章于 2025-05-26 15:10:45 发布

阅读量986

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/dahailantian1/article/details/78584773

mysql 专栏收录该内容

24 篇文章

订阅专栏

本文详细记录了在整体迁移Zabbix过程中遇到的历史数据过大问题及其解决办法，包括删除旧历史数据、使用truncate命令以及处理MySQL锁阻塞现象。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

整体迁移zabbix，备份数据库时发现history开头的表都很大（8GB+）。zabbix的数据存储机制让zabbix每小时对history*表进行统计，统计值（最大值、最小值、平均值）放到trends*表中。注：zabbix配置监控项时有历史数据保存时间、趋势数据保存时间两项，分别对应history*、trends*。
备份zabbix数据库，由于history*表很大，为了减轻备份压力和缩短迁移时间决定抛弃一部分历史数据（7天前的数据全部删掉）.
1、根据表中clock字段，删除几张大表7天前的数据（类似这样的语句）。

  
   mysql> delete from history_uint where clock<unix_timestamp(adddate(now(),-7));
mysql> delete from history where clock<unix_timestamp(adddate(now(),-7));

2、然后就是无尽的等待这个事务的完成，数据太多发现没有半天是删不完的，决定kill这个线程然后truncate这些表，放弃所有的历史数据（必尽还有每小时统计一次的趋势数据，不用担心）

mysql> show processlist;
mysql> kill 136765
mysql> truncate table history_uint

3、发现truncate表时没有之前快了，不是感觉变慢了而是根本就没有执行的感觉。查看一下线程状态，是这样的。

***************************26. row***************************
Id: 384238681
User: root
db: zabbix
Host: localhost
Command: Query
Time: 385
State: Waiting for table metadate lock
Info: truncate table history_uint
***************************27. row***************************

truncate在等待一个锁，除了这个线程，还有好几个insert 、select等待锁：

***************************29. row***************************
Id: 384163828
User: zabbix
db: zabbix
Host: localhost
Command: Query
Time: 379
State: Waiting for table metadate lock
Info: select min(clock) from history_unit where itemid=28041
***************************30. row***************************

***************************30. row***************************
Id: 384244861
User: zabbix
db: zabbix
Host: localhost
Command: Query
Time: 53
State: Waiting for table metadate lock
Info: insert into history_unit (itemid,clock,value,ns) values (27326,1431400946,724714852352,435254068) ... ... ... ...
***************************31. row***************************

4、发现这个现象，有种不好了感觉，为了蔡某大神。大神就是大神，果然专业，一看就知道问题所在了。
大神的意思是这样的：
truncate没有拿到mdl锁，MySQL在回滚delete回滚结束前持有mdl锁，truncate被锁后续insert被truncate锁（表锁），杀掉truncate就可以正常 insert、select，完成delete回滚，回滚完成后就可以truncate了。这是一种锁阻塞现象。
按照大神的意思操作，杀掉truncate线程，等待MySQL的delete回滚结束，然后truncate表，然后、然后就顺利完成了zabbix数据库迁移。
文章出处：http://www.xiaomastack.com/2015/05/14/mysql-lock/