MySQL内存溢出问题:故障排除指南

在本文中，我将向您展示如何使用新版本的MySQL(5.7+)，以及如何更容易地解决 MySQL内存分配中出现的问题。

故障排除从来都不是一项有趣的任务，尤其是像这种MySQL因为内存不足而崩溃的故障。Peter Zaitsev在2012年写了一篇博客文章：用许多有用的技巧解决MySQL内存使用问题。

有了新版本的MySQL(5.7+)和performance_schema，一切都不同了，我们可以更轻松地对MySQL内存分配进行故障排除。

在本文中，我将向您展示如何使用它。

首先，MySQL由于内存不足而崩溃的主要情况有3种：

MySQL试图分配比可用内存更多的内存，因为用户在设置中设定的值过高。例如：您没有正确设置innodb_buffer_pool_size，这种问题很容易修复。

服务器上运行有其他进程在分配RAM。例如：它可以是某种应用程序(Java、Python、PHP)、web服务器，甚至是备份(即mysqldump)等。当问题的根源被确定后，就可以直接修复了。

MySQL中的内存泄漏。这是最坏的情况，我们才需要进行故障排除。

从哪里开始对MySQL内存泄漏进行故障排除

下面是我们可以从下面步骤开始（(假设它是一个Linux服务器)）：

第1部分:Linux操作系统和配置检查

1. 通过检查MySQL错误日志和Linux日志文件(例如/var/log/messages或/var/log/syslog)来确定mysql崩溃的原因。比如：你可能会看到一个日志条目说OOM程序杀死了MySQL进程。每当MySQL进程被OOM“dmesg”杀死时，日志中也会显示相关的周围环境细节信息。

2. 检查可用的内存数量:

• free -g
• cat /proc/meminfo

3. avCheck检查哪些应用程序正在使用RAM：“top”或“htop”(参见常驻内存与虚拟内存)。

4. 检查MySQL配置:检查/etc/ MySQL .cnf或一般的/etc/my*(包括/etc/mysql/*和其他文件)。

MySQL可能使用不同的my.cnf运行(运行ps ax| grep MySQL)

5.运行vmstat 5，查看系统是否通过虚拟内存进行读写，以及是否进行交换

6. 对于非生产环境，我们可以使用其他工具(如Valgrind、gdb等)来检查MySQL的使用情况

第2部分:检查MySQL内部

现在，我们可以检查MySQL内部的内容，以查找潜在的MySQL内存泄漏。

MySQL在很多地方分配内存，特别是：

• 表缓存（Table cache）
• Performance_schema (运行命令：show engine performance ce_schema status，并查看最后一行，这可能是系统导致RAM减小(即1G或更少)的原因）
• InnoDB (运行show engine InnoDB状态并检查缓冲池部分，为buffer_pool和相关缓存分配的内存)
• 内存中的临时表Temporary tables in RAM (执行查询 select * from information_schema.tables where engine='MEMORY'，来查找内存中的所有临时表)
• 准备好特定的SQL语句，在它没有被释放时（通过deallocate命令检查准备好的命令的数量，运行显示全局变量的语句：'Com_prepare_sql';show global status like 'Com_dealloc_sql'）。

好消息是，从MySQL 5.7开始，在performance_schema中有内存分配。以下是我们如何使用它:

1、首先，我们需要启用收集内存指标。

运行：

UPDATE setup_instruments SET ENABLED = 'YES'WHERE NAME LIKE 'memory/%';

2、从sys模式运行报告：

select event_name, current_alloc, high_allocfrom sys.memory_global_by_current_byteswhere current_count > 0;

3、通常，这将在分配内存时为您提供代码中的位置。它通常是自解释的。在某些情况下，我们可以搜索bug，或者需要检查MySQL源代码。

例如，对于在触发器中过度分配内存的bug (https://bugs.mysql.com/bug.php?id=86821)， select会显示：

内存的最大块通常是缓冲池，但是存储过程中的3G似乎太高了。

根据MySQL源代码文档，sp_head表示存储程序的一个实例，它可以是任何类型(存储过程、函数、触发器、事件)。在上面的例子中，我们有一个潜在的内存泄漏。

此外，我们还可以得到每一个高级事件的总体报告：

我希望这些简单的步骤可以帮助解决由于内存不足而导致的MySQL崩溃，任何问题可在评论区留言。