Version : mysql Ver 15.1 Distrib 10.3.9-MariaDB

OS : CentOS Linux release 7.5.1804 (Core)

Specification : 384GB RAM

Context :

I am using this server only for database (with no other processes running). Currently, I am doing mass insertions (migrating) into the this server (potentially few terabytes data).

Despite setting my buffer pool to only 240G, somehow the total used memory shown by top or free -m is almost close to 95% (370GB+) of my total physical RAM (384GB).

However, the strange thing is the total memory used by mysqld is shown as 240GB+ (via top command).

To further confirm that the 370GB+ RAM was used by mysqld and not some hidden processes, I restarted MariaDB via service mariadb restart. The moment it restarted successfully, I verified the memory usage via free -m and saw almost 340GB+ free (under available column).

Hence, am I missing out in some settings in my configuration file or is that how mysqld fundamentally work? Or could it also be there are memory leaks, which resulted in memory not being freed properly in mysqld?

free –mega (when database has been ran for several hours)

# free --mega
              total        used        free      shared  buff/cache   available
Mem:         386689      376532        8120           8        2036        7353
Swap:          4095        1189        2906

/etc/my.cnf

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#[client-server]
[mysqld]
datadir=/zfs2/db/mysql/
user=mysql
socket=/zfs2/db/mysql/mysql.sock
innodb_file_per_table=1
innodb_buffer_pool_size=240G
innodb_log_file_size=768M
max_allowed_packet=128M
max_connections=50000
innodb_thread_concurrency=48
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_use_native_aio=0
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
open_files_limit=50000
skip-name-resolve

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

/etc/my.cnf (END)

top command

top - 17:08:25 up 2 days,  3:32,  1 user,  load average: 5.21, 4.61, 4.77
Tasks: 387 total,   2 running, 385 sleeping,   0 stopped,   0 zombie
%Cpu(s):  6.8 us,  3.7 sy,  0.0 ni, 82.4 id,  7.0 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem : 39596972+total,  7963856 free, 38592006+used,  2085784 buff/cache
KiB Swap:  4194300 total,  2978664 free,  1215636 used.  7179308 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 1230 mysql     20   0  271.8g 258.4g  14248 S 188.9 68.4   2391:28 mysqld
10260 root      20   0  162248   2488   1528 R  16.7  0.0   0:00.04 top
....
....

SHOW GLOBAL STATUS

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 538573, free list len 270263, seg size 808837, 6384375 merges
merged operations:
 insert 228438676, delete mark 406242, delete 239439
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 63742607, node heap has 33386 buffer(s)
Hash table size 63742607, node heap has 0 buffer(s)
Hash table size 63742607, node heap has 106 buffer(s)
Hash table size 63742607, node heap has 552581 buffer(s)
Hash table size 63742607, node heap has 33558 buffer(s)
Hash table size 63742607, node heap has 1 buffer(s)
Hash table size 63742607, node heap has 81 buffer(s)
Hash table size 63742607, node heap has 499411 buffer(s)
38842.73 hash searches/s, 66931.80 non-hash searches/s
---
LOG
---
Log sequence number 6070350783605
Log flushed up to   6070347109837
Pages flushed up to 6069248723606
Last checkpoint at  6069205734998
0 pending log flushes, 0 pending chkp writes
1463927 log i/o's done, 20.87 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 263771389952
Dictionary memory allocated 5552286
Buffer pool size   15726960
Free buffers       7641
Database pages     14600195
Old database pages 5389362
Modified db pages  108091
Percent of dirty pages(LRU & free pages): 0.740
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7116675, not young 154182163
146.02 youngs/s, 751.54 non-youngs/s
Pages read 13002091, created 20524731, written 50122771
103.42 reads/s, 316.99 creates/s, 704.93 writes/s
Buffer pool hit rate 999 / 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: 14600195, unzip_LRU len: 0
I/O sum[289016]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   1966080
Free buffers       978
Database pages     1824943
Old database pages 673640
Modified db pages  15664
Percent of dirty pages(LRU & free pages): 0.858
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886318, not young 21558561
19.48 youngs/s, 59.30 non-youngs/s
Pages read 1627657, created 2567248, written 7270228
12.76 reads/s, 36.39 creates/s, 112.55 writes/s
Buffer pool hit rate 999 / 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: 1824943, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   1965840
Free buffers       989
Database pages     1825185
Old database pages 673729
Modified db pages  12978
Percent of dirty pages(LRU & free pages): 0.711
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888506, not young 17872488
18.83 youngs/s, 87.37 non-youngs/s
Pages read 1620960, created 2551959, written 6091321
13.00 reads/s, 40.28 creates/s, 91.33 writes/s
Buffer pool hit rate 999 / 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: 1825185, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   1965840
Free buffers       979
Database pages     1824947
Old database pages 673642
Modified db pages  13895
Percent of dirty pages(LRU & free pages): 0.761
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886797, not young 18128418
17.33 youngs/s, 151.48 non-youngs/s
Pages read 1618497, created 2564253, written 6108565
12.46 reads/s, 36.33 creates/s, 98.29 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824947, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   1965840
Free buffers       999
Database pages     1825047
Old database pages 673678
Modified db pages  12517
Percent of dirty pages(LRU & free pages): 0.685
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888765, not young 20029901
17.59 youngs/s, 59.42 non-youngs/s
Pages read 1628730, created 2569645, written 6152662
13.06 reads/s, 42.35 creates/s, 76.28 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825047, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   1965840
Free buffers       980
Database pages     1824878
Old database pages 673616
Modified db pages  13641
Percent of dirty pages(LRU & free pages): 0.747
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 878757, not young 17787806
18.41 youngs/s, 104.46 non-youngs/s
Pages read 1613388, created 2567887, written 6120053
13.30 reads/s, 46.02 creates/s, 86.76 writes/s
Buffer pool hit rate 999 / 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: 1824878, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   1965840
Free buffers       877
Database pages     1825227
Old database pages 673745
Modified db pages  13217
Percent of dirty pages(LRU & free pages): 0.724
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 899041, not young 21347971
17.78 youngs/s, 104.68 non-youngs/s
Pages read 1640617, created 2569267, written 6121197
13.65 reads/s, 39.11 creates/s, 70.81 writes/s
Buffer pool hit rate 999 / 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: 1825227, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   1965840
Free buffers       921
Database pages     1825177
Old database pages 673726
Modified db pages  13437
Percent of dirty pages(LRU & free pages): 0.736
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 895011, not young 19236578
17.28 youngs/s, 95.22 non-youngs/s
Pages read 1630603, created 2572247, written 6148992
11.98 reads/s, 47.04 creates/s, 86.09 writes/s
Buffer pool hit rate 999 / 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: 1825177, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   1965840
Free buffers       918
Database pages     1824791
Old database pages 673586
Modified db pages  12742
Percent of dirty pages(LRU & free pages): 0.698
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 893480, not young 18220440
19.31 youngs/s, 89.61 non-youngs/s
Pages read 1621639, created 2562225, written 6109753
13.22 reads/s, 29.48 creates/s, 82.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824791, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1230, Main thread ID=139849981024000, state: making checkpoint
Number of rows inserted 1244250277, updated 5901, deleted 187006, read 1627791678
17677.60 inserts/s, 0.13 updates/s, 0.00 deletes/s, 10865.63 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================