发布于 由admin

MySQL高级讲座篇之：如何利用`Performance Schema`的`Events`表，进行死锁和锁等待的根源分析？

大家好，我是你们的老朋友，今天咱们聊聊MySQL的“侦探工具”——Performance Schema，尤其是它里面的Events表，看看怎么用这些家伙揪出死锁和锁等待的幕后黑手。

开场白：谁动了我的数据？

想象一下，你精心设计的电商系统，平时跑得飞快，突然有一天，用户开始抱怨“卡卡的”，提交订单半天没反应，后台监控报警一片红。 作为一个优秀的DBA/开发者，你得立刻行动起来，找到问题的根源。 锁等待和死锁，就是这种问题的常见“嫌疑犯”。

Performance Schema：MySQL的“黑匣子”

别慌！MySQL其实早就准备好了“黑匣子”——Performance Schema。 它可以记录数据库服务器运行时的各种事件，就像飞机上的黑匣子记录飞行数据一样。 通过分析这些数据，我们就能还原事故现场，找到问题的症结。

Events表族：事件的“档案馆”

Performance Schema里有一堆以Events开头的表，它们记录了各种各样的事件，比如查询执行、锁等待、事务提交等等。 咱们今天主要关注的是跟锁相关的Events表：

  • events_statements_current：当前正在执行的语句的事件信息。
  • events_statements_history：最近执行过的语句的事件信息（存储在内存中，数量有限）。
  • events_statements_history_long：最近执行过的语句的事件信息（存储在磁盘上，数量更多）。
  • events_waits_current：当前正在等待的事件信息。
  • events_waits_history：最近发生的等待事件信息（存储在内存中）。
  • events_waits_history_long：最近发生的等待事件信息（存储在磁盘上）。
  • events_transactions_current：当前事务的事件信息。
  • events_transactions_history：最近事务的事件信息。
  • events_transactions_history_long：最近事务的事件信息。

这些表就像一个个“档案馆”，记录了事件的各种细节，比如开始时间、结束时间、持续时间、关联的线程ID等等。

配置Performance Schema：打开“侦探模式”

要使用Performance Schema，首先要确保它已经启用。 MySQL 5.7.20 及以上版本默认启用，如果没启用，可以通过修改 my.cnf 文件来启用：

[mysqld]
performance_schema=ON

然后重启MySQL服务。

其次，我们需要配置 Performance Schema 的 instrumentation。 默认情况下，很多 instrumentation 是关闭的，我们需要打开跟锁相关的 instrumentation，才能收集到足够的信息。

UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE '%lock%';
UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE '%wait%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%statement%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%stage%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%wait%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%transaction%';

FLUSH INSTRUMENT STATISTICS; -- 清空缓存

案例一：揪出死锁的“元凶”

死锁就像两辆车在狭窄的道路上迎头相撞，谁也动不了，只能互相等待。 在数据库里，死锁是指两个或多个事务互相等待对方释放资源，导致所有事务都无法继续执行。

MySQL会自动检测死锁，并选择一个事务回滚，以打破死锁。 但是，我们不能坐等MySQL解决问题，而应该找出死锁的原因，避免再次发生。

  1. 复现死锁

为了演示，我们先模拟一个简单的死锁场景：

-- 会话 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT SLEEP(5); -- 模拟执行时间
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 会话 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
SELECT SLEEP(5); -- 模拟执行时间
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

如果两个会话同时执行这段代码，就很有可能发生死锁。 因为会话1先锁定了 accounts 表的 id=1 的行，然后尝试锁定 id=2 的行； 而会话2先锁定了 id=2 的行，然后尝试锁定 id=1 的行。 这样就形成了互相等待的局面，导致死锁。

  1. 查看错误日志

当发生死锁时，MySQL会将死锁信息写入错误日志。 错误日志的位置可以通过 SHOW VARIABLES LIKE 'log_error'; 命令查看。

错误日志中会包含详细的死锁信息，包括涉及的事务、锁定的资源、执行的SQL语句等等。

  1. 利用 Performance Schema 分析死锁

虽然错误日志提供了死锁信息，但是不够直观，也不方便分析。 我们可以利用 Performance Schema 的 Events 表来更详细地分析死锁。

首先，我们可以查询 events_waits_history_long 表，找到发生死锁的等待事件：

SELECT
    event_name,
    source,
    timer_wait,
    object_schema,
    object_name,
    index_name,
    operation,
    number_of_bytes,
    mysql_version,
    sql_text
FROM
    performance_schema.events_waits_history_long
WHERE event_name LIKE 'wait/lock/table/sql/handler'
ORDER BY timer_start DESC
LIMIT 10;

这个查询会返回最近发生的锁等待事件，包括事件名称、源文件、等待时间、锁定的对象、SQL语句等等。 通过分析这些信息，我们可以找到导致死锁的SQL语句。

接着，我们可以查询 events_statements_history_long 表，找到执行这些SQL语句的事务：

SELECT
    THREAD_ID,
    EVENT_ID,
    SQL_TEXT,
    DIGEST,
    LOCK_TIME,
    ROWS_AFFECTED,
    ROWS_SENT,
    ROWS_EXAMINED,
    CREATED_TMP_DISK_TABLES,
    CREATED_TMP_TABLES,
    SELECT_FULL_JOIN,
    SELECT_RANGE,
    SELECT_SCAN,
    SORT_MERGE_PASSES,
    SORT_RANGE,
    SORT_ROWS,
    SORT_SCAN,
    SUM_NO_GOOD_INDEX_USED,
    SUM_NO_INDEX_USED,
    FIRST_SEEN,
    LAST_SEEN
FROM
    performance_schema.events_statements_history_long
WHERE DIGEST IN (SELECT DISTINCT DIGEST FROM performance_schema.events_waits_history_long WHERE event_name LIKE 'wait/lock/table/sql/handler')
ORDER BY LAST_SEEN DESC
LIMIT 10;

这个查询会返回执行了导致死锁的SQL语句的事务的信息，包括线程ID、事件ID、SQL语句、锁定时间、影响的行数等等。 通过分析这些信息，我们可以找到导致死锁的事务。

最后，我们可以结合 events_transactions_history_long 表，查看事务的详细信息：

SELECT
    THREAD_ID,
    EVENT_ID,
    STATE,
    TRX_ID,
    GTID,
    XA_ID,
    START_TIME,
    END_TIME,
    NNER_TRANSITIONS,
    ROWS_AFFECTED,
    ROWS_SENT,
    ROWS_EXAMINED,
    CREATED_TMP_DISK_TABLES,
    CREATED_TMP_TABLES,
    SELECT_FULL_JOIN,
    SELECT_RANGE,
    SELECT_SCAN,
    SORT_MERGE_PASSES,
    SORT_RANGE,
    SORT_ROWS,
    SORT_SCAN,
    SUM_NO_GOOD_INDEX_USED,
    SUM_NO_INDEX_USED,
    FIRST_SEEN,
    LAST_SEEN
FROM
    performance_schema.events_transactions_history_long
WHERE THREAD_ID IN (SELECT DISTINCT THREAD_ID FROM performance_schema.events_statements_history_long WHERE DIGEST IN (SELECT DISTINCT DIGEST FROM performance_schema.events_waits_history_long WHERE event_name LIKE 'wait/lock/table/sql/handler'))
ORDER BY LAST_SEEN DESC
LIMIT 10;

这个查询会返回导致死锁的事务的详细信息，包括事务ID、状态、开始时间、结束时间等等。 通过分析这些信息，我们可以更全面地了解死锁的来龙去脉。

  1. 解决死锁

找到死锁的原因后，就可以采取相应的措施来解决死锁，比如：

  • 优化SQL语句：避免长事务，尽量减少锁定的资源。
  • 调整事务隔离级别：使用较低的隔离级别，减少锁冲突。
  • 调整锁定顺序：保证所有事务按照相同的顺序锁定资源。
  • 使用乐观锁：避免显式锁定资源。

案例二：定位锁等待的“瓶颈”

锁等待是指一个事务需要等待另一个事务释放锁才能继续执行。 锁等待是性能瓶颈的常见原因之一。

  1. 模拟锁等待

为了演示，我们模拟一个简单的锁等待场景：

-- 会话 1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT SLEEP(10); -- 模拟长时间操作
COMMIT;

-- 会话 2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 会被阻塞
COMMIT;

会话1先锁定了 accounts 表的 id=1 的行，并且长时间没有释放锁。 会话2尝试锁定同一行，会被阻塞，进入锁等待状态。

  1. 利用 Performance Schema 分析锁等待

我们可以利用 Performance Schema 的 Events 表来分析锁等待的原因。

首先，我们可以查询 events_waits_current 表，找到当前正在等待的事件：

SELECT
    event_name,
    source,
    timer_wait,
    object_schema,
    object_name,
    index_name,
    operation,
    number_of_bytes,
    mysql_version,
    sql_text
FROM
    performance_schema.events_waits_current
WHERE event_name LIKE 'wait/lock/table/sql/handler'
ORDER BY timer_start DESC
LIMIT 10;

这个查询会返回当前正在发生的锁等待事件，包括事件名称、源文件、等待时间、锁定的对象、SQL语句等等。 通过分析这些信息，我们可以找到导致锁等待的SQL语句。

然后，我们可以查询 events_statements_current 表，找到执行这些SQL语句的事务：

SELECT
    THREAD_ID,
    EVENT_ID,
    SQL_TEXT,
    DIGEST,
    LOCK_TIME,
    ROWS_AFFECTED,
    ROWS_SENT,
    ROWS_EXAMINED,
    CREATED_TMP_DISK_TABLES,
    CREATED_TMP_TABLES,
    SELECT_FULL_JOIN,
    SELECT_RANGE,
    SELECT_SCAN,
    SORT_MERGE_PASSES,
    SORT_RANGE,
    SORT_ROWS,
    SORT_SCAN,
    SUM_NO_GOOD_INDEX_USED,
    SUM_NO_INDEX_USED,
    FIRST_SEEN,
    LAST_SEEN
FROM
    performance_schema.events_statements_current
WHERE THREAD_ID IN (SELECT DISTINCT THREAD_ID FROM performance_schema.events_waits_current WHERE event_name LIKE 'wait/lock/table/sql/handler')
ORDER BY LAST_SEEN DESC
LIMIT 10;

这个查询会返回执行了导致锁等待的SQL语句的事务的信息，包括线程ID、事件ID、SQL语句、锁定时间、影响的行数等等。 通过分析这些信息，我们可以找到导致锁等待的事务。

最后，我们可以结合 events_transactions_current 表，查看事务的详细信息：

SELECT
    THREAD_ID,
    EVENT_ID,
    STATE,
    TRX_ID,
    GTID,
    XA_ID,
    START_TIME,
    END_TIME,
    NNER_TRANSITIONS,
    ROWS_AFFECTED,
    ROWS_SENT,
    ROWS_EXAMINED,
    CREATED_TMP_DISK_TABLES,
    CREATED_TMP_TABLES,
    SELECT_FULL_JOIN,
    SELECT_RANGE,
    SELECT_SCAN,
    SORT_MERGE_PASSES,
    SORT_RANGE,
    SORT_ROWS,
    SORT_SCAN,
    SUM_NO_GOOD_INDEX_USED,
    SUM_NO_INDEX_USED,
    FIRST_SEEN,
    LAST_SEEN
FROM
    performance_schema.events_transactions_current
WHERE THREAD_ID IN (SELECT DISTINCT THREAD_ID FROM performance_schema.events_statements_current WHERE THREAD_ID IN (SELECT DISTINCT THREAD_ID FROM performance_schema.events_waits_current WHERE event_name LIKE 'wait/lock/table/sql/handler'))
ORDER BY LAST_SEEN DESC
LIMIT 10;

这个查询会返回导致锁等待的事务的详细信息，包括事务ID、状态、开始时间、结束时间等等。 通过分析这些信息，我们可以更全面地了解锁等待的来龙去脉。

  1. 解决锁等待

找到锁等待的原因后，就可以采取相应的措施来解决锁等待，比如：

  • 优化SQL语句：避免长事务，尽量减少锁定的资源。
  • 优化索引：提高查询效率，减少锁定时间。
  • 缩短事务时间：尽量减少事务的执行时间，快速释放锁。
  • 拆分大事务：将大事务拆分成小事务，减少锁冲突。

一些小技巧

  • 善用 DIGEST：DIGEST 是 SQL 语句的哈希值，可以用来快速查找相同的 SQL 语句。
  • 关注 LOCK_TIME：LOCK_TIME 表示锁定时间，可以用来判断锁等待是否严重。
  • 结合其他工具：可以结合 SHOW PROCESSLIST、INNODB STATUS 等命令，更全面地了解数据库的状态。

总结

Performance Schema 是 MySQL 自带的强大的性能分析工具，通过分析 Events 表，我们可以轻松地找到死锁和锁等待的根源，并采取相应的措施来解决问题，提升数据库的性能。

当然，Performance Schema 的功能远不止这些，还有很多其他的表和功能可以用来分析数据库的性能问题。 熟练掌握 Performance Schema，你就能成为一名真正的 MySQL “侦探”，轻松揪出数据库的“幕后黑手”。

友情提示

  • Performance Schema 会消耗一定的系统资源，建议只在需要的时候启用。
  • Performance Schema 的数据存储在内存中，重启 MySQL 服务后会丢失。 可以通过配置将数据存储到磁盘上。
  • Performance Schema 的数据量可能会很大，建议定期清理。

希望今天的讲座对大家有所帮助，谢谢大家！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注