syslog 向内存中缓存,mysql致命错误：无法为缓冲池分配内存

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.

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 can't restart because it's out of memory, check that you have an appropriate swapfile configured.

Didn't help? If that's not your issue, more qualified questions to continue research are:

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

The logs showed the same error that the OP posted. My reading of the error (timestamps removed) is:

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

Checking /var/log/syslog and searching for mysql yields:

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

Note: you may have to gunzip and search through archived logs if the error occurred before the logs were rotated by 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.

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.

Tutorials on setting one up:

Note that bigger is not necessarily better! From the 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)...

Regarding the other answers here...

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):

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