MySQL数据库服务器内存占用过高多次死机问题排查以及解决——by罗群

问题现象
发现一台单节点MySQL数据库服务器每隔一段时间就卡死，服务器无法ssh远程访问，数据库无法连接。
解决的思路和措施
阶段一：
通过和机房网管沟通，查看服务器运行调度发现是内存占用过高。增加虚拟机内存，从16g逐渐扩大到70g内存。
从一开始的一周死机一次，变成间隔一个月左右死机一次。同样是内存过高，每次死机重启后正常一个月到两个月左右。期间内存仍然是波动很高，未根本解决问题。

阶段二：
从系统层面解决问题，使用内存{echo 1 > /proc/sys/vm/drop_caches}，这个操作通常用于在某些情况下强制释放缓存，它只是清理了缓存，并没有解决导致内存过高的根本原因。如果内存过高是由于某个应用程序存在内存泄漏或者系统负载持续过高导致不断申请内存等原因引起的，那么在一段时间后内存可能又会再次升高。期间内存仍然是波动很高，未根本解决问题。

阶段三：
定位问题
编写简单shell脚本，监控服务器cpu、内存等运行情况。

监控日志脚本运行情况：发现系统在12:00以后开始内存占用高升，且不稳定，系统负载占用高。

问题原因：发现系统设置了数据库备份的定时任务，从11点开始运行，且在12:30~14:00左右在备份最大的数据库，备份完毕后服务器对文件进行压缩。且每次内存高涨都是在此时间段，基本可以确定是数据库备份导致的内存长时间高占用，系统负载高问题。

优化问题
1、导出备份语句优化
为MySQL dump备份加上quick参数，使mysqldump在导出数据时不将整个结果集缓存到内存中，而是逐行读取和写入，减少内存占用。
不加quick参数的运行，会有以下不同：
一、备份方式
全表读取：
不加--quick参数时，mysqldump在备份表的过程中会先将整个表的数据读取到内存中，然后再进行处理和输出。这意味着对于大型表，如果内存不足以容纳整个表的数据，可能会导致大量的磁盘交换（swap）操作，从而降低备份速度并增加系统负载。
例如，对于一个包含数百万行数据的大型表，在没有--quick参数的情况下，mysqldump可能会一次性尝试将所有数据读入内存，这可能会导致内存耗尽，系统开始使用磁盘作为虚拟内存，从而使备份过程变得非常缓慢。
二、对服务器性能的影响
资源占用：
由于不加--quick参数会全表读取到内存，这会占用更多的服务器内存资源。如果同时有其他数据库操作在进行，可能会影响数据库服务器的整体性能，导致其他查询和事务的响应时间变长。
例如，在备份过程中，如果有用户正在进行实时的业务操作，可能会感受到明显的延迟，因为备份操作占用了大量的内存资源，影响了数据库的正常处理能力。
I/O操作：
全表读取可能会导致更多的磁盘I/O操作，因为数据需要从磁盘读取到内存中。这不仅会增加备份的时间，还可能对磁盘的寿命产生一定的影响。
例如，频繁的磁盘读取可能会使磁盘的机械部件（如磁头）更加频繁地移动，从而加速磁盘的磨损。

2、数据库性能提升
服务器硬件内存从16G扩容到70G后，MySQL可使用内存没有调整，调整最重要参数innodb_buffer_pool_size，从原来的5G提升到40G。
innodb_buffer_pool_size调整推荐：
作用：InnoDB存储引擎的缓冲池大小，用于缓存表数据、索引等信息。这个缓冲池越大，能够缓存的数据就越多，减少磁盘I/O操作，从而提高性能。
调整建议：如果服务器内存足够大，可以将这个参数设置为服务器内存的50%-70%左右。例如，对于一个有16GB内存的服务器，可以尝试将innodb_buffer_pool_size设置为8GB-11GB。
修改前：

修改后：

修改后运行情况
修改后，MySQL占用内存稳定在40G左右，MySQL可用硬件资源较高。

数据库备份时间段来看，系统备份时间段系统运行稳定，内存和cpu波动属于正常区间。