GreatSQL社区

搜索

[已解决] mysql8.0.22版本undo文件过大,如何快速清理?

246 14 2024-9-14 16:11
问题:mysql8.0.22版本的数据库undo文件很大,如下,按照https://greatsql.cn/forum.php?mo ... d=46&highlight=undo的步骤操作了,半小时过去了,文件还是没有清理。


1.5T        undo_001
3.8T        undo_002

操作步骤:


CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';

ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;

SET GLOBAL innodb_undo_log_truncate=ON;

FLUSH TABLES;

BEGIN;
COMMIT;


select @@innodb_purge_rseg_truncate_frequency;
set global innodb_purge_rseg_truncate_frequency=32;


不分参数入下:

mysql> show variables like '%undo%';\G
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |
+--------------------------+------------+
5 rows in set (0.01 sec)

ERROR:
No query specified





mysql> select * from information_schema.INNODB_TABLESPACES where  name like '%undo%'\G;
*************************** 1. row ***************************
         SPACE: 4294964739
          NAME: innodb_undo_001
          FLAG: 0
    ROW_FORMAT: Undo
     PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Undo
FS_BLOCK_SIZE: 4096
     FILE_SIZE: 1549099073536
ALLOCATED_SIZE: 1549102735360
SERVER_VERSION: 8.0.22
SPACE_VERSION: 1
    ENCRYPTION: N
         STATE: inactive
*************************** 2. row ***************************
         SPACE: 4294939465
          NAME: innodb_undo_002
          FLAG: 0
    ROW_FORMAT: Undo
     PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Undo
FS_BLOCK_SIZE: 4096
     FILE_SIZE: 4175870033920
ALLOCATED_SIZE: 4175880052736
SERVER_VERSION: 8.0.22
SPACE_VERSION: 1
    ENCRYPTION: N
         STATE: active
*************************** 3. row ***************************
         SPACE: 4294967277
          NAME: undo_003
          FLAG: 0
    ROW_FORMAT: Undo
     PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Undo
FS_BLOCK_SIZE: 4096
     FILE_SIZE: 3699376128
ALLOCATED_SIZE: 3699388416
SERVER_VERSION: 8.0.22
SPACE_VERSION: 1
    ENCRYPTION: N
         STATE: active
3 rows in set (0.02 sec)






mysql> show variables like '%truncate%'; \G  
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 16    |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+
2 rows in set (0.00 sec)



经过差不多1小时了,innodb_undo_001的状态还是inactive,并没有变成empty,所以这个文件也没有回收处理。该如何去操作才能快速收回空间,或者是说还要继续等待?
怎么看他内部是在进行回收操作 ?



全部回复(14)
yejr 2024-9-14 16:14:07
猜测还有大事务未完成(提交/回滚),详情参考 https://greatsql.cn/docs/8.0.32- ... 8%E7%A9%BA%E9%97%B4

执行 show engine innodb status\G ,把结果贴上来看看吧
fengzhencai 2024-9-14 16:41:45
yejr 发表于 2024-9-14 16:14
猜测还有大事务未完成(提交/回滚),详情参考 https://greatsql.cn/docs/8.0.32- ... 8%E7%A9%BA%E9%97%B4 ...

=====================================
2024-09-14 16:39:08 0x7f2ebbfff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 112 srv_active, 0 srv_shutdown, 140 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4934905
OS WAIT ARRAY INFO: signal count 1401905
RW-shared spins 1333844, rounds 2418586, OS waits 1068173
RW-excl spins 618581, rounds 3660015, OS waits 128245
RW-sx spins 4068, rounds 36886, OS waits 455
Spin rounds per wait: 1.81 RW-shared, 5.92 RW-excl, 9.07 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 86724706924
Purge done for trx's n < 74690025805 undo n < 0 state: running
History list length 6009890459
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421314869962040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869961144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869960248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869959352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869958456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869957560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869956664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869954872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869953976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869953080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869952184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869951288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869950392, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869949496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869948600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869947704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869946808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869945912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869945016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869944120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869940536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869939640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869930680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869931576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421314869929784, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 86724706923, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 5, OS thread handle 139839874615040, query id 0 Queueing master event to the relay log
---TRANSACTION 86724706922, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 14, OS thread handle 139839706724096, query id 235970 Applying batch of row changes (update)
---TRANSACTION 86724706919, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 139839874025216, query id 235968 Applying batch of row changes (update)
---TRANSACTION 86724706903, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 3
MySQL thread id 15, OS thread handle 139839706429184, query id 235962 Applying batch of row changes (write)
---TRANSACTION 86724706872, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 20, OS thread handle 139839308945152, query id 235941 Applying batch of row changes (write)
---TRANSACTION 86724706847, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 19, OS thread handle 139839572248320, query id 235931 Applying batch of row changes (write)
---TRANSACTION 86724706835, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 139839573133056, query id 235925 Applying batch of row changes (update)
---TRANSACTION 86724706797, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 8
MySQL thread id 13, OS thread handle 139839707019008, query id 235905 Applying batch of row changes (write)
---TRANSACTION 86724706746, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
81 lock struct(s), heap size 8400, 0 row lock(s)
MySQL thread id 9, OS thread handle 139839841048320, query id 235873 Applying batch of row changes (delete)
---TRANSACTION 86724679636, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 448
MySQL thread id 35, OS thread handle 139839304234752, query id 235764 Applying batch of row changes (write)
---TRANSACTION 86724678628, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
420 lock struct(s), heap size 57552, 419 row lock(s), undo log entries 419
MySQL thread id 10, OS thread handle 139839840753408, query id 235260 Applying batch of row changes (write)
---TRANSACTION 86722897729, ACTIVE 49 sec unlock_row
mysql tables in use 1, locked 1
196402 lock struct(s), heap size 25321680, 2 row lock(s)
MySQL thread id 21, OS thread handle 139805547558656, query id 0 Compressing gtid_executed table
--------
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 (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (read thread)
I/O thread 11 state: waiting for completed aio requests (read thread)
I/O thread 12 state: waiting for completed aio requests (read thread)
I/O thread 13 state: waiting for completed aio requests (read thread)
I/O thread 14 state: waiting for completed aio requests (read thread)
I/O thread 15 state: waiting for completed aio requests (read thread)
I/O thread 16 state: waiting for completed aio requests (read thread)
I/O thread 17 state: waiting for completed aio requests (read thread)
I/O thread 18 state: waiting for completed aio requests (read thread)
I/O thread 19 state: waiting for completed aio requests (read thread)
I/O thread 20 state: waiting for completed aio requests (read thread)
I/O thread 21 state: waiting for completed aio requests (read thread)
I/O thread 22 state: waiting for completed aio requests (read thread)
I/O thread 23 state: waiting for completed aio requests (read thread)
I/O thread 24 state: waiting for completed aio requests (read thread)
I/O thread 25 state: waiting for completed aio requests (read thread)
I/O thread 26 state: waiting for completed aio requests (read thread)
I/O thread 27 state: waiting for completed aio requests (read thread)
I/O thread 28 state: waiting for completed aio requests (read thread)
I/O thread 29 state: waiting for completed aio requests (read thread)
I/O thread 30 state: waiting for completed aio requests (read thread)
I/O thread 31 state: waiting for completed aio requests (read thread)
I/O thread 32 state: waiting for completed aio requests (read thread)
I/O thread 33 state: waiting for completed aio requests (read thread)
I/O thread 34 state: waiting for completed aio requests (read thread)
I/O thread 35 state: waiting for completed aio requests (read thread)
I/O thread 36 state: waiting for completed aio requests (read thread)
I/O thread 37 state: waiting for completed aio requests (read thread)
I/O thread 38 state: waiting for completed aio requests (read thread)
I/O thread 39 state: waiting for completed aio requests (read thread)
I/O thread 40 state: waiting for completed aio requests (read thread)
I/O thread 41 state: waiting for completed aio requests (read thread)
I/O thread 42 state: waiting for completed aio requests (read thread)
I/O thread 43 state: waiting for completed aio requests (read thread)
I/O thread 44 state: waiting for completed aio requests (read thread)
I/O thread 45 state: waiting for completed aio requests (read thread)
I/O thread 46 state: waiting for completed aio requests (read thread)
I/O thread 47 state: waiting for completed aio requests (read thread)
I/O thread 48 state: waiting for completed aio requests (read thread)
I/O thread 49 state: waiting for completed aio requests (read thread)
I/O thread 50 state: waiting for completed aio requests (read thread)
I/O thread 51 state: waiting for completed aio requests (read thread)
I/O thread 52 state: waiting for completed aio requests (read thread)
I/O thread 53 state: waiting for completed aio requests (read thread)
I/O thread 54 state: waiting for completed aio requests (read thread)
I/O thread 55 state: waiting for completed aio requests (read thread)
I/O thread 56 state: waiting for completed aio requests (read thread)
I/O thread 57 state: waiting for completed aio requests (read thread)
I/O thread 58 state: waiting for completed aio requests (read thread)
I/O thread 59 state: waiting for completed aio requests (read thread)
I/O thread 60 state: waiting for completed aio requests (read thread)
I/O thread 61 state: waiting for completed aio requests (read thread)
I/O thread 62 state: waiting for completed aio requests (read thread)
I/O thread 63 state: waiting for completed aio requests (read thread)
I/O thread 64 state: waiting for completed aio requests (read thread)
I/O thread 65 state: waiting for completed aio requests (read thread)
I/O thread 66 state: waiting for completed aio requests (write thread)
I/O thread 67 state: waiting for completed aio requests (write thread)
I/O thread 68 state: waiting for completed aio requests (write thread)
I/O thread 69 state: waiting for completed aio requests (write thread)
I/O thread 70 state: waiting for completed aio requests (write thread)
I/O thread 71 state: waiting for completed aio requests (write thread)
I/O thread 72 state: waiting for completed aio requests (write thread)
I/O thread 73 state: waiting for completed aio requests (write thread)
I/O thread 74 state: waiting for completed aio requests (write thread)
I/O thread 75 state: waiting for completed aio requests (write thread)
I/O thread 76 state: waiting for completed aio requests (write thread)
I/O thread 77 state: waiting for completed aio requests (write thread)
I/O thread 78 state: waiting for completed aio requests (write thread)
I/O thread 79 state: waiting for completed aio requests (write thread)
I/O thread 80 state: waiting for completed aio requests (write thread)
I/O thread 81 state: waiting for completed aio requests (write thread)
I/O thread 82 state: waiting for completed aio requests (write thread)
I/O thread 83 state: waiting for completed aio requests (write thread)
I/O thread 84 state: waiting for completed aio requests (write thread)
I/O thread 85 state: waiting for completed aio requests (write thread)
I/O thread 86 state: waiting for completed aio requests (write thread)
I/O thread 87 state: waiting for completed aio requests (write thread)
I/O thread 88 state: waiting for completed aio requests (write thread)
I/O thread 89 state: waiting for completed aio requests (write thread)
I/O thread 90 state: waiting for completed aio requests (write thread)
I/O thread 91 state: waiting for completed aio requests (write thread)
I/O thread 92 state: waiting for completed aio requests (write thread)
I/O thread 93 state: waiting for completed aio requests (write thread)
I/O thread 94 state: waiting for completed aio requests (write thread)
I/O thread 95 state: waiting for completed aio requests (write thread)
I/O thread 96 state: waiting for completed aio requests (write thread)
I/O thread 97 state: waiting for completed aio requests (write thread)
I/O thread 98 state: waiting for completed aio requests (write thread)
I/O thread 99 state: waiting for completed aio requests (write thread)
I/O thread 100 state: waiting for completed aio requests (write thread)
I/O thread 101 state: waiting for completed aio requests (write thread)
I/O thread 102 state: waiting for completed aio requests (write thread)
I/O thread 103 state: waiting for completed aio requests (write thread)
I/O thread 104 state: waiting for completed aio requests (write thread)
I/O thread 105 state: waiting for completed aio requests (write thread)
I/O thread 106 state: waiting for completed aio requests (write thread)
I/O thread 107 state: waiting for completed aio requests (write thread)
I/O thread 108 state: waiting for completed aio requests (write thread)
I/O thread 109 state: waiting for completed aio requests (write thread)
I/O thread 110 state: waiting for completed aio requests (write thread)
I/O thread 111 state: waiting for completed aio requests (write thread)
I/O thread 112 state: waiting for completed aio requests (write thread)
I/O thread 113 state: waiting for completed aio requests (write thread)
I/O thread 114 state: waiting for completed aio requests (write thread)
I/O thread 115 state: waiting for completed aio requests (write thread)
I/O thread 116 state: waiting for completed aio requests (write thread)
I/O thread 117 state: waiting for completed aio requests (write thread)
I/O thread 118 state: waiting for completed aio requests (write thread)
I/O thread 119 state: waiting for completed aio requests (write thread)
I/O thread 120 state: waiting for completed aio requests (write thread)
I/O thread 121 state: waiting for completed aio requests (write thread)
I/O thread 122 state: waiting for completed aio requests (write thread)
I/O thread 123 state: waiting for completed aio requests (write thread)
I/O thread 124 state: waiting for completed aio requests (write thread)
I/O thread 125 state: waiting for completed aio requests (write thread)
I/O thread 126 state: waiting for completed aio requests (write thread)
I/O thread 127 state: waiting for completed aio requests (write thread)
I/O thread 128 state: waiting for completed aio requests (write thread)
I/O thread 129 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 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, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 288
4052394 OS file reads, 15494424 OS file writes, 150596 OS fsyncs
7 pending preads, 1 pending pwrites
11762.19 reads/s, 16383 avg bytes/read, 102309.72 writes/s, 751.69 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 611431, free list len 897503, seg size 1508935, 489086 merges
merged operations:
insert 2063297, delete mark 3103615, delete 74357
discarded operations:
insert 0, delete mark 15548, delete 585
Hash table size 5312557, node heap has 79 buffer(s)
Hash table size 5312557, node heap has 26 buffer(s)
Hash table size 5312557, node heap has 4 buffer(s)
Hash table size 5312557, node heap has 16 buffer(s)
Hash table size 5312557, node heap has 1 buffer(s)
Hash table size 5312557, node heap has 120 buffer(s)
Hash table size 5312557, node heap has 314 buffer(s)
Hash table size 5312557, node heap has 172 buffer(s)
20706.44 hash searches/s, 58791.52 non-hash searches/s
---
LOG
---
Log sequence number          129059336049118
Log buffer assigned up to    129059336049118
Log buffer completed up to   129059336049118
Log written up to            129059336048640
Log flushed up to            129059334355687
Added dirty pages up to      129059336049118
Pages flushed up to          129058875509481
Last checkpoint at           129058875509481
Checkpoint age target        478645248
Modified age no less than    456324017
Checkpoint age               460539637
Max checkpoint age           466429952
Number of logs               2
Log size                     268435456
Log total size               536870912
12537385 log i/o's done, 84453.50 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 21988638720
Dictionary memory allocated 31985123
Buffer pool size   1310560
Buffer pool size, bytes 21472215040
Free buffers       669
Database pages     1307611
Old database pages 482568
Modified db pages  443052
Pending reads      7
Pending writes: LRU 0, flush list 114, single page 0
Pages made young 431559, not young 8541221
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4042194, created 75781, written 2902065
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 977 / 1000, young-making rate 5 / 1000 not 79 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1307611, unzip_LRU len: 0
I/O sum[11079136]:cur[167272], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   163820
Buffer pool size, bytes 2684026880
Free buffers       96
Database pages     163451
Old database pages 60317
Modified db pages  60968
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 56927, not young 1085032
0.00 youngs/s, 0.00 non-youngs/s
Pages read 519587, created 9526, written 394676
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 988 / 1000, young-making rate 3 / 1000 not 40 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163451, unzip_LRU len: 0
I/O sum[1384892]:cur[20909], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   163820
Buffer pool size, bytes 2684026880
Free buffers       69
Database pages     163481
Old database pages 60335
Modified db pages  54664
Pending reads      1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 54587, not young 1084004
0.00 youngs/s, 0.00 non-youngs/s
Pages read 503067, created 9493, written 358417
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 969 / 1000, young-making rate 7 / 1000 not 109 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163481, unzip_LRU len: 0
I/O sum[1384892]:cur[20909], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   163820
Buffer pool size, bytes 2684026880
Free buffers       76
Database pages     163442
Old database pages 60325
Modified db pages  54227
Pending reads      2
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 52490, not young 997086
0.00 youngs/s, 0.00 non-youngs/s
Pages read 494737, created 9450, written 354751
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 967 / 1000, young-making rate 7 / 1000 not 108 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163442, unzip_LRU len: 0
I/O sum[1384892]:cur[20909], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   163820
Buffer pool size, bytes 2684026880
Free buffers       89
Database pages     163467
Old database pages 60327
Modified db pages  54915
Pending reads      3
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 53191, not young 1094096
0.00 youngs/s, 0.00 non-youngs/s
Pages read 504658, created 9479, written 360681
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 969 / 1000, young-making rate 7 / 1000 not 106 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163467, unzip_LRU len: 0
I/O sum[1384892]:cur[20909], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   163820
Buffer pool size, bytes 2684026880
Free buffers       78
Database pages     163436
Old database pages 60319
Modified db pages  54469
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 53312, not young 1049996
0.00 youngs/s, 0.00 non-youngs/s
Pages read 502069, created 9449, written 355921
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 970 / 1000, young-making rate 7 / 1000 not 104 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163436, unzip_LRU len: 0
I/O sum[1384892]:cur[20909], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   163820
Buffer pool size, bytes 2684026880
Free buffers       78
Database pages     163460
Old database pages 60319
Modified db pages  54837
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 54281, not young 1094541
0.00 youngs/s, 0.00 non-youngs/s
Pages read 505880, created 9473, written 359523
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 970 / 1000, young-making rate 6 / 1000 not 100 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163460, unzip_LRU len: 0
I/O sum[1384892]:cur[20909], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   163820
Buffer pool size, bytes 2684026880
Free buffers       88
Database pages     163437
Old database pages 60315
Modified db pages  53967
Pending reads      1
Pending writes: LRU 0, flush list 49, single page 0
Pages made young 53157, not young 1068089
0.00 youngs/s, 0.00 non-youngs/s
Pages read 502207, created 9514, written 356639
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 985 / 1000, young-making rate 3 / 1000 not 52 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163437, unzip_LRU len: 0
I/O sum[1384892]:cur[20909], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   163820
Buffer pool size, bytes 2684026880
Free buffers       95
Database pages     163437
Old database pages 60311
Modified db pages  55005
Pending reads      0
Pending writes: LRU 0, flush list 65, single page 0
Pages made young 53614, not young 1068377
0.00 youngs/s, 0.00 non-youngs/s
Pages read 509989, created 9397, written 361457
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 965 / 1000, young-making rate 7 / 1000 not 116 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163437, unzip_LRU len: 0
I/O sum[1384892]:cur[20909], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
13 RW transactions active inside InnoDB
Process ID=1, Main thread ID=139814690002688 , state=sleeping
Number of rows inserted 734594, updated 109391, deleted 221972, read 331363
6552.62 inserts/s, 508.54 updates/s, 2242.40 deletes/s, 2748.77 reads/s
Number of system rows inserted 11150, updated 2717183, deleted 5897, read 2908732
95.24 inserts/s, 16940.00 updates/s, 0.00 deletes/s, 16939.67 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


上面是SHOW ENGINE INNODB STATUS;的数据


mysql> SELECT
    ->     trx_id,
    ->     trx_state,
    ->     trx_started,
    ->     trx_wait_started,
    ->     trx_query,
    ->     trx_rows_locked,
    ->     trx_rows_modified
    -> FROM
    ->     INFORMATION_SCHEMA.INNODB_TRX
    -> ORDER BY
    ->     trx_started\G;
*************************** 1. row ***************************
           trx_id: 86726561816
        trx_state: RUNNING
      trx_started: 2024-09-14 16:40:09
trx_wait_started: NULL
        trx_query: NULL
  trx_rows_locked: 1
trx_rows_modified: 0
*************************** 2. row ***************************
           trx_id: 86729223032
        trx_state: RUNNING
      trx_started: 2024-09-14 16:41:22
trx_wait_started: NULL
        trx_query: NULL
  trx_rows_locked: 1
trx_rows_modified: 1
*************************** 3. row ***************************
           trx_id: 86729215922
        trx_state: RUNNING
      trx_started: 2024-09-14 16:41:22
trx_wait_started: NULL
        trx_query: NULL
  trx_rows_locked: 177
trx_rows_modified: 177
*************************** 4. row ***************************
           trx_id: 86729215777
        trx_state: RUNNING
      trx_started: 2024-09-14 16:41:22
trx_wait_started: NULL
        trx_query: NULL
  trx_rows_locked: 223
trx_rows_modified: 223
*************************** 5. row ***************************
           trx_id: 86729215764
        trx_state: RUNNING
      trx_started: 2024-09-14 16:41:22
trx_wait_started: NULL
        trx_query: NULL
  trx_rows_locked: 197
trx_rows_modified: 197
*************************** 6. row ***************************
           trx_id: 86729215536
        trx_state: RUNNING
      trx_started: 2024-09-14 16:41:22
trx_wait_started: NULL
        trx_query: NULL
  trx_rows_locked: 300
trx_rows_modified: 300
6 rows in set (0.00 sec)
yejr 2024-9-14 16:44:24
fengzhencai 发表于 2024-9-14 16:41
=====================================
2024-09-14 16:39:08 0x7f2ebbfff700 INNODB MONITOR OUTPUT
== ...

看来的确如此,之前应该执行过大事务,所以产生了大量的undo还没来得及被清理,结果中的 "History list length 6009890459" 已经表明了这个信息,先耐心等待清理吧,可以通过周期性的观察 "History list length" 数值变化,计算出每秒清理的速度,从而估算出预计什么时候能清理完毕。
yejr 2024-9-14 17:44:33
fengzhencai 发表于 2024-9-14 16:41
=====================================
2024-09-14 16:39:08 0x7f2ebbfff700 INNODB MONITOR OUTPUT
==== ...

还可以参考GreatSQL用户手册中的这部分内容:Undo Log(撤销日志) | GreatSQL用户手册
fengzhencai 2024-9-19 10:28:58
yejr 发表于 2024-9-14 17:44
还可以参考GreatSQL用户手册中的这部分内容:Undo Log(撤销日志) | GreatSQL用户手册 ...

等待了三天了,这个undo log文件还是没有回收,期间改动了SET GLOBAL innodb_purge_threads = 12; 由原来的默认4改为12,目前这个没有其他变化
1.5T        undo_001
4.6T        undo_002
405G        undo_003.ibu

mysql> SELECt  name, state FROM information_schema.INNODB_TABLESPACES WHERE SPACE_TYPE='Undo'\G;
*************************** 1. row ***************************
name: innodb_undo_001
state: inactive
*************************** 2. row ***************************
name: innodb_undo_002
state: active
*************************** 3. row ***************************
name: undo_003
state: active
3 rows in set (0.03 sec)


mysql> SELECT * FROM information_schema.innodb_metrics WHERE name LIKE 'trx_rseg_history_len'\G;
*************************** 1. row ***************************
           NAME: trx_rseg_history_len
      SUBSYSTEM: transaction
          COUNT: 7167342939
      MAX_COUNT: 7167342939
      MIN_COUNT: 6952713614
      AVG_COUNT: NULL
    COUNT_RESET: 7167342939
MAX_COUNT_RESET: 7167342939
MIN_COUNT_RESET: 6952713614
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2024-09-18 18:20:22
  TIME_DISABLED: NULL
   TIME_ELAPSED: 58033
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: value
        COMMENT: Length of the TRX_RSEG_HISTORY list
1 row in set (0.00 sec)

这里面的COUNT: 7167342939还是很大

问过GPT给出的回复是:
根据你提供的信息和你已经采取的步骤,我可以做以下分析和建议:

分析
你所采取的步骤是正确的:从你描述的步骤来看,你已经按照建议的操作创建了新的undo表空间,设置了旧表空间为inactive,并启用了undo log truncation。
innodb_purge_rseg_truncate_frequency 设置:你已经设置了innodb_purge_rseg_truncate_frequency,默认值为16,这意味着InnoDB每16次purge会尝试truncate undo log segment。如果你的系统有大量的事务,这个过程可能需要一些时间。
InnoDB状态监控:从提供的SHOW ENGINE INNODB STATUS输出来看,系统正在进行大量的I/O操作,undo log段的purge也是一个持续的背景任务。
建议步骤
确认配置生效:

确认所有配置已生效,特别是innodb_undo_log_truncate和innodb_purge_rseg_truncate_frequency,这些配置需要在MySQL重启后生效。
确保你当前的配置已经生效:SHOW VARIABLES LIKE 'innodb_undo_log_truncate'; 和 SHOW VARIABLES LIKE 'innodb_purge_rseg_truncate_frequency';
检查purge进度:

运行以下查询来检查当前purge操作进度:
SQL
SELECT * FROM information_schema.innodb_metrics WHERE name LIKE 'trx_rseg_history_len';
trx_rseg_history_len指标显示当前purge操作剩余的undo log记录数,如果这个值在逐步减少,那么purge操作正在进行中。
手动加速purge:

如果需要快速清理,可以暂时将innodb_purge_threads增加到一个更高的值来加速purge操作:
SQL
SET GLOBAL innodb_purge_threads = 4;  -- 或者更高
同时,可以将 innodb_max_purge_lag 设置为一个较小的值以提高purge频率:
SQL
SET GLOBAL innodb_max_purge_lag = 100000;
监控undo表空间状态:

使用以下查询来监控undo表空间状态变化:
SQL
SELECT tablespace_id, name, state FROM information_schema.INNODB_TABLESPACES WHERE SPACE_TYPE='Undo';
一旦表空间状态变为empty,就可以删除该表空间文件。
最终删除inactive的undo表空间:

当inactive的undo表空间状态变为empty后,可以使用以下命令删除该表空间文件:
SQL
DROP UNDO TABLESPACE innodb_undo_001;
DROP UNDO TABLESPACE innodb_undo_002;
继续等待还是采取进一步措施?
由于undo log truncation是一个持续的背景任务,如果你看到trx_rseg_history_len在逐步减少,那么系统正在正确地进行purge操作。可以继续等待一段时间,观察空间是否逐渐被释放。如果你的系统有大量的历史事务需要清理,这个过程可能需要一些时间。

如果在观察了一段时间后仍然没有显著改善,可以考虑手动加速purge操作或者联系MySQL技术支持以获取进一步帮助。

请尝试上述建议,并随时告知我结果或遇到的问题,以便进一步提供帮助。

按照上面做的没有效果。
yejr 2024-9-19 10:40:28
fengzhencai 发表于 2024-9-19 10:28
等待了三天了,这个undo log文件还是没有回收,期间改动了SET GLOBAL innodb_purge_threads = 12; 由原来 ...

请间隔1分钟左右,执行2-3次 show engine innodb status\G 查看下面这几个信息的变化情况


  1. Trx id counter 86724706924
  2. Purge done for trx's n < 74690025805 undo n < 0 state: running
  3. History list length 6009890459
复制代码
fengzhencai 2024-9-19 10:45:18
本帖最后由 fengzhencai 于 2024-9-19 10:48 编辑
yejr 发表于 2024-9-19 10:40
请间隔1分钟左右,执行2-3次 show engine innodb status\G 查看下面这几个信息的变化情况

...
  1. 10:42
  2. Purge done for trx's n:o < 75336947794 undo n:o < 0 state: running
  3. History list length 7170617944

  4. 10:43
  5. Purge done for trx's n:o < 75337103297 undo n:o < 0 state: running
  6. History list length 7170881878

  7. 10:45
  8. <blockquote>Purge done for trx's n:o < 75337250780 undo n:o < 0 state: running
复制代码
yejr 2024-9-19 10:48:54
yejr 发表于 2024-9-19 10:40
请间隔1分钟左右,执行2-3次 show engine innodb status\G 查看下面这几个信息的变化情况

...

还存在两种可能性是:

1. 当前数据库实例的事务负载较高,而undo purge的速度跟不上事务新产生的速度,所以永远也追赶不上,这种时候只能通过调整前端应用请求,降低事务数来缓解压力。

2. 因为等待被purge的undo数据量太大了,达到某个极限值触发了bug,导致purge工作停滞或者一直无法追上新事务产生的undo。

先补充提交上面提到的信息看看吧。
yejr 2024-9-19 10:53:12

看了下数据变化,这一分钟内,新增的事务比已被purge的事务数更小
75337103297 - 75336947794 = 155503
7170881878 - 7170617944 = 263934
263934(等待被purge数量) - 155503(新增事务数) = 108431

按照这个速度下去,purge是跟不上的,会导致undo log文件越来越大。
12下一页
fengzhencai

6

主题

0

博客

22

贡献

新手上路

Rank: 1

积分
38

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-10-7 07:49 , Processed in 0.025167 second(s), 22 queries , Redis On.
快速回复 返回顶部 返回列表