mysql Fatal error: cannot allocate memory for the buffer pool
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:
- mysqld service stops once a day on ec2 server
- 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".
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
<repeated>
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:
- 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:
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):
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.
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.
See: https://stackoverflow.com/a/16470822/2586761
I found this answer adds to the discussion: 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;
<IfModule mpm_prefork_module>
StartServers 3
MinSpareServers 3
MaxSpareServers 5
MaxRequestWorkers 25
MaxConnectionsPerChild 1024
</IfModule>
The solution is NOT more space, Problem is Apache web server not mysql, actually you need to decrease 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.
Would also add a CRON to check the db status and restart it if you dont want to change to ngnx or httplight.