发布于 由admin

MySQL的`SHOW ENGINE INNODB STATUS`：如何排查`Innodb`问题？

使用 SHOW ENGINE INNODB STATUS 诊断 InnoDB 问题

大家好，今天我们来深入探讨如何利用 SHOW ENGINE INNODB STATUS 命令来诊断 InnoDB 存储引擎的问题。这是一个非常有用的工具，可以提供关于 InnoDB 内部状态的详细信息，帮助我们识别性能瓶颈、死锁、事务问题等等。

SHOW ENGINE INNODB STATUS 的基本结构

首先，我们来看一下 SHOW ENGINE INNODB STATUS 命令输出的基本结构。执行这个命令后，你会得到一个很长的文本输出，它被组织成多个逻辑部分。我们首先需要知道有哪些重要的部分以及它们包含的信息。

一个典型的 SHOW ENGINE INNODB STATUS 输出大致可以分为以下几个关键部分：

  1. Overview: 简要描述 InnoDB 引擎的版本和一些全局性的状态信息。
  2. Log sequence number operations: 记录了日志序列号（LSN）相关的操作，包括日志写入、刷新等。
  3. Background threads: 显示 InnoDB 后台线程的状态，比如 purge 线程、insert buffer merge 线程等。
  4. Semaphores: 报告 InnoDB 内部使用的信号量信息，可以用来检测线程竞争。
  5. TRANSACTIONS: 这是最重要的部分之一，详细列出当前活跃的事务，包括事务ID、状态、锁信息等等。
  6. FILE I/O: 显示文件 I/O 相关的信息，包括读取和写入操作的次数和字节数。
  7. INSERT BUFFER AND ADAPTIVE HASH INDEX: 提供关于插入缓冲区（Insert Buffer）和自适应哈希索引（Adaptive Hash Index）的状态信息。
  8. LOG: 显示 InnoDB 日志系统的状态，包括日志缓冲区的使用情况、日志文件的数量和大小等。
  9. BUFFER POOL AND MEMORY: 报告缓冲池的使用情况，包括总大小、已使用大小、脏页数量等。
  10. ROW OPERATIONS: 显示行操作的统计信息，包括插入、更新、删除和读取操作的次数。
  11. 锁 (LOCKS)：显示当前存在的锁信息，包括表锁、行锁等。

接下来，我们将逐一分析这些部分，并结合实际案例，讲解如何利用这些信息来诊断 InnoDB 的问题。

1. Overview 和 Background Threads

Overview 部分提供了 InnoDB 的版本信息以及启动时间等基本信息。Background threads 部分则显示了 InnoDB 后台线程的状态。例如：

=====================================
2023-10-27 10:00:00 0x7f2a8c000700 INNODB STATUS
=====================================
  Version: 8.0.30
  ...
Background threads:
 srv_master_thread loops: 115049 srv_active, 1 srv_shutdown, 13878471 srv_idle
 srv_buf_resize_thread loops: 1064
 srv_page_cleaner_thread loops: 244, made page_full loops: 0, page_cleaner rnds: 0
 srv_log_flush_thread loops: 115049, made page_full loops: 0
  • srv_master_thread: 这是 InnoDB 的主线程，负责执行各种后台任务，比如刷新脏页、合并插入缓冲区等。srv_active 表示活跃状态的循环次数，srv_idle 表示空闲状态的循环次数。如果 srv_active 循环次数明显高于 srv_idle，可能表示系统压力较大。
  • srv_page_cleaner_thread: 这是页面清理线程，负责将缓冲池中的脏页刷新到磁盘。made page_full loops 表示缓冲池已满，需要强制清理脏页的次数。如果这个值很高，可能表示缓冲池太小，无法容纳足够的数据。
  • srv_log_flush_thread: 这是日志刷新线程，负责将日志缓冲区中的数据刷新到磁盘。

案例分析：

如果发现 srv_page_cleaner_thread 的 made page_full loops 值很高，可以考虑增加 innodb_buffer_pool_size 的大小。

-- 查看当前的缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 动态修改缓冲池大小 (需要重启 MySQL 才能生效)
SET GLOBAL innodb_buffer_pool_size = 8589934592; -- 8GB

2. Semaphores

Semaphores 部分显示 InnoDB 内部使用的信号量信息。信号量用于控制多个线程对共享资源的访问，如果线程长时间等待某个信号量，可能表示存在锁竞争。

------------
SEMAPHORES
------------
OS WAIT ARRAY INFO: reservation count 145452927, signal count 115452927
Mutex spin waits 0, rounds 2246177692, OS waits 2142450
RW-shared spins 128206439, rounds 2298263667, OS waits 813528
RW-excl spins 10400048, rounds 167759394, OS waits 17366
  • Mutex spin waits: 表示自旋锁等待的次数。自旋锁是一种尝试在用户空间获取锁的机制，如果获取失败，线程会不断重试，直到获取成功。如果 Mutex spin waits 很高，可能表示线程竞争激烈。
  • OS waits: 表示线程因为等待信号量而被操作系统挂起的次数。如果 OS waits 很高，通常表示锁竞争非常严重。

案例分析：

如果发现 OS waits 很高，可以使用 SHOW PROCESSLIST 命令查看当前正在执行的 SQL 语句，找出持有锁的语句，并尝试优化这些语句，减少锁的持有时间。

-- 查看当前正在执行的 SQL 语句
SHOW PROCESSLIST;

或者，可以启用性能模式（Performance Schema），收集更详细的锁信息。

-- 启用 Performance Schema (如果未启用)
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'lock%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%events_waits_current';

然后，查询 performance_schema.events_waits_current 表，查看当前正在等待锁的事件。

SELECT
    event_name,
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_mode,
    lock_duration
FROM performance_schema.events_waits_current
WHERE event_name LIKE 'wait/lock/table/sql/%';

3. TRANSACTIONS

TRANSACTIONS 部分是诊断事务相关问题的关键。它列出了当前活跃的事务，包括事务 ID、状态、锁信息等等。

------------
TRANSACTIONS
------------
Trx id counter 1513971
Purge done for trx's n:o < 1513971 undo n:o < 0 state: running but idle
History list length 14
LIST OF TRANSACTIONS FOR EACH CONNECTION
---TRANSACTION 421620399214304, not started
mysql tables in use 1, locked 1
...
---TRANSACTION 421620399213256, ACTIVE 27 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 12345, OS thread handle 140736362766080, query id 1234567 localhost user
TABLE LOCK table `mydb`.`mytable` trx id 421620399213256 lock mode IS
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `mydb`.`mytable` id 789 lock_mode X locks rec but not gap
Record lock, heap no 2 physical record: n_fields 5; compact format; info bits 0
...
  • Trx id counter: 表示事务 ID 的计数器，每次创建一个新事务，这个值都会递增。
  • Purge done for trx's n:o: 表示已经完成清理的事务 ID。
  • History list length: 表示 Undo 日志的长度。Undo 日志用于回滚事务，如果这个值很高，可能表示存在长时间运行的事务，或者 Purge 线程无法及时清理 Undo 日志。
  • TRANSACTION ... ACTIVE ... sec: 表示一个活跃的事务，ACTIVE 后面的数字表示事务的持续时间。如果事务持续时间很长，可能表示存在性能问题。
  • TABLE LOCK: 表示表锁，lock mode IS 表示意向共享锁。
  • RECORD LOCKS: 表示行锁，lock_mode X locks rec but not gap 表示排他锁，锁定记录但不锁定间隙。

案例分析：

1. 长时间运行的事务：

如果发现 TRANSACTION ... ACTIVE ... sec 中的持续时间很长，可以使用 SHOW PROCESSLIST 命令查看该事务正在执行的 SQL 语句，找出性能瓶颈。

2. 死锁：

InnoDB 会自动检测死锁，并在 TRANSACTIONS 部分报告死锁信息。例如：

*** (1) TRANSACTION:
TRANSACTION 421620399214304, ACTIVE 10 sec, process no 12345, OS thread 140736362766080
mysql tables in use 1, locked 1
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `mydb`.`mytable` id 789 lock_mode X locks rec but not gap
...
*** (2) TRANSACTION:
TRANSACTION 421620399213256, ACTIVE 27 sec, process no 67890, OS thread 140736362766336
mysql tables in use 1, locked 1
...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `mydb`.`mytable` id 789 lock_mode X locks rec but not gap
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `mydb`.`mytable` id 789 lock_mode X locks rec but not gap
...
*** WE ROLL BACK TRANSACTION (1)

这段信息表示事务 1 正在等待事务 2 持有的锁，而事务 2 也在等待事务 1 持有的锁，导致死锁。InnoDB 自动回滚了事务 1，解决了死锁。

要解决死锁问题，可以尝试以下方法：

  • 优化 SQL 语句：减少锁的持有时间，避免长时间锁定资源。
  • 调整事务隔离级别：降低事务隔离级别，减少锁的竞争。但是需要权衡数据一致性和并发性能。
  • 使用更细粒度的锁：尽量使用行锁，避免使用表锁。
  • 避免交叉更新：避免多个事务同时更新同一行数据。
  • 设置锁等待超时时间：使用 innodb_lock_wait_timeout 参数设置锁等待超时时间，避免事务长时间阻塞。
-- 设置锁等待超时时间为 5 秒
SET GLOBAL innodb_lock_wait_timeout = 5;

4. FILE I/O

FILE I/O 部分显示文件 I/O 相关的信息。

------------
FILE I/O
------------
I/O thread 0 state: waiting for completion of aio request (insert buffer thread)
I/O thread 1 state: waiting for completion of aio request (log thread)
I/O thread 2 state: waiting for completion of aio request (read thread)
I/O thread 3 state: waiting for completion of aio request (read thread)
I/O thread 4 state: waiting for completion of aio request (read thread)
I/O thread 5 state: waiting for completion of aio request (read thread)
I/O thread 6 state: waiting for completion of aio request (write thread)
I/O thread 7 state: waiting for completion of aio request (write thread)
...
Pending normal aio reads: 16 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 12 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
...
  • I/O thread state: 显示 I/O 线程的状态。如果线程长时间处于等待状态，可能表示 I/O 存在瓶颈。
  • Pending normal aio reads: 表示等待完成的异步读取请求的数量。如果这个值很高，可能表示磁盘 I/O 压力较大。
  • Pending normal aio writes: 表示等待完成的异步写入请求的数量。如果这个值很高，可能表示磁盘 I/O 压力较大。

案例分析：

如果发现 Pending normal aio reads 或 Pending normal aio writes 的值很高，可以尝试以下方法：

  • 使用更快的磁盘：比如使用 SSD 替代机械硬盘。
  • 增加 I/O 线程的数量：通过调整 innodb_read_io_threads 和 innodb_write_io_threads 参数来增加 I/O 线程的数量。
-- 查看当前的 I/O 线程数量
SHOW VARIABLES LIKE 'innodb_read_io_threads';
SHOW VARIABLES LIKE 'innodb_write_io_threads';

-- 修改 I/O 线程数量 (需要重启 MySQL 才能生效)
SET GLOBAL innodb_read_io_threads = 8;
SET GLOBAL innodb_write_io_threads = 8;
  • 优化 SQL 语句：减少不必要的 I/O 操作。
  • 调整 innodb_flush_method 参数：尝试不同的刷新方法，比如 O_DIRECT 或 O_DSYNC。

5. INSERT BUFFER AND ADAPTIVE HASH INDEX

INSERT BUFFER AND ADAPTIVE HASH INDEX 部分提供关于插入缓冲区和自适应哈希索引的状态信息。

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash index own total bytes 4670280
Hash index stats (main table hash index):
  buckets 31, stride: 512, list_size: 176
length sum 1356, mean 7.70, standard deviation 15.00, max 92
hash searches/s: 0.0, merges/s: 0.0
  • Ibuf: size: 表示插入缓冲区的大小。
  • Ibuf: free list len: 表示插入缓冲区的空闲列表长度。
  • merged operations: 表示已经合并到主表的插入、删除标记和删除操作的数量。
  • Hash index own total bytes: 表示自适应哈希索引使用的内存大小。
  • buckets: 表示自适应哈希索引的桶数量。
  • list_size: 表示哈希桶的平均链表长度。

案例分析：

  • 插入缓冲区合并缓慢：如果发现 merged operations 的数量增长缓慢，可能表示插入缓冲区合并存在瓶颈。可以尝试增加 innodb_io_capacity 参数，提高磁盘 I/O 性能。
  • 自适应哈希索引冲突严重：如果发现 list_size 的平均链表长度很长，可能表示自适应哈希索引冲突严重。可以尝试禁用自适应哈希索引，通过设置 innodb_adaptive_hash_index 参数为 OFF。
-- 禁用自适应哈希索引
SET GLOBAL innodb_adaptive_hash_index = OFF;

6. LOG

LOG 部分显示 InnoDB 日志系统的状态。

------------
LOG
------------
Log sequence number 145452927
Log flushed up to   145452927
Pages flushed up to 145452927
Last checkpoint at  145452927
0 pending log flushes, 0 pending chkp writes
171727 log i/o's done, 0.00 log i/o's/second
  • Log sequence number: 表示当前的日志序列号 (LSN)。
  • Log flushed up to: 表示已经刷新到磁盘的 LSN。
  • Pages flushed up to: 表示已经刷新到磁盘的页面 LSN。
  • Last checkpoint at: 表示上次检查点发生的 LSN。
  • pending log flushes: 表示等待刷新的日志数量。
  • pending chkp writes: 表示等待写入检查点的数量。
  • log i/o's done: 表示已经完成的日志 I/O 操作的数量。

案例分析：

  • 日志刷新延迟：如果发现 pending log flushes 的值很高，可能表示日志刷新存在延迟。可以尝试增加 innodb_log_file_size 参数，增加日志文件的大小，减少日志刷新的频率。
-- 查看当前的日志文件大小
SHOW VARIABLES LIKE 'innodb_log_file_size';

-- 修改日志文件大小 (需要重启 MySQL 才能生效)
SET GLOBAL innodb_log_file_size = 536870912; -- 512MB

同时，确保 innodb_flush_log_at_trx_commit 参数设置合理。

  • innodb_flush_log_at_trx_commit = 1：每次事务提交时都将日志刷新到磁盘，提供最高的数据安全性，但性能较差。
  • innodb_flush_log_at_trx_commit = 0：每秒将日志刷新到磁盘一次，数据安全性较低，但性能较好。
  • innodb_flush_log_at_trx_commit = 2：每次事务提交时将日志写入操作系统缓冲区，然后由操作系统定期刷新到磁盘，数据安全性和性能介于 0 和 1 之间。

7. BUFFER POOL AND MEMORY

BUFFER POOL AND MEMORY 部分报告缓冲池的使用情况。

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8589934592
Dictionary memory allocated 1942284
Buffer pool size   524288
Free buffers       4466
Database pages     519163
Old database pages 191076
Modified db pages  16000
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16720407, not young 34920129
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1334597, created 2336827, written 2876656
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000
...
  • Total large memory allocated: 表示分配的总内存大小。
  • Buffer pool size: 表示缓冲池的大小，以页为单位。
  • Free buffers: 表示空闲缓冲区的数量。
  • Database pages: 表示缓冲池中缓存的数据库页面的数量。
  • Modified db pages: 表示缓冲池中的脏页的数量。
  • Buffer pool hit rate: 表示缓冲池的命中率。

案例分析：

  • 缓冲池命中率低：如果发现 Buffer pool hit rate 很低，比如低于 95%，可能表示缓冲池太小，无法容纳足够的数据。可以考虑增加 innodb_buffer_pool_size 的大小。
  • 脏页数量过多：如果发现 Modified db pages 的数量很高，可能表示页面清理线程无法及时将脏页刷新到磁盘。可以尝试增加 innodb_io_capacity 参数，提高磁盘 I/O 性能，或者调整 innodb_max_dirty_pages_pct 参数，控制脏页的比例。
-- 查看当前的脏页比例
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct';

-- 修改脏页比例
SET GLOBAL innodb_max_dirty_pages_pct = 80; -- 设置为 80%

8. ROW OPERATIONS

ROW OPERATIONS 部分显示行操作的统计信息。

------------
ROW OPERATIONS
------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 lock wait timeouts, 0 lock waits
Per second averages calculated from the last 59 seconds
0 row lock waits, 0.00 rows locked/s
0 rows read/s, 0.00 rows modified/s
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s
0 internal reads/s, 0 internal creates/s, 0 internal updates/s, 0 internal deletes/s
  • row lock waits: 表示行锁等待的次数。
  • rows read/s: 表示每秒读取的行数。
  • rows modified/s: 表示每秒修改的行数。
  • inserts/s: 表示每秒插入的行数。
  • updates/s: 表示每秒更新的行数。
  • deletes/s: 表示每秒删除的行数。

案例分析：

  • 行锁等待过多：如果发现 row lock waits 的值很高，可以使用 SHOW PROCESSLIST 命令查看当前正在执行的 SQL 语句，找出持有锁的语句，并尝试优化这些语句，减少锁的持有时间。或者，可以启用性能模式（Performance Schema），收集更详细的锁信息，如前文所述。
  • 行操作性能瓶颈：通过分析 rows read/s、rows modified/s、inserts/s、updates/s 和 deletes/s 的数值，可以了解系统的负载情况，找出性能瓶颈。如果某个操作的数值很高，可以尝试优化相关的 SQL 语句，提高性能。

9. 锁 (LOCKS)

LOCKS部分提供了有关当前数据库服务器上存在的锁的信息。这对于诊断并发问题和死锁很有用。

案例分析

在SHOW ENGINE INNODB STATUS中，如果你看到LOCK WAIT或FOREIGN KEY约束相关的错误信息，这通常表示存在锁竞争或死锁。你可以进一步分析TRANSACTIONS部分，找出涉及锁的事务，然后优化相关SQL语句，或者调整事务隔离级别。

总结

SHOW ENGINE INNODB STATUS 提供了非常丰富的信息，可以帮助我们诊断 InnoDB 存储引擎的各种问题。通过仔细分析 Overview、Background threads、Semaphores、TRANSACTIONS、FILE I/O、INSERT BUFFER AND ADAPTIVE HASH INDEX、LOG、BUFFER POOL AND MEMORY 和 ROW OPERATIONS 等部分的信息，我们可以识别性能瓶颈、死锁、事务问题等等，并采取相应的措施进行优化。记住，没有银弹，需要结合具体情况进行分析。多实践，多总结，才能更好地利用这个工具。

希望今天的分享对大家有所帮助！

发表回复

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