跳到主要内容

第13章 MySQL监控

1、监控锁情况​

1）什么是死锁​

在数据库系统中，死锁（Deadlock）指的是两个或多个事务在执行的过程中，因各自持有对方想要获取的资源而相互等待，导致这几个事务都无法继续执行的现象。

通俗一点说，事务 A 拿着事务 B 想要的锁，同时事务 B 也占着事务 A 想要的锁，双方都不肯“让步”，就陷入了无限等待的僵局，这就是死锁。

当数据库检测到死锁时，往往会主动回滚其中一个或多个事务，以打破僵局。


2）死锁产生的原因​

1. **并发事务过多**
多个事务在高并发场景下，操作同一批数据或同一张表，频繁地获取和释放锁。如果事务之间锁的申请顺序不一致，很容易导致死锁。
2. **锁的粒度与方式**
InnoDB 中的行级锁、间隙锁（Gap Lock）、Next-Key Lock 等机制，会在复杂的查询和更新语句中造成锁范围的扩大或锁等待冲突，从而引发死锁。
3. **未能及时提交或回滚事务**
长事务没有及时结束（提交/回滚），导致锁被长时间持有，其它事务在获取锁时被阻塞，增加了死锁发生的概率。
4. **SQL 语句执行顺序**
相同的业务逻辑，如果开发者在代码层面未对锁的获取顺序进行规范（如先锁表 A 再锁表 B 或者先锁表 B 再锁表 A），在高并发条件下，各事务获取资源的顺序不一致，就可能导致死锁。


3）死锁有哪些分类​

行锁

表锁


4）监控死锁语句涉及到的表及命令​

相关的表：

谁在等待锁（通过 innodb_trx / innodb_lock_waits）

谁持有锁（innodb_trx / innodb_lock_waits）

正在执行什么语句（performance_schema.events_statements_current）

执行完成后的历史语句（performance_schema.events_statements_history）

对应会话/线程信息（performance_schema.threads）


相关监控命令：


# 查看 InnoDB 锁相关状态

SHOW GLOBAL STATUS like 'innodb_rows_lock%'


# 当前事务信息

select * from information_schema.innodb_trx;


# 锁等待依赖信息

select * from sys.innodb_lock_waits;


# 线程信息

select * from performance_schema.threads;


# 当前正在执行的 SQL

select * from performance_schema.events_statements_current;


# 历史执行的 SQL

select * from performance_schema.events_statements_history;

5）死锁实验​


# 创建测试表与插入数据

CREATE DATABASE test;
USE test;
CREATE TABLE test_lock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    value INT
) ENGINE=InnoDB;

INSERT INTO test_lock (name, value) VALUES ('alice', 100), ('bob', 100);


# Session A（占用行锁）

START TRANSACTION;
UPDATE test_lock SET value = value + 10 WHERE name = 'alice';
-- 不要提交或回滚，让事务保持打开状态


# Session B（触发锁等待）

USE test;
START TRANSACTION;
UPDATE test_lock SET value = value + 20 WHERE name = 'alice';
-- 该语句将被阻塞，等待 Session A 释放锁


# 查看 InnoDB 锁相关状态

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
5 rows in set (0.06 sec)


# 当前所有 InnoDB 事务信息

mysql> SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_mysql_thread_id, trx_query
    -> FROM information_schema.innodb_trx
    -> ORDER BY trx_started;
+--------+-----------+---------------------+---------------------+---------------------+--------------------------------------------------------------+
| trx_id | trx_state | trx_started         | trx_wait_started    | trx_mysql_thread_id | trx_query                                                    |
+--------+-----------+---------------------+---------------------+---------------------+--------------------------------------------------------------+
| 3852   | RUNNING   | 2025-01-10 18:34:17 | NULL                |                   3 | NULL                                                         |
| 3853   | LOCK WAIT | 2025-01-10 18:34:32 | 2025-01-10 18:34:32 |                   4 | UPDATE test_lock
SET value = value + 20
WHERE name = 'alice' |
+--------+-----------+---------------------+---------------------+---------------------+--------------------------------------------------------------+
2 rows in set (0.00 sec)

6）发现死锁后处理流程​

1、通过分析找到锁源的SQL语句

2、将相关SQL语句抄送给技术老大及开发负责人

3、根据开发反馈可能有两种情况：

第一种：不是代码问题

可能是数据库繁忙导致事务语句执行失败

建议临时kill掉有问题的会话

第二种：是代码问题

发现是代码业务逻辑问题导致的死锁

临时解决方案 kill掉有问题的会话

最终解决方案 修复代码中的逻辑


7）监控死锁脚本​

ChatGPT Plus编写的检查脚本：

cat > check-v4.sh << 'EOF'
#!/bin/bash

# ===================================================================


# check_v2_optimized.sh

#

# 说明：


#   - 基于 v2 版本的锁检查脚本，输出包括：


#       [1] InnoDB 行锁统计


#       [2] 当前所有 InnoDB 事务信息


#       [3] 锁等待关系 (sys.innodb_lock_waits)


#       [4] 关联 InnoDB 事务与 PROCESSLIST


#   - 仅在第 [4] 段末尾添加一行说明，解释当 innodb_trx 中的SQL


#     和 processlist 中的SQL 相同时是正常现象。


# ===================================================================


HOST="127.0.0.1"
PORT="3306"
USER="root"
PASS="root"
DB="test"  # 可视情况指定，或留空

MYSQL_CMD="mysql -h$HOST -P$PORT -u$USER -p$PASS -N"  # -N 去除列名，减少干扰
LINE="--------------------------------------------------"


# [1] InnoDB 行锁统计

echo
echo "=================== [1] InnoDB 行锁统计 ==================="
echo

$MYSQL_CMD -e "SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';" 2>/dev/null | while read var_name var_value; do
  case "$var_name" in
    "Innodb_row_lock_current_waits") echo "当前锁等待个数: $var_value" ;;
    "Innodb_row_lock_time")          echo "行锁等待总时间(毫秒): $var_value" ;;
    "Innodb_row_lock_time_avg")      echo "平均每次锁等待时间(毫秒): $var_value" ;;
    "Innodb_row_lock_time_max")      echo "最长一次锁等待时间(毫秒): $var_value" ;;
    "Innodb_row_lock_waits")         echo "自启动以来锁等待的总次数: $var_value" ;;
  esac
done


# [2] 当前所有 InnoDB 事务信息

echo
echo "=================== [2] 当前所有 InnoDB 事务信息 ==================="
echo


# 使用 CONCAT_WS('|', ...) 避免空格错位

innodb_trx_output=$($MYSQL_CMD -e "
SELECT CONCAT_WS('|',
  trx_id,
  trx_state,
  trx_started,
  IFNULL(trx_wait_started,'NULL'),
  IFNULL(trx_mysql_thread_id,'NULL'),
  IFNULL(REPLACE(REPLACE(trx_query, '\n',' '), '\t',' '),'NULL')
)
FROM information_schema.innodb_trx
ORDER BY trx_started;
" 2>/dev/null)

if [ -z "$innodb_trx_output" ]; then
  echo "当前无 InnoDB 事务或无法读取 innodb_trx 表。"
else
  while IFS='|' read -r trx_id trx_state trx_started trx_wait_started trx_thread_id trx_query; do
    echo "事务ID: $trx_id"
    echo " - 状态: $trx_state"
    echo " - 开始时间: $trx_started"
    if [ "$trx_wait_started" != "NULL" ]; then
      echo " - 锁等待开始时间: $trx_wait_started"
    fi
    echo " - 所属线程ID: $trx_thread_id"
    echo " - 当前SQL: $trx_query"
    echo "$LINE"
  done <<< "$innodb_trx_output"
fi


# [3] 锁等待关系 (sys.innodb_lock_waits)

echo
echo "=================== [3] 锁等待关系 ==================="
echo
echo "使用 sys.innodb_lock_waits 查询："

lock_waits_res=$($MYSQL_CMD -e "
SELECT
  waiting_trx_id,
  blocking_trx_id,
  waiting_thread_id,
  blocking_thread_id
FROM sys.innodb_lock_waits;
" 2>/dev/null)

if [ -z "$lock_waits_res" ]; then
  echo "当前无锁等待记录。"
else
  echo "$lock_waits_res"
fi


# [4] 关联 InnoDB 事务与 PROCESSLIST

echo
echo "=================== [4] 关联 InnoDB 事务与 PROCESSLIST ==================="
echo

trx_process_res=$($MYSQL_CMD -e "
SELECT CONCAT_WS('|',
  t.trx_id,
  IFNULL(t.trx_mysql_thread_id,'NULL'),
  IFNULL(REPLACE(REPLACE(t.trx_query, '\n',' '), '\t',' '),'NULL'),
  IFNULL(REPLACE(REPLACE(p.INFO, '\n',' '), '\t',' '),'NULL')
)
FROM information_schema.innodb_trx t
LEFT JOIN information_schema.processlist p
       ON t.trx_mysql_thread_id = p.ID
ORDER BY t.trx_started;
" 2>/dev/null)

if [ -z "$trx_process_res" ]; then
  echo "无事务或无法关联 processlist。"
else
  while IFS='|' read -r trx_id thr_id t_query p_info; do
    echo "事务 $trx_id <-> 线程 $thr_id"
    echo " - innodb_trx 中记录的SQL: $t_query"
    echo " - processlist 中实际在执行的SQL: $p_info"
    echo "   （说明：如果这两处SQL相同，通常表示该事务正在执行或等待同一条SQL，属正常情况。）"
    echo "$LINE"
  done <<< "$trx_process_res"
fi

echo
echo "【脚本执行完毕】以上为当前锁等待及事务信息的简要解析。"
echo "如果发现某事务长时间占用锁，可考虑 COMMIT 或 ROLLBACK 或执行: KILL <线程ID>"
echo
EOF

执行效果：

1736511966904-d924db2c-664f-4975-88ce-7e9cdb3f46af.png

2、监控指标​

以下是针对MySQL 5.7的重要监控指标，包括单位、含义、监控命令以及官方建议值

MySQL监控的指标众多，我们可以参考阿里云上的RDS的监控作为参考：

指标名称单位含义监控命令正常情况（示例）异常情况（示例）
查询吞吐量 (QPS)次/秒每秒执行的查询次数，反映数据库的负载情况SHOW GLOBAL STATUS LIKE 'Questions';稳定在预期范围内，如1000 QPS突然 spike 到 2000 QPS 或 drop 到 500 QPS
连接数个当前连接到 MySQL 的客户端数量SHOW GLOBAL STATUS LIKE 'Threads_connected';低于最大连接数，如 100/150接近或达到最大连接数，如 149/150
缓存命中率%InnoDB 缓冲池的命中率，反映缓冲池的使用效率SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';大于90%小于80%
锁等待时间秒事务等待锁的时间，反映锁争用情况SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time';接近零或很低，如 0.01 秒持续高于 0.1 秒
慢查询数量个执行时间超过 long_query_time 的查询数量SHOW GLOBAL STATUS LIKE 'Slow_queries';很低，如每天少于 10 个突然增加到每秒多个
临时表创建数量个创建的磁盘临时表数量，反映查询的优化情况SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';很低，如每天少于 100 个持续高于 1000 个
网络流量字节MySQL 接收和发送的网络数据量SHOW GLOBAL STATUS LIKE 'Bytes_received'; 和 SHOW GLOBAL STATUS LIKE 'Bytes_sent';稳定趋势，如每天 10GB突然 spike 到 100GB
打开的表数量个当前打开的表数量，反映表缓存的使用情况SHOW GLOBAL STATUS LIKE 'Open_tables';低于表缓存设置，如 500/1000接近或超过表缓存设置，如 999/1000
复制延迟秒从库相对于主库的复制延迟时间SHOW SLAVE STATUS\G 中的 Seconds_Behind_Master0 或接近零持续高于 60 秒
InnoDB 日志写等待次InnoDB 日志写操作的等待次数，反映日志写入的性能SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';接近零持续高于 10 次
线程缓存命中率%线程缓存的命中率，反映线程缓存的使用效率SHOW GLOBAL STATUS LIKE 'Threads_cached';大于80%小于50%
临时文件创建数量个创建的临时文件数量，反映查询的优化情况SHOW GLOBAL STATUS LIKE 'Created_tmp_files';很低，如每天少于 50 个持续高于 500 个
InnoDB 缓冲池使用率%InnoDB 缓冲池的使用率，反映缓冲池的内存使用情况SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';大于80%小于70% 或过高导致 swapping
InnoDB 行锁等待时间秒InnoDB 行锁的等待时间，反映行锁的争用情况SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time';接近零或很低持续高于 0.05 秒

这个表格提供了各个监控指标的正常和异常情况示例，帮助数据库管理员更好地理解和监控MySQL 5.7的性能和健康状况。

3、监控图形及解释​

MySQL CPU/内存 利用率​

1736498450901-2944ec49-4d37-4f98-ad6c-8ca7f6b8b48f.png

1736498481107-21dff51f-5aaa-410f-bfbe-68fbf1fd512b.png

MySQL存储空间使用量(MB)​

1736498527953-00ef7f5c-b50e-4833-9679-6c384841a029.png

1736498551165-91e82566-d1fb-44e7-a4bb-be1945759a09.png

磁盘使用率(%)​

1736498576280-5f41cf38-e247-4d58-bfdd-293369aa8c03.png

1736498600863-71532de6-49b2-4107-9b1d-49c2a02d477d.png

MySQL物理存储空间使用量(MB)​

1736498627782-d1e6d1c0-28d8-4454-aa86-82608b048981.png

1736498670064-0cf02023-3ab3-40a6-af04-7b7ed1cc3930.png

MySQL IOPS​

1736498711775-dd4f5215-26c4-4638-8aa1-b72f028fbbc5.png

1736498735805-666ba2f7-5e65-40b2-b8c0-3324b6600bd6.png

IOPS使用率​

1736498761455-b8563cfd-0d2a-43dd-b962-067f4c79f484.png

1736498772954-4d458a8b-cdc9-42d1-a0f0-76bf3cfa01ca.png

MySQL每秒读写吞吐量(B)​

1736498792669-bbe8b26b-a3ab-46e4-bcdc-95c601bb542f.png

1736498806876-9bc7ce1c-e343-4c1d-96cf-19be68f6b387.png

流量吞吐(KB)​

1736498854360-21e3e2c1-a38d-4034-845c-a2ba3aa05da2.png

1736498865693-87471d7f-0a81-4e00-9214-07e7dfe451ce.png

TPS/QPS​

1736498883186-4168f992-bf10-4bec-b536-cdbd37d1ae6f.png 1736498900709-ef9cc960-e932-459c-9f5f-5bdfaf35d71e.png

会话连接​

1736498957296-b5ece305-3046-45d0-9a4c-62611f4620af.png

1736498973313-c00c259a-a65a-49a3-a22e-1d15d1f84778.png

执行次数​

1736498991911-93533ce8-e721-465e-b0c8-4ddeffaaa5a8.png

1736499003272-09deb0e7-4ee6-4f99-81ad-8619c44660f3.png

线程​

1736499035449-2cac1b87-a572-4427-b6f1-f4e5d7c27ef8.png

1736499051595-1033873e-469a-40a1-9d3d-38b4e4d43c26.png

MySQL_SelectScan​

1736499079046-f4dba052-a25a-42f0-b2a3-8585aeef193b.png

1736499086979-8529ba5a-7005-4c8f-bfd5-f9397009f3c2.png

刷盘次数​

1736499106646-0434d80a-f372-4ed7-bf60-f9b81968cf15.png

1736499118142-4f3198fe-a9a1-47a0-a412-b08a852d2edb.png

InnoDB Data 读写吞吐量(KB)​

1736499135831-a9ff7652-a638-4c42-9ddb-81d82bc35b04.png

1736499160216-2d7ac24f-a027-4623-ad6d-196ad0dccd9a.png

InnoDB Buffer Pool 请求次数​

1736499173812-3c410840-8a4c-4d7f-b232-6f36100e0a48.png

1736499192746-54683d64-7e13-4915-923a-fd790bd2545c.png

InnoDB Buffer Pool 命中率​

1736499207118-968f761a-abf7-4c77-ad85-51fb1716561d.png

1736499227124-17ac6d74-6d79-43b8-be62-bf7d56790b2f.png

行锁​

1736499245489-f7cfd152-03ff-4b1d-af53-057a6a19975f.png

1736499262261-f7a60d38-c576-4beb-9216-15e61a5d186a.png

节点复制线程状态​

1736499277484-b550b292-09c7-4305-a648-7c9ada0f233b.png

1736499293854-e05f082f-da9a-475e-bfb1-d60e1607f4d3.png

节点复制延迟(second)​

1736499326609-36b1670c-0dc2-440c-a8a8-bd1d9aebe578.png

1736499336006-ede0ad66-70ff-4ad3-91b3-7db476bb5bad.png

更新: 2025-01-11 11:18:38

最后于 更新