mysql占用内存过多

一、计算mysql所需的内存

https://www.cnblogs.com/cheyunhua/p/9045057.html 理论有待学习

https://www.cnblogs.com/simplelogic/archive/2012/12/06/2804798.html

mysql used mem = key_buffer_size + query_cache_size + tmp_table_size
+ innodb_buffer_pool_size + innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections * (
read_buffer_size + read_rnd_buffer_size
+ sort_buffer_size+ join_buffer_size
+ binlog_cache_size + thread_stack
)
在mysql 中输入如下命令，可自动计算自己的当前配置最大的内存消耗
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW VARIABLES LIKE ‘innodb_additional_mem_pool_size’;
SHOW VARIABLES LIKE ‘innodb_log_buffer_size’;
SHOW VARIABLES LIKE ‘thread_stack’;
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;

SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack
) ) / @giga_bytes AS MAX_MEMORY_GB;
在mysql 中输入如下命令，可显示各占内存参数大小
SHOW VARIABLES LIKE ‘key_buffer_size’;
SHOW VARIABLES LIKE ‘query_cache_size’;
SHOW VARIABLES LIKE ‘tmp_table_size’;
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW VARIABLES LIKE ‘innodb_additional_mem_pool_size’;
SHOW VARIABLES LIKE ‘innodb_log_buffer_size’;
SHOW VARIABLES LIKE ‘read_buffer_size’;
SHOW VARIABLES LIKE ‘read_rnd_buffer_size’;
SHOW VARIABLES LIKE ‘sort_buffer_size’;
SHOW VARIABLES LIKE ‘join_buffer_size’;
SHOW VARIABLES LIKE ‘binlog_cache_size’;
SHOW VARIABLES LIKE ‘thread_stack’;

二、查看innodb缓冲池实际使用内存

通过将缓冲池中可用的数据与InnoDB页面（InnoDB缓冲池单位）大小相乘，可以计算InnoDB缓冲池此时正在使用的实际内存。从MySQL 5.7.6开始，GLOBAL_STATUS表中提供的信息从Performance Schema获取。

set @ibpdata = (select variable_value from performance_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');
select @ibpdata;
set @idbpgsize = (select variable_value from performance_schema.global_status where variable_name = 'innodb_page_size');
select @idbpgsize;
set @ibpsize = @ibpdata * @idbpgsize / (1024*1024*1024);
select @ibpsize;

分配36G，实际使用34.5G，占比95%

由top命令可知mysql数据库占服务器内存93%，即

数据库内存：48*0.93=44.64GB

数据库内存明细：34.55G+32+128+1600*4+200+3200=44.74GB

三、查看数据库连接数情况

Max_connections：整个服务器的用户限制，即mysql上限连接数，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存

Max_user_connections: 限制每个用户的session连接个数，例如max_user_connections=1 ，那么用户u1只能连接的session数为1，如果还有用户u2，还是可以连接，但是连接数仍然为1，如果数据库只有一个用户，那Max_user_connections自然等于Max_connections。

Max_used_connections：mysql历史响应最大连接数

Max_connect_errors：默认10，每个主机的连接请求异常中断的最大次数

Max_connections_used_rate：最大连接数使用率，历史最大连接数占上限连接数的85%左右，如果发现比例在10%以下，证明MySQL服务器连接数上限设置的过高了

show variables like '%connect%';
show global status like 'Max_used_connections';
show global status like 'Threads_connected';
--最大连接数使用率(建议85%)
Max_used_connections/max_connections
--当前连接数使用率
Threads_connected/max_connections

从最大连接数使用率可知max_connections设置的过高了

四、优化数据库连接数配置

优化如下：

max_connections=350
max_connect_errors=50
max_user_connections=300

这里只是从连接数角度来做优化，实际场景需结合当前正在运行的sql做分析，例如可能有条sql正在做全扫，占了内存池很大空间，可以同时从show full processlist和sys.memory_global_by_current_bytes来进一步观察，这里也是需要考虑的一个点。

五、一个实际案例

1、linux系统内存消耗主要有三个地方：

进程
slab
pagecacge

用 free 命令查看到的是系统整体的内容使用情况，而使用 ps 和 top 看到的内存使用情况都是以进程维度来看的，因此看不到 slabcache 和pagecache 的内存占用信息。

2、判断应用程序是否有内存泄露问题，只根据进程的内存使用或机器的内存变化来判定都不太准确，如果单凭进程的内存变化可能会疏忽一些小对象的内存泄露问题。

同时对于机器的内存的使用也要做是否合理的判断。对于不同语言的应用都有相应的神器可以辅助定位内存泄露问题，同时结合linux内存的监控工具进行分析，除了 top，free还有 pmap，/proc/meminfo 和 /slabinfo，slaptop等。

3、此外 page cache，dentries和inodes cache，系统是会自动回收的。

可以通过以下几种方式加速其回收，不过实际并不需要这么做。

手工清除内存缓存

echo 1 > /proc/sys/vm/drop_caches 清除page cache
echo 2 > /proc/sys/vm/drop_caches 清除denries和inodes
echo 3 > /proc/sys/vm/drop_caches 清除page cache ，dentries及inodes
调整 vm.vfs_cache_pressure 值大小，默认是100。值越大，dentries和inodes cache的回收速度会越快
调整 vm.min_free_kbytes 值大小，该值为系统开始内存回收的阀值，越大表示系统会越早开始回收(一般情况下此值不建议调整)。

用 ps 大概统计下所有程序占用的总内存

$ echo `ps aux |awk '{mem += $6} END {print mem/1024/1024}'` GB

0.595089

结果显示所有进程占用的内存还不到 1G，实际上，因为free, ps的统计方式的差别和 Copy-on-write 和 Shared libraries等内存优化机制的存在，这两者的统计结果通常是不一样的。但是一般情况下绝对不会相差十几个G，肯定是有什么隐藏的问题，free没有专门统计另一项缓存: Slab。

Slab Allocation 是Kernel 2.2之后引入的一个内存管理机制，专门用于缓存内核的数据对象，可以理解为一个内核专用的对象池，可以提高系统性能并减少内存碎片。(Kernel 2.6.23之后，SLUB成为了默认的allocator)

查看Slab缓存

$ cat /proc/meminfo

# 其中，Slab相关的数据为
Slab: 154212 kB
SReclaimable: 87980 kB
SUnreclaim: 66232 kB

SReclaimable(Linux 2.6.19+) 都是 clean 的缓存，随时可以释放。回到之前的内存问题，

查看服务器上Slab占用的内存：

法一

$ cat /proc/meminfo|grep Slab
Slab: 12777668 kB

法二

echo `cat /proc/meminfo|grep Slab|awk '{mem += $2} END {print mem/1024/1024}'` GB
12G

12G的Slab缓存，有意思的是free把Slab缓存统计到了 usedmemory 中，这就是之前那个问题的症结所在了。另外，还可以查看 /proc/slabinfo (或使用 slabtop 命令)来查看Slab缓存的具体使用情况。结果发现，ext3_inode_cache 和 dentry_cache 占用了绝大部分内存。考虑到这台服务器会频繁地用 rsync 同步大量的文件，这个结果也并不意外。

解决问题

如果问题仅仅是Slab占用了太多的内存(SReclaimable)，那么通常不需要太操心，因为这根本不是个问题(如果是SUnreclaim太多且不断增长，那么很有可能是内核有bug)。但是，如果是因为Slab占用内存太多而引起了其他的问题，建议继续往下阅读

清除Slab可回收缓存

通过 /proc/sys/vm/drop_caches 这个配置项，可以手动清除指定的可回收缓存(SReclaimable)

echo 2 > /proc/sys/vm/drop_caches 或者 sysctl vm.drop_caches=2

上面的命令会主动释放 Slab 中 clean的缓存(包括inode和dentry的缓存)，然后再 free -g 一下，空闲的内存陡增了十几个G

注意的是，手动清除缓存可能会在一段时间内降低系统性能。原则上不推荐这么做，因为如果有需要，系统会自动释放出内存供其他程序使用。另外，手动清除Slab缓存是一个治标不治本的办法。因为问题不在Slab，实际操作的时候发现，清除缓存一段时间后，Slab缓存很快又会反弹回去。

如果需要治本，要么搞定问题进程，要么修改系统配置，要么增加物理内存。

调整系统 vm 配置

风险预警: 调整以下系统配置可能会对系统性能造成负面影响，请仔细测试并谨慎操作

/etc/sysctl.conf 里有以下几个对内存管理影响比较大的内核参数配置：

vm.vfs_cache_pressure

系统在进行内存回收时，会先回收page cache, inode cache, dentry cache和swap cache。vfs_cache_pressure 越大，每次回收时，inode cache和dentry cache所占比例越大。

vfs_cache_pressure

默认是100，该值越大inode cache和dentry cache的回收速度会越快，越小则回收越慢，为0的时候完全不回收，内存溢出(OOM!)。

vm.min_free_kbytes

系统的"保留内存"的大小，"保留内存"用于低内存状态下的"atomic memory allocation requests"(eg. kmalloc + GFP_ATOMIC)，该参数也被用于计算开始内存回收的阀值，默认在开机的时候根据当前的内存计算所得，越大则表示系统会越早开始内存回收。vm.min_free_kbytes 过大可能会导致 OOM，太小可能会导致系统出现死锁等问题。查看默认设置：cat /proc/sys/vm/min_free_kbytes

vm.swappiness

该配置用于控制系统将内存 swap out 到交换空间的积极性，取值范围是[0, 100]。vm.swappiness 越大，系统的交换积极性越高，默认是60，如果为0则不会进行交换。

$ vim /etc/sysctl.conf
vm.swappiness = 1

查看默认设置：cat /proc/sys/vm/swappiness

drop_caches

默认值为0，将此值设置为1，2或3，使内核删除页面缓存和slab缓存的各种组合。

1 系统使所有的页面缓冲存储器失效并释放。
2 系统释放所有未使用的slab缓存内存。
3 系统释放所有的页面缓存和slab缓存内存。

查看默认设置：cat /proc/sys/vm/drop_caches

注：以上参数设置是非破坏性的操作，由于脏数据不能被释放，建议设置以上参数的值之前运行sync把内存数据写入硬盘。在生产环境中不建议使用drop_caches释放内存。

https://mp.weixin.qq.com/s/EJj3v7XaQzuuZ6LinIKaww 学习案例

https://www.toutiao.com/i6866731337642934792/?tt_from=weixin&utm_campaign=client_share&wxshare_count=1&timestamp=1601656777&app=news_article&utm_source=weixin&utm_medium=toutiao_android&use_new_style=1&req_id=202010030039370100260771991E5BF5A8&group_id=6866731337642934792 一次生产环境MySQL服务器cpu飙升800%优化案例