我正在使用 Mysql - 服务器版本：8.0.29 MySQL 社区服务器 - GPL 我的服务器有 100GB RAM。其中我将 innodb_buffer_pool_size 分配为 90 GB SET GLOBAL innodb_buffer_pool_size = 96636764160;
我正在运行一个加载脚本 (shell)，该脚本基本上将所有数据 (.sql.gz) 从不同的 mysql 转储文件加载到此服务器。该脚本和此 MYsql 在同一台服务器上运行。
转储文件将仅包含常见的 SQL 语句，如插入、删除、更新。首先，它们加载到暂存表中，然后通过添加时间戳和一些常量来表示数据来自哪个源，然后将它们加载到 DWH。所以它的 SQL 并不那么复杂。
该脚本运行了几个月都没问题..现在我发现 RAM 已被完全占用并停止了脚本。
下面是我的 htop 结果
我不明白为什么 mysql 占用的空间远远超过 innodb_buffer_pool_size。我应该如何正确配置这个值？
我可以进行什么配置来解决此简单交易的高 RAM 使用率问题吗？
请帮我提出建议。
附件是我的“显示服务器状态”结果。
=====================================
2023-06-01 16:16:34 140408454989376 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4632 srv_active, 0 srv_shutdown, 9184 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 71285
OS WAIT ARRAY INFO: signal count 39709
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 70217250
Purge done for trx's n:o < 70217232 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421885061902336, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421885061899104, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421885061901528, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421885061900720, 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: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 86
1585066 OS file reads, 28106287 OS file writes, 5683280 OS fsyncs
0.03 reads/s, 16384 avg bytes/read, 4137.73 writes/s, 740.60 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 42, seg size 44, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 15937451, node heap has 46 buffer(s)
Hash table size 15937451, node heap has 5 buffer(s)
Hash table size 15937451, node heap has 4 buffer(s)
Hash table size 15937451, node heap has 5 buffer(s)
Hash table size 15937451, node heap has 4 buffer(s)
Hash table size 15937451, node heap has 6 buffer(s)
Hash table size 15937451, node heap has 39 buffer(s)
Hash table size 15937451, node heap has 6 buffer(s)
56330.99 hash searches/s, 7874.63 non-hash searches/s
---
LOG
---
Log sequence number 3898324786933
Log buffer assigned up to 3898324786933
Log buffer completed up to 3898324786933
Log written up to 3898324786933
Log flushed up to 3898324786933
Added dirty pages up to 3898324786933
Pages flushed up to 3897708734717
Last checkpoint at 3897680986253
18250356 log i/o's done, 3569.36 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 1279769
Buffer pool size 5897717
Free buffers 33984
Database pages 5863618
Old database pages 2164337
Modified db pages 36810
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 48283, not young 115104
5.12 youngs/s, 95.16 non-youngs/s
Pages read 1528658, created 6518562, written 7786359
0.03 reads/s, 1298.00 creates/s, 391.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: 5863618, unzip_LRU len: 0
I/O sum[120144]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 737214
Free buffers 4281
Database pages 732916
Old database pages 270529
Modified db pages 4568
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3867, not young 15433
0.00 youngs/s, 1.01 non-youngs/s
Pages read 191072, created 814914, written 973111
0.00 reads/s, 161.28 creates/s, 49.06 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: 732916, unzip_LRU len: 0
I/O sum[15018]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 737216
Free buffers 4109
Database pages 733092
Old database pages 270594
Modified db pages 4735
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3412, not young 11783
0.00 youngs/s, 5.72 non-youngs/s
Pages read 191115, created 815921, written 976986
0.00 reads/s, 164.64 creates/s, 54.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: 733092, unzip_LRU len: 0
I/O sum[15018]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 737213
Free buffers 4276
Database pages 732928
Old database pages 270533
Modified db pages 4591
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3912, not young 14953
0.00 youngs/s, 1.01 non-youngs/s
Pages read 191605, created 814102, written 972291
0.00 reads/s, 161.96 creates/s, 46.72 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: 732928, unzip_LRU len: 0
I/O sum[15018]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 737217
Free buffers 4326
Database pages 732878
Old database pages 270515
Modified db pages 4550
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14842, not young 13833
0.05 youngs/s, 3.17 non-youngs/s
Pages read 191090, created 814874, written 974985
0.00 reads/s, 161.44 creates/s, 47.74 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: 732878, unzip_LRU len: 0
I/O sum[15018]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 737214
Free buffers 4354
Database pages 732841
Old database pages 270501
Modified db pages 4510
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5900, not young 15491
0.00 youngs/s, 1.51 non-youngs/s
Pages read 190640, created 815524, written 972041
0.00 reads/s, 160.32 creates/s, 46.54 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: 732841, unzip_LRU len: 0
I/O sum[15018]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 737216
Free buffers 4173
Database pages 733030
Old database pages 270571
Modified db pages 4562
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4865, not young 15885
5.07 youngs/s, 78.91 non-youngs/s
Pages read 191194, created 814720, written 971904
0.00 reads/s, 162.32 creates/s, 48.57 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 733030, unzip_LRU len: 0
I/O sum[15018]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 737215
Free buffers 4226
Database pages 732978
Old database pages 270551
Modified db pages 4578
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3958, not young 14734
0.00 youngs/s, 1.01 non-youngs/s
Pages read 191074, created 814517, written 970882
0.00 reads/s, 162.14 creates/s, 47.58 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: 732978, unzip_LRU len: 0
I/O sum[15018]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 737212
Free buffers 4239
Database pages 732955
Old database pages 270543
Modified db pages 4716
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7527, not young 12992
0.00 youngs/s, 2.81 non-youngs/s
Pages read 190868, created 813990, written 974159
0.03 reads/s, 163.91 creates/s, 50.96 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: 732955, unzip_LRU len: 0
I/O sum[15018]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=611, Main thread ID=140387823040064 , state=sleeping
Number of rows inserted 72092333, updated 30, deleted 0, read 35616682
20227.99 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 28121, updated 98397, deleted 26260, read 337468
10.82 inserts/s, 38.39 updates/s, 22.10 deletes/s, 146.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
答案1
内存和交换空间不足。降低内存，innodb_buffer_pool_size直到内存有余量，并且基本上没有交换空间在使用。
70％ 的 RAM 通常是一个安全值innodb_buffer_pool_size；而您有 90％。
buffer_pool 是一个“缓存”，因此降低它可能不会对性能产生太大影响。
buffer_pool_size的动态变化：
可以使用 SET 语句动态设置 innodb_buffer_pool_size 配置选项，这样您就可以调整缓冲池的大小而无需重新启动服务器。例如：
mysql> 设置全局 innodb_buffer_pool_size=402653184;
注意缓冲池大小必须等于或等于 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。更改这些变量设置需要重新启动服务器。
阅读此文；它有更多信息：