MySQL错误日志的核心是 InnoDB 缓冲池耗尽 + 海量行锁导致数据库崩溃MySQL数据库因InnoDB缓冲池内存

=====================================
2025-11-07 17:45:29 0x32fc INNODB MONITOR OUTPUT

Per second averages calculated from the last 20 seconds

BACKGROUND THREAD

srv_master_thread loops: 4897 srv_active, 0 srv_shutdown, 24059 srv_idle
srv_master_thread log flush and writes: 28956

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 143753
OS WAIT ARRAY INFO: signal count 153331
RW-shared spins 0, rounds 1300420, OS waits 99956
RW-excl spins 0, rounds 2624877, OS waits 10536
RW-sx spins 6897, rounds 178770, OS waits 5630
Spin rounds per wait: 1300422.00 RW-shared, 2624877.00 RW-excl, 25.92 RW-sx

TRANSACTIONS

Trx id counter 697165821
Purge done for trx's n:o < 697165821 undo n:o < 0 state: running but idle
History list length 51
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283539193489200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283539193491816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283539193492688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283539193490072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283539193494432, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283539193490944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 697165624, ACTIVE 4772 sec fetching rows
mysql tables in use 2, locked 2
ROLLING BACK 1048418 lock struct(s), heap size 126804176, 63973889 row lock(s), undo log entries 21574948
MySQL thread id 154, OS thread handle 25544, query id 29378 localhost 127.0.0.1 root Sending data
UPDATE sidentity s
JOIN department d ON s.department_id = d.id
SET s.department_level = d.level where 1=1

FILE I/O

I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1616998 OS file reads, 1845924 OS file writes, 73963 OS fsyncs
883.66 reads/s, 16384 avg bytes/read, 903.15 writes/s, 42.75 fsyncs/s

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 3096, seg size 3098, 14 merges
merged operations:
insert 14, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 18 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
32135.19 hash searches/s, 20651.87 non-hash searches/s

LOG

Log sequence number 3467913912742
Log flushed up to 3467911264038
Pages flushed up to 3467908117548
Last checkpoint at 3467882812700
0 pending log flushes, 0 pending chkp writes
20975 log i/o's done, 9.15 log i/o's/second

BUFFER POOL AND MEMORY

Total large memory allocated 137297920
Dictionary memory allocated 584478
Buffer pool size 8192
Free buffers 77
Database pages 352
Old database pages 0
Modified db pages 347
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12353, not young 475582500
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1616930, created 579952, written 1781705
883.71 reads/s, 0.00 creates/s, 873.66 writes/s
Buffer pool hit rate 997 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 352, unzip_LRU len: 0
I/O sum[56537]:cur[2], unzip sum[0]:cur[0]

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=5708, Main thread ID=13056, state: sleeping
Number of rows inserted 257, updated 49614258, deleted 0, read 133781669
0.00 inserts/s, 0.05 updates/s, 0.00 deletes/s, 0.40 reads/s

END OF INNODB MONITOR OUTPUT
**============================ **** 2025-11-07T09:45:37.189912Z 154 [ERROR] [FATAL] InnoDB: Over 95 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. Your buffer pool size is 128 MB. Maybe you should make the buffer pool bigger? We intentionally generate a seg fault to print a stack trace on Linux!
2025-11-07 17:45:37 0x63c8 InnoDB: Assertion failure in thread 25544 in file ut0ut.cc line 942
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: dev.mysql.com/doc/refman/…
InnoDB: about forcing recovery.
09:45:37 UTC - mysqld got exception 0x80000003 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

这份 MySQL 错误日志的核心是 InnoDB 缓冲池因海量行锁耗尽内存，最终导致数据库崩溃，以下是逐段详细的错误解释：

一、核心错误结论

日志最终的致命错误明确了崩溃原因：InnoDB: Over 95 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Your buffer pool size is 128 MB.

即：InnoDB 缓冲池（仅 128MB）的 95% 以上被 “锁堆” 或 “自适应哈希索引” 占用，内存耗尽触发崩溃。

二、关键日志片段解读（按逻辑链排序）

2.1 卡死的全量更新事务（问题根源）

---TRANSACTION 697165624, ACTIVE 4772 sec fetching rows
mysql tables in use 2, locked 2
ROLLING BACK 1048418 lock struct(s), heap size 126804176, 63973889 row lock(s), undo log entries 21574948
UPDATE sidentity s JOIN department d ON s.department_id = d.id SET s.department_level = d.level where 1=1

事务状态：该 UPDATE 事务已运行 4772 秒（约 1.3 小时），因资源不足进入 “回滚（ROLLING BACK）” 阶段。
资源占用：持有6397 万条行锁，生成104 万个锁结构，占用堆内存约121MB（接近缓冲池总大小 128MB）。
操作问题：where 1=1 导致全表更新，且未有效利用索引（后续执行计划可验证），触发全表扫描 + 全量行锁。

2.2 缓冲池资源耗尽（崩溃直接原因）

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137297920（≈128MB）
Free buffers       77（空闲缓冲极少）
Database pages     352（实际缓存的数据页很少）

缓冲池是 InnoDB 的核心内存区域，需同时缓存数据页、索引页、锁结构、undo 日志等。128MB 的缓冲池完全无法支撑 5000 万数据的全表更新 —— 仅锁结构就占用了 121MB，剩余内存不足以维持数据库正常运行。

2.3 数据库崩溃的断言失败（自我保护机制）

2025-11-07 17:45:37 0x63c8  InnoDB: Assertion failure in thread 25544 in file ut0ut.cc line 942
InnoDB: We intentionally generate a memory trap.

这是 InnoDB 的 “自我保护逻辑”：当缓冲池被异常占用超 95%，系统判断为致命错误，线程25544，主动触发内存陷阱（崩溃），避免数据损坏。

2.4 全表扫描引发的 I/O 压力暴增

FILE I/O
--------
1616998 OS file reads, 1845924 OS file writes, 73963 OS fsyncs
883.66 reads/s, 903.15 writes/s, 42.75 fsyncs/s

共执行 161.7 万次文件读、184.6 万次文件写、7.4 万次 fsync（磁盘同步）
每秒 883 次读、903 次写操作，远超正常数据库的 I/O 负载。
原因是无索引的 JOIN 更新需要频繁读写磁盘（全表扫描），进一步加剧系统资源消耗，加速了缓冲池耗尽和崩溃。

三、错误逻辑链总结

无有效索引 → 全表扫描 + 全量行锁 → 锁结构 /undo 日志占用大量缓冲池内存 → 128MB 缓冲池完全耗尽 → I/O 压力暴增 → InnoDB 触发自我保护崩溃 → 事务回滚。

核心矛盾是 *“极小的缓冲池配置”与“超大数据量的无索引全量更新” * 不匹配，导致锁资源和内存资源被彻底耗尽。

四、分析sql

EXPLAIN
UPDATE sidentity s 
JOIN department d ON s.department_id = d.id 
SET s.department_level = d.level;
WHERE 1=1;

编辑

字段	内容	分析
`select_type`	`UPDATE`/`SIMPLE`	表示这是更新操作，涉及两张表的关联查询
`table`	`s`/`d`	分别对应sidentity 和`department`表
`type`	`ALL`/`ref`	表`s`是`ALL`（全表扫描），表`d`是`ref`（索引扫描，有效利用了`id_idx`索引）
`possible_keys`	`index_department_id`	表`s`的`department_id`有可用索引，但未被实际使用
`key`	空 /`id_idx`	表`s`未使用任何索引，表`d`使用了`id_idx`索引
`rows`	`65478878`	表`s`需要扫描约 6500 万行数据，这是性能问题的核心

五、解决方案

5.1分批更新（避免单次锁爆炸）

UPDATE sidentity s
JOIN department d ON s.department_id = d.id
SET s.department_level = d.level;
WHERE 1=1;

一次性把sidentity表**65478878记录全部更新了，可以分批次执行update。给where增加时间参数，一天一天的更新。**

5.2调整 MySQL 参数（提升执行效率）

5.2.1 查看当前缓冲池大小

-- 查看当前全局缓冲池大小（单位：字节）
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

例如，结果为134217728表示当前是 128MB（134217728 字节 = 128×1024×1024）。

5.2.2. 动态调整缓冲池大小

执行以下命令临时修改（以调整为 8GB 为例，8GB = 8×1024×1024×1024 = 8589934592 字节）：

-- 调整为8GB（单位：字节）
SET GLOBAL innodb_buffer_pool_size = 8589934592;

MySQL错误日志的核心是 InnoDB 缓冲池耗尽 + 海量行锁导致数据库崩溃

===================================== 2025-11-07 17:45:29 0x32fc INNODB MONITOR OUTPUT