대용량 가져오기 InnoDB - 오류 로그 항목 분석

대용량 가져오기 InnoDB - 오류 로그 항목 분석

현재 대용량 CSV 파일을 가져오고 있으며, error.log 파일에 들어오는 로그를 이해하려고 합니다.가져오기는 계속 진행되므로 아무것도 아닐 수 있지만 기본 설정으로 error.log 파일에 로그인되어 있고 무엇을 이해해야 할지 모르기 때문에 MariaDB 커뮤니티에 문의하여 무언가를 배울 때가 될 수 있습니다.

로그는 다음과 같습니다.

=====================================
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
============================

저는 이것이 무언가 시간 초과로 인한 출력이라고 믿지만, 저는 "그것"이 무엇일 수 있는지 전혀 모릅니다.수입도 멈추지 않고 계속됩니다.이 로그를 해부하는 데 도움(또는 포인터)을 주시면 감사하겠습니다!

언급URL : https://stackoverflow.com/questions/69398861/large-import-innodb-error-log-entry-analysis