DevOps开发运维
成长之路

MySQL锁的监控及处理

锁等待模拟

概念:读的一致性,防止幻读
Record Lock 行级锁
Next Lock 下级锁
GAP Lock间隙锁
X 排查锁
IX意向排查锁
S 共享锁,与读取相关
IS

锁等待演示

## tx1:
db03 [oldboy]>select @@autocommit;保证自动提交关闭
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
USE oldboy
UPDATE t_100w SET k1='av' WHERE id=10;
## tx2:
USE oldboy 
UPDATE t_100w SET k1='az' WHERE id=10;

监控锁状态

## 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 : 当前被阻塞的操作(一般是要丢给开发的)

查看锁源,谁锁的我!

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 : 锁源的连接线程号(processlist_id)

找到锁源的thread_id

SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41

找到锁源的SQL语句

-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;
得出结果,丢给开发
表信息 
被阻塞的
锁源SQL
练习:
一键获得以上信息,请写出具体的SQL语句

优化项目:锁的监控及处理

1. 背景

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

2. 项目的职责

1 通过top详细排查,发现mysqld进程占比达到了700-800%
2 其中有量的CPU是被用作的SYS和WAIT,us处于正常
3 怀疑是MySQL 锁 或者SQL语句出了问题
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,说明历史上发生过的锁等待很多
5 查看那个事务在等待(被阻塞了)
6 查看锁源事务信息(谁锁的我)
7 找到锁源的thread_id 
8 找到锁源的SQL语句

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

(1)开发人员描述,此语句是事务挂起导致
我们提出建议是临时kill 会话,最终解决问题
(2) 开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待
临时解决方案,将阻塞事务的会话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
赞(0)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图