當我增加 max_heap_table_size 值時 MySQL 崩潰

Question 1

有了 120GB 內存，您的機器似乎還可能擁有多個物理 CPU 和非統一內存訪問 (NUMA) 架構……如果是這樣的話，您可能擁有大量可用內存，但仍然與直覺相反，可用內存不足。

如果到目前為止我進展順利，那麼當 MySQL 嘗試增加分配給表的內存時，您可能會遇到這個問題MEMORY，並且遇到與Linux 在此體系結構上運行時處理內存分配的方式相關的錯誤- 僅透過考慮到直接連接到某個特定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/

當然，我正在猜測這是否適用於您的系統，但這似乎是值得的可能性。當我嘗試配置 64GB InnoDB 緩衝池並且 MySQL 無法在機器上找到 64GB 可用空間時，我在新的 128GB 機器上遇到問題時，我正是使用了此修復程序沒有什麼否則運行。當我意識到我可以成功使用的最高值小於 16 核心機器（具有 4 個處理器）的總記憶體的 1/4 時，身體的處理器......有點像你似乎遇到的......是當我將各個部分放在一起以了解問題的本質時。

Answer

有了 120GB 內存，您的機器似乎還可能擁有多個物理 CPU 和非統一內存訪問 (NUMA) 架構……如果是這樣的話，您可能擁有大量可用內存，但仍然與直覺相反，可用內存不足。

如果到目前為止我進展順利，那麼當 MySQL 嘗試增加分配給表的內存時，您可能會遇到這個問題MEMORY，並且遇到與Linux 在此體系結構上運行時處理內存分配的方式相關的錯誤- 僅透過考慮到直接連接到某個特定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/

當然，我正在猜測這是否適用於您的系統，但這似乎是值得的可能性。當我嘗試配置 64GB InnoDB 緩衝池並且 MySQL 無法在機器上找到 64GB 可用空間時，我在新的 128GB 機器上遇到問題時，我正是使用了此修復程序沒有什麼否則運行。當我意識到我可以成功使用的最高值小於 16 核心機器（具有 4 個處理器）的總記憶體的 1/4 時，身體的處理器......有點像你似乎遇到的......是當我將各個部分放在一起以了解問題的本質時。

Question 2

的文檔tmp_表大小表示此設定是臨時表可以達到的最大大小在記憶中。超過該大小不會觸發任何表已滿錯誤；它觸發從使用記憶體中的表到使用磁碟上的 MyISAM 表的變更。

系統正在崩潰，因為您允許臨時表使用比應有的更多的記憶體。與內存表相同。

您應該刪除 tmp_table_size方式向下。請記住，此設定不是總體最大值。這是每個臨時表的最大值。如果您有 5 個查詢產生一個巨大的臨時表（例如每個查詢不到 20GB），那麼您現在的臨時表會佔用 100GB 的 RAM。再加上第六個，您使用的 RAM 就比伺服器的總記憶體還要多。

如果您的腳本實際上使用 MEMORY 儲存引擎，那麼在您應該考慮更改之後將資料寫入 MyISAM 檔案。如果確實需要在這麼大的臨時表上實現如此快速的效能，那麼您應該考慮獲得更快的儲存（例如，融合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