线上MySQL8锁表的问题
源头
最近线上出现了锁表,一个查询大表的sql语句居然执行了好几个小时,然后其他的sql语句去操作大表,然后运维人员提示出现了锁表问题。
什么情况下会出现锁表
- 锁表一般发生在insert、delete、update语句中。
- insert、update、delete等语句操作表,使用独占锁,锁住表。但是如果表数据量比较大,那么上述的操作可能会很慢,这样其他的写操作语句会阻塞。
排查锁表的情况
查看当前的连接
mysql> show processlist;
查看当前锁表的状态
mysql> SHOW OPEN TABLES;
是否存在未正常释放的锁
mysql> SHOW ENGINE INNODB STATUS;
查看当前运行的事务
SELECT * FROM information_schema.innodb_trx;
查看当前出现的锁
select * from performance_schema.data_locks;
查看当前等待的锁
select * from performance_schema.data_lock_waits;
杀死锁表的线程
kill 线程id
思考,出现锁表的根源?
锁表的出现主要在于当前的sql语句操作表,处理太慢,那么针对慢sql,我们要开启慢sql日志。
- 是否开启了慢查询日志
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/izuf6ea4rfo45y44oj74jhz-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)
- 开启慢查询日志
set global slow_query_log=1;
- MySQL配置文件my.cnf配置慢查询
slow_query_log =1
slow_query_log_file=/var/log/sunpy-slow.log
long_query_time=1
- slow_query_log 慢查询开启状态
- slow_query_log_file 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录)
- long_query_time 查询超过多少秒才记录
参考
为什么MySQL中的kill命令杀死线程,却未马上杀死?
https://www.jb51.net/article/284531mdb.htm
information_schema.innodb_trx表字段的含义?
https://blog.csdn.net/sugarCYF/article/details/108432902