Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql Fatal error: cannot allocate memory for the buffer pool

Tags:

mysql

innodb

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 
like image 546
Hakim Avatar asked Sep 22 '14 01:09

Hakim


People also ask

What is buffer pool in MySQL?

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

What should be the size of InnoDB buffer pool?

The innodb_buffer_pool_size system variable defines the buffer pool size. Typically, a recommended innodb_buffer_pool_size value is 50 to 75 percent of system memory. innodb_buffer_pool_size can be configured dynamically, while the server is running.

What is buffer size in MySQL?

The log buffer is the memory area that holds data to be written to the log files on disk. Log buffer size is defined by the innodb_log_buffer_size variable. The default size is 16MB. The contents of the log buffer are periodically flushed to disk.


1 Answers

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

like image 179
ptim Avatar answered Oct 01 '22 10:10

ptim