mysql fatal error : cannot allocate memory for the buffer pool

coder 2023-06-10 原文

我有这个来自 MySQL 的错误日志，你知道吗？网站工作了一段时间，然后我在几个小时后完全关闭了 MySQL。

140919 10:48:27 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140919 10:48:27 [Note] Plugin 'FEDERATED' is disabled.
140919 10:48:27 InnoDB: The InnoDB memory heap is disabled
140919 10:48:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140919 10:48:27 InnoDB: Compressed tables use zlib 1.2.3.4
140919 10:48:28 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
140919 10:48:28 InnoDB: Completed initialization of buffer pool
140919 10:48:28 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140919 10:48:28 [ERROR] Plugin 'InnoDB' init function returned error.
140919 10:48:28 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140919 10:48:28 [ERROR] Unknown/unsupported storage engine: InnoDB
140919 10:48:28 [ERROR] Aborting

140919 10:48:28 [Note] /usr/sbin/mysqld: Shutdown complete

140919 10:48:28 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140919 10:48:28 [Note] Plugin 'FEDERATED' is disabled.
140919 10:48:28 InnoDB: The InnoDB memory heap is disabled
140919 10:48:28 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140919 10:48:28 InnoDB: Compressed tables use zlib 1.2.3.4
140919 10:48:28 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
140919 10:48:28 InnoDB: Completed initialization of buffer pool
140919 10:48:28 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140919 10:48:28 [ERROR] Plugin 'InnoDB' init function returned error.
140919 10:48:28 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140919 10:48:28 [ERROR] Unknown/unsupported storage engine: InnoDB
140919 10:48:28 [ERROR] Aborting

140919 10:48:28 [Note] /usr/sbin/mysqld: Shutdown complete

最佳答案

TLDR;

由于内存不足，Mysql 无法重新启动，请检查您是否配置了适当的交换文件。

没有帮助？如果这不是您的问题，继续研究的更多合格问题是:

背景

我在 EC2 上设置的第一个系统就遇到了这个问题，其特点是托管在那里的 wordpress 站点有时会出现“建立数据库连接时出错”。

日志显示了与 OP 发布的相同的错误。我对错误的阅读(删除了时间戳)是:

内存不足错误: InnoDB: fatal error :无法为缓冲池分配内存
InnoDB 无法在没有足够内存的情况下启动 [错误] 插件“InnoDB”初始化函数返回错误。 [错误] 插件“InnoDB”注册为存储引擎失败。 [错误] 未知/不支持的存储引擎:InnoDB [错误] 中止
mysqld 正在关闭，在这种情况下，这实际上意味着无法重新启动! [注意]/usr/sbin/mysqld:关机完成

检查 /var/log/syslog 并搜索 mysql 产生:

Out of memory: Kill process 15452 (mysqld) score 93 or sacrifice child
Killed process 15452 (mysqld) total-vm:888672kB, anon-rss:56252kB, file-rss:0kB
init: mysql main process (15452) killed by KILL signal
init: mysql main process ended, respawning
type=1400 audit(1443812767.391:30): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=21984 comm="apparmor_parser"
init: mysql main process (21996) terminated with status 1
init: mysql main process ended, respawning
init: mysql post-start process (21997) terminated with status 1
<repeated>

注意:如果错误发生在 cron 轮换日志之前，您可能需要压缩并搜索归档日志。

解决方案

就我而言，根本问题是我忽略了配置交换文件。

你可以通过运行free -m来检查你是否配置了一个。

缓存的已用空闲共享缓冲区总数电话:604340 587364 16976 0 29260 72280 -/+ 缓冲区/缓存:485824 118516 交换:0 0 0

在上面的例子中，Swap: 0 表示没有交换文件。

设置教程:

注意，越大不一定越好!来自Ubuntu guide :

The "diminishing returns" means that if you need more swap space than twice your RAM size, you'd better add more RAM as Hard Disk Drive (HDD) access is about 10³ slower then RAM access, so something that would take 1 second, suddenly takes more then 15 minutes! And still more then a minute on a fast Solid State Drive (SSD)...

关于这里的其他答案...

`InnoDB 内存堆被禁用`

This isn’t really an error, just an indication that InnoDB is using the system’s internal memory allocator instead of its own. The default is yes/1, and is acceptable for production.

According to the docs, this command is deprecated, and will be removed in MySQL versions above 5.6 (and I assume MariaDB):

http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-use_sys_malloc.html

感谢:Ruben Schade comment

`[Note] 插件“FEDERATED”已禁用。`

The message about FEDERATED disabled is not an error. It just meant that the FEDERATED engine its not ON for your mysql server. It's not used by default. If you don't need it, don't care about this message.

见:https://stackoverflow.com/a/16470822/2586761

关于mysql fatal error : cannot allocate memory for the buffer pool，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/25965638/