冒昧留言，我看了下。配置文件一和配置文件二分别都是MGR 配置文件三是单机 这个是不是已经排除掉了。MGR ...
我目前都是在配置文件三的基础上测的 Yep. 发表于 2025-3-18 09:24
我修改了innodb_flush_log_at_trx_commit=0，虚拟机内存16G，innodb_buffer_pool_size由10G改为了12G，再 ...
CPU 的%user和%sys非常高，需要再深入排查下原因，perf尽量搞定吧
另外，再在数据库中多执行几次 show engine innodb status\G ，也贴结果 yejr 发表于 2025-3-18 14:22
CPU 的%user和%sys非常高，需要再深入排查下原因，perf尽量搞定吧
另外，再在数据库中多执行几次 show e ...
导入前：
(Tue Mar 18 08:46:09 2025)[(none)]> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-03-18 14:31:31 140459658831616 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 26 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3949 srv_active, 0 srv_shutdown, 62058 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 43396
OS WAIT ARRAY INFO: signal count 53258
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 47156
Purge done for trx's n:o < 47156 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421936924462440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924469224, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924461592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924460744, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1051 OS file reads, 6185759 OS file writes, 398189 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 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 2656321, node heap has 0 buffer(s)
Hash table size 2656321, node heap has 12 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 0 buffer(s)
Hash table size 2656321, node heap has 5 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 9751095836
Log buffer assigned up to 9751095836
Log buffer completed up to 9751095836
Log written up to 9751095836
Log flushed up to 9751095836
Added dirty pages up to 9751095836
Pages flushed up to 9751095836
Last checkpoint at 9751095836
Log minimum file id is 118
Log maximum file id is 145
Modified age no less than 9751095836
Checkpoint age 0
Max checkpoint age 1806985216
5258103 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2818205
Buffer pool size 786336
Buffer pool size, bytes 12883329024
Free buffers 631767
Database pages 154546
Old database pages 56886
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1009, created 153537, written 724965
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 154546, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77973
Database pages 20316
Old database pages 7479
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108, created 20208, written 94025
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20316, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79437
Database pages 18853
Old database pages 6940
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 18767, written 88913
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18853, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77935
Database pages 20355
Old database pages 7493
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 85, created 20270, written 96903
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20355, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79061
Database pages 19229
Old database pages 7078
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227, created 19002, written 88464
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 19229, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79551
Database pages 18737
Old database pages 6896
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 18669, written 88488
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18737, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 80284
Database pages 18005
Old database pages 6626
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58, created 17947, written 83096
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18005, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 78293
Database pages 19996
Old database pages 7361
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 19910, written 94461
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 19996, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79233
Database pages 19055
Old database pages 7013
Modified db pages0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 291, created 18764, written 90615
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 19055, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
Process ID=39345, Main thread ID=140449788454656 , state=sleeping
Number of rows inserted 6006421, updated 17499, deleted 4002307, read 19069758
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 6515, updated 6012, deleted 55, read 18243
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.08 sec)
ERROR:
No query specified
导入过程中查询一：
(Tue Mar 18 14:38:40 2025)[(none)]> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-03-18 14:43:08 140459658831616 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4335 srv_active, 0 srv_shutdown, 62369 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 45721
OS WAIT ARRAY INFO: signal count 55296
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 47705
Purge done for trx's n:o < 47702 undo n:o < 0 state: running but idle
History list length 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421936924462440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924461592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924460744, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 47704, ACTIVE 1 sec
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1578
MySQL thread id 73, OS thread handle 140460799682304, query id 15182338 192.168.200.15 root waiting for handler commit
insert into dlsoftdb.teleindication values('yx-123-2417','YX-2417',0,'1','1',123,2417,400,'',0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,0,0,'','','',0,0,0,0,'','','','','','','','','','','','',0,'','','' )
--------
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: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1051 OS file reads, 7023134 OS file writes, 454369 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1510.86 writes/s, 110.82 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 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
1.89 hash searches/s, 2390.51 non-hash searches/s
---
LOG
---
Log sequence number 11058810800
Log buffer assigned up to 11058810800
Log buffer completed up to 11058810800
Log written up to 11058810800
Log flushed up to 11058128052
Added dirty pages up to 11058810800
Pages flushed up to 10422956987
Last checkpoint at 10421433957
Log minimum file id is 145
Log maximum file id is 164
Modified age no less than 634633877
Checkpoint age 637376843
Max checkpoint age 1806985216
5969175 log i/o's done, 1286.95 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2818205
Buffer pool size 786336
Buffer pool size, bytes 12883329024
Free buffers 631736
Database pages 154589
Old database pages 56905
Modified db pages69325
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1009, created 153580, written 823971
0.00 reads/s, 0.00 creates/s, 207.80 writes/s
Buffer pool hit rate 1000 / 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: 154589, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77975
Database pages 20316
Old database pages 7479
Modified db pages9022
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108, created 20208, written 106744
0.00 reads/s, 0.00 creates/s, 25.66 writes/s
Buffer pool hit rate 1000 / 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: 20316, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79438
Database pages 18853
Old database pages 6940
Modified db pages8457
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 18767, written 100851
0.00 reads/s, 0.00 creates/s, 43.78 writes/s
Buffer pool hit rate 1000 / 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: 18853, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77928
Database pages 20362
Old database pages 7497
Modified db pages9122
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 85, created 20277, written 110073
0.00 reads/s, 0.00 creates/s, 18.11 writes/s
Buffer pool hit rate 1000 / 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: 20362, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79062
Database pages 19229
Old database pages 7078
Modified db pages8572
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227, created 19002, written 101065
0.00 reads/s, 0.00 creates/s, 29.34 writes/s
Buffer pool hit rate 1000 / 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: 19229, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79553
Database pages 18737
Old database pages 6896
Modified db pages8556
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 18669, written 100377
0.00 reads/s, 0.00 creates/s, 31.79 writes/s
Buffer pool hit rate 1000 / 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: 18737, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 80286
Database pages 18005
Old database pages 6626
Modified db pages7926
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58, created 17947, written 95330
0.00 reads/s, 0.00 creates/s, 25.10 writes/s
Buffer pool hit rate 1000 / 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: 18005, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 78289
Database pages 20002
Old database pages 7364
Modified db pages9214
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 19916, written 107200
0.00 reads/s, 0.00 creates/s, 12.40 writes/s
Buffer pool hit rate 1000 / 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: 20002, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79205
Database pages 19085
Old database pages 7025
Modified db pages8456
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 291, created 18794, written 102331
0.00 reads/s, 0.00 creates/s, 21.63 writes/s
Buffer pool hit rate 1000 / 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: 19085, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
1 RW transactions active inside InnoDB
Process ID=39345, Main thread ID=140449788454656 , state=sleeping
Number of rows inserted 6081396, updated 17499, deleted 6005404, read 21573183
2091.36 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 6515, updated 6012, deleted 55, read 18631
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
导入过程中查询二：
(Tue Mar 18 14:43:08 2025)[(none)]> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-03-18 14:47:04 140459658831616 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4571 srv_active, 0 srv_shutdown, 62369 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 48229
OS WAIT ARRAY INFO: signal count 57437
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 47990
Purge done for trx's n:o < 47986 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421936924462440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924461592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924460744, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 47989, ACTIVE 1 sec
mysql tables in use 2, locked 2
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1549
MySQL thread id 73, OS thread handle 140460799682304, query id 15627496 192.168.200.15 root update
insert into dlsoftdb.telemetering values('yc-1-1040','YC-1040',0,'1','1',0,0,'',1,1040,1,1,1,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','',0,0,0,0,'','','','','','','',0,0,'','','' )
--------
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: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1051 OS file reads, 7397301 OS file writes, 494330 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1682.59 writes/s, 0.00 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 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
0.00 hash searches/s, 508000.00 non-hash searches/s
---
LOG
---
Log sequence number 11605861220
Log buffer assigned up to 11605861220
Log buffer completed up to 11605861220
Log written up to 11605861220
Log flushed up to 11605079956
Added dirty pages up to 11605861220
Pages flushed up to 11212772258
Last checkpoint at 11212772258
Log minimum file id is 145
Log maximum file id is 172
Modified age no less than 393088962
Checkpoint age 393088962
Max checkpoint age 1806985216
6244486 log i/o's done, 280.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2818205
Buffer pool size 786336
Buffer pool size, bytes 12883329024
Free buffers 631737
Database pages 154589
Old database pages 56905
Modified db pages22244
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1009, created 153580, written 902450
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 154589, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77975
Database pages 20316
Old database pages 7479
Modified db pages3447
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108, created 20208, written 117001
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20316, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79438
Database pages 18853
Old database pages 6940
Modified db pages2166
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 18767, written 110197
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18853, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77928
Database pages 20362
Old database pages 7497
Modified db pages3271
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 85, created 20277, written 120254
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20362, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79062
Database pages 19229
Old database pages 7078
Modified db pages2621
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227, created 19002, written 110717
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19229, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79554
Database pages 18737
Old database pages 6896
Modified db pages2244
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 18669, written 110274
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18737, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 80286
Database pages 18005
Old database pages 6626
Modified db pages2580
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58, created 17947, written 104363
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18005, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 78289
Database pages 20002
Old database pages 7364
Modified db pages2961
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 19916, written 118004
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20002, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79205
Database pages 19085
Old database pages 7025
Modified db pages2954
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 291, created 18794, written 111640
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19085, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
1 RW transactions active inside InnoDB
Process ID=39345, Main thread ID=140449788454656 , state=sleeping
Number of rows inserted 6517367, updated 17499, deleted 6005704, read 21573483
452000.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 6515, updated 6012, deleted 55, read 18631
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
导入过程中查询三：
(Tue Mar 18 14:51:01 2025)[(none)]> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-03-18 14:51:06 140459658831616 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4813 srv_active, 0 srv_shutdown, 62369 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 49345
OS WAIT ARRAY INFO: signal count 58361
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 48267
Purge done for trx's n:o < 48254 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421936924462440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924461592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924460744, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 48266, ACTIVE 1 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1921
MySQL thread id 73, OS thread handle 140460799682304, query id 16481547 192.168.200.15 root closing tables
insert into dlsoftdb.telemetering values('yc-15-6777','YC-6777',0,'1','1',0,0,'',15,6777,1,1,1,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','',0,0,0,0,'','','','','','','',0,0,'','','' )
--------
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: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1051 OS file reads, 7756198 OS file writes, 509661 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1529.58 writes/s, 49.74 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 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
0.00 hash searches/s, 2204.40 non-hash searches/s
---
LOG
---
Log sequence number 12087728664
Log buffer assigned up to 12087728664
Log buffer completed up to 12087728664
Log written up to 12087728664
Log flushed up to 12086951391
Added dirty pages up to 12087728664
Pages flushed up to 11694063902
Last checkpoint at 11693341289
Log minimum file id is 170
Log maximum file id is 180
Modified age no less than 387300509
Checkpoint age 394387375
Max checkpoint age 1806985216
6567917 log i/o's done, 1558.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2818205
Buffer pool size 786336
Buffer pool size, bytes 12883329024
Free buffers 631737
Database pages 154589
Old database pages 56905
Modified db pages21550
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1009, created 153580, written 930148
0.00 reads/s, 0.00 creates/s, 83.05 writes/s
Buffer pool hit rate 1000 / 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: 154589, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77975
Database pages 20316
Old database pages 7479
Modified db pages2875
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108, created 20208, written 121027
0.00 reads/s, 0.00 creates/s, 9.13 writes/s
Buffer pool hit rate 1000 / 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: 20316, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79438
Database pages 18853
Old database pages 6940
Modified db pages2602
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 18767, written 113485
0.00 reads/s, 0.00 creates/s, 3.19 writes/s
Buffer pool hit rate 1000 / 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: 18853, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77928
Database pages 20362
Old database pages 7497
Modified db pages2836
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 85, created 20277, written 123977
0.00 reads/s, 0.00 creates/s, 5.02 writes/s
Buffer pool hit rate 1000 / 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: 20362, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79062
Database pages 19229
Old database pages 7078
Modified db pages2374
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227, created 19002, written 114135
0.00 reads/s, 0.00 creates/s, 22.36 writes/s
Buffer pool hit rate 1000 / 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: 19229, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79554
Database pages 18737
Old database pages 6896
Modified db pages2649
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 18669, written 113188
0.00 reads/s, 0.00 creates/s, 12.78 writes/s
Buffer pool hit rate 1000 / 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: 18737, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 80286
Database pages 18005
Old database pages 6626
Modified db pages2299
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58, created 17947, written 107587
0.00 reads/s, 0.00 creates/s, 14.60 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18005, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 78289
Database pages 20002
Old database pages 7364
Modified db pages2815
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 19916, written 121489
0.00 reads/s, 0.00 creates/s, 3.19 writes/s
Buffer pool hit rate 1000 / 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: 20002, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79205
Database pages 19085
Old database pages 7025
Modified db pages3100
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 291, created 18794, written 115260
0.00 reads/s, 0.00 creates/s, 12.78 writes/s
Buffer pool hit rate 1000 / 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: 19085, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
1 RW transactions active inside InnoDB
Process ID=39345, Main thread ID=140449788454656 , state=sleeping
Number of rows inserted 6943739, updated 17499, deleted 6005704, read 21573483
1959.52 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 6515, updated 6012, deleted 55, read 18631
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
导入过程中查询四：
(Tue Mar 18 14:54:32 2025)[(none)]>show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-03-18 14:54:47 140459658831616 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5034 srv_active, 0 srv_shutdown, 62369 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 50251
OS WAIT ARRAY INFO: signal count 59105
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 48488
Purge done for trx's n:o < 48485 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421936924462440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924461592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924460744, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 48487, ACTIVE 1 sec
mysql tables in use 2, locked 2
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 822
MySQL thread id 73, OS thread handle 140460799682304, query id 17252538 192.168.200.15 root update
insert into dlsoftdb.telemetering values('yc-197-2187','YC-2187',0,'1','1',0,0,'',197,2187,1,1,1,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','',0,0,0,0,'','','','','','','',0,0,'','','' )
--------
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: complete io for buf page (write thread)
Pending normal aio reads: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 1
1051 OS file reads, 8076752 OS file writes, 522236 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1490.02 writes/s, 60.47 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 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
0.44 hash searches/s, 2117.70 non-hash searches/s
---
LOG
---
Log sequence number 12522714945
Log buffer assigned up to 12522714945
Log buffer completed up to 12522714945
Log written up to 12522714945
Log flushed up to 12522710485
Added dirty pages up to 12522714945
Pages flushed up to 12097176539
Last checkpoint at 12097176539
Log minimum file id is 170
Log maximum file id is 186
Modified age no less than 425411954
Checkpoint age 425538406
Max checkpoint age 1806985216
6859829 log i/o's done, 1342.75 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2818205
Buffer pool size 786336
Buffer pool size, bytes 12883329024
Free buffers 631737
Database pages 154589
Old database pages 56905
Modified db pages23628
Pending reads 0
Pending writes: LRU 0, flush list 28, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1009, created 153580, written 952417
0.00 reads/s, 0.00 creates/s, 107.50 writes/s
Buffer pool hit rate 1000 / 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: 154589, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77975
Database pages 20316
Old database pages 7479
Modified db pages2745
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108, created 20208, written 123972
0.00 reads/s, 0.00 creates/s, 10.50 writes/s
Buffer pool hit rate 1000 / 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: 20316, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79438
Database pages 18853
Old database pages 6940
Modified db pages2991
Pending reads 0
Pending writes: LRU 0, flush list 7, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 18767, written 116223
0.00 reads/s, 0.00 creates/s, 14.02 writes/s
Buffer pool hit rate 1000 / 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: 18853, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77928
Database pages 20362
Old database pages 7497
Modified db pages3307
Pending reads 0
Pending writes: LRU 0, flush list 7, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 85, created 20277, written 126855
0.00 reads/s, 0.00 creates/s, 5.82 writes/s
Buffer pool hit rate 1000 / 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: 20362, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79062
Database pages 19229
Old database pages 7078
Modified db pages2819
Pending reads 0
Pending writes: LRU 0, flush list 7, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227, created 19002, written 116582
0.00 reads/s, 0.00 creates/s, 15.03 writes/s
Buffer pool hit rate 1000 / 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: 19229, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79554
Database pages 18737
Old database pages 6896
Modified db pages2479
Pending reads 0
Pending writes: LRU 0, flush list 7, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 18669, written 115976
0.00 reads/s, 0.00 creates/s, 18.01 writes/s
Buffer pool hit rate 1000 / 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: 18737, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 80286
Database pages 18005
Old database pages 6626
Modified db pages3071
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58, created 17947, written 109951
0.00 reads/s, 0.00 creates/s, 10.89 writes/s
Buffer pool hit rate 1000 / 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: 18005, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 78289
Database pages 20002
Old database pages 7364
Modified db pages2820
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 19916, written 124432
0.00 reads/s, 0.00 creates/s, 20.31 writes/s
Buffer pool hit rate 1000 / 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: 20002, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79205
Database pages 19085
Old database pages 7025
Modified db pages3396
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 291, created 18794, written 118426
0.00 reads/s, 0.00 creates/s, 12.91 writes/s
Buffer pool hit rate 1000 / 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: 19085, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
1 RW transactions active inside InnoDB
Process ID=39345, Main thread ID=140449788454656 , state=sleeping
Number of rows inserted 7328640, updated 17499, deleted 6005704, read 21573483
1882.23 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 6515, updated 6012, deleted 55, read 18631
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
查询五：
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421936924462440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924469224, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924461592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924460744, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1051 OS file reads, 8654935 OS file writes, 547287 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 983.13 writes/s, 78.25 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 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
1.62 hash searches/s, 1200.34 non-hash searches/s
---
LOG
---
Log sequence number 13292052573
Log buffer assigned up to 13292052573
Log buffer completed up to 13292052573
Log written up to 13292052573
Log flushed up to 13292052573
Added dirty pages up to 13292052573
Pages flushed up to 12889903764
Last checkpoint at 12889452726
Log minimum file id is 170
Log maximum file id is 198
Modified age no less than 402148809
Checkpoint age 402599847
Max checkpoint age 1806985216
7379938 log i/o's done, 812.23 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2818205
Buffer pool size 786336
Buffer pool size, bytes 12883329024
Free buffers 631729
Database pages 154597
Old database pages 56908
Modified db pages21397
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1009, created 153588, written 997735
0.00 reads/s, 0.00 creates/s, 138.19 writes/s
Buffer pool hit rate 1000 / 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: 154597, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77975
Database pages 20316
Old database pages 7479
Modified db pages3203
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108, created 20208, written 129852
0.00 reads/s, 0.00 creates/s, 12.17 writes/s
Buffer pool hit rate 1000 / 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: 20316, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79438
Database pages 18853
Old database pages 6940
Modified db pages2468
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 18767, written 121781
0.00 reads/s, 0.00 creates/s, 16.48 writes/s
Buffer pool hit rate 1000 / 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: 18853, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77928
Database pages 20362
Old database pages 7497
Modified db pages2395
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 85, created 20277, written 132969
0.00 reads/s, 0.00 creates/s, 15.49 writes/s
Buffer pool hit rate 1000 / 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: 20362, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79054
Database pages 19237
Old database pages 7081
Modified db pages2671
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227, created 19010, written 122081
0.00 reads/s, 0.00 creates/s, 20.41 writes/s
Buffer pool hit rate 1000 / 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: 19237, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79554
Database pages 18737
Old database pages 6896
Modified db pages3131
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 18669, written 121275
0.00 reads/s, 0.00 creates/s, 19.88 writes/s
Buffer pool hit rate 1000 / 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: 18737, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 80286
Database pages 18005
Old database pages 6626
Modified db pages2332
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58, created 17947, written 115619
0.00 reads/s, 0.00 creates/s, 14.61 writes/s
Buffer pool hit rate 1000 / 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: 18005, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 78289
Database pages 20002
Old database pages 7364
Modified db pages2275
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 19916, written 129850
0.00 reads/s, 0.00 creates/s, 17.93 writes/s
Buffer pool hit rate 1000 / 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: 20002, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79205
Database pages 19085
Old database pages 7025
Modified db pages2922
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 291, created 18794, written 124308
0.00 reads/s, 0.00 creates/s, 21.21 writes/s
Buffer pool hit rate 1000 / 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: 19085, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
Process ID=39345, Main thread ID=140449788454656 , state=sleeping
Number of rows inserted 8009821, updated 17499, deleted 6005707, read 21573486
1066.65 inserts/s, 0.00 updates/s, 0.12 deletes/s, 0.12 reads/s
Number of system rows inserted 6515, updated 6012, deleted 55, read 18631
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
导入结束后查询：
(Tue Mar 18 15:01:30 2025)[(none)]>show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-03-18 15:01:50 140459658831616 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5427 srv_active, 0 srv_shutdown, 62399 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 52168
OS WAIT ARRAY INFO: signal count 60674
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 48881
Purge done for trx's n:o < 48879 undo n:o < 0 state: running but idle
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421936924462440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924469224, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924461592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421936924460744, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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: , aio writes: ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1051 OS file reads, 8660075 OS file writes, 549590 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 259.31 writes/s, 116.19 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 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
Hash table size 2656321, node heap has 2 buffer(s)
Hash table size 2656321, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 13292052573
Log buffer assigned up to 13292052573
Log buffer completed up to 13292052573
Log written up to 13292052573
Log flushed up to 13292052573
Added dirty pages up to 13292052573
Pages flushed up to 12949820851
Last checkpoint at 12949369610
Log minimum file id is 170
Log maximum file id is 198
Modified age no less than 342231722
Checkpoint age 342682963
Max checkpoint age 1806985216
7379938 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2818205
Buffer pool size 786336
Buffer pool size, bytes 12883329024
Free buffers 631729
Database pages 154597
Old database pages 56908
Modified db pages17397
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1009, created 153588, written 1001735
0.00 reads/s, 0.00 creates/s, 201.80 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 154597, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77975
Database pages 20316
Old database pages 7479
Modified db pages2703
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108, created 20208, written 130352
0.00 reads/s, 0.00 creates/s, 25.23 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20316, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79438
Database pages 18853
Old database pages 6940
Modified db pages1968
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 18767, written 122281
0.00 reads/s, 0.00 creates/s, 25.23 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18853, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 77928
Database pages 20362
Old database pages 7497
Modified db pages1895
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 85, created 20277, written 133469
0.00 reads/s, 0.00 creates/s, 25.23 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20362, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79054
Database pages 19237
Old database pages 7081
Modified db pages2171
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227, created 19010, written 122581
0.00 reads/s, 0.00 creates/s, 25.23 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 19237, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79554
Database pages 18737
Old database pages 6896
Modified db pages2631
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 18669, written 121775
0.00 reads/s, 0.00 creates/s, 25.23 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18737, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 80286
Database pages 18005
Old database pages 6626
Modified db pages1832
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58, created 17947, written 116119
0.00 reads/s, 0.00 creates/s, 25.23 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 18005, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 78289
Database pages 20002
Old database pages 7364
Modified db pages1775
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 86, created 19916, written 130350
0.00 reads/s, 0.00 creates/s, 25.23 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20002, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 98292
Buffer pool size, bytes 1610416128
Free buffers 79205
Database pages 19085
Old database pages 7025
Modified db pages2422
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 291, created 18794, written 124808
0.00 reads/s, 0.00 creates/s, 25.23 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 19085, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
Process ID=39345, Main thread ID=140449788454656 , state=sleeping
Number of rows inserted 8009821, updated 17499, deleted 6005707, read 21573486
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 6515, updated 6012, deleted 55, read 18631
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
Yep. 发表于 2025-3-18 15:09
导入前：
导入过程中查询一：
不要在虚机+凝思环境下跑GreatSQL，改成在物理机环境下测试，先排除虚机包了一层可能受到的影响 yejr 发表于 2025-3-18 15:54
不要在虚机+凝思环境下跑GreatSQL，改成在物理机环境下测试，先排除虚机包了一层可能受到的影响 ...
好的谢谢叶老师，我得协调下机器，另外您可以看下我的安装过程吗，我想使用systemd管理，但是按照用户手册的这个方法：使用systemd管理GreatSQL，配置如下：
Description=GreatSQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
WantedBy=multi-user.target
# some limits
# file size
LimitFSIZE=infinity
# cpu time
LimitCPU=infinity
# virtual memory size
LimitAS=infinity
# open files
LimitNOFILE=65535
# processes/threads
LimitNPROC=65535
# locked memory
LimitMEMLOCK=infinity
# total threads (user+kernel)
TasksMax=infinity
TasksAccounting=false
User=mysql
Group=mysql
#如果是GreatSQL 5.7版本，此处需要改成simple模式，否则可能服务启用异常
#如果是GreatSQL 8.0版本则可以使用notify模式
#Type=simple
Type=notify
TimeoutSec=0
PermissionsStartOnly=true
ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd
ExecStart=/usr/local/mysql_64/bin/mysqld $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=falsemysqld_pre_system中旧的安装包名称也已改为了mysql，systemctl daemon-reload没有报错，但是依旧不能使用systemctl start greatsql，提示Failed to start greatsql.service: Unit greatsql.service failed to load: No such file or directory.
我觉得安装手册中对于像凝思系统系列的安装不够清晰，安装过程中遇到很多与安装说明不一样的指令、目录和预期结果
Yep. 发表于 2025-3-18 16:44
好的谢谢叶老师，我得协调下机器，另外您可以看下我的安装过程吗，我想使用systemd管理，但是按照用户手 ...
执行systemctl status greatsql 或 journalctl -ex 查看具体信息才知道启动失败的原因 yejr 发表于 2025-3-18 16:59
执行systemctl status greatsql 或 journalctl -ex 查看具体信息才知道启动失败的原因 ...
以下是执行结果
root@server:/etc/mysql# systemctl daemon-reload
root@server:/etc/mysql# systemctl start mysqld.service
Job for mysqld.service failed. See 'systemctl status mysqld.service' and 'journalctl -xn' for details.
root@server:/etc/mysql# systemctl status mysqld.service
● mysqld.service - GreatSQL Server
Loaded: loaded (/lib/systemd/system/mysqld.service; disabled)
Active: failed (Result: exit-code) since 三 2025-03-19 22:55:54 CST; 14s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 11166 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 11136 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 11166 (code=exited, status=1/FAILURE)
Status: "Server shutdown complete"
root@server:/etc/mysql# journalctl -xn
No journal files were found.
root@server:/etc/mysql# journalctl -ex
No journal files were found.
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
...skipping...
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
Yep. 发表于 2025-3-19 15:00
以下是执行结果
谢谢已经解决了，是有进程在所以没办法管理，关闭后可以正常使用systemd
页:
1
[2]