MySQL一次性删除千万级数据后卡死解决办法
- 进入mysql后,查看卡死进程,命令 show full processlist;
- 杀掉进程 kill id号
- 退出mysql后,输入 /etc/init.d/mysql stop 和 killall mysqld
- 等待几分钟后,重新启动 mysql
MySQL一次性删除千万级数据后无法执行语句解决办法
错误提示 “ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction”
- 进入 mysql 后执行 SHOW ENGINE INNODB STATUS;
- 通过上述命令,查找被锁住的事务,关键语句类似如下
TRANSACTIONS
Trx id counter 51551600
Purge done for trx's n:o < 51075432 undo n:o < 7379206 state: running
History list length 236498
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422170592817336, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 51515032, ACTIVE 3679 sec recovered trx
ROLLING BACK 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1035216
3. 可以等待执行失败回滚完成,或者在命令行下执行 mysqld -- set innodb_rollback_on_timeout=1 后(set innodb_lock_wait_timeout=100;和 show variables like 'innodb_lock_wait_timeout';可以先设置超时时间,单位秒),查看是否生效命令(进mysql执行) SHOW GLOBAL VARIABLES LIKE 'innodb_rollback_on_timeout'; 和 SHOW GLOBAL STATUS LIKE 'com_rollback';若未生效则重启mysql
4. uptime或者top查看负载,/etc/init.d/mysql status 查看mysql内存占用,通过执行命令 echo 3 > /proc/sys/vm/drop_caches 释放mysql内存