MySQL「Out of memory (Needed 16777224 bytes)」錯誤完整排查與解決實戰

錯誤現象

應用報錯：Out of memory (Needed 16777224 bytes)，約 16 MB 記憶體分配失敗。

初步嘗試與參數調整

依據官方文檔，重點調整以下記憶體相關參數（單位：bytes）：

修改 my.cnf 後重啟驗證：

mysql> SHOW VARIABLES LIKE 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 67108864 |
+---------------------+----------+

深入根因：預處理語句未釋放

查閱官方 Bug #31898 及文檔 Out of Memory，發現核心原因在於：

• 大量使用 Prepared Statement（預處理語句）
• 結果集極大
• 執行後未執行 DEALLOCATE PREPARE 或將用戶變量置空

問題代碼模式

PREPARE stmt FROM @sql;
EXECUTE stmt;
-- 缺少清理步驟，導致記憶體洩漏

修正代碼模式

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql = '';  -- 釋放用戶變量佔用的記憶體

綜合優化措施

1. 修正所有存儲過程：確保每個 PREPARE 都有對應的 DEALLOCATE PREPARE。
2. 優化查詢結構：將隱式內連接改為顯式 LEFT JOIN，配合 EXPLAIN 建立必要索引。
3. 升級版本：MySQL 5.0.45 修復了已知記憶體洩漏 Bug（#31898）。
4. 監控指標：關注 Prepared_stmt_count、Qcache_free_memory、Created_tmp_disk_tables。

驗證結果

修正部署後持續觀察一週，錯誤完全消失，記憶體佔用穩定在合理區間。