MySQL 性能优化-数据库死锁监控

MySQL性能优化-数据库死锁监控

by:授客 QQ：1033553122

1）表锁定

通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析表锁定。

SHOW  STATUS  LIKE  'table%';

说明：

Table_locks_immediate：能够立即获得表级锁的锁请求次数

Table_locks_waited：不能立即获取表级锁而需要等待的锁请求次数

分析：

如果table_locks_waited值较高，且存在性能问题，则说明存在着较严重的表级锁争用情况。这时，需要对应用做进一步的检查，来确定问题所在，应首先优化查询，然后拆分表或复制表。

2）行级锁

通过检查 Innodb_row_lock状态变量来分析行锁的争用情况

SHOW STATUS LIKE 'Innodb_row_lock%';

说明：

Innodb_row_lock_current_waits：当前锁等待的数量

Innodb_row_lock_time：自系统启动到现在，锁定的总时间，单位：毫秒 ms。

Innodb_row_lock_time_avg：平均锁定的时间，单位：毫秒 ms。

Innodb_row_lock_time_max：最大锁定时间，单位：毫秒 ms。

Innodb_row_lock_waits：自系统启动到现在，锁等待次数，即锁定的总次数。

分析：

针对如果InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，说明可能存在锁争用的情况，针对 Innodb 类型的表，可以通过设置InnoDB Monitors来进一步观察发生锁争用的表、数据行等，并分析锁争用的原因，如下：

注：可通过语句SHOW CREATE TABLE table_name;查看表table_name使用的引擎(查询输出结果中找到ENGINE=xxxx，这里xxxx即为使用的引擎);

1、先设置InnoDB Monitor

CREATE  TABLE  innodb_monitor(a  INT)  ENGINE=INNODB;

2.查看

SHOW ENGINE INNODB STATUS;

说明：

1.输出结果包含了详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等等。打开监视器以后，默认情况下每 15 秒会向日志中记录监控的内容，如果长时间打开会导致.err 文件变得非常的巨大，所以我们在确认问题原因之后，要记得删除监控表(DROP  TABLE innodb_monitor;)以关闭监视器。

2.输出结果为基于一段时间的数据采样，得出的每秒平均值，这里的时间取自系统启动到当前时间的时间间隔或者上次输出到当前时间的时间间隔

3.找到TRANSACTIONS部分的内容，可以查看事务死锁争用的相关情况