当我增加 max_heap_table_size 值时，MySQL 崩溃

Question 1

有了 120GB 的内存，您的机器似乎也可能具有多个物理 CPU 和非统一内存访问 (NUMA) 架构......如果是这种情况，您可能有足够的可用内存，但与直觉相反，仍然没有足够的可用内存。

如果我到目前为止都没有出错的话，那么当 MySQL 尝试增加分配给表的内存时，您可能会遇到此问题MEMORY，并且会遇到与 Linux 在这种架构上运行时处理内存分配的方式相关的错误 - 只考虑直接连接到一个特定 CPU 的内存条上的可用内存，而这个 CPU 被任意选择为可从中处理内存请求的“那个”CPU - 尽管主板上其他地方还有其他物理内存是空闲的。

NUMA 在理论上是好的，但可能并不适合需要大量内存的单个进程，就像 MySQL 的情况一样......但有一个解决方法。

修复方法是修改mysqld_safe脚本，添加以下行：

cmd="/usr/bin/numactl --interleave all $cmd"

...立即地后这条线……

cmd="$NOHUP_NICENESS"

这篇优秀的文章解释了其中的原因，它最初是为了解决为什么这种架构的 MySQL 服务器尽管有可用内存，却仍大量进行交换而写的；然而，这是一个更大问题的征兆，作者随后指出“这不完全是交换问题”，即使禁用交换，也可能包括“内存分配失败”。

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

当然，我只是在猜测这是否适用于你的系统，但这似乎是一种值得尝试的可能性。当我在一台新的 128GB 机器上遇到问题时，我使用了这个修复方法，当时我试图配置一个 64GB 的 InnoDB 缓冲池，而 MySQL 无法在一台有没有什么否则正在运行。当我意识到我能成功使用的最高值小于 16 核机器总内存的 1/4 时，其中有 4身体的处理器......有点像你遇到的情况......当我把各个部分放在一起时，就会发现问题的本质。

Answer

有了 120GB 的内存，您的机器似乎也可能具有多个物理 CPU 和非统一内存访问 (NUMA) 架构......如果是这种情况，您可能有足够的可用内存，但与直觉相反，仍然没有足够的可用内存。

如果我到目前为止都没有出错的话，那么当 MySQL 尝试增加分配给表的内存时，您可能会遇到此问题MEMORY，并且会遇到与 Linux 在这种架构上运行时处理内存分配的方式相关的错误 - 只考虑直接连接到一个特定 CPU 的内存条上的可用内存，而这个 CPU 被任意选择为可从中处理内存请求的“那个”CPU - 尽管主板上其他地方还有其他物理内存是空闲的。

NUMA 在理论上是好的，但可能并不适合需要大量内存的单个进程，就像 MySQL 的情况一样......但有一个解决方法。

修复方法是修改mysqld_safe脚本，添加以下行：

cmd="/usr/bin/numactl --interleave all $cmd"

...立即地后这条线……

cmd="$NOHUP_NICENESS"

这篇优秀的文章解释了其中的原因，它最初是为了解决为什么这种架构的 MySQL 服务器尽管有可用内存，却仍大量进行交换而写的；然而，这是一个更大问题的征兆，作者随后指出“这不完全是交换问题”，即使禁用交换，也可能包括“内存分配失败”。

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

当然，我只是在猜测这是否适用于你的系统，但这似乎是一种值得尝试的可能性。当我在一台新的 128GB 机器上遇到问题时，我使用了这个修复方法，当时我试图配置一个 64GB 的 InnoDB 缓冲池，而 MySQL 无法在一台有没有什么否则正在运行。当我意识到我能成功使用的最高值小于 16 核机器总内存的 1/4 时，其中有 4身体的处理器......有点像你遇到的情况......当我把各个部分放在一起时，就会发现问题的本质。

Question 2

文档临时表大小表示此设置是临时表的最大大小在记忆中。超过该大小不会触发任何表已满错误；它会触发从使用内存中的表到使用磁盘上的 MyISAM 表的更改。

系统崩溃是因为你允许临时表占用比应有的更多的内存。内存表也是一样。

你应该删除 tmp_table_size方式减少。请记住，此设置不是总体最大值。它是每个临时表的最大值。如果您有 5 个查询生成一个非常大的临时表（例如每个查询不到 20GB），则现在临时表占用了 100GB 的 RAM。加上第 6 个，您使用的 RAM 已经超过了服务器的总 RAM。

如果您的脚本实际上使用的是 MEMORY 存储引擎，那么在将数据写入 MyISAM 文件之后，您应该考虑进行更改。如果真的需要在如此大的临时表上实现如此快的性能，您应该考虑获取更快的存储（例如。Fusion-io，维里登特等）。如果这些太过分或者价格太贵，我至少会考虑消费级 SSD。

当 MyISAM 的数据缓存到系统内存（未使用的 RAM）中时，其性能最佳。如果您的脚本每次执行大型查询（使用 MEMORY 引擎）时都会耗尽缓存，则 MyISAM 性能将受到影响。

我怀疑该脚本正在使用 MEMORY 存储引擎来“提高”性能，但实际上它正在终止它。如果它是一个临时表，它应该使用临时表，并且您的 tmp_table_size 应该小得多，在它破坏 tmp_table_size 后强制将其写入磁盘。

max_heap_table_size 和 tmp_table_size 的默认值均为 16MB。如果可能，我建议将设置改回默认值。在监控资源使用情况（磁盘 i/o、总内存使用情况、CPU 使用情况等）的同时，以小幅度向上调整，直到找到适合您的数据集的设置。

Answer