MySQL占用了太多内存

Question

版本: mysql Ver 15.1远端10.3.9-MariaDB

操作系统: CentOS Linux 7.5.1804版(核心)

规格:384 RAM

背景：

我只将此服务器用于数据库(没有运行其他进程)。目前，我正在对此服务器进行大量插入(迁移)(可能只有几兆字节的数据)。

尽管将缓冲池设置为240 g，但顶部或空闲-m显示的总内存几乎接近我的物理内存总数(384 of )的95% (370GB+)。

然而，奇怪的是mysqld使用的总内存显示为240GB+ (通过top命令)。

为了进一步确认mysqld使用了370GB+内存，而不是一些隐藏进程，我通过重新启动服务MariaDB重新启动了mariadb。当它成功地重新启动时，我通过空闲-m验证了内存的使用情况，并且几乎看到了340GB+空闲(在available栏下)。

因此，我是否遗漏了配置文件中的某些设置，或者mysqld是如何从根本上工作的？或者也可能是内存泄漏，导致内存无法在mysqld中正确释放？

免费-超级大(当数据库已经运行了几个小时)

# 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 - 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
....
....

显示全局状态

-------------------------------------
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
============================

Answer 1

不要将max_connections设置得这么高。建议1000。

innodb_buffer_pool_instances = 16

如需进一步调查，请在SHOW GLOBAL STATUS;和SHOW VARIABLES;运行一天后提供。

你算过了吗？加载所有数据需要多长时间？它来得多快？(如果你需要的话，我有窍门。)