mysql 致命错误：无法为缓冲池分配内存
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25965638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql Fatal error: cannot allocate memory for the buffer pool
提问by Hakim
I have this error log from MySQL, any idea? Website works for some time and then I get MySQL shutdown completely after a couple of hours.
我有来自 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
回答by ptim
TLDR;
TLDR；
Mysql can't restart because it's out of memory, check that you have an appropriate swapfile configured.
Mysql 无法重新启动，因为它的内存不足，请检查您是否配置了适当的交换文件。
Didn't help? If that's not your issue, more qualified questions to continue research are:
没有帮助？如果这不是您的问题，可以继续研究的更符合条件的问题是：
- mysqld service stops once a day on ec2 server
- https://askubuntu.com/questions/422037/optimising-mysql-settings-mysqld-running-out-of-memory
- mysqld 服务每天在 ec2 服务器上停止一次
- https://askubuntu.com/questions/422037/optimising-mysql-settings-mysqld-running-out-of-memory
Background
背景
I had exactly this problem on the very first system I set up on EC2, characterised by the wordpress site hosted there going down on occasion with "Error establishing database connection".
我在 EC2 上设置的第一个系统上就遇到了这个问题，其特点是托管在那里的 wordpress 站点有时会因“建立数据库连接时出错”而出现故障。
The logs showed the same error that the OP posted. My reading of the error (timestamps removed) is:
日志显示的错误与 OP 发布的错误相同。我对错误的解读（删除了时间戳）是：
- Out of memory error:
InnoDB: Fatal error: cannot allocate memory for the buffer pool - InnoDB can't start without enough memory
[ERROR] Plugin 'InnoDB' init function returned error. [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. [ERROR] Unknown/unsupported storage engine: InnoDB [ERROR] Aborting - mysqld is shutting down, which in this context, really means failing to restart!
[Note] /usr/sbin/mysqld: Shutdown complete
- 内存不足错误：
InnoDB: Fatal error: cannot allocate memory for the buffer pool - InnoDB 在没有足够内存的情况下无法启动
[ERROR] Plugin 'InnoDB' init function returned error. [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. [ERROR] Unknown/unsupported storage engine: InnoDB [ERROR] Aborting - mysqld 正在关闭，在这种情况下，这实际上意味着无法重新启动！
[Note] /usr/sbin/mysqld: Shutdown complete
Checking /var/log/syslogand searching for mysqlyields:
检查/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>
Note: you may have to gunzip and search through archived logs if the error occurred before the logs were rotated by cron.
注意：如果在 cron 轮换日志之前发生错误，您可能需要对存档日志进行压缩和搜索。
Solution
解决方案
In my case the underlying issue was that I'd neglected to configure a swapfile.
就我而言，潜在的问题是我忽略了配置交换文件。
You can check to see if you have one configured by running free -m.
您可以通过运行free -m.
total used free shared buffers cached
Mem: 604340 587364 16976 0 29260 72280
-/+ buffers/cache: 485824 118516
Swap: 0 0 0
total used free shared buffers cached
Mem: 604340 587364 16976 0 29260 72280
-/+ buffers/cache: 485824 118516
Swap: 0 0 0
In the example above, Swap: 0 indicates no swapfile.
在上面的例子中，Swap: 0 表示没有交换文件。
Tutorials on setting one up:
设置教程：
- https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-14-04
- https://help.ubuntu.com/community/SwapFaq
- https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-14-04
- https://help.ubuntu.com/community/SwapFaq
Note that bigger is not necessarily better!From the Ubuntu guide:
请注意，越大不一定越好！从Ubuntu 指南：
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 103 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)...
“收益递减”意味着如果您需要比 RAM 大小两倍更多的交换空间，您最好添加更多 RAM，因为硬盘驱动器 (HDD) 访问比 RAM 访问慢约 103，因此需要 1 秒，突然需要超过 15 分钟！在快速固态驱动器 (SSD) 上还有一分钟以上......
Regarding the other answers here...
关于这里的其他答案......
The InnoDB memory heap is disabled
The InnoDB memory heap is disabled
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
这并不是真正的错误，只是表明 InnoDB 正在使用系统的内部内存分配器而不是它自己的分配器。默认值为 yes/1，可用于生产。
根据文档，此命令已弃用，并将在 MySQL 5.6 以上版本中删除（我假设是 MariaDB）：
http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-use_sys_malloc.html
Thanks to: Ruben Schade comment
[Note] Plugin 'FEDERATED' is disabled.
[Note] Plugin 'FEDERATED' is disabled.
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.
有关 FEDERATED disabled 的消息不是错误。这只是意味着 FEDERATED 引擎对于您的 mysql 服务器没有打开。默认情况下不使用它。如果您不需要它，请不要关心此消息。
回答by nodws
The solution is NOT more space, Problem is Apache web server not mysql, actually you need to decrease innodb-buffer-pool-size
解决方案不是更多空间，问题是 Apache web 服务器不是 mysql，实际上你需要减少 innodb-buffer-pool-size
This buffer is used by the mysql process right off the start, so when Apache needs more resources the kernel will clear RAM from services this means stopping mysql instead of crashing the server.
该缓冲区由 mysql 进程一开始就使用，因此当 Apache 需要更多资源时，内核将从服务中清除 RAM，这意味着停止 mysql 而不是使服务器崩溃。
Would also add a CRON to check the db status and restart it if you dont want to change to ngnx or httplight.
如果您不想更改为 ngnx 或 httplight，还会添加一个 CRON 来检查数据库状态并重新启动它。
回答by Joe T
I found this answer adds to the discussion: https://www.digitalocean.com/community/questions/mysql-server-keeps-stopping-unexpectedly?answer=26021
我发现这个答案增加了讨论：https: //www.digitalocean.com/community/questions/mysql-server-keeps-stopping-unexpectedly?answer=26021
In short, on top of setting innodb_buffer_pool_size to something reasonable like 64M, you also may need to modify /etc/apache2/mods-enabled/mpm_prefork.conf to reduce the number of connections started by apache;
简而言之，除了将innodb_buffer_pool_size 设置为64M 之类的合理值之外，您可能还需要修改/etc/apache2/mods-enabled/mpm_prefork.conf 以减少apache 启动的连接数；
<IfModule mpm_prefork_module>
StartServers 3
MinSpareServers 3
MaxSpareServers 5
MaxRequestWorkers 25
MaxConnectionsPerChild 1024
</IfModule>
