I am using Mysql - Server version: 8.0.29 MySQL Community Server - GPL
My server has 100GB RAM.
Of it I allocated innodb_buffer_pool_size as 90 GB
SET GLOBAL innodb_buffer_pool_size = 96636764160;
I am running a load script (shell) that basically load all data (.sql.gz) from different mysql dump files into this server. The script and this MYsql run on same server.
The dump files will have common sql statements like Insert, delete, update only.
At first they load into a staging table and then they get loaded to DWH by adding timestamps and some constants to represent which source the data is from. so its NOT so complicated SQLs..
The script ran fine for some months.. Now i see the RAM is getting occupied fully and halting the script.
Below is my htop result
I could not understand why mysql is occupying much more than innodb_buffer_pool_size..
How should I properly configure this value ?
And is there any configuration that i can do to fix the High RAM usage for this simple transactions ?
Please help me with your suggestions.
Attached is my "SHOW SERVER STATUS" result.
=====================================
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
============================
