mysql致命错误：无法为缓冲区p分配内存

Question

我有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

Answer 1

解决方案不是空间太大，问题是Apache web服务器不是mysql，实际上需要减小innodb缓冲池的大小

mysql进程在一开始就使用这个缓冲区，因此当Apache需要更多资源时，内核将从服务中清除RAM，这意味着停止mysql而不是崩溃服务器。

如果不想更改为ngnx或httplight，还可以添加CRON来检查db状态并重新启动它。

Answer 2

TLDR

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

帮不上忙？如果这不是你的问题，更适合继续研究的问题是：

• mysqld service stops once a day on ec2 server
• https://askubuntu.com/questions/422037/optimising-mysql-settings-mysqld-running-out-of-memory

背景

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

日志显示的错误与OP发布的相同。我读取的错误（删除时间戳）是：

• 内存不足错误： 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

检查/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检查是否配置了一个。

total used free shared buffers cached Mem: 604340 587364 16976 0 29260 72280 -/+ buffers/cache: 485824 118516 Swap: 0 0 0

在上面的示例中，Swap:0表示没有swapfile。

关于设置一个的教程：

• https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-14-04
• https://help.ubuntu.com/community/SwapFaq

注意，越大未必越好！来自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)...

---

关于这里的其他答案

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

感谢：Ruben Schade comment

[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.

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

Answer 3

我发现这个答案增加了讨论：https://www.digitalocean.com/community/questions/mysql-server-keeps-stopping-unexpectedly?answer=26021

简而言之，除了将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>