7.?锁的监控及处理
7.1?锁等待模拟
##?tx1:
USE?test
UPDATE?t100w?SET?k1='av'?WHERE?id=10;
##?tx2:
USE?test
UPDATE??t100w?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?:?当前被阻塞的操作(一般是要丢给开发的)
7.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????:?锁源的线程号
7.4.?找到锁源的thread_id
SELECT?*?FROM?performance_schema.threads?WHERE?processlist_id=6;
====>?28
7.5.?找到锁源的SQL语句
--?当前在执行的语句
SELECT?*?FROM?performance_schema.`events_statements_current`?WHERE?thread_id=28;
--?执行语句的历史
SELECT?*?FROM?performance_schema.`events_statements_history`?WHERE?thread_id=31;
得出结果,丢给开发
表信息
被阻塞的
锁源SQL
处理方案:
1.?经过以上步骤,
a.?被锁的语句,表信息,锁的类型,PID
b.?锁源的相关信息,PID,SQL_THREAD_ID,SQL_TEXT
c.?和开发进行沟通:1.?可不可以kill。2.?业务逻辑有没有问题。
d.?DBA:?1.索引合不合理(explain)?。?2.?辅助判断语句的逻辑问题。RC级别。
练习:
一键获得以上信息,请写出具体的SQL语句
7.6?优化项目:锁的监控及处理
1.?背景:
硬件环境:?DELL?R720,E系列16核,48G?MEM,SAS*900G*6,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,说明历史上发生过的锁等待很多
2.5?查看那个事务在等待(被阻塞了)
2.6?查看锁源事务信息(谁锁的我)
2.7?找到锁源的thread_id
2.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;