锁的监控及处理

2021-02-16 | 129 浏览

锁的监控及处理

1.锁等待定位过程

1.1锁等待模拟

tx1:
USE brm
UPDATE t1 SET k1='av' WHERE id=10;
tx2:
USE brm 
UPDATE t1 SET k1='az' WHERE id=10;
``
**1.2监控锁状态**

###### 1.看有没有锁等待
SHOW STATUS LIKE 'innodb_row_lock%';
###### 2.查看那个事务在等待(被阻塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';

trx_id : 事务ID号
trx_state : 当前事务的状态
trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 当前被阻塞的操作(一般是要丢给开发的)
###### 3.查看锁源,谁锁的我!
SELECT * FROM sys.innodb_lock_waits; ====>被锁的和锁定它的之间关系

locked_table : 哪张表出现的等待
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID
blocking_pid : 锁源的线程号
###### 4.找到锁源的thread_id 
SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41
###### 5.找到锁源的SQL语句
-- 当前在执行的语句
SELECT * FROM performance_schema.events_statements_current WHERE thread_id=41;
-- 执行语句的历史
SELECT * FROM performance_schema.events_statements_history WHERE thread_id=41;
得出结果,丢给开发
表信息 
被阻塞的
锁源SQL

Shell脚本：
一键获得以上信息,请写出具体的SQL语句

2.锁的监控及处理

1.背景:

硬件环境: DELL R720,E系列16核,48G MEM,SAS900G6,RAID10
在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)

2.项目的职责
2.1 通过top详细排查,发现mysqld进程占比达到了700-800%
2.2 其中有量的CPU是被用作的SYS和WAIT,us处于正常
2.3 怀疑是MySQL 锁 或者SQL语句出了问题
2.4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句
(1) pt-query-diagest 查看慢日志

(2) 锁等待有没有?
db03 [(none)]>show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
情况一:
有100多个current_waits,说明当前很多锁等待情况
情况二:
1000多个lock_waits,说明历史上发生过的锁等待很多

查看那个事务在等待(被阻塞了)
查看锁源事务信息(谁锁的我)
找到锁源的thread_id
找到锁源的SQL语句

3.找到语句之后,和应用开发人员进行协商

临时解决方案,将阻塞事务的会话kill掉.
最终解决方案,修改代码中的业务逻辑

项目结果:
经过排查处理,锁等待的个数减少80%.解决了CPU持续峰值的问题.

锁监控设计到的命令:
show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;
死锁监控
show engine innodb status\G
show variables like '%deadlock%';

vim /etc/my.cnf 
innodb_print_all_deadlocks = 1 

数据库