'Mysql InnoDB memory leak
I have an installation of MySQL Ver 8.0.26 for Linux on x86_64 which allocates a lot more memory than I expected. I'd like to understand where it has gone to fix the root cause.
The Linux server has a total of 370GB total RAM but it's not a database server only it's where the applications are also run from. My database size right now is about 5GB with a total of less than 25 tables.
After trying to locate the memory leak I stumbled upon the Mysql Open tables which seems to be correlating with the increased memory usage. (Graph shown below is over 5 days time using netdata).
Memory usage over time:
Open tables number growing over time.
Here are the changes I have done in the my.cnf file
[mysqld]
max_connections = 512
innodb_log_file_size = 1024M
innodb_buffer_pool_size = 16G
innodb_print_all_deadlocks = 1
wait_timeout = 31536000
interactive_timeout = 31536000
innodb_autoinc_lock_mode=2
max_allowed_packet=512M
default_storage_engine = InnoDB
After running the show engine innodb status; here is what I get.
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 9626
877 OS file reads, 109038826 OS file writes, 78122941 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 238.31 writes/s, 181.31 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 4425293, node heap has 1237 buffer(s)
Hash table size 4425293, node heap has 3907 buffer(s)
Hash table size 4425293, node heap has 18904 buffer(s)
Hash table size 4425293, node heap has 10211 buffer(s)
Hash table size 4425293, node heap has 135 buffer(s)
Hash table size 4425293, node heap has 2833 buffer(s)
Hash table size 4425293, node heap has 1450 buffer(s)
Hash table size 4425293, node heap has 469 buffer(s)
3181.09 hash searches/s, 6282.52 non-hash searches/s
---
LOG
---
Log sequence number 48666558274
Log buffer assigned up to 48666558274
Log buffer completed up to 48666558274
Log written up to 48666558274
Log flushed up to 48666558274
Added dirty pages up to 48666558274
Pages flushed up to 48496882991
Last checkpoint at 48496882991
98154440 log i/o's done, 222.11 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 17541103616
Dictionary memory allocated 702518
Buffer pool size 1048480
Free buffers 482537
Database pages 526797
Old database pages 194298
Modified db pages 24379
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2328320, not young 21676
0.00 youngs/s, 0.00 non-youngs/s
Pages read 854, created 539313, written 7072261
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: 526797, unzip_LRU len: 0
I/O sum[3200]:cur[64], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 131058
Free buffers 60244
Database pages 65964
Old database pages 24329
Modified db pages 3277
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 957873, not young 10993
0.00 youngs/s, 0.00 non-youngs/s
Pages read 175, created 71085, written 1399422
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: 65964, unzip_LRU len: 0
I/O sum[400]:cur[8], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 131060
Free buffers 60410
Database pages 65754
Old database pages 24252
Modified db pages 2831
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 787777, not young 8988
0.00 youngs/s, 0.00 non-youngs/s
Pages read 130, created 69632, written 1222975
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: 65754, unzip_LRU len: 0
I/O sum[400]:cur[8], unzip sum[0]:cur[0]
.. and so forth for the rest of the buffers
My question of course is, how can I fix the root cause and is this the cause of it?
Edit 1:
In order to verify whether it's MySQL or a some setting that has to do with the memory leak I was thinking of stopping and re-starting the service of MySQL but without stopping any other application, which should free all the memory it has allocated and should show right away if I am looking in the wrong direction or not, correct?
Edit 2:
# top -o %MEM -n 1 | head -n 15
top - 11:04:29 up 78 days, 20:49, 1 user, load average: 11.16, 11.51, 12.71
Tasks: 925 total, 4 running, 921 sleeping, 0 stopped, 0 zombie
%Cpu(s): 66.1 us, 1.4 sy, 0.0 ni, 32.0 id, 0.0 wa, 0.4 hi, 0.1 si, 0.0 st
MiB Mem : 386566.5 total, 143783.1 free, 186723.6 used, 56059.8 buff/cache
MiB Swap: 4091.0 total, 4091.0 free, 0.0 used. 196546.7 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
43237 mysql 20 0 37.0g 11.3g 38336 S 29.4 3.0 1585:24 mysqld
1235657 root 20 0 4392096 3.4g 63936 S 5.9 0.9 366:28.82 node
1395899 root 20 0 2105132 1.1g 62748 S 0.0 0.3 103:38.66 node
1485407 root 20 0 2092800 1.1g 63400 S 0.0 0.3 169:23.37 node
1537596 root 20 0 2012632 1.0g 62328 S 0.0 0.3 134:55.01 node
1950191 root 20 0 1971424 1.0g 59572 S 0.0 0.3 69:58.87 node
1009156 root 20 0 1947612 985.4m 62700 S 0.0 0.3 67:36.20 node
1048076 root 20 0 1897160 991924 63304 S 5.9 0.3 77:52.66 node
# free
total used free shared buff/cache available
Mem: 395844132 191200164 147219260 1552 57424708 201268652
Swap: 4189180 0 4189180
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|