Large import InnoDB错误日志分析：大CSV导入日志解读求助

你看到的内容是InnoDB监控诊断输出，不属于致命错误，因此导入任务没有终止，仍可继续运行。默认配置下，当InnoDB检测到超过阈值的长时间资源争用/锁等待时，就会自动打印该诊断信息到错误日志，用于性能问题排查。

=====================================
2021-09-30 21:03:22 0x7f0848d41640 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 564 srv_active, 0 srv_shutdown, 1068 srv_idle
srv_master_thread log flush and writes: 1632
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 309014
--Thread 139674486203968 has waited at trx0undo.cc line 777 for 436.00 seconds the semaphore:
Mutex at 0x56464c440288, Mutex REDO_RSEG created trx0rseg.cc:397, lock var 2

--Thread 139673591895616 has waited at buf0flu.cc line 1214 for 431.00 seconds the semaphore:
SX-lock on RW-latch at 0x7f085fda1ab0 created in file buf0buf.cc line 1563
a writer (thread id 139673460528704) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time write locked in file trx0rseg.ic line 43
OS WAIT ARRAY INFO: signal count 1296199
RW-shared spins 1239237, rounds 9381718, OS waits 207220
RW-excl spins 865910, rounds 6692891, OS waits 88463
RW-sx spins 375, rounds 11083, OS waits 365
Spin rounds per wait: 7.57 RW-shared, 7.73 RW-excl, 29.55 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 429
Purge done for trx's n:o < 425 undo n:o < 87636886 state: running
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 428, ACTIVE 1482 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 51774178
MySQL thread id 48, OS thread handle 139674486203968, query id 78 localhost root Reading file
LOAD DATA INFILE 'file.csv' INTO TABLE TABLE_X
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (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: 1
452318 OS file reads, 589584 OS file writes, 172513 OS fsyncs
66.75 reads/s, 16384 avg bytes/read, 134.49 writes/s, 134.49 fsyncs/s
-------------------------------------
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 table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 104 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 237726885168
Log flushed up to   237726880386
Pages flushed up to 237724062470
Last checkpoint at  237724062470
0 pending log flushes, 0 pending chkp writes
3473 log i/o's done, 0.95 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 405471232
Dictionary memory allocated 24640
Buffer pool size   8192
Free buffers       0
Database pages     8088
Old database pages 3003
Modified db pages  3448
Percent of dirty pages(LRU & free pages): 42.626
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 2, single page 1
Pages made young 1119, not young 211124188
0.00 youngs/s, 200.24 non-youngs/s
Pages read 452305, created 195449, written 499116
66.75 reads/s, 0.00 creates/s, 66.75 writes/s
Buffer pool hit rate 900 / 1000, young-making rate 0 / 1000 not 298 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8088, unzip_LRU len: 0
I/O sum[27390]:cur[946], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=37451, Main thread ID=139673583502912, state: sleeping
Number of rows inserted 51774178, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

• SEMAPHORES段：检测到两个线程出现了超过400秒的锁等待，分别是等待重做日志回滚段的互斥锁、等待缓冲池数据页的排他锁，核心原因是导入的写入压力超过了当前数据库配置的处理上限。
• TRANSACTIONS段：该LOAD DATA INFILE任务已经运行了1482秒，累计插入5177万条记录，整个导入操作在单个大事务中执行，产生的undo日志量极大，直接加剧了回滚段资源的争用。
• BUFFER POOL AND MEMORY段：当前InnoDB缓冲池仅配置了8192页（约128MB，默认值），已经完全被占满，脏页占比达42%，缓冲池命中率仅90%，远低于正常生产环境99%以上的水平。内存不足导致需要频繁刷脏页、读取磁盘，进一步拖慢导入速度，放大锁等待问题。
• ROW OPERATIONS段：当前实时插入速度已经降到0，说明导入任务已经被资源争用卡住，只是未触发崩溃逻辑，仍在等待资源释放。

• 若当前导入任务未报错退出，可等待其执行完成，不会出现数据损坏问题，仅耗时较长。
• 后续执行同类大型CSV导入任务时，可通过以下配置优化避免同类问题：
  • 临时调大innodb_buffer_pool_size参数，建议设置为物理内存的50%~70%
  • 拆分CSV文件为多个小文件，分批次导入，每批导入完成后手动提交，避免单个事务产生过多的undo/redo日志
  • 导入前临时关闭目标表的非主键索引、外键约束，导入完成后再重建
  • 根据磁盘实际IOPS调整innodb_io_capacity参数，调大innodb_max_dirty_pages_pct到90，提升刷脏页效率

内容的提问来源于stack exchange，提问作者jlos